奇怪的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的问题吗，还是我的操作有误？

- user3525290

---

这对我来说看起来还不错，我从未听说过添加cfqueryparam会导致查询变慢。也许尝试不同的cfsqltype，如cf_sql_numeric或cf_sql_idstamp，看看是否有任何不同。 - andrewdixon

ColdFusion的哪个版本？您是否使用随该版本一起提供的SQL Server驱动程序？ - Adrian J. Moreno

可能相关：https://dev59.com/o2PVa4cB1Zd3GeqP8LWT 我清楚地看到你的数据类型被指定为整数，但是表的数据类型设置为什么？你有没有调整cfsqltype的值来匹配？ - Regular Jo

我尝试使用cf_sql_numeric和cf_sql_idstamp。结果相同。我正在运行CF 10，使用随CF和SQL一起提供的驱动程序。数据库中的数据类型设置为int。 - user3525290

这两个列表中有多少个值？另外，使用分析器，这两个执行计划是什么样的？即使用和不使用cfqueryparam的情况下。 - Leigh

Answer 1

在 IN 子句中使用长列表会导致 SQL 性能急剧下降。如果您可以减少列表长度，则查询性能可能会得到改善。

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

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

此外，cfqueryparam 可以为您提供大量防止 SQL 注入攻击 的保护。从安全角度考虑，我建议将传递到查询中的所有值都使用 cfqueryparam。

最后，尝试在 SQL Server Management Studio 中运行查询并单击 Show Actual Execution Plan 按钮。它可以帮助您确定是否在表上添加一个或多个索引会有助于执行时间。
SQL Server Management Studio 中的“缺失索引”功能

- Kevin Morris

---

即使使用参数，每次查询的执行计划仍然可能因参数的数量变化而生成不同的执行计划。值得注意的是，并没有为每个一次性硬编码查询缓存执行计划。 - Leigh

@Leigh 非常好的观点。将列表传递到 SQL 查询中会引发很多性能问题。每当列表密集的代码接近数据库时，我就开始思考如何进行重构。 - Kevin Morris

我曾经见过一个罕见的与cfqueryparam有关的bug，但那并不涉及INT。我认为启动SQL Profiler并查看执行计划可能是找出原因的下一个合乎逻辑的步骤。 - Leigh