Everyone knows that you should be using CFQUERYPARAM. It offers many benefits in the areas of security, data validation, and enhanced performance for your applications. In fact, Adobe recommends that you use the CFQUERYPARAM tag within every CFQUERY, and I completely agree with this recommendation. I think you would be very hard pressed to find a valid reason NOT to use it.
Many of us know the benefit of CFQUERYPARAM from the application side, though we may not always be aware of how it can also help out the database side. In this excerpt from my session at CF Summit 2018, Faster Queries In ColdFusion & SQL Server, we will take a look at what goes on behind the scenes in SQL Server when processing queries with, and without, CFQUERYPARAM.
What happens when SQL Server processes a query?
Whenever you run a query in SQL Server, it will generate an Execution Plan which basically tells SQL Server how to gather and filter the data your query is requesting. Once an Execution Plan has been created, SQL Server stores it in the Plan Cache, which is a section of memory set aside for keeping Execution Plans. The reason for this is so that the next time that query is executed, SQL Server will be able to reuse the previously generated Execution Plan, and not have to go through the overhead of generating it again. So, for example, if we were to run the following query and then check the Plan Cache, we would see where SQL Server has generated an Execution Plan for it.
Notice how the variable that we passed to the query is actually a hard coded value when SQL Server runs the query. Now, if we were to change the value of the variable we are using in our query, then check the Plan Cache, we would see that SQL Server has generated a separate Execution Plan for this query, which would have the new variable value hard coded. In fact, if we were to loop through multiple values for our variable, SQL Server would generate an Execution Plan for each version of our query:
Why is this a problem?
When the variable in our query is rendered by ColdFusion and the query is processed, SQL Server sees each version of the query as a completely different query and therefore has to create a new Execution Plan for it. Having an Execution Plan generated for every version of this query is expensive, in both processing overhead and resource utilization. If you run that query 1,000 times per day with a different ID each time, you have 1,000 copies of that same execution plan sitting in RAM on your SQL Server. Plus, the processing overhead of actually generating an Execution Plan is expensive, that’s why SQL Server wants to cache it so that it doesn’t have to do it every time. Repeatedly generating an Execution Plan can lead to higher CPU usage, and slower queries.
How does CFQUERYPARAM help?
CFQUERYPARAM uses bind variables when sending a query to SQL Server. A bind variable will allow SQL Server to use parameters for the variables you pass in via CFQUERYPARAM, which will allow it to generate and reuse a single execution plan for the query. Execution Plan reuse is a big deal in SQL Server! If we take a look at our last example and add CFQUERYPARAM, we can see how SQL Server now creates a single parameterized Execution Plan, and reuses it for each query.
By reusing the same Execution Plan for each of these queries SQL Server is able to process the query faster, while also providing more efficient resource utilization by not having a lot of individual “one-off” Execution Plans sitting in memory. This can have a big impact on the performance of your SQL Server, especially when you consider that this applies to every query your application runs. Yes, even your UPDATE and DELETE queries!
Use CFQUERYPARAM Everywhere!
Is this overkill? Maybe for some, but as I like to say in my presentations, scalability doesn’t happen by accident, you have to plan for it and code for it. So always remember to use CFQUERYPARAM as much as possible, your SQL Server will thank you for it!
Newbie
1 posts
Is it possible to implement in the SELECT portion of the QUERY? If yes, a tutorial please. Been toying with this for the past fifteen minutes turning to Microsoft Bing to search for an answer.
SELECT u.#FORM.DesiredField#
FROM dbo.Users
WHERE …
Allowing the user to return and output one specific field of choice (DisplayName, ActualName, etc)
<cfoutput> #Evaluate(“getUsers.#FORM.DesiredField#”)# </cfoutput>
What Statement do you use to query the execution_count?
Bernhard, what “execution_count” are you referring to? I just don’t see the quoted string you list here, which is why I am asking. You don’t mean the count of records in the query result (the classic queryname.recordcount variable), do you?
Bernhard, to query SQL Server’s internal query statistics, I use the following:
SELECT dm_exec_sql_text.text AS TSQL_Text, dm_exec_query_stats.execution_count
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE sys.databases.name = ‘YourDatabaseNameHere’;
Ah, I see now that Bernhard was referring to the count of executions of a given query plan, which I now see was listed in an image here.
I was (and still am) viewing this on my phone and the image is small. And when I searched for the string, I didn’t find it because it was in that image.
Sorry for my confusion, and of course thanks for the post, Eric.
Hi Adobe,
If you see my comment here, can you please document the fact that “cf_sql_” isn’t needed (ex: cfsqltype=”cf_sql_integer” can be shortened to cfsqltype=”integer”)? And can you please document which version removed that requirement? Was it CF11?
Thanks!,
-Aaron
How does this affect hard coded variables that aren’t variables? For example:
SELECT firstName, lastName FROM People WHERE (Deleted = 0) AND (personID = <cfqueryparam cfsqltype=”integer” value=”#local.personID#”>)
Should the “0” in this instance be in a cfqueryparam?
Here is my best guess.
It helps, but not a lot. I am imagining that Deleted. If you are only looking for deleted=0 then there is only going to be one query plan. Using <cfqueryparam>, you still have only one query plan.
You got to this one before I did. I like it a lot.
A good follow up would be the QueryExecute version on this. I believe, but I might be wrong, that that passing parameters as an array, does some auto-parameterization.
You must be logged in to post a comment.
23 Nov
10AM Pacific
Online
