This is a very interesting change in CF9 (and 9.0.1), which has slipped under the radar for the most part as far as I can tell.

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?

I think this is a very important setting, and though it has been a hidden gem, it seems, it's one that people should consider. That's why I've flagged this in my CF911 category.

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 think most folks know that the underlying database drivers in CF are from DataDirect. Well, they've announced new "Type 5" drivers. While you would have to buy and install them separately from those built-into CF (for now, as Adobe has not yet certified CF for use with them), I think some people may want to give them serious consideration even before then.

Several Performance Advantages, and Failover As Well

Among their advantages (over the older Type 4 drivers that are built into CF) are faster performance and lower memory use (more here), as well as greater tunability in general (discussed among other features between 4 and 5), and still more.

For some, more compelling still is the new driver-based failover, to have the driver detect a connection failure and switch communications to another DB instance (much like CF 8 Enterprise added the ability to do the same with mail server configuration in CF). This could be a huge improvement for many.

Getting More Info, Trials, Purchase

You can learn more about (and purchase or get a trial of) the drivers at http://www.datadirect.com/products/jdbc/index.ssp. The page also includes links to more on the benefits of type 5, comparison to Type 4, and so on. (Sadly, I just missed their cleverly titled "These go to 11" webinar earlier today. Perhaps they'll offer another soon.)

When Might Adobe Bundle Them in CF?

You may have caught that I referred to "purchase" of the drivers. Sadly, no, the drivers are not free. Adobe has licensed and included them in past releases of CF. So when Adobe may modify CF to bundle the new drivers?

Some have heard that Adobe's working on an updater for CF 9 (mentioned publicly at cf.Objective() among other places). Can we expect the drivers to be updated in that release? It's hard to say. In the 7.0.2 timeframe, the then-new drivers (3.5) came out just after the release and so were not included in the updater but instead were mentioned as a manual option in the CHF 1 technote. (Many missed that and to this day I suspect many shops still running CF 7 still have updated those drivers.) The point is, they did not include it with the updater (as I blogged about at the time in this blog entry.)

I can understand why they may not bundle new DB drivers with a CF updater: with updaters, they have to rebuild and retest all the installers on all the platforms, and updaters are generally not about adding "new features". It's a slippery slope as to whether driver updates are "new". It may also be a matter of timing, with respect to when Adobe learned about the drivers (likely sooner than most of us) and whether they found them compelling enough to consider, and at what cost in terms of backward compatibility. It's worth noting that the Type 5 drivers do assert to bring all their benefits "without code changes", so who knows?

The bigger question for some may really be simply, "I'm ok if they make me install it manually. It would just be nice to not have to license them ourselves." That's a fair point, and we just won't know until Adobe does address this issue (perhaps as they did in 7.0.2 CHF 1.) Until then, we have to wait and see.

But again, I do think the update compelling enough that shops with interest in performance should at least consider testing out the free trial to see how things go, or to prepare for the possibility of leveraging that new failover feature. (It also remains to be seen if Adobe may somehow try to force that failover feature to be an Enterprise-only feature, just as the mail server failover is.)

About the Company and Product Name Variations

Some may wonder if they visit the site for the drivers and notice the company being named Progress, when it used to be Merant. You can read about the fairly long chain of changes in the company history page.

One other thing worth noting is that the product is sometimes referred to as "DataDirect JDBC Type 5 drivers" (as I show in the subject here) and yet also sometimes as "DataDirect Connect for JDBC". They're the same thing.

One Boo Boo on the DataDirect Site

As I was tooling around their site, I happened on the page about use of the tool with JBoss servers. They said this:

The top web and application server vendors embed or recommend DataDirect JDBC drivers as part of their J2EE certification strategies (JBoss, IBM WebSphere, Sun Microsystems, Oracle BEA, and Macromedia.)

Can you spot the mistake? There is no more Macromedia, of course. I tried to find a page on the site to send a comment, but their Contact page didn't seem to offer a suitable option for such a comment. Oh well, so I am adding it here in case someone who cares can get something done about it.

Finally, I'll point out another useful page on their site, not new to the Type 5 drivers, but a helpful list of performance tips.

As people use CF9's ORM feature (or other ORMs like Transfer and Reactor, or indeed Hibernate, on any CFML engine), they may be left wondering what sort of SQL interactions happen "under the covers" between the ORM framework and the database engine (whether in a given request, or perhaps at startup of CF).

Well, there are several ways you can watch them, as this entry will discuss, and some may be better suited to the job than others. It can be very interesting to discover what's going on, especially if you're having any suspected performance problems which you think may be related to ORM processing (or just if you wonder what all it does for you).

As for spying on the SQL, of course ORM support is just a different way that the CFML engine (through the ORM framework) sends SQL to a database via a regular DSN, just like any other request, so there's nothing really "tricky" about this. It's just about realizing that while you don't write the SQL yourself, it's still generated by the CFML engine/ORM framework, and you may not realize/consider the available tools which can spy on it, just like any other DB processing from within CF. Indeed, some people may not even realize how many options exist to spy on JDBC interactions from their CFML engine to the database engine.

The good news is that there are several approaches, some included in CF (some depending on the edition), and some available separately which would work in any edition of CF or the other CFML engines (Open BlueDragon, Railo, etc.), and with any of the ORM frameworks. And again, some may be better than others for certain challenges.

(FWIW, besides the aforementioned Transfer and Reactor, there are still other ORM solutions for CFML, which I mention in my CF411 list as CFML ORM Frameworks. Indeed, note that you can run Hibernate on CF prior to CF9, if you want to.)

Built-in ORM Logging Option

First, note that there is indeed a built-in option in the CF ORM setup where one can enable logging, settable in the application.cfc: see the this.ormsettings option and its available key/value pair, logSQL="true".

There are several resources where you can learn more on that (and a related log4j property file approach to logging this). Besides the CF9 docs page on the ORM settings, there is also a blog entry by Adobe engineer Rupesh Kumar.

The default is to log this information to the console, but you can manipulate those log4j settings to tell it to use a file (see the links above). Even so, this will result in quite a lot of data being logged, which you will then need to connect back to your specific requests. The following approaches may be preferable.

Using FusionReactor or SeeFusion

Users of any CF edition (6+) or CFML engine can use tools like SeeFusion and FusionReactor, which have always had the ability to monitor database interactions by "wrapping" the datasource to be monitored. FusionReactor engineer John Hawksley has posted a recent article specifically on monitoring CF9's ORM interaction, in the FR Devnet site, Using FusionReactor's JDBC Driver Wrapper With ColdFusion 9 ORM. Its concepts would apply to any ORM, of course.

Similarly, I've written generically about FusionReactor's database monitoring feature in What is the FusionReactor datasource monitoring feature? Why would I use it? Powerful stuff. As I point out in that article, the concepts discussed apply as well to SeeFusion's ability to monitor queries by wrapping datasources.

That said, it's worth noting that FusionReactor does have a couple of advantages, in that it provides for the display of all queries for a given request (while viewing the details of that request), whereas SeeFusion only lets you see the slowest query in a given request. FusionReactor also provides a separately available display of all the slowest queries (across all requests). It also logs every query (connecting it to a given request as well), while SeeFusion (Enterprise, at least) can also log the slowest queries to a database.

And note that both of these track any requests coming out of CF, not just those associated with a given request. So if there is ORM SQL that is associated with the startup of CF, that's tracked too. (And for those aware of issues with CF's Client Variables, such DB activity is also tracked, even that done by the hourly purge, which takes place on a background, non-jrpp thread.)

CF Enterprise Server Monitor

Those running CF 8 or 9 (Enterprise only) will find that its available Server Monitor does offer built-in monitoring of the SQL executed against CF datasources, at least, as long as you enable "Start Profiling" (which also enables other features, and overhead, as well). In this way, the Enterprise Server Monitor can monitor database interactivity, including ORM interactions.

Unlike FusionReactor (and like SeeFusion), it focuses only on showing queries that exceed certain limits, and at that it shows them only in a "Slowest Queries" interface, tracking the slowest queries among all requests. The CF Enterprise Server Monitor also has no logging ability at all.

Being able to see every single DB interaction for a given request (or across all requests) may be all the more interesting for discovering/observing what's happening with ORM interactivity.

Another alternative CF feature

Still another little-known feature for spying on JDBC interactions in CF is by way of the JDBC "spy" feature, which does in fact allow logging of all JDBC interactions mde from within CF. This feature was first enabled by way of the DataDirect 3.5 driver update which was made available (as an optional upgrade for 6 and 7) in the CF 7.02 timeframe. I wrote about the Spy feature back back in Aug 2006.

Since then, CF 8 (and now 9) offer it instead as a new "log activity" option in the "advanced settings" for a datasource definition in the CF Admin (which is disabled by default). I pointed this out in another entry from 2007 as one of many easily missed changes for the CF 8 Admin.

This "log activity" output is not as easy to interpret as FusionReactor's logs, and can indeed be voluminous (moreso than FR's), so be careful. Anyway, it's one of the several ways you can monitor JDBC interactions between CFML and your DB engine. Again, any of these may be useful for monitoring any of your CFML/database interactions.

Generic DB Monitoring tools

Indeed, it's worth noting finally that while the focus here has been watching the DB interaction from CF (and the ORM framework) to the database (by watching the JDBC traffic going out of CF and returning), you could just as well watch the DB interactivity from the DB's perspective instead (watching it coming and and being returned).

There are many tools that can monitor database processing, available for each of the major databases (free and commercial). I list several such tools in one of my CF411 section, Database/SQL Monitoring Tools.

Hope all that's helpful, whether you use ORM or not.

Following onto my 2 previous entries related to updating JDBC drivers in CFMX 6 and 7, some readers may appreciate a little more help on the matter of determining just what version of the drivers they do now have installed.

This news has been shared in various blog entries over the past couple of years. I'd like to reprise them here for your edification. Just pay close attention to whether you mean to have them look at CFMX or CFMX 7. They each point to a directory such as C:\CFusionMX\lib or C:\CFusionMX7\lib. Make sure you use the value appropriate to your configuration:

• Pete Freitag showed some CFML code that gives you the answer on screen:
• Brandon Purcell showed a CFML approach that writes its output to the the CFMX log files instead (as of tonight, that blog entry is failing, but you can find its info thanks to the Google cache entry for that page)
• Finally, Sarge showed how to get the info via the command line

Hope that helps someone as much as it did me.

In my last entry, I mentioned briefly the new "JDBC Spy" feature that's available in the new 3.5 JDBC drivers for CFMX. I want to point out some more info for those interested in investigating the Spy feature further.

First, notice that there is a site at the DataDirect site (from whom Adobe purchases the JDBC drivers):

http://media.datadirect.com/download/docs/jdbc/alljdbc/reference/spy.html

It discusses some additional attributes. And still more useful info is offered at another page on their site:

http://media.datadirect.com/download/docs/jdbc/alljdbc/reference/diagnostics2.html

This actually shows what the log of info would look like, if you want to decide first if its info is worth the bother setting up.

This other page also talks about ways to turn the spy logging on and off at runtime, using Java method calls. Perhaps someone with more familiarity with the underlying Java classes for these drivers can translate the code there into something we can use in CFML. (It's late, and I don't have the energy.)

As such, be aware that the spy feature could generate a lot of data, so you may want to consider creating two datasource for each DB you may want to monitor. Either rename them to enable whichever you want to be in effect, or change if your code lets you specify a DSN via a variable, you can change the value to the "spying" version when you need it.

I did test and determined that also you can just add/remove the connection string from the end of the JDBC URL (see the technote for more), and immediately the logging stopped against the datasource. As a trick, I stuck the connectionstring value into the "description" field of the datasource definition, to keep it readily accessible if I wanted to add it back. (See my previous entry for a warning about putting it into the ConnectionString field instead.)

Finally, I should clarify that this Spy feature is not to be confused with the much older (and more powerful) p6Spy project, which has gone from open source to commercial to kind of stagnant. It still has great use, and in fact has a useful related SQL Profiler that's worth looking at. Still more at http://www.p6spy.com/, and maybe I'll write more about it later.