Why with parameters, the stored procedure executesql is slow in Dapper?

dapper sql-server

Question

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'
1
15
6/7/2012 1:59:20 PM

Accepted Answer

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.

1. Update

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).

2. Update

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.

Volume 2 of the SQL Server MVP Deep Dives

30
7/16/2018 2:13:29 AM

Popular Answer

ZZZ_tmp


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow