Did you know there is now a setting in the DSN page of the CF Admin (for most DB drivers) that allows you to set a maximum timeout for queries against that DSN? It's a new feature enabled for the DataDirect drivers udpated in CF 9. The caveat? It is ONLY settable there, not in CFQUERY itself, which is a shame (the existing TIMEOUT attribute is not the same and generally does not work). Still, the value of this even at the DSN level is too important to ignore for some challenges. More on that (and some other thoughts) in a moment.

As for the setting, it's in the "Advanced Settings" section for a DSN in the CF 9 Admin, and it's called "Query Timeout". This should not to be confused with the older settings, "Timeout" (which is about inactive connections) or "Login Timeout" (which is about logging in to the connection). The screenshot at right shows all 3. (This blog entry continues, with more information below it.)

I've run a test, and it really does do the job, which is huge. Why? Because it's been a long-time issue that if a CFQUERY got hung up waiting for a response, that request thread (doing the CFQUERY) is then hung until the query finishes, which can sometimes be many minutes, or even hours or days, due to some odd situations. More important, a thread waiting for a query with no timeout can't be terminated (by the JVM, or CF, or the monitoring tools) because the thread was in a native thread state.

With this new option specified, if the request exceeds the timeout, the request does now fail, with a JDBC error, "Execution timeout expired." The same test does NOT timeout with the older cfquery TIMEOUT attribute.

Here are some other notes on the new feature:

• It works with most of the database drivers. I have confirmed that the setting appears in the DSN settings page for SQL Server, Oracle, MySQL (datadirect), DB2, Informix, Sybase) in CF 9 Enterprise, and in both SQL Server and MySQL (Datadirect) on CF 9 Standard.
• The timeout's specified in seconds.
• You can learn more in the CF Admin guide, in this specific page.
• Oddly, the Admin manual page above only references this new setting in that CF Admin manual is in the the MySQL settings, but again it does appear in all the drivers above.
• The manual page does even reference the other DBMSs by name in its naming the methods of the Admin API (for other DBMSs) which you can use as well, which can be used to set this default setting in the DSN programmatically.
• That said, again there is sadly no new QueryTimeout (or QTimeout) attribute for CFQUERY, so for now we can only set this at the datasource level, not per query.
• I've raised the concerns above on the CF Admin livedocs page (or whatever we are to call it now.)
• If you look under the covers (in [cf]\lib\neo-datasource.xml), there is in fact a querytimeout connectionstring that this setting controls. If only there was a way we could pass connectionstring values to the CFQUERY, we'd be golden. Some may recall use used to have just such an attribute (ConnectString), but sadly it was deprecated in CF 6. I did try it, to no avail.
• I will raise have raised a bug with Adobe to get a new attribute for CFQUERY related to this. When I do that, I'll report it here. It's bug 83592. Please add your vote of support for it.
• There's another page on this Admin setting, in the CF Dev Guide, for those who may be interested in following any other possible places where this feature may be discussed (in the comments there).
• If you want some code to use to test a request waiting a long time for the DB to return, most databases offer a statement that tells the DB to wait for some time. In SQL Server, that's WAITFOR DELAY 'hrs:mins:secs'. Just use that in a CFQUERY, assuming your DSN definition doesn't limit what SQL statements you can use, in the 'Allowed SQL' section of the Adcanced Settings page.

Why is this setting important?

If you're suffering situations where requests are hanging due to long-running queries, and you have not been able to solve the real root cause for why they are hanging (which I always recommend first and foremost), then at least this option can help avoid a situation where queries can run without time limit. An Admin can decide that no queries should be allowed to run more than x seconds.

With that power comes responsibility, of course, and caution. You wouldn't want to preclude someone being able to run a query that really needed to take a long time. That's why it's really better if this was settable on a per-query basis. (And no, the CF page timeout settings are NOT the solution here, because again as I said above, they cannot timeout some kinds of long-running tags, like CFQUERY, CFHTTP, CFINVOKE of a web service, etc.)

What one could do, though (for now), is create different DSNs, where one could be used for most query processing, and another could be used for long-running requests. Yes, it's ok to have 2 DSNs point to the same DB. This same technique has been used when wanting to have most queries run against one DSN with a limited set of "allowed SQL" (per the DSN advanced settings) while another DSN has unfettered SQL access.

Hope this is helpful to some. Let me know what you think, whether this was helpful or if you feel I left something out. Especially please let me know if you may know of a way that we can indeed pass querystring values on a per-query basis.

I mentioned last week that I was starting my "Hidden Gems in CF9" series of blog entries, and I'll eventually do a class based on that. In the meantime, I will also soon start sharing some of these CFBuilder hidden gems here as well.

So if you're coming to CFinNC, please look me up and say hello. And if you're not yet planning to attend but live within a few hours driving distance (I'll be driving the 5 hours from Atlanta), you should definitely consider it. There will ba a lot of great speakers and content, as well as community and festivities, all for free.

Help spread the word, online or by grabbing a flyer to put up at your workplace. It's not too late!