Ben Nadel wrote:
>
> I threw together a well-commented example of something that might
> accomplish what you are talking about. It uses smaller record sets and
> buffered output via the Java buffered output object. I am no Java coder,
> so I am not always 100% sure what I am doing, but this might help point
> you in a particular direction:
>
> http://www.bennadel.com/blog/446-Writing-Enormous-Files-Based-On-Massive>-Record-Sets-In-ColdFusion.htm
Since I am no Java coder either I won't comment on your use of Java to write
the resultset to a file. Your way of splitting up the query in multiple
resultsets is not very efficient though. The database needs to re-sort the
entire resultset on every iteration. This can be done much cleaner an efficient
through a cursor:
<cftransaction>
<cfquery datasource="pg_scorpio">
DECLARE bigCursor NO SCROLL CURSOR FOR SELECT * FROM memtest FOR READ ONLY
</cfquery>
<cfset fetch.recordcount = TRUE>
<cfloop condition="#fetch.recordcount#">
<cfquery name="fetch" datasource="pg_scorpio">
FETCH FORWARD 1000 FROM bigCursor
</cfquery>
</cfloop>
<cfquery datasource="pg_scorpio">
CLOSE bigCursor
</cfquery>
</cftransaction>
This works on PostgreSQL and should work on any other database with minor
changes.
I also ran some tests on the memory consumption of large recordsets. I created
a table using:
create table memtest (ID INTEGER, string VARCHAR(16));
Then I filled it with a million records. Reading the entire table into CF
peaked at 220 MB. Using the code above with a cursor the memory consumption got
lost in the garbage collector noise.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265064
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
