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?
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