8

In our SQL Server 2005 database (tested using Management Studio with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS), the following statement is fast (~0.2s compile time, ~0.1s execution time):

SELECT ... FROM ... WHERE a = 1 AND b = '' ...

The following statement, however, is slow (~0.2s compile time, 7-11s execution time):

exec sp_executesql N'SELECT ... FROM ... WHERE a = @a AND b = @b ...', N'@a int, @b nvarchar(4000), ...', @a=1, @b=N'', ...

SQL Server chooses a different execution plan, although the queries are equal. This makes sense, since, in the first case, SQL Server has the actual values of a, b and all the other parameters available and can use the statistics to create a better plan. Apparently, the query plan for the concrete values of the parameters is much better than the generic one and definitely outweighs any "query plan caching" performance benefit.

Now my question: ADO.NET always seems to use the second option (sp_executesql) when executing parameterized queries, which usually makes sense (query plan caching, etc.). In our case, however, this kills performance. So, is there some way to either

  • force ADO.NET to use something different than sp_executesql (i.e., something where the SQL Server query analyzer takes the actual parameter values into account) OR
  • force SQL Server to recaclulate the query plan of the SQL passed to sp_executesql taking the parameter values into account?

And please don't tell me I have to go back to ugly, old, dangerous sql = "WHERE b = " + quoteAndEscape(parameterB)...

Putting the SQL into a stored procedure makes no difference (slow, with and without WITH RECOMPILE). I did not post the actual SQL statment since it is quite complex (joins over multiple tables, including sub-SELECTs and aggregation).

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • Converting it to a stored procedure is as slow as adhoc query? This is a VERY strange behavior, so I must ask: are you sure? – Rubens Farias Jan 11 '10 at 11:36
  • @Rubens - why do you say that? What performance advantage does a stored procedure offer over a cached, compiled, parameterised adhoc query? – David M Jan 11 '10 at 11:38
  • Yes, I am sure. Why should it be faster? The compilation time is insignificantly small (0.2s) as compared to the execution time (7-11s). – Heinzi Jan 11 '10 at 11:40
  • @David, @Heinzi: I said that because query execution plan (QEP) cache; – Rubens Farias Jan 11 '10 at 11:42
  • @Rubens - Which you get for adhoc queries as well, no? – David M Jan 11 '10 at 11:46
  • Adhoc queries, parameterised queries, stored procedures can all get into the execution plan cache - it's just that parameterised query/stored procedure cache items are more beneficial as they are much more likely to be reused – AdaTheDev Jan 11 '10 at 12:12
  • @Ada - agreed, but I did specify parameterised in my first comment. – David M Jan 11 '10 at 12:23
  • I answered a similar question [here](http://stackoverflow.com/a/15477956/11808). – Christoffer Lette Mar 18 '13 at 13:26

4 Answers4

4

Old thread I know, but I just found it by googling pretty much the exact same phrase! I had exactly the same issue (query ran very fast in Management Studio using parameters, but then really slow via ADO.Net) and replicated the issue by running the query in Management Studio via "exec sp_execute". The two execution plans were very different, even with the Optimize for query hint, so instead what I did was do an initial select of some of the data into a temporary table. That seemed to make the difference, and given you say that your query is a complex one, it might very well make the difference in your case too - I'm not quite sure how it worked, but it seemed to kick the execution plan into line even when using sp_execute.

MajorRefactoring
  • 3,713
  • 3
  • 20
  • 26
  • That's actually exactly what we ended up doing: Split the query into smaller ones using temporary tables (`#...`) to prevent the query optimizer from choosing a terribly bad plan. – Heinzi Nov 25 '11 at 14:23
  • When you just exec query in sql server it is different then by sp_execute, sp_execute cache execution plan in sql server without considering any parameters.So maybe your cached execution plan is good enough for the first parameters which has been applied, but for other ones it is not optimal one.To be sure you have to clear cache plan for the current query.That kind of problem is well known as parameter sniffing. – Artur Hovhannisyan Apr 30 '20 at 14:46
2

You could try the OPTIMIZE FOR query hint which (quote):

Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. OPTIMIZE FOR can counteract the parameter detection behavior of the optimizer or can be used when you create plan guides

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
2

I believe the issue has to do with using the VARCHAR data type in the database. SQL Server doesn't appear to use the index specified if the where parameter is declared as NVARCHAR.

You could, however, change your database column to NVARCHAR (this would increase the size, of course) and then the index performance likely improve.

I am currently having this issue with LINQ, and may actually need to revert to using stored procedures to get around it.

The issue is explained in detail in this Microsoft Connect discussion

Troy Alford
  • 26,660
  • 10
  • 64
  • 82
Alex Ayscough
  • 21
  • 1
  • After hours reading about, sniffing parameter problems, setting the ARITHABORT parameter, etc... this happens to be my problem, I wasn't explicitly setting the parameters type on my query and they were being passed as nvarchar, and I have varchar on the database. Thanks – J.J Sep 25 '14 at 15:28
0

I would move query to Stored Procedure and then in command specify command.CommandType = CommandType.StoredProcedure.

This doesn't create sp_executesql and increase performance

A Bergi
  • 1