Watch Out for CFQUERYPARAM and Garbage Collection

In the past few months I've spent a fair amount of time doing large data transformations for a couple projects, in to and out of MSSQL databases. In both projects, I've ended up refactoring my code to eliminate CFQUERYPARAM on oft-run queries, because it seems to leak memory (yes, I have debugging disabled), as well as be less performant.

In both cases, the primary offender was repetitive INSERT statements, ranging from 10,000 executions per request up to around 50,000. Stripping the CFQUERYPARAM tags gave about a 40% improvement in performance (batches in ~4 seconds instead of 7-8 seconds), and a huge improvement in memory consumption.

I've also noticed that while CF does do garbage collection within requests (thank god that got fixed), it doesn't do aggressive collection. A few well placed System.gc() calls have been a lifesaver for the larger processes, particularly when combined with judicious use of structDelete to eliminate non-essential references. Just using structDelete makes a difference, but the effect is magnified with the explicit collections.

NB: neither of these are solutions to be taken lightly; both have potentially serious side effects, and go against generally accepted best practices. Not using CFQUERYPARAM exposes you to SQL injection attacks among other things, and garbage collection is expensive in terms of processor utilization.

NB: these results are for ColdFusion 8 (not 8.0.1) against 64-bit MSSQL 9.0.3054 Developer Edition, using the stock CF driver. Other CF runtimes and other databases (and possibly even other MSSQL database drivers) will undoubtedly yield different results.