NHibernate select grows exponentially slow

Question

My problem is that NHibernate gets exponentially slow when fetching records from the database. I had a request to basically pull all the data from a very large database to be used in a report.

I figured, well since I can't get all the records in one shot because the recordset is so large, i thought try breaking it up. Basically I'm iterating through ranges of an index, ie. records id x to y, then y+1 to z, and so forth.

Each result set is about 10megs. The first 20 or so pulls takes less than a minute each, then on the next pull, it takes 10minutes, then 30minutes, and 1hr. I stopped the program there, didn't want to wait till the next pull will come. I ran the program again starting from the index where I left off, again, the first 20 or so pulls are really quick, then for some odd reason there is a major slowdown.

Any help would be greatly appreciated.

Answer 1

There are several ways to increase the performance of NHiberante

• Second level cache
• Eager loading to prevent select n+1
• Several ways to squeeze the performance out of the mapping
• Modifying database indexes

When it takes that long as you describe, I don't think any things describe above will work good enough for you.

NHibernate is not designed for large datasets. NHibernate is useful for most of the queries used in an application, especially for queries that have not much more results than presented to the user on one screen. If you want to use larger datasets in some parts of your application, every small performance penalty paid by any tool you use can become significant when the dataset increases. The maximum performance can (unfortunately) only be reached by plain sql.

Answer 2

NHibernate is doing a lot of work for you, and that might be slowing it down. It keeps objects you've pulled from in the session, which is basically a first level cache. When you run queries, it will check that set of objects to see if they need to be flushed back to the database (maybe you've changed them so that they now qualify as the result of the query!). So one strong possibility is that your query is secretly walking through a huge number of objects trying to figure out what it should write to the database before it ever sends your query to the database.

If this is the case, you can try a periodic session.Clear() or session.Evict(object) which removes objects from the cache, so that NH won't try to make the database consistent with them. You can always reattach objects later if you want NH to "pay attention" to them.

Answer 3

A very likely cause is missing indexes. Suggest you post your query, table definition and current indexes on those tables.

For reporting, you may need to create a stored procedure (possibly even an indexed view if heavy aggregation is involved), and wrap this with NHibernate.

I would also fire up SQL Profiler to see what queries are being executed.

Of interest?: Optimizing Performance in NHibernate

Answer 4

As an option you can try to use StatelessSession. It won't track all pulled objects and won't slow down your application