I generally don't care to write about topics that have already had the stuffing blogged out of them. However, I've still seen some of these questions floating around and I figure it never hurts to have more than one place on the 'net talking about it. The two things I wanted to cover real quick are how cfqueryparam may (or may not) enhance the performance of your queries. Also, when does criteria in a query NOT need a cfqueryparam.The security side of cfqueryparam has pretty well been beaten into the ground, but there are other really good reasons to use it. Some of the SQL here that I will show you is specific to MSSQL, but most of these principles are pretty generic.

The Setup

Every time a SQL statement is run on your database, your DBMS compiles the statement into an execution plan based on the indexes available to use and latest data statistics. The query plan basically states in what order each table or index will be processed and how the results will be gathered. A simple select has a pretty basic plan, a giant select of doom with 10 tables and derived tables can get pretty hairy. MS SQL stores the plan as an XML file which can be viewed in a nice graphical format in Enterprise Manager. It is important to note that an execution plan will ALWAYS be used. There's no way around it. Generating the plan can also be costly depending on the complexity of the code. For this reason, your DBMS will cache a plan once it is generated in memory for later use if the same query comes up again. To get a list of cached plans in memory for MSSQL 2005 ordered by the most used, run the following select:
[code]SELECT
cache_plan.objtype,
cache_plan.size_in_bytes,
cache_plan.cacheobjtype,
cache_plan.usecounts,
sql_text.text
FROM sys.dm_exec_cached_plans as cache_plan
outer apply sys.dm_exec_sql_text (cache_plan.plan_handle) as sql_text
ORDER BY cache_plan.usecounts DESC
[/code]

The Problem

When a query gets run, your SQL server looks for a cached plan that matches before it compiles a new one.
  • Cached plans have to match pretty much exactly to be used
  • Your DBMS will only keep so many plans. The unused ones will be purged from memory
Let's say on an order detail screen you output the order number directly into the query and then view 100,000 different orders. Your SQL server will spend time generating 100,000 different execution plans and will try and cache all of them. Each of the following queries are different in your database's eyes:
[code]SELECT order_id FROM orders WHERE order_id = 1[/code]
[code]SELECT order_id FROM orders WHERE order_id = 2[/code]
[code]SELECT order_id FROM orders WHERE order_id = 3[/code]
[code]SELECT order_id FROM orders WHERE order_id = 4[/code]

The Fix

Now, what if we had used cfqueryparam. Our code would have looked something like this:
[code]<cfquery name="qry_order_id" datasource="foo">
SELECT order_id 
FROM orders 
WHERE order_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.order_id#">
</cfquery>[/code]
All 100,000 page views would have used the exact same SQL statement because all your database sees is this:
[code]SELECT order_id FROM orders WHERE order_id = ?[/code]
  • Imagine yow much processing time would have been saved by not compiling an extra 99,999 plans.
  • Imagine how much memory would be saved by not storing an extra 99,999 plans!
The more complex your queries are, and the higher traffic your site gets, the bigger of a performance increase to stand to receive from this.

The Catch (There's always one of these)

To be fair, I have to point out that not EVERY SQL statement will benefit from a generic, reusable, execution plan. My example above was pretty simple. Changes are there is an index on the order_id column and the query was a covered select that only required an index seek. The order_id column probably has equally disbursed data which can be arranged in ascending order very easily in your index. There are times though when your database will choose not to use a perfectly good index. If an index scan will be required and the database is going to have to scan through most of the entire index, and then do some bookmark lookups, it will have a good chance of saying "Screw the index, I'm just going straight to the table." And in that case it may very well be faster. Especially if the table is fairly small. Let's imagine our orders table has an order_state column to represent the state that the order was placed in and there is an index on that column. Your company is based out of Missouri so 90% of the records in the table are Missouri. Let's say you are searching for orders by state:
[code]<cfquery name="qry_orders_by_state" datasource="foo">
SELECT order_id, state, cust_name, invoice_num
FROM orders 
WHERE order_state = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.order_state#">
</cfquery>[/code]
If you search for all your Missouri orders (most of the table), a table scan might be the most efficient way to get all those records (becuase you are returning almost the entire table). If you are searching for California orders (which you RARELY have) then your order_state index would be much faster since you are only pulling a couple records. Now, if SQL server is re-using a generic execution plan from the very first query that was compiled today, it is possible that some of your queries would have been stuck with a plan that didn't really fit, but a new one was NOT compiled. The affects of this are real, but often negligible. I just want you to know the problem has the potential to exist.

Not Necessary

Also, one other quick note on cfqueryparam. Since we have established its main uses as:
  • Separating parameters from SQL logic to prevent arbitrary text from being confused with SQL and executed
  • Making your SQL statement generic so oft changing pieces will not cause constant recompiling of the plan
I've seen a couple people ask if they need to use cfqueryparam on static content in a query simply because it was used in the where clause. The answer is no. For instance if the following query ALWAYS filters for open orders...
[code]<cfquery name="qry_open_orders_by_state" datasource="foo">
SELECT order_id, state, cust_name, invoice_num
FROM orders 
WHERE order_state = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.order_state#">
AND order_status = 'OPEN'
</cfquery>[/code]
... then it is NOT necessary to parameterize the order_status like so:
[code]<cfquery name="qry_open_orders_by_state" datasource="foo">
SELECT order_id, state, cust_name, invoice_num
FROM orders 
WHERE order_state = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.order_state#">
AND order_status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="OPEN">
</cfquery>[/code]
That will work and it won't hurt anything, but it is unnecessary on your part since security and performance will not be affected by it. For more reading, Mark Kruger has some very nice articles over at ColdFusion Muse concerning execution plans. Check them out. http://www.coldfusionmuse.com/index.cfm/2005/6/28/cfqueryparam