Is there a solution to this cfqueryparam memory leak?

Question

Updates:

• I have submitted the bug to Adobe and referenced this SO question

• In my real-world code where the problem occurred I decided to just remove my use of cfqueryparam. I am now using a custom function to format the param based on type. There are security and speed concerns that I will have to deal with but it gets the particular process working acceptably under current load.

• In the future I am planning on going to process that pulls the data files into temporary tables in the database. I'll then perform operations on the data and transfer data to live tables using SQL as much as possible, instead of relying on ColdFusion

---

I am having a problem with looping over queries using cfqueryparam tags while inserting data. (I have not tested with select or update queries). The looping progressively takes up more memory that is not released until the request is done. However, the problem only occurs when looping over a query while in a function.

It appears to be very sensitive to the number of cfqueryparam tags used. In this example there are 15 values being inserts however in my code that actually needs this to work I am inserting an unknown number of values that can make the problem more severe.

Below is code that shows the problem. Give it a datasource name (tested on MSSQL) and it will create a tmp table and insert records as example with and without being in a function. Memory usage is display before, after the non-function loop, then after the in-function loop. It also requests garbage collection and waits 10 seconds before outputting memory info to ensure it is displaying info as accurately as possible.

In my experience with this particular test the in-function loop resulted in over 200mb of memory being used. In my real world uses it crashes ColdFusion :-(

<cfsetting enablecfoutputonly="true">
<cfsetting requesttimeout="600">

<cfset insertCount = 100000>
<cfset dsn = "TmpDB">

<cfset dropTmpTable()>
<cfset createTmpTable()>

<cfset showMemory("Before")>
<cfflush interval="1">

<cfloop from="1" to="#insertCount#" index="i">
    <cfquery name="testq" datasource="#dsn#">
        INSERT INTO tmp ( [col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[col11],[col12],[col13],[col14],[col15] )
        VALUES ( <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR"> )
    </cfquery>
</cfloop>

<cfset showMemory("After Non-Function INSERTS")>
<cfflush interval="1">

<cfset funcTest()>

<cfset showMemory("After Function based INSERTS")>

<cfset dropTmpTable()>

<cffunction name="funcTest" output="false">
    <cfset var i = 0>
    <cfset var testq = "">
    <cfloop from="1" to="#insertCount#" index="i">
        <cfquery name="testq" datasource="#dsn#">
            INSERT INTO tmp ( [col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10],[col11],[col12],[col13],[col14],[col15] )
            VALUES ( <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR">, <cfqueryparam value="TestValue" cfsqltype="CF_SQL_CHAR"> )
        </cfquery>
    </cfloop>
</cffunction>

<cffunction name="showMemory" output="true">
    <cfargument name="label" required="true">

    <cfset var runtime = "">
    <cfset var memoryUsed = "">
    <cfset requestGC("10")>
    <cfset runtime = CreateObject("java","java.lang.Runtime").getRuntime()>
    <cfset memoryUsed = (runtime.totalMemory() - runtime.freeMemory()) / 1024 / 1024>
    <cfoutput>
        <h2>#arguments.label#</h2>
        Memory Used: #Round(memoryUsed)#mb
    </cfoutput>
</cffunction>

<cffunction name="requestGC">
    <cfargument name="waitSeconds" required="false" default="0" type="numeric">
    <cfscript>
        createObject("java","java.lang.Runtime").getRuntime().gc();
        createObject("java", "java.lang.Thread").sleep(arguments.waitSeconds*1000);
    </cfscript>
</cffunction>

<cffunction name="dropTmpTable" output="false">
    <cftry>
        <cfquery datasource="#dsn#">
            DROP TABLE tmp
        </cfquery>
        <cfcatch type="database"></cfcatch>
    </cftry>
</cffunction>

<cffunction name="createTmpTable" output="false">
    <cfquery datasource="#dsn#">
        CREATE TABLE tmp(
            col1 nchar(10) NULL, col2 nchar(10) NULL, col3 nchar(10) NULL, col4 nchar(10) NULL, col5 nchar(10) NULL, col6 nchar(10) NULL, col7 nchar(10) NULL, col8 nchar(10) NULL, col9 nchar(10) NULL, col10 nchar(10) NULL, col11 nchar(10) NULL, col12 nchar(10) NULL, col13 nchar(10) NULL, col14 nchar(10) NULL, col15 nchar(10) NULL
        )  ON [PRIMARY]
    </cfquery>
</cffunction>

Just to show that memory can be released during an operation, here is example code that builds up a larger struct and shows memory used before and after the variable is overwritten and garbage collected. In my run of this memory used after population is 118mb and after overwriting and garbage collection it is 31mb.

<cfset showMemory("Before struct creation")>
<cfflush interval="1">

<cfset tmpStruct = {}>
<cfloop from="1" to="1000000" index="i">
    <cfset tmpStruct["index:#i#"] = "testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue testvalue">
</cfloop>

<cfset showMemory("After struct population")>
<cfflush interval="1">

<cfset tmpStruct = {}>
<cfset showMemory("After struct overwritten")>

Answer 1

Do you have debugging on in Administrator?

If so, even if you've got showdebugoutput="false", CF will be keeping debug information about all of those queries, and with that many queries, the debugging information could quickly build up.

Also, if you've really got 80,000 rows to insert, you probably want to be doing this a different way - e.g. generating an import script that runs directly against the DB, (without CF/JDBC getting in the way).