ColdFusion Muse

Superfluous Data Binding Can be Good for You

Mark Kruger August 23, 2006 9:51 AM Coldfusion Optimization, Coldfusion MX 7 Comments (2)

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 (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:

      SELECT * FROM Users   
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:
      SELECT * FROM Users   
      WHERE 1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1">
That get's the job done nicely.

  • Share:


Leave this field empty

Write a comment

If you subscribe, any new posts to this thread will be sent to your email address.

  • Ben Nadel's Gravatar
    Posted By
    Ben Nadel | 8/23/06 2:47 PM
    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.
  • Harel Malka's Gravatar
    Posted By
    Harel Malka | 8/24/06 4:52 AM
    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.