ColdFusion Muse

Superfluous Data Binding Can be Good for You

If you have read my previous posts on "Execution Plans" and Data Binding you know I am a big believer in using Cfqueryparam for performance as well as security. Today I picked up a tip on this topic from harelmalka.com (a blog I had never read before). I had never considered it before, but a query with no "WHERE" clause does not using binding because it lacks the opportunity. Therefore, a statement like:

<CFQUERY ...>
      SELECT * FROM Users   
   </CFQUERY>
Would not include a prepare statement and not hit the execution plan. Hmmm.... The solution (originally detailed by Barny) is to add a superfluous binding merely for the purpose of kicking off the "prepare statement" method. Like so:
<CFQUERY ...>
      SELECT * FROM Users   
      WHERE 1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1">
   </CFQUERY>
That get's the job done nicely.

Comments
Ben Nadel's Gravatar Thanks! This is really interesting stuff. I am not sure I full understand it, even after reading the other post, but if it works, it works.
# Posted By Ben Nadel | 8/23/06 2:47 PM
Harel Malka's Gravatar In a nutshell:
When you put a sql query inside a cfquery tag, with all sorts of cf variables in it like: "Select this, that from contacts where name='#name#' and age=#age#" etc. the query has to be reparsed every time you send it to the database because its going in as a string. When you're using bind variables (in CF via the cfqueryparam) the query is turned into a prepared statement which is an already parsed query, all nicely packaged up in a PreparedStatement object, which then only needs to accept the various bind variables to use in the query. No reparsing is needed. This eliminates a large overhead. When you're doing one query in one request you won't feel the difference. But when 1000 users are hitting that query, you'll see degregation in the performance times.
;o)
Harel
# Posted By Harel Malka | 8/24/06 4:52 AM



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