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

Answer 2

I Recently ran into the same issue. The first thing I did was adding a NonClustered Covering Index on the columns in my where statement.

This improved the execution time on SQL, but when dapper was performing the query it was still slow, in fact it was timing out.

Then i realized that the query generated by dapper was passing in the parameter as nvarchar(4000) where as my db table column was a varchar(80) this caused it to perform an index scan instead of a seek (I suggest you read up on indexes if that does not make sense to you.). upon realizing this I updated my dapper where statement to be like this:

WHERE Reference = convert(varchar(80),@Reference)

Executing the with the where statement above resulted in an index seek, and 100% performance improvement.

Just to Add: The Option(Recompile) did not work for me.

And after all this song and dance, there is a way to tell dapper to do this for you by default:

Dapper.SqlMapper.AddTypeMap(typeof(string), System.Data.DbType.AnsiString);

This will by default map any string parameters to a varchar(4000) rather than a nvarchar(4000). If you do need Unicode string comparison, then you can explicitly do the conversion on the parameter.