CFStoredProc Timing Out

Question

I have a very basic app that plugs data into a stored procedure which in turn returns a recordset. I've been experiencing what I thought were 'timeouts'. However, I'm now no longer convinced that this is what is really happening. The reason why is that the DBA and I watched sql server spotlight to see when the stored procedure was finished processing. As soon as the procedure finished processing and returned a recordset, the ColdFusion page returned a 'timeout' error. I'm finding this to be consistent whenever the procedure takes longer than a minute. To prove this, I created a stored procedure with nothing more than this:

BEGIN

  WAITFOR DELAY '00:00:45';
  SELECT TOP 1000 * 
  FROM AnyTableName

END

If I run it for 59 seconds I get a result back in ColdFusion. If I change it to one minute:

WAITFOR DELAY '00:01';

I get a cfstoredproc timeout error. I've tried running this in different instances of ColdFusion on the same server, different databases/datasources. Now, what is strange, is that I have other procedures that run longer than a minute and return a result. I've even tried this locally on my desktop with ColdFusion 10 and get the same result. At this point, I'm out of places to look so I'm reaching out for other things to try. I've also increased the timeout in the datasource connections and that didn't help. I even tried ColdFusion 10 with the timeout attribute but no luck there either. What is consistent is that the timeout error is displayed when the query completes.

Also, I tried adding the WAITFOR in cfquery and the same result happened. It worked when set for 59 seconds, but timed out when changed to a minute. I can change the sql to select top 1 and there is no difference in the result.

have you tried using cfsetting to extend the page timeout? `` — genericHCU, Apr 12 '13 at 15:37
What is the query timeout setting in your ColdFusion administrator for that datasource? I believe that in previous versions of ColdFusion the timeout setting of the cfquery tag didn't actually work. Well, it worked as you are describing. The tag would wait until the database server responded (good or bad) and then throw the timeout. Supposedly the datasource timeout setting in the administrator resolves this. See this: [New for CF9 (and 9.0.1): a query timeout that really works, with a caveat](http://www.carehart.org/blog/client/index.cfm/2010/7/14/hidden_gem_in_cf9_admin_querytimeout) . — Miguel-F, Apr 12 '13 at 15:46

score 3 · Answer 1 · answered Apr 12 '13 at 19:16

3

Per the comments, it looks like your request timeout is set to sixty seconds.

Use cfsetting to extend your timeout to whatever you need.

<cfsetting requesttimeout = "{numberOfSeconds}">

answered Apr 12 '13 at 19:16

genericHCU

4,394
2
22
34

score 0 · Answer 2 · answered Apr 13 '13 at 13:51

0

The default timeout for all pages is 60s, you need to change this in the cfadmin if it is not enough, but most pages should not run this long. Take some time to familiarise yourself with the cfadmin and all its settings to avoid such head scratching. As stated use cfsetting tag to override for specific pages.

answered Apr 13 '13 at 13:51

snake

732
1
6
11

This wasn't a matter of me not being familiar with the CF admin. It is my understanding that CFStoredProc ignores the timeout bc it relies on an external source outside of CF. It would receive the resultset from sql, but throw a timeout at the exact time. This is where the 'headscratching' started. I spent plenty of time in the CF Admin adjust query timeout settings in the DNS and everything else. I would have thought that the timeout setting in CF10 would have fixed it but that didn't work either. This is why I went looking for help, not bc I didn't research it on my own first. Tx... – user2274650 Apr 15 '13 at 16:49
1

The page timeout actually doesn't work proeprly if the query takes too long to execute as this is a native java process so cf cannot kill the running request while it is connected to database, however if the query takes more than 60s to run but does actually run, when the resultset is finally returned to cf the page will then throw a timeout because it has been running more than 60s. Hope that makes sense. Sorry for the edits, the form submits if you hit enter in a textarea? odd – snake Apr 15 '13 at 20:15

CFStoredProc Timing Out

2 Answers2