sp_executesql的是参数慢(sp_executesql is slow with para

我使用短小精悍的点网作为ORM并可以产生以下，慢执行（1700ms），SQL代码。

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毫秒）。 如果缺少这些参数实际上使这个大的区别，为什么？

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:

添加OPTION（RECOMPILE）到最后

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

我怀疑遇到“参数嗅探”

如果是这样的话，我们可以通过选项离开它，或考虑替代方案

更新1

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

你了解的插件和，因为它会让你了解SQL Server的内部更好的（也可以咬），我的意见。

如果你了解它，你就会知道，如果经常执行的语句重新编译选项的权衡可能成为一个性能下降。

我个人添加选项重新编译后，我知道根本原因是参数嗅探并在离开它，除非有性能问题。 重写一份声明，以避免错误的参数嗅探导致意向的损失，这降低了可维护性。 但也有情况下，当重写是有道理的（使用很好的意见，当你这样做）。

更新2

最好看的我关于这个问题在第32章被称为“嗅探参数：你最好的朋友......除了当它是不是由”格兰特FRITCHEY

它的建议。

SQL Server的MVP深海潜水，第2卷

Answer 2:

我最近遇到了同样的问题。 我做的第一件事是在我的WHERE语句上的列添加一个非聚集覆盖索引。

这提高了对SQL的执行时间，但是当短小精悍被执行查询它仍然是缓慢的，其实它是超时。

后来我意识到，通过短小精悍生成的查询被传入参数为为nvarchar（4000），其中作为我的数据库表列是一个varchar（80）这导致它执行索引扫描，而不是寻求 （我建议你阅读了关于如果该指标没有意义给你。）。 在意识到这一点，我更新了我的短小精悍的WHERE语句是这样的：

WHERE参考=转换（VARCHAR（80），@参考）

与WHERE语句执行上述导致索引查找，和100％的性能提升。

我想补充：选项（重新编译）并没有为我工作。

而这一切的歌舞之后，有一种方法来告诉短小精悍，默认情况下为你做这个：

Dapper.SqlMapper.AddTypeMap（typeof运算（字符串），System.Data.DbType.AnsiString）;

这将通过缺省任何字符串参数映射到一个varchar（4000），而不是为nvarchar（4000）。 如果您确实需要Unicode字符串比较，那么你可以明确地做参数转换。

文章来源: sp_executesql is slow with parameters