All geek questions in one place

Is there a solution for this cfqueryparam memory leak?

Update:

  • I posted an Adobe error and referenced this SO question

  • In my real world code where the problem occurred, I decided to simply remove the use of cfqueryparam. Now I use a custom function to format the parameter based on the type. There are security and speed issues that I have to deal with, but it becomes acceptable under the current load.

  • In the future, I plan that you are going to process data files into temporary tables in the database. I will then perform operations on the data and transfer the data to live tables using SQL as much as possible, instead of relying on ColdFusion

I had a problem with query cyclization using cfqueryparam tags when inserting data. (I have not tested using select or update queries). Slowing down gradually increases the amount of memory that is not displayed until the request is completed. However, the problem only occurs when going on demand during a function.

It seems very sensitive to the number of cfqueryparam tags used. In this example, 15 values ​​are inserts, however, in my code that really needs this, I insert an unknown number of values ​​that can make the problem more serious.

Below is the code that shows the problem. Give it the name of the data source (tested in MSSQL), and it will create a tmp table and insert records as an example with and without a function. The memory usage is displayed before, after a non-functional cycle, then after a function cycle. It also requests garbage collection and waits 10 seconds before the memory information is displayed to ensure that it is displayed as accurately as possible.

In my experience with this particular test, the built-in loop led to the use of more than 200 mb of memory. In my real world, this leads to ColdFusion crashes: - (

<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>

, , , , . , , 118 , - 31 .

<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")>
+3
10

cfqueryparam , cfqueryparam. , CF , CF . , , , # .

0

Administrator?

, showdebugoutput="false", CF , .


, 80 000 , , , - - . script, ( CF/JDBC ).

+4

, ? , , .

. " ", , / insterting.

+3

, , - - .

, , , .

<cffunction name="funcTest" output="false">
    <cfargument name="from" />
    <cfargument name="to" />
    <cfset var i = 0>
    <cfset var testq = "">
    <cfloop from="#arguments.from#" to="#arguments.to#" index="i">
        <cfquery name="testq" datasource="#dsn#">
            ...
        </cfquery>
    </cfloop>
</cffunction>


<cfset BlockSize = 100 />
<cfloop index="CurBlock" from="1" to="#(InsertCount/BlockSize)#">

    <cfset funcTest
        ( from : CurBlock*(BlockSize-1) + 1
        , to   : CurBlock*BlockSize
        )/>

</cfloop>
+2

.

http://misterdai.wordpress.com/2009/06/24/when-not-to-use-cfqueryparam/

. , cfqueryparam, . , , SQL. , . SQL . , SQL, 50 ( ) ArrayToList CfQuery. , , .

. - , ColdFusion , , ,

+2

, cfqueryparam - type = "CF_SQL_CHAR". ? , , .

+1

, CF8... , CF7...

"Max Pooled Statementments" ( ) ... ...

... CF, ... 100% - ...

+1

"". cffunctions. , .

, :

<cfquery name="testq" datasource="CongressPlus">


<cfquery name="variables.testq" datasource="CongressPlus">

,

0

, CF , . GC . , .

, - CF . , 80K CF, , .

, , , , CSV XML ; MSSQL TON .

, , - MSSQL, BCP BULK INSERT, , .

, , CF, - , MMSQL . MSSQL BCP BULK INSERT INFINITELY , CF-.

0

I have no idea if this will fix the problem, but what I usually do when I have several attachments, such as this is the loop of the SQL statement itself, and not the whole cfquery.

So, instead of:

<cfloop from="1" to="#insertCount#" index="i">
    <cfquery name="testq" datasource="#dsn#">
        ...
    </cfquery>
</cfloop>

I do:

<cfquery name="testq" datasource="#dsn#">
    <cfloop from="1" to="#insertCount#" index="i">
        ...
    </cfloop>
</cfquery>

Therefore, instead of having a multiple call in the database, you only have one big one.

I have no idea how this will affect the memory leak problem, but I never experienced memory leaks doing it this way.

0
source

Source: https://habr.com/ru/post/1708226/

More articles:


All Articles