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.

Answer 1

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}">

Answer 2

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.