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:
SELECT * FROM Users
</CFQUERY>
<CFQUERY ...>
SELECT * FROM Users
WHERE 1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1">
</CFQUERY>
That get's the job done nicely.
SELECT * FROM Users
WHERE 1 = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1">
</CFQUERY>
2 Comments
-
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. -
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
Sites to Watch
Aggregators
RSS
