Coldfusion OutOfMemoryError (CF9/Wheels)

Question

I have a function which loops over a query and updates a database row for each item. After about 7000 iterations it's throwing an out of memory error - Java heap space. Is there anything obviously wrong with this code ?

<cfloop query=loc.fixItems>
    <cfset loc.count = loc.count + 1>
    <cfset var categoryName = loc.fixItems.categoryName>
    <cfinvoke component="Item" method="updateCode"
        itemId="#loc.fixItems.itemId#" code="#loc.fixItems.newCode#"/>

    <!--- Increment counter for category --->

    <cfif structKeyExists(categoryMap, categoryName)>
        <cfset var inc = structFind(categoryMap, categoryName) + 1>
        <cfset structUpdate(categoryMap, categoryName, inc)>    
    <cfelse>
        <cfset structInsert(categoryMap, categoryName, 1)>  
    </cfif>
</cfloop>

and in the update component:

<cffunction name="updateCode">
    <cfargument name="itemId" type="numeric" required="yes">
    <cfargument name="code" type="string" required="yes">

    <cfset var loc = {}>
    <cfquery name="loc.update">
        update items
        set code = <cfqueryparam value="#code#">
        where id = <cfqueryparam value="#itemId#">
    </cfquery>
</cffunction>

If at all possible you should not be using CF for this. *Re: loops over a query and updates a database row for each item* What is the source of the query? — Leigh, Nov 15 '13 at 15:47
You may want to consider the solution outlined in: http://stackoverflow.com/questions/20007935/updating-multiple-rows-from-a-cfquery-recordset — James A Mohler, Nov 15 '13 at 18:30

score 3 · Answer 1 · answered Nov 15 '13 at 15:55

3

Don't use cfinvoke to create your Item component every iteration of your fixItems query. Create it once before that using createObject and simply call the updateCode method each time directly on the object.

answered Nov 15 '13 at 15:55

duncan

31,401
13
78
99

This is the first thing I'd try. `` and `CreateObject()` are SLOW. Suggestions in other answer are good too. – Chris Peters Nov 15 '13 at 15:59

James A Mohler · Answer 2 · 2013-11-15T17:00:47.667

1

The following can be done:

Change your <cfqueryparam> to use the appropriate cf_sql type. Are code and id really strings?
Don't give your <cfquery> a name. You are not keeping the result anyway. var loc doesn't help either
Bump up you memory to the JVM Addtional approach Use Java 7 and G1GC
Every 100 to 1000 iterations do a forced Garbage Collect
Update your data in bulk. XML based table variables can do this.
Make your function silent
Consider ORM on this

edited Nov 15 '13 at 17:00

answered Nov 15 '13 at 15:35

James A Mohler

11,060
15
46
72

1

[You cannot force Garbage Collection](http://stackoverflow.com/questions/1147386/when-to-garbage-collect). If possible, performing a bulk operation in the db would be much better than 7K db calls. What is the source of this "query"? – Leigh Nov 15 '13 at 15:53
2

Even if it did work though, it is basically a band-aid. Better to look at the various suggestions and either find an alternative that with less o/h (bulk operation within the db). Failing that, try and reduce the impact ([reusing objects](http://stackoverflow.com/a/20005136/104223), etcetera). – Leigh Nov 15 '13 at 17:33

Coldfusion OutOfMemoryError (CF9/Wheels)

2 Answers2

Linked