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:

...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.