Coldfusion 9 CFTransaction/CFQuery Timeouts

[BobKlaas]

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?

<cfsetting                    requesttimeout="900000"                    showdebugoutput="true"                    enablecfoutputonly="false"          />

[Dan_Bracuk]

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.

[BobKlaas]

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.

[kaxig99]

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.

[BobKlaas]

Hey kavig99,

So after I ran the server monitor and analyzed my script, I realized how much SQL was actually executing. I had over 100K sql statements executing everytime I ran the import. This is why it was crashing. The solution for me was to restructure my SQL to be more efficient.

For example, I had to insert a bunch of child records into a lookup table. I was looping over these child records in ColdFusion and inserting them one at a time. This was causing major performance issues. So, instead of inserting them one by one, I figureout out I can insert the whole group at once by restructuring my SQL. The easiest way I found to do this was to use a INSERT INTO with a SELECT statement to retreive the records you want to insert. (See Below)

Instead of doing this for each record: INSERT INTO myTable VALUES('#Value1#','#Value1#')

Do this once for all records: INSERT INTO myTable SELECT '#GradeUID#', SizeGroupUID FROM myTable2 WHERE SizeGroupUID IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#SizeRangeUIDList#" list="true">)

Just toy around with restructuring your SQL. After I restructured my SQL my script worked awesome. It was also running 90% less SQL to performe the exact same task.

Good Luck Man!

-Bob

[kaxig99]

Good to hear from you Bob!

Merging my sql statements together is  exactly what I have been doing the last 24 hours, unfortunately without luck.

For inserts, I have used this syntax:

INSERT INTO table (column_1,column2) VALUES

('value 1 for column 1','value 1 for column 2')

,(value 2 for column 1','value 2 for column 2')

,(value 3 for column 1','value 3 for column 2')

..etc

The problem is that the majority of my queries are update queries, so I changed my DSN to be able to handle multiple queries within the same <cfquery> by adding "allowMultipleQuery=true" in the text box "connection string" under advanced settings for my DSN.

Then I merged chunks of 250 update statements together throughout my loop (of about 6000 rows), and then outputted them in a <cfquery>, then starting on a new chunk and so on. This decreased the number of queries vastly off course, but my request ran even slower.

I produce the sql statements as strings (off course), and therefore I can't use any cfqueryparams, but that's ok. I read that they could cause memory leaks in some situations, so in this case, I was only glad to be able to remove them (I ALWAYS use them otherwise, to avoid sql injection off course).

I should also mention that I have a cftransaction around every 250 rows, that commits the insert and update queries, throughout the loop (every 250th row). From the beginning I had the transaction around the entire loop, which worked pretty well up to about 5000 rows.

Now I'm looking into memory leak issues. One guy wrote that he had a similar project like ours, but had problems with the memory usage, so he said he wrote a function that imported a part of the content, then "did a gateway call to the CFC" at the end, to the same function, with an argument that told the function to import the next part and so on. By doing this he said that the memory was flushed during the import, between the different parts of the import, resolving his issue. You can read about it here (one of the replies)

http://stackoverflow.com/questions/2364682/memory-implications-of-returning-a-query-from-a-cfc

I'm not sure though what he means with "gateway call". If it has anything to do with event gateways? If so, I'm really lost. Never tried that.

Anyway, now I will try to run the cf debugger, and see how my queries are doing, hopefully it will bring some clarity. I'm in Sweden, so it's getting late. 10:14 PM.

/Jörgen

[BobKlaas]

One thing that helped me to debug this issue was writing all the SQL out to a log file. I tried to run all the SQL in SQL Server Management Studio and it was super slow. That indicated to me that it was my SQL that was the issue.

Also, make sure you have debugging set to OFF when running the import. Especially if you have Robust Exception Information enabled in your CFADMIN. All that debugging information will make a script crash for sure.

<cfsetting showDebugOutput="No">