-1

I am currently using NHibernate to read a block of 50k rows. It takes almost 20 minutes. Data which is to be read is prepared by joining more than 5 tables in SQLSERVER 2005. My main concern is NHibernate prepares a new query for each row retrieval, which is quite time consuming.

I know it's necessary as NHibernate has to map each row to an object, but this approach is useless where reading is main concern.

I cannot use Stateless sesstion as I am using collections to store data

I also need to apply some filter through parameters. I have also implemented Stored Procedure, but somehow it didn't help.

Is there any other way to handle large collection of data.





------Edit-------
Here are my two config file which I was using to map

    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="myNameSpace" assembly="myNameSpace">
    <class name="MyClass" table="TableNameFromDb">
        <id name="ID" column="ID">
            <generator class="native" />
        </id>
        <property name="prop1"/>
        <property name="prop2"/>

    <bag name="bag1" cascade="all-delete-orphan" inverse="false"  fetch="join" lazy="false">
            <key column="prop1"/>
            <one-to-many class="MyClass2" />
    </bag>
    </class>
</hibernate-mapping>    

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="myNameSpace" assembly="myNameSpace">
    <class name="MyClass2" table="TableNameFromDb2">
        <id name="prop2" column="ID">
            <generator class="native" />
        </id>
        <property name="prop2"/>
        <property name="prop3"/>

    </class>
</hibernate-mapping>




Now the main concern here is NHibernate is creating a new query for each row and sending to the database which is an overhead.

Chaturvedi Dewashish
  • 1,469
  • 2
  • 15
  • 39
  • 4
    show the mapping and the code, you are probably facing the Select N+1 problem – Felice Pollano Jul 24 '12 at 12:27
  • We still need to see your queries, but if you are joining 5 tables as "lookup" values, it can help to query them before your main query. I.e. `Dim manufacturers as IList(of Mfg) = session.QueryOver(of Mfg).List` and then query your cars `Dim cars as IList(of Car) = session.QueryOver(of Car).List`. NHibernate will then pull the Mfg's from the cache instead of joining them in the query. – Origin Jul 24 '12 at 12:56
  • I have included config file please check it. Thanks. – Chaturvedi Dewashish Jul 25 '12 at 08:04
  • Is there a reason why you use a bag instead of a set ? Also, have you looked at the SQL statements that NHibernate generates ? – Frederik Gheysels Jul 25 '12 at 15:07

2 Answers2

1

First of all, I think you'll have to verify what's causing the performance issue.

As Felice Pollano already mentionned, it could be that you're facing a select n+1 issue. This can possibly be solved by modifying your mapping, or, by specifying the FetchMode that must be used in the Criteria that you're using to retrieve the data.

Another thing that you should contemplate: is it really necessary to retrieve all those rows in one go? What are you trying to do? Do you really need all the data that's represented by your entities, or can you use a projection which only loads those columns that you're interested in?
Can you use Paging?
Perhaps you can use a MultiCriteria...

However, in order to solve this issue, I think more input from you is needed.

Community
  • 1
  • 1
Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
0

The problem could be due to the reason; by default, NHibernate does not fully qualify table names within its queries sent to SQL Server. To leverage sp_execsql to its fullest, requests must include fully qualified table names.

To fully qualify NHibernate queries, in app/web.config file, add the following key in NHibernate settings:

<add value="my_DB_name.dbo" key="hibernate.default_schema" />

(replace dbo as necessary.)

Try it and tell us if it gives you any performance boost?

vulcan raven
  • 32,612
  • 11
  • 57
  • 93
  • 1
    The OP said its taking 20 minutes on 50k tuples. If the NHibernate is taking time in retrieving and mapping data, then probably its qualifying table name at every step of the way. The suggested setting in NHibernate preemptively qualify queries. Moreover, if the OP provides actual code/further information, then we can further comment on "how exactly". For now, this web.config setting is worth a try and its a general rule to consider while dealing with NHibernate (which Entity Framework handles natively). – vulcan raven Jul 24 '12 at 12:58