Query Results too much for memory

[naMwodahS]

I'm running a simple CFQUERY that returns 2 columns, an integer and a CLOB,
from my table. I then write out the data to a file for downloading. The row
count is getting rather large, sometimes over 200,000. When I watch the memory
usage, I've seen it run incredibly large, in excess of 1GB. This has choked
ColdFusion and locked up several associated sites, requiring a CF restart.

As a temporary solution, I'm running a loop so it only returns x amount of
rows at a time, depending on the amount of data that will be queried.
Unfortunately if I run this query several times concurrently, usually around 5,
I'll crash the server.

Is there anyway for ColdFusion to store the queried data to disk rather than
memory? Or does anyone have any optimization suggestions?

Thank you!
Jason

[paross1]

I suppose that one way around this would be to break this into two queries. The
first query might return only the primary key from your table ,based on all of
the search criteria from your original "simple" query. Then, you would enclose
your second query within a cfloop or cfoutput, where you would loop through
your list of primary key values, one at a time, and perform the search for your
three columns, then write the data out to the file for each row returned on
each pass through the loop. Not very efficient nor fast, but it might prevent
you from running out of memory. (I haven't tried this myself, so this is just
speculation.)

Phil

[naMwodahS]

Well I finally figured out the problem. After much headaches, I did some
searching and found [url]http://www.bpurcell.org/blog/index.cfm?mode=entry&entry=988[/url]
to determine what version of the driver you are running.

Stay safe and keep your drivers updated!

Thanks for your suggestions!

Jason