Coldfusion 9 CFTransaction/CFQuery Timeouts

Forum|Forum|13 years ago
April 12, 2013
1 reply
2931 views

I have a script that is pulling in data from an excel document using cfspreedsheet, performing data checks, and inserting the data into the appropriate tables. Each XLS document only has about 1000 records. The scripts are wrapped in a CFTransaction tag to ensure rollback if the script errors.

When I run these scripts they will succeed if I'm only importing around 400 records or less. If I try to import any more than that I receive a 500 server error from IIS. I'm guessing that this is some sort of timeout issue. In order to try and force the success of the script, I upped the requesttimeout setting to 900000. This did not solve the problem.

Can anyone tell me how to troubleshoot and/or fix this problem?

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Depending on what's available to you, transferring work from ColdFusion to your database might be a good idea. As a minimum, see if it's appropriate to throw all the data into a staging table and doing your validation with database queries.

B

BobKlaasAuthor

Inspiring

I get your logic. However, I would still have to read in the data from an XLS to put it into a staging table. So, this step wouldn't be eliminated. Also, with the amount of data I'm loading from CFSPREEDSHEET to create a ColdFusion query, I don't beleive I should be having these issues. Were talking less than 1000 records.

I really have a suspicion that there is a setting that is timing out my script. I don't know where this setting might be, but it makes sense from a technical standpoint. When I run the import for 400 records, they import in under 15 seconds. So, why would 500 records throw a 500 error?

My guess is that there is a memory limit of some sort that is preventing my script from fully executing. We are running x64 CF9 and we have a BEEFY server, so I don't see how this amount of data could cause a problem.

K

kaxig99

Participant

BobxMarley, did you solve this issue?

I have the exact same problem

CF 9 on a Mac OSX server, with Unix.

Trying to import a excel spreadsheet with 10.000 rows and about 15 columns.

It runs very fast up to about 6.000 rows, then it slows down more and more until it stops, and then I get a 500 error.

First I hade the entire loop within a cftransaction, but I've also tried to have the cftransaction inside the loop.

I have about 4-5 sql queries within the loop that puts the data into different tables.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.