Is the same SQL query slower from an NHibernate application than SQL Studio?

Question

Our application issues an SQL query generated by NHibernate. At runtime, the application takes about 12 seconds to work with the SQL Server database. SQL Profiler shows over 500,000 views.

However, if I commit the query text using SQL Profiler and run it again from SQL Studio, it takes 5 seconds and displays less than 4600 records.

The query uses several parameters, the values ​​of which are given at the end of the SQL text, and I read a little about sniffing parameters and inefficient query plans, but I thought that this was related to stored procedures. Perhaps NHibernate maintains open results when it creates instances of its entities, which can explain the longer duration, but what can explain the additional 494,000 “reads” for the same query as NHibernate? (No additional queries are displayed in the SQL Profiler trace.)

The query is defined as a LINQ query using the NHibernate 3.1 LINQ tool. I did not include the query itself, because it seems like a basic philosophical question: what can explain such a dramatic difference?

In case this is appropriate, the varbinary (max) column is also present in the results, but in our situation it always contains null.

Any insight is greatly appreciated!

Answer 1

Be sure to read: http://www.sommarskog.se/query-plan-mysteries.html

The same rules apply for procs and sp_executesql. A huge reason for untidy plans can be passed in the nvarchar parameter for the varchar field, this causes index scans, not searches.

I very much doubt that the output affects perf here, it will probably be a problem with one of the parameters sent or the selectivity of the base tables.

When testing the output from the profiler, be sure to include sp_executesql and make sure that your settings match (for example, SET ARITHABORT ), otherwise you will create a new plan.

You can always dig a crappy plan from the execution cache via sys.dm_exec_query_stats