4
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

When I run the above code it takes 11 to 15 seconds. If I remove the cfqueryparam, and just use the idlist2 variable, the query only takes 32 milliseconds.

Is this an issue with cfqueryparam, or am I doing something incorrect?

Leigh
  • 28,765
  • 10
  • 55
  • 103
user3525290
  • 1,557
  • 2
  • 20
  • 47
  • This looks ok to me and I've never known the adding of a cfqueryparam to cause a query to take longer. Maybe try a different cfsqltype like cf_sql_numeric or cf_sql_idstamp and see if that makes any difference. – andrewdixon Feb 19 '15 at 21:45
  • What version of ColdFusion? Are you using the SQL Server drivers that ship with that version? – Adrian J. Moreno Feb 19 '15 at 21:53
  • 2
    Possible related: http://stackoverflow.com/questions/10543755/slow-query-with-cfqueryparam-searching-on-indexed-column-containing-hashes I see clearly that your data type is specified as integer but what is the table's data type set to? Have you fiddling with the value of `cfsqltype` to match? – Regular Jo Feb 20 '15 at 00:09
  • I've tried using cf_sql_numeric, and cf_sql_idstamp. I get the same result. I am running cf 10 I am using whatever drivers that shipped with CF and SQL. The data type in the database is set as int. – user3525290 Feb 20 '15 at 16:53
  • How many values in the two lists? Also, using a profiler - what do the two execution plans look like? ie With and without cfqueryparam? – Leigh Feb 23 '15 at 23:12

1 Answers1

2

SQL performance can drop precipitously with long lists in an IN clause. If you can reduce the length of the lists, your query performance will likely improve.

When you use cfqueryparam, the values are passed to SQL as a list of arguments/parameters/variables. When you do NOT use cfqueryparam, the list of values is hardcoded into the query string. This allows SQL's "query execution plan" to be pre-optimized for that specific list of values. It also allows the plan to be cached from one execution to the next. This can result in subsequent identical queries to execute very fast, like during debugging and testing.

If this is a dynamic query, if the list of values changes each time the query is run, then you want to make sure to use cfqueryparam so that SQL Server isn't caching the execution plan for each one-time hardcoded query.

Furthermore, cfqueryparam gives you a LOT of protection against SQL Injection attacks. From a security aspect, I recommend that all values being passed into a query should use cfqueryparam.

Finally, try running the query in SQL Server Management Studio and click the Show Actual Execution Plan button. It can help you determine if adding one or more indexes on your tables would help the execution time.
'Missing Index' feature of SQL Server Management Studio

Kevin Morris
  • 334
  • 1
  • 8
  • *isn't caching the execution plan for each one-time hardcoded query* Though it is worth noting even with params, it may still generate separate execution plans whenever the *number* of params change. – Leigh Dec 09 '16 at 21:20
  • @Leigh Excellent point. Passing lists into a SQL query presents a lot of performance concerns. Any time list-heavy code gets near a database, I start to wonder how it can be refactored. – Kevin Morris Dec 09 '16 at 21:26
  • I have seen a rare bug involving cfqueryparam, but that did not involve INT's. I think firing up the SQL Profiler and looking at the execution plans is probably the next logical step in figuring out the cause. – Leigh Dec 09 '16 at 21:31