sp_executesql使用参数缓慢 [英] sp_executesql is slow with parameters

问题描述

我正在使用dapper-dot-net作为ORM，它会产生以下缓慢执行（1700毫秒）的SQL代码。

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

当我通过删除参数来修改此代码时，查询的执行速度非常快（20毫秒）。

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'

推荐答案

在末尾添加选项（重新编译）

Add OPTION (RECOMPILE) to the end

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

我怀疑您正在遇到参数嗅探

I suspect you are experiencing "parameter sniffing"

如果是这种情况，我们可以将其留给OPTION或考虑其他选择

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

更新1

下面的文章将向您介绍参数嗅探 http://pratchev.blogspot.be/2007/08/parameter-sniffing.html

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

我建议您了解来龙去脉，因为它会使您的状态更好了解sql server内部（可以咬）。

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

如果您理解它，就会知道，如果执行该语句，则使用选项recompile 的权衡会降低性能。 / em>。

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.

我个人在之后添加选项reem 我知道根本原因是参数嗅探，除非有性能问题，否则将其保留。重写语句以避免错误的参数嗅探会导致意图丧失，从而降低了可维护性。但是在某些情况下，重写是合理的（这样做时请使用好的注释）。

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

更新2

关于该主题的最佳读物是在第32章中，称为
参数嗅探：您最好的朋友...，除非不是通过 GRANT FRITCHEY

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

建议。

SQL Server MVP深潜，第2卷

这篇关于sp_executesql使用参数缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！