22

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'
StepUp
  • 36,391
  • 15
  • 88
  • 148
m__
  • 1,721
  • 1
  • 17
  • 30

2 Answers2

40

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

Matt
  • 179
  • 7
buckley
  • 13,690
  • 3
  • 53
  • 61
  • Certainly, something to do with indexing. – Philip Kelley Jun 07 '12 at 14:04
  • this seems to have done the trick, do I have to keep this for every exceution or just this one time in order to recompile it? (will accept your answers after I've done some additional tests, thank you) – m__ Jun 07 '12 at 14:04
  • 1
    "I personally leave option recompile in my sql unless there is a perf issue." Did you mean you usually *don't* use option recompile unless there is a perf issue? I don't think it's good practice (never mind advice) to always use OPTION RECOMPILE by default unless it causes a problem. – Aaron Bertrand Jun 07 '12 at 14:18
  • @AaronBertrand Nice to hear from you again. Once I have diagnosed that the root cause is sniffing I will add option recompile, do some stress tests and if there is no perf degradation I will leave the option recompile. If the recompile *is* an issue I will rewrite it using known techniques (you remember) which do make the statement generally loose its intent and makes it less maintainable. I don't start with option recompile if that is what you thought. Execution plan caching is a good thing in general. – buckley Jun 07 '12 at 14:24
  • @buckley Thanks for the clarification. I would clarify that in the answer. I took it as "I don't always write queries, but when I do, I use OPTION RECOMPILE." – Aaron Bertrand Jun 07 '12 at 14:25
  • @AaronBertrand Rewrote the last paragraph – buckley Jun 07 '12 at 14:30
  • Thanks for the link on parameter sniffing. The blog pointed to the ability to create a plan guide. This solved it for me as I couldn't modify the query itself. – kinstephen Mar 12 '15 at 18:51
11

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.

Dev_Corps
  • 341
  • 3
  • 7