It is one of those things, some say avoid them at all costs but in some cases they can be a good addition to the arsenal.
"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Ben Nadel To: CF-Talk Sent: Wed Dec 27 12:45:26 2006 Subject: RE: Creating text files w/ 1mil + lines 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:265080 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
