ColdFusion Muse

Cached Plans and Static Variables

Mark Kruger December 16, 2008 11:12 AM ColdFusion, MS SQL Server Comments (2)

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:

    SELECT fname, lname
    FROM    users    
    WHERE    active = 1
...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.

Read More
  • Share:

Can Performance Suffer With Cfqueryparam?

Mark Kruger November 18, 2008 9:18 PM ColdFusion Comments (38)

I heard an excellent presentation by CF giant Charlie Arehart yesterday. It was one of the "unconference" sessions title CfMythbusters. Later that day I was priveledged to share the mic with Charlie and talk bout CF Troubleshooting. Anyway, while discussing cfqueryparam Charlie said something that made me sit up a bit and say "huh?". It has long been the contention of myself and others that the use of Cfqueryparam benefits both security and performance when used against an RDBMS like SQL server or Oracle. While this is a generalization it usually holds true. Charlie, however, illustrated to me a case where cfqueryparam might be detrimental to performance and he was so convincing I thought I would share it with you.

NOTE: Check out the comments for some cavaets and opposing viewpoints. Also note that the tip on constants may not hold water. See this discussion on Brad Wood's blog for more insight on that item

Read More
  • Share: