sql-server - 奇怪的 Coldfusion cfqueryparam

Question

SELECT  DISTINCT Table3.ID 
FROM    Table1 
          INNER JOIN Table2 ON Table1.thisID = Table2.thisID 
          INNER JOIN Table3 ON Table2.ID = Table3.ID 
WHERE ( Table1.ID IN 
         ( 
            <cfqueryparam cfsqltype="cf_sql_integer" 
                value="#idlist#" list="yes">
         )
      ) 
AND   Table2.ID IN 
      (  
           <cfqueryparam cfsqltype="cf_sql_integer" 
                 value="#idlist2#" list="yes">
      ) 
AND  Table3.active=1 
ORDER BY Table3.ID

当我运行上面的代码时，需要 11 到 15 秒。如果我删除 cfqueryparam，并仅使用 idlist2 变量，则查询只需要 32 毫秒。

这是 cfqueryparam 的问题，还是我做错了什么？

Answer 1

IN 子句中的长列表会使 SQL 性能急剧下降。如果您可以减少列表的长度，您的查询性能可能会提高。

当您使用cfqueryparam时，值将作为参数/参数/变量列表传递给 SQL。当您不使用 cfqueryparam 时，值列表将被硬编码到查询字符串中。这允许 SQL 的“查询执行计划”针对特定值列表进行预先优化。它还允许将计划从一次执行缓存到下一次执行。这可能会导致后续相同查询执行得非常快，例如在调试和测试期间。

如果这是动态查询，并且每次运行查询时值列表都会更改，那么您需要确保使用 cfqueryparam 以便 SQL Server 不会缓存执行计划对于每个一次性硬编码查询。

此外，cfqueryparam 为您提供了大量针对 SQL Injection attacks 的保护。 。从安全角度来看，我建议传递到查询中的所有值都应使用cfqueryparam。

最后，尝试在 SQL Server Management Studio 中运行查询并单击显示实际执行计划按钮。它可以帮助您确定在表上添加一个或多个索引是否有助于缩短执行时间。
'Missing Index' feature of SQL Server Management Studio