sql server - sp_executesql is slow with parameters

Question

I'm using dapper-dot-net as an ORM and it produces the following, slow-executing (1700ms), SQL code.

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (@id1,@id2) AND SensorId = @sensor AND SensorValue != -32768 AND SensorValue != -32767',N'@id1 bigint,@id2 bigint,@sensor int',@id1=139,@id2=726,@sensor=178

When I modify this code by removing the parameters the query executes blazingly fast (20ms). Should the lack of these parameters actually make this big difference and why?

exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'

Answer 1

Add OPTION (RECOMPILE) to the end

... AND SensorValue != -32767 OPTION (RECOMPILE) 

I suspect you are experiencing "parameter sniffing"

If that's the case we can leave it with the OPTION or consider alternatives

Update 1

The following article will introduce you to "parameter sniffing" http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

I advice that you get to know the ins and out because it will make you much better in understanding sql server internals (that can bite).

If you understand it you will know that the tradeoff with option recompile can be a performance decrease if the statement is executed very often.

I personally add option recompile after I know the root cause is parameter sniffing and leave it in unless there is a performance issue. Rewriting a statement to avoid bad parameter sniffing leads to loss of intent and this lowers maintainability. But there are cases when the rewrite is justified (use good comments when you do).

Update 2

The best read I had on the subject was in chapter 32 called "Parameter sniffing: your best friend... except when it isn't by " by GRANT FRITCHEY

It's recommended.

SQL Server MVP Deep Dives, Volume 2