NHibernate takes a long time to run a request

Question

This is done using Fluent NHibernate.

I have a NHibernate search that retrieves data from a single table. If I take the generated sql and run it through the query analyzer, it will take ~ 18 ms to complete.

Using NHProfiler, I get the duration of this query as ~ 1800 ms - 100 times longer than sql!

Query duration - Database only:1800ms - Total: 1806ms 

The object that is populating contains a child class, but this child is loaded from NHibernate's second-level cache

The data that is returned is uploaded (50 per request), although, as far as I can tell, this should not make any difference.

I also have a counter, and again it takes ~ 4 ms in the query analyzer and ~ 1800 ms according to NHProfiler.

Is NH Profiler showing query execution time or total time to retrieve, map classes, and graph objects? And if this is the first - why does it take much longer than directly executing the request?

EDIT: I just found this Ayende post about the Request Duration value set in NH Profiler: http://ayende.com/Blog/archive/2009/06/28/nh-prof-query-duration.aspx - so this is definitely a request database that takes a lot of time

Answer 1

Finally I managed to search for the problem.

The primary key for the object is varchar in the database. NHibernate converted the value to nvarchar when it executed the request. Unfortunately, this was not obvious when viewing the generated sql in NH Profiler. Slowdown caused by sql converting nvarchar back to varchar

I specified a mapping to use a custom type

 map.Id(x => x.Id).CustomType("AnsiString"); 

and the problem is resolved.

Greetings to all people help :)

Answer 2

these problems are usually resolved online between you and your database. QA usually connects directly to the database, and all that needs to be sent is raw data, where it is formatted. Your application will probably convert your result set into a data set or similar construct. To prove this, change some code (not the entire data layer) to use SQL Data Reader to read your data. Just read all the records without trying to parse all the columns and save the data. It will most likely run as fast as your network allows.