Coldfusion CFQUERY time limit exceeded

Question

I have a saved XML file which is 7.1mb and contains over 1000 properties and all the info for those properties. My cfscript parses and then inserts the properties into the property table along with features and image URLs to their respective tables.

However, the process bombs out usually after it has passed 250 records and then gives me this error:

 The request has exceeded the allowable time limit Tag: CFQUERY 

I have put a timeout value of 9000000 in my cfquery tag and that does nothing. I don't know what else to do to resolve this.

Solution

The error says "The request has exceeded the allowable time".

It only tells you what tag was responsible so you know what CF was doing in that moment. Increasing the query timeout does not increase the overall request timeout.

<cfsetting requesttimeout="500">

https://wikidocs.adobe.com/wiki/display/coldfusionen/cfsetting

In parallel you should try to rewrite the query to take less time as well.

Comments

[Scott Stroz]

Quick solution is what Tomalak posted, however, I am curious about the process by which you are parsing the XML - which may be the underlying problem. Seems odd you would have a time out on the file. Can you provide some code that shows the process, and possibly a snippet of the XML?

[johnnyc0506]

Tomalaks solution worked, the code is rather long, i will update my question in an hour and paste some of my code and some of the xml code

[Dan Bracuk]

Depending on your database engine, you may be able to process the xml file without using coldfusion.

[johnnyc0506]

@ Tomalak, thanks for your answer, im trying it now. Basically i update the properties first, get the max id of the property and then insert images and features to their respective tables

[Tomalak]

Well, whatever you do, if it takes longer than your standard request timeout (30 seconds?) and is not complex reporting or other processing of a huge record set, something is very wrong.

[johnnyc0506]

thank you for your response, i will add that to the code and get the amount of time taken

[Dan Bracuk]

I just tried that in version 9 and got this error. Attribute validation error for CFSETTING. The value of the REQUESTTIMEOUT attribute is invalid. The value specified, 0.0, must be greater than 0.0.

[Anit Kumar]

You are correct @DanBracuk . It doesn't like the infinite value in CF9 but works with CF10 and CF11

[Leigh]

@johnnyc0506 - Totally unrelated to your question, but this comment caught my eye: "I..get the max id of the property and then insert ...". If you mean you are using select max(id) to get the id of record you just INSERT[ed] - don't ;-) It is not thread safe. Use a thread safe mechanism like result.generatedKey instead.