这个 cfqueryparam 内存泄漏有解决方案吗? [英] Is there a solution to this cfqueryparam memory leak?
问题描述
更新:
我已将错误提交给 Adobe 并引用了这个 SO question
I have submitted the bug to Adobe and referenced this SO question
在发生问题的实际代码中，我决定只删除对 cfqueryparam 的使用.我现在使用自定义函数来根据类型格式化参数.我必须处理安全和速度方面的问题，但它可以让特定进程在当前负载下正常工作.
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.
将来我计划将数据文件拉入数据库中的临时表中.然后我将尽可能使用 SQL 对数据执行操作并将数据传输到实时表，而不是依赖 ColdFusion
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
我在插入数据时使用 cfqueryparam 标记循环查询时遇到问题.(我没有测试过选择或更新查询).循环逐渐占用更多内存，直到请求完成才释放.但是，仅当在函数中循环查询时才会出现此问题.
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.
它似乎对使用的 cfqueryparam 标签的数量非常敏感.在此示例中，插入了 15 个值，但是在我的代码中实际上需要它才能工作，我插入了未知数量的值，这可能会使问题更加严重.
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.
下面是显示问题的代码.给它一个数据源名称(在 MSSQL 上测试)，它将创建一个 tmp 表并插入记录作为示例，无论是否在函数中.内存使用情况显示在非函数循环之前、之后、函数内循环之后.它还请求垃圾收集并在输出内存信息之前等待 10 秒，以确保它尽可能准确地显示信息.
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.
根据我对这个特定测试的经验，函数内循环导致使用了超过 200mb 的内存.在我的现实世界中使用它会使 ColdFusion 崩溃 :-(
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>
只是为了说明可以在操作期间释放内存，下面是示例代码，它构建了一个更大的结构，并显示了在变量被覆盖和垃圾收集之前和之后使用的内存.在我的运行中，填充后使用的内存为 118mb，覆盖和垃圾收集后为 31mb.
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")>
推荐答案
在大型查询循环中防止 cfqueryparam 内存泄漏的方法是不使用 cfqueryparam.然而，更广泛的答案是避免 CF 的低效率和内存泄漏是在这些情况下不使用 CF.我当时将特定过程的负载提高到可接受的水平，但从长远来看，我会用另一种语言重写它，可能直接在数据库引擎中使用 C#.
The way to prevent memory leaks from cfqueryparam in a large loop of queries was to not use cfqueryparam. However a broader answer is on avoiding CF's inefficiencies and memory leaks is to not use CF in these situations. I got the particular process to an acceptable level for the load at the time but in the long run will be rewriting it in another language, probably C# directly in the database engine.
这篇关于这个 cfqueryparam 内存泄漏有解决方案吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
