Developing a web application (ASP.Net MVC, NHibernate, SQL Server) I needed to implement a couple of HTML reports.
I designed a database view, mapped it in NH, and retrieved the data using Query<>. Worked nice on dev, but on prod the page to 15 seconds to fetch and render 1000 records.
My first thought was that the view (1 CROSS JOIN, some LEFT JOINS) was the culprit, but running it from SSMS returned the complete result set immediately.
Searching for “NHibernate”, “performance” and the usual keywords I finally found the solution: StatelessSession.
The NH SessionFactory provides two kinds of sessions:
With OpenSession(), all objects created from database retrieval are managed in NHibernate’s caching system, whereas OpenStatelessSession() does not cache the objects. Since we do not need to manipulate the data objects when generating reports, the stateless session should be first choice for reports.
After implementing the changes, I immediately hit the next problem:
If you retrieve an object that is detached from the NH session, because it had been retrieved using a stateless session, you cannot access the lazy-loaded properties.
Well, in fact you can, but you need to Select() all required columns, including the lazy-loaded columns, in the lambda
statelessSession.Query<MyTable>
.Select(t => new { t.column1, t.lazyloadedcolumn1, ... })
Finally, with the stateless session and the fixed query in place, a request is completed in under 1 second for 1000 records! 😉
Sources:
devioblog 