The ORM I'm using, dapper-dot-net, generates the following slow-running (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 remove the arguments from this code, the query runs really quickly (20ms). Why, if at all, should the absence of these factors make such a significant difference?
exec sp_executesql N'SELECT TOP 5 SensorValue FROM "Values" WHERE DeviceId IN (139,726) AND SensorId = 178 AND SensorValue != -32768 AND SensorValue != -32767'
At the end, add OPTION (RECOMPILE).
... AND SensorValue != -32767 OPTION (RECOMPILE)
You may be experiencing "parameter sniffing," in my opinion.
If so, we may choose to go with the OPTION or look at other options.
You will learn about "parameter sniffing" in the essay that follows. http://pratchev.blogspot.be/2007/08/parameter-sniffing.html
Knowing the ins and outs will help you comprehend sql server internals lot better, thus I advise you to do so (that can bite).
If you comprehend it, you will be aware that the performance will suffer if the statement is run very often if the option to recompile is chosen.
Personally, I add the recompile after option. I leave parameter sniffing in until there is a performance problem since I am aware that it is the fundamental reason. Intent is lost when a statement is rewritten to prevent poor parameter sniffing, which reduces maintainability. However, there are instances when a rewrite is appropriate (use good comments when you do).
The finest chapter I found on the issue was chapter 32 of GRANT FRITCHEY's book, "Parameter sniffing: your best friend... unless when it isn't."
It is advised.