Jul 26 2008
ColdFusion, Performance, Security, SQL
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 SetupEvery 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 ProblemWhen 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
[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 FixNow, 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 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 NecessaryAlso, 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
[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