c# - 为什么这个参数化的 SQL 需要很长时间，而同一个硬编码的 SQL 会立即执行

Question

我有一个看起来像这样的查询:

SELECT ct, 
       text AS ST, 
       kval.idkwd 
FROM   (SELECT ST = kv.idkwd, 
               Count(kv.idkwd) CT, 
               kv.idkwd 
        FROM   mwf 
               INNER JOIN info 
                       ON mwf.ident = info.idinfo
               INNER JOIN rel 
                       ON rel.idinfo = info.idinfo
               INNER JOIN pers 
                       ON pers.idpers = rel.idpers 
               LEFT JOIN kwd kv 
                      ON kv.idkwd = info.kwsvstatus 
        WHERE  mwf.id IN ( :mwfIds) 
        GROUP  BY idkwd) kw 
       INNER JOIN kwd kval 
               ON kw.idkwd = kval.idkwd 
ORDER  BY text 

在 ASP.NET 应用程序中，此查询使用 NHibernate 以这种方式执行:

session.CreateQuery(query);
query.SetParameterList("mwfIds", mwfIds, NHibernateUtil.Guid);
return query.List();

出于未知原因，有时需要 30 秒才能运行(对于某些给定参数)。这些措施由 SQL Profiler 给出。

我尝试在 SSMS 上使用相同的参数执行相同的查询(从 SQL Profiler 输出复制)，它运行不到 1 秒。

更糟糕的是，如果我将 C# 代码更改为

session.CreateQuery(hardcodedQuery);
return query.List();

其中 hardcodedQuery 与我在 SSMS 中运行的查询相同(即与往常一样，只是没有使用 NH 设置任何参数)，它的运行时间也不到 1 秒。

为什么参数化查询要花这么多时间？

Answer 1

正如 Sean Lange 所说 in his comment这种行为很可能是由参数嗅探引起的。

根据我的经验，它总是通过修复索引来解决。 (不要太快添加索引，索引太多可能会导致其他性能问题。例如查询优化器选择错误的索引，导致临时数据库溢出。)

参数嗅探不仅仅发生在存储过程中。通过示例，它发生在通过 sp_executesql 或 EXEC() 执行的 sql 查询上。它甚至可能发生在查询中创建的自动参数化标量值中。

参数嗅探是 SQL Server 在缺少索引的情况下使用的优化回退。它使用特定参数值为第一个查询生成查询计划，然后将其缓存在查询计划缓存中。对具有不同参数值和相似连接属性的同一查询的所有后续调用都将使用该查询计划，无论参数值是什么。

如果第一个查询调用的值对应于从一个表中产生高过滤条件的极端情况，但其他调用值不会导致相同的高过滤，则缓存的查询计划会导致它们执行不佳。

SSMS 很少有与您的应用程序相同的连接选项，导致它不会重用应用程序使用的缓存查询计划。如果您缺少索引，则会生成另一个查询计划，以适应您正在测试的查询参数值。所以 SSMS 似乎表现更好......但不，它只是使用为您正在测试的特定参数值量身定制的查询计划。

可以在 Slow in the Application, Fast in SSMS? Understanding Performance Mysteries 中阅读更详细、准确和充分的解释。博文。

不要被它原始的一面吓倒，在我看来，这个博客是一个很好的资源。不要被 SQL Server 如何编译存储过程 标题所迷惑，他在标题后的第二句话中写道:

If your application does not use stored procedures, but submits SQL statements directly, most of what I say this chapter is still applicable.

这篇博文还将为您提供有关如何解决此类问题的指导。