ColdFusion Muse

Cached Plans and Static Variables

Regarding Static Variables in SQL Statements

In my last post I indicated that even static variables passed to SQL statements should be bound using Cfqueryparam. My understanding was that the DB server could only create cached plans if all the variables in the statement were bound - so I believed that a statement like the following:

<cfquery>
    SELECT fname, lname
    FROM    users    
    WHERE    active = 1
</cfquery>
...Could not benefit from the execution plan cache. In the comments of the previous post a number of people disputed this idea, saying that if the variable is static it will cause the execution plan to be cached. Now, Chris Secord has given me a tip on how to prove that I am wrong.

Using a DB where the activity is completely under your control (like a local installation). count the rows in the sys.dm_exec_cached_plans table (that's where MSSQL stores the cached plans). Then run the query above. Recount and you should see the number increase by 1. Rerun the same query and the number stays exactly the same - so no "new" execution plan was created. Now change active = 1 to active = 2 and rerun the query. Viola - the number increments by 1. A new cached plan has been inserted. You now have a cached plan for "active = 1" and another cached plan for "active = 2".

This means that it is perfectly acceptable to use static variables in your SQL statements. It also means that some queries run without CFQUERYPARAM that use the same data over and over again are likely still benefiting from cached plans. Of course this is the performance side of the argument. CFQUERYPARAM is still important for security and there are many more reasons to use it than not to use it.

Comments
pasta oyunlar?'s Gravatar Hopefully the next version of ColdFusion will have the ability to utilize new versions of the Ext JS as they come out. But, until that happens we will have to rely on the great work of people like Jason.
# Posted By pasta oyunlar? | 12/27/08 3:18 PM
giydirme oyunlar?'s Gravatar Hopefully the next version of ColdFusion will have the ability to utilize new versions of the Ext JS as they come out. But, until that happens we will have to rely on the great work of people like Jason.
# Posted By giydirme oyunlar? | 2/9/09 5:09 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.