CODEWITHSUNDEEP
exceptioncoldfusioncfwheels
balytskyi
balytskyi

Reputation: 303

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

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.

Upvotes: 3

Views: 262

Answers (3)

Chris Peters
Chris Peters

Reputation: 18090

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.

Upvotes: 0

LarZuK
LarZuK

Reputation: 826

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

Upvotes: 1

orangepips
orangepips

Reputation: 9971

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.

Upvotes: 5

Related Questions