CODEWITHSUNDEEP
sql-servercoldfusion
user3525290
user3525290

Reputation: 1607

Weird Coldfusion cfqueryparam

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?

Upvotes: 4

Views: 366

Answers (1)

Kevin Morris
Kevin Morris

Reputation: 354

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

Upvotes: 2

Related Questions