Jochem, This is very interesting. I have never really learned very much about CURSORS but they seem quite cool. I didn't realize that you could make any SQL objects that would live past more than one CFQuery tag. I will definitely give this a try for experimentation sake. CURSORS do seem like something that are very useful... Are there any downsides to CURSORS that I should know about right off the bat?
...................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 27, 2006 3:08 AM To: CF-Talk Subject: Re: Creating text files w/ 1mil + lines 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-Massi > ve>-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:265067 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
