java.lang.OutOfMemoryError when saving a huge amount of records

Question

I have a problem with saving a huge amount of records to database using CFWheels. Here is an example:

<cfloop from="1" to="10000" index="i">
 <cfset var newUser = model("user").new()>
 <cfset newUser.name = "Test"&i>
 <cfset newUser.save()>
</cfloop>

This causes java.lang.OutOfMemoryError

Please help me how to solve this problem.

score 5 · Answer 1 · edited May 23 '17 at 12:12

Looping over multiple database calls leading to OOM is a known ColdFusion bug. Fortunately, there is a workaround, use <cfthread/>. You should be able to alter your code as such:

<cfloop from="1" to="10000" index="i">
 <cfset threadName = "thread" & createUuid()>
 <cfthread name="#threadName#">
  <cfset var newUser = model("user").new()>
  <cfset newUser.name = "Test"&i>
  <cfset newUser.save()>
 </cfthread>
 <cfthread action="join" name="#threadName#">
</cfloop>

In this situation, you're using the thread solely for its side effect, running in a different context so that it doesn't get retained on the heap. Thus the immediate join right after declaring the thread, so it's not actually running anything in parallel.

@Henry: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=85736 is the logged bug. AFAIK Adobe has not confirmed or commented on this issue. In practice it's relatively rare. — orangepips, Jun 01 '11 at 17:20

score 1 · Answer 2 · answered Jun 01 '11 at 12:41

1

You can try to run the Garbage collector: http://www.beetrootstreet.com/blog/index.cfm/2009/6/25/Clearing-ColdFusion-memory-using-garbage-collection-when-memory-gets-low

answered Jun 01 '11 at 12:41

LarZuK

746
1
8
16

3

This is dangerous at best. There are numerous articles about why having an application attempt to collect JVM itself is a bad idea. Instead understand how and why memory is filling up using tools like VisualVM and Eclipse Memory Analyzer. – orangepips Jun 01 '11 at 13:05

score 0 · Accepted Answer · answered Jun 02 '11 at 12:30

There are a couple fairly inefficient things going on here. First, it's generating 1,000 user objects, which isn't really a good idea to do in a single request in ColdFusion. Second, it's running 1,000 database queries, which isn't really a good idea to do in any programming language.

I would stop using model objects for a case like this and figure out how to condense the logic into a single database query. The ORM stuff in Wheels is generally very useful, but it has its limits in situations like this one.

For example, if you're using SQL Server 2008, you can do this inside your user model to keep everything under a single call to cfquery:

<cffunction name="batchCreate">
    <cfquery datasource="#get('dataSourceName')#">
        INSERT INTO
            #this.tableName()# (#this.columnNameForProperty("name")#)
        VALUES
            <cfloop from="1" to="10000" index="i">
                (<cfqueryparam cfsqltype="cf_sql_varchar" value="Test#i#">)
                <cfif i lt 10000>,</cfif>
            </cfloop>
    </cfquery>
</cffunction>

Of course, the query will look different if you're using MySQL or another database engine.

java.lang.OutOfMemoryError when saving a huge amount of records

3 Answers3