Highlighted

Memory leak/overload when looping by index over a large query and updating each DB record

New Here ,
Dec 03, 2014

Copy link to clipboard

Copied

I am importing a CSV file into a temporary table and then running a select query that joins my actual database table with the temporary table, looking for any changes in the data. If changes exist, the select query is looped from 1 to #recordCount# and an update is applied to each record via cfquery. It runs very quickly (much more quickly than looping the query itself), but my memory spikes and overloads after about 1500 updates are completed. I need to be able to do upwards of 20000 at a time, without killing my system. I have tried manually setting the runtime garbage collection to trigger after X number of loops, but that doesn't seem to help. I am running CF8. See below for loop example:

<cfloop from="1" to="#updatedRecordsQuery.recordCount#" index="a">

<cftry>

                <cfquery datasource="#db#" name="doUpdate">

                    UPDATE

                        CI

                    SET

                        firstname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#updatedRecordsQuery.firstname#" />,

                        lastname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#updatedRecordsQuery.lastname#" />,

                        etc, for about 15 various fields

                    FROM

                        client_info CI

                    WHERE

                        CI.client_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#updatedRecordsQuery.client_id#" />

                </cfquery>

          

                <cfcatch type="database">

                    <cfset local.updateErrorList = listappend(local.updateErrorList,updatedRecordsQuery.client_id) />

                    <cfset local.error = true />

                </cfcatch>

           </cftry>

</cfloop>

Views

314

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Memory leak/overload when looping by index over a large query and updating each DB record

New Here ,
Dec 03, 2014

Copy link to clipboard

Copied

I am importing a CSV file into a temporary table and then running a select query that joins my actual database table with the temporary table, looking for any changes in the data. If changes exist, the select query is looped from 1 to #recordCount# and an update is applied to each record via cfquery. It runs very quickly (much more quickly than looping the query itself), but my memory spikes and overloads after about 1500 updates are completed. I need to be able to do upwards of 20000 at a time, without killing my system. I have tried manually setting the runtime garbage collection to trigger after X number of loops, but that doesn't seem to help. I am running CF8. See below for loop example:

<cfloop from="1" to="#updatedRecordsQuery.recordCount#" index="a">

<cftry>

                <cfquery datasource="#db#" name="doUpdate">

                    UPDATE

                        CI

                    SET

                        firstname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#updatedRecordsQuery.firstname#" />,

                        lastname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#updatedRecordsQuery.lastname#" />,

                        etc, for about 15 various fields

                    FROM

                        client_info CI

                    WHERE

                        CI.client_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#updatedRecordsQuery.client_id#" />

                </cfquery>

          

                <cfcatch type="database">

                    <cfset local.updateErrorList = listappend(local.updateErrorList,updatedRecordsQuery.client_id) />

                    <cfset local.error = true />

                </cfcatch>

           </cftry>

</cfloop>

Views

315

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 03, 2014 0
Most Valuable Participant ,
Dec 03, 2014

Copy link to clipboard

Copied

You can simplify your code a bit by looping over the updatedRecordsQuery directly, instead of doing an index loop:

<cfloop query="updatedRecordsQuery">

Then inside your loop, you don't need the loop index references at all, so "updatedRecordsQuery.firstname" becomes "updatedRecordsQuery.firstname".

That probably won't address your performance issue though.  Looping that many records is going to be resource intensive.  Since you are already storing the new data in a temporary table on the database server, it seems like you could try to offload the process of updating your "CI" table to your database server as well.  Maybe you can write a stored procedure that you can call from CF using <cfstoredproc> or <cfquery>.  What DBMS are you using?

-Carl V.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 03, 2014 0
Participant ,
Dec 08, 2014

Copy link to clipboard

Copied

I would suggest to use select update instead of looping over query object and update each row one-by-one.

Procedure:

- Insert your CSV data into temp table.

- Use a select update SQL query to update the changed data instead of looping over a select query.

Example:

UPDATE
  
Table
SET
  
Table.col1 = other_table.col1,
  
Table.col2 = other_table.col2
FROM
  
Table
INNER JOIN
  other_table
ON
  
Table.id = other_table.id

NOTE: You can put all your scripts in a Procedure.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 08, 2014 0
ranger LATEST
Participant ,
Dec 09, 2014

Copy link to clipboard

Copied

Hi,

Maybe this is a dumb question but how are you detecting updated rows in the temp table.

Is there a "update date" field that indicates a change or are you scanning/comparing multiple fields.

If the answer is the second option maybe you can add a date stamp to the application that creates the csv file and add a date variable to your temp table query WHERE statement.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 09, 2014 0