Storing Too Much in ColdFusion Variables – My Lesson Learned
As part of a recent a ColdFusion project, I needed to query an Oracle database and accept anywhere from 0 to 30k-40k records, then export into a CSV. It didn’t seem like too difficult of a problem at first. I used Toad to build the perfect query. Once optimized to pull down the max amount of records in the least amount of time, I copied to ColdFusion, built my front end, tested, and released. How’d I do it? Seemed simple — I setup a variable with a CSV header row, and then appended the variable record by record with the new data, and at the end CFFILE’d it all into a temp file which I then presented to the user.
Almost immediately after releasing the page into the application, I started having memory issues on my ColdFusion box. I’d watch the task list and just see JRUN rise to unreasonable percentages, and every so often – just hang. Killing the process and restarting the service was the only solution. It was obvious that the report was the problem, and it needed to be fixed ASAP.
The solution is something that should have come to mind immediately. Trying to store upwards of 30k records in a variable probably was not the smartest solution I’ve come up with, and probably was maxing out those few gigs of RAM that I have allocated to that box. My solution? Instead of writing the data out to a temp file when I had collected it all to begin with, I change the code so that it would export after I had a complete record, and then clearing the variable. Not only did this alleviate all of the JRUN issues I was having, but also sped up the report significantly.
My lesson learned? Just because you can do something, doesn’t mean it’s the best way; take time to think. Unfortunately deadlines will often conflict with your ability to reason with your code 
