timeout attribute of cfquery ignored?

[cherdt]

I set up the following SELECT * test on a table with a huge number of rows, just to make sure the query would run over 1 second:

<cfquery name="test" datasource="#ds#" timeout="1">

          SELECT *

          FROM request_stats

</cfquery>

The debugging info indicates that the query took 2539ms, but instead of throwing an error the query ran & returned results. Maybe I am not understanding how the timeout attribute should work? The CF docs indicate that "Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value" -- but does a simple query like this one have suboperations?

I'm using CF9.

[Tim Cunningham]

If you run the query directly on the SQL machine, how long does it take? Less than one second?

[cherdt]

Good question. Unfortunately, I don't have access to the machine.

[Tim Cunningham]

Also what type of datasource did you set up in the CFAdministrator?

[cherdt]

For the test example, I've been using an Oracle database. (Although for the real issue I'm trying to address, it is an ODBC socket.)

[cherdt]

I just ran the query in SQL*Plus and the autotrace show that it is only taking 1 second for the select statement and 1 second for the table access full--so if those are the suboperations vis-a-vis the CF documentation, then they are not exceeding 1 second. Looks like I need a bigger table for my test.

[Tim Cunningham]

Good catch, that is what I was wondering, if the actual query doesn't take a second I wouldn't expect it to time out. Displaying the results could take longer than one second if it is a lot of data.

Tim

[cherdt · Accepted solution]

It turned out that in both my test case and the actual case I was trying to solve, the queries were not exceeding the 1 second threshhold.

It ended up being the database login time that was excessive, not the actual query time. In the CF administrator I set the Login Timout to 1 second (under the Advanced Settings). After I applied that, the query would throw a database error when the login time exceeded 1 second. (The login time does not, apaprently, factor into the cfquery timeout.)

Thanks to Tim and Dan for the help!

[cherdt]

According to Charlie Arehart on New for CF9 (and 9.0.1): a query timeout that really works, with a caveat, the timeout attribute of the cfquery tag "generally does not work."

As mentioned in the same post, there is a query timeout option available in the CF Administrator for certain data source types, but unfortunately not for the ODBC Socket that I'm using.

Additionally, the CF9 docs indicate, regarding the timeout attribute, that "For JDBC statements, ColdFusion sets this attribute. For other drivers, see the driver documentation." So it's possible that the timeout attribute does work, but only for some drivers.

[Dan_Bracuk]

I just ran queries with 1 second timeouts against redbrick, oracle, and sql server databases and in all three cases I got error messages saying the query had timed out.

Don't ignore Tim Cunningham's reply.  It was well thought out.

[cherdt]

Thanks for running those tests, it's good to know that it should work as expected.

(I wasn't ignoring Tim--he posted his question as I was adding an unrelated comment.)