SELECT * and cfqueryparam

I've been using cfqueryparam for some time for both its performance and security benefits, but today I ran into something new with this tag. (I should state that this behavior was observed and confirmed using CFMX 7 and SQL Server 2000.) It all started with a question from a friend about some strange behavior he was seeing with a query which used cfqueryparam. He had added columns to a table, but his query against this table was not returning these new columns. The query in question used a wildcard select with cfqueryparam so it looked something like:

<cfquery name="myQuery" datasource="myDSN">
SELECT *
FROM myTable
WHERE myTableID = <cfqueryparam value="#x#" cfsqltype="cf_sql_numeric" />
</cfquery>

I suspected it had something to do with the caching of the execution plan, which most databases will do if you use bound parameters, and it turns out I was correct. To explain the issue lets say we start with a table with two columns. The first time we run this query in a ColdFusion template the database compiles and caches the execution plan for the query. If you add a column and run the template again the query will still only return our first two columns because the database uses its cached execution plan. Because the query uses the star wildcard and the plan was compiled before the third column was added to the table the cached plan only knows about our first two columns and completely ignores our new third column.

Now there are several ways around this issue. First don't use wildcard select statements. I generally list all the columns I want to return in my select statement, which may explain why I haven't run into this issue before, but I'll admit that there may be times when this just isn't practical. In those situations you can either not use cfqueryparam or use SET FORCEPLAN to force SQL Server to recompile the execution plan like so:

<cfquery name="myQuery" datasource="myDSN">
SET FORCEPLAN ON;
SELECT *
FROM myTable
WHERE myTableID = <cfqueryparam value="#x#" cfsqltype="cf_sql_numeric" />;
SET FORCEPLAN OFF;
</cfquery>

One other thing I didn't realize is that execution plan reuse is tied to the database connection, so if you restart ColdFusion after adding your new column you should get the expected results.

Anyway, just something to watch out for, especially since many of the examples I've seen for using queryparam use SELECT * and don't mention this behavior.

Comments
Shivang Patel's Gravatar Good finding. Glad to know the details.
# Posted By Shivang Patel | 9/26/06 10:00 AM
Daniel Roberts's Gravatar Thanks! Makes sense but you just saved me hours of time and a headache if this ever comes up.
# Posted By Daniel Roberts | 9/28/06 9:13 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.