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:
SELECT fname, lname
FROM users
WHERE active = 1
</cfquery>
Can Performance Suffer With Cfqueryparam?
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
Sites to Watch
Aggregators
RSS
