How Do I Debug Slow Nhibernate Select Query?

I'm doing a simple query on the database, a search on two columns. I have an index on the columns. When I do the search in SQL Server Management Studio, it takes only a few millise

Solution 1:

To start with, post the exact query shown by the profiler and the query you run in SSMS. No offense, but what looks 'fine' to you may reveal a wealth of information to a trained eye. Second, post the exact schema of your tables, including all the indexes.

An example of possible problem could be nvarchar coercion due to datatype precedence (a search predicate with an nvarchar @variable on an varchar index will results into a full scan).

As for the more general question, how does one approach such an issue, the answer is: apply a method like Waits and Queues. All the information you need is available in various DMVs like sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_db_index_usage_stats. The execution plan also reveals a lot about what s going on, but is significantly more difficult to interpret correctly the information in the execution plan.

Solution 2:

As well as Remus' answer (which addresses the idiotic treatment of (n)varchar by nHibernate), I'd add the column BadData to the INCLUDE clause too

Solution 3:

How are you creating your query, with HQL or the criteria framework?

I have had the same situation. Running the SQL spit out by Nhibernate profiler was lightening quick, yet the app was running very slow. Switching from the criteria framework to HQL fixed the issue.

I think there is a bug/feature of the criteria framework that makes it really slow in some circumstances.