Cfqueryparam and Query Schema Caching

The other day I was working on a report that was running for minutes before finally timing out. The report was written in ColdFusion and queried the database.  I manually ran the query in SQL Manager.  It ran in less than a second.  Checked ColdFusion and server timeouts: all OK.  What in the world was causing this query to behave so strangely?Puzzle pieces

At last I tried an experiment: Remove the cfqueryparam tags from the code … the query runs fine; add the cfqueryparam tags back … the query slows to a crawl.

After being baffled as to why in the world the use of cfqueryparam tags would cause a query to misbehave so badly, I shared my mystery with fellow developer Eric Mosel, who promptly found an answer:  The query schema was being cached.  

Queries with cfqueryparam tags use Java prepared statements, causing the database to treat the query as a stored procedure. This means that even if changes to the table are made, the old query schema is being used when ColdFusion tries to execute the query, as nothing flags the database changes to your “stored procedure.” [1]

What to do, then, to refresh the query schema?

Option 1: OPTION (RECOMPILE)

To fix my long-running query, Eric suggested that I use a T-SQL Query Hint, OPTION (RECOMPILE), to force my query to re-compute its execution plan every time it runs.   This isn’t ideal for all queries, as the SQL server usually does the best job of coming up with an execution plan for queries. However, in this case it was necessary and solved the problem.

Option 2: Add a Semicolon

When the query schema is being cached, the exact text of the query matters.   If you change the query in ColdFusion, it causes the query schema to be refreshed.  But how to change the query when you don’t want to change the query but do want to refresh the schema? Add a semicolon to the end of your query.  Semicolons are the end-of-statement characters in SQL, and while not required for ColdFusion queries, are allowed and are enough of a change to force your query schema to refresh.

Have you run into this problem?  How did you solve it?

[1] Comment from Brandon Purcell, on Ray Camden’s blog post Odd Issue w/ CFQueryParam

1 thought on “Cfqueryparam and Query Schema Caching

  1. I’ve run into this before – I remember solving it by adding a SQL comment ( — like this) to the query text. In fact, that’s also pretty useful when you’ve cached a query result.

    I had an resource expensive query that only needed to refresh results when one of the joined tables had new data. I also needed the query to refresh as soon as new data was available. My solution: I ran a small query to get the most recent record then put the record ID in a SQL comment in the larger query (e.g. — #ID#), hence the query was recompiled and re-cached until the next data point came in.

Comments are closed.