Nhibernate query is way slower than SSMS query

Question

When executing a query in linq, my nhibernate debug log shows me it uses a parameter @p0 to represent a value in the WHERE clause in the generated SQL. This takes about 1 minute.

When I run the same query (a simple select with WHERE Campaign = 500) in SQL Server Management Studio, this query takes 1 second.

When I DECLARE @p0 int & SET @p0 = 500 and then use WHERE Campaign = @p0, the query takes 1 minute as well...

Is there a way to make nHibernate "translate" the @p0 into 500 before it gets executed on my database?

Answer 1

Not to my knowledge - NHibernate will use parameterized queries and this is usually a good thing. The difficulty is that occasionally the database engine will generate a query execution plan that is good for some possible values of @p0 and bad for others.

I recommend you check your indexing, statistics, and look into problems with cached query plans. Depending on the exact circumstances, the exact solution may vary. Perhaps some index can only be used if the value is known beforehand (can you then alter the index to make it more general). Or maybe you just need to rebuild them? There are also ways to ask SQL server not to use a cached query plan - it will then be recalculated with the known parameter value for every execution.