Jochem,
I was just talking about the actual file writing. I 100% agree re: the
DB query overhead in that case.
To avoid needing this much juice for the query, assuming you don't have
the resources you'd need to proceed, you could try making your own "row
buffer" by writing your CFQueries with a MaxRows attribute, and
(Assuming you have something sequential) you can keep iterating through
#maxRows# rows at a time, where SequentialColumn >
MaximumValueFromPreviousLoop. Then, you can just loop through that
until you're out of results.
EX:
(assuming you already have a java.io.PrintWriter or something optimized
for buffered output, called "output")
Let's assume you have some sort of ID:
<cfset rows=-1>
<cfset BufferSize="250">
<Cfset myMaxValue=-1>
<CFLOOP condition="#rows# neq 0>
<CFQUERY name="q" .... MaxRows="#BufferSize#">
SELECT * from my Table
WHERE ID > <CFQUERYPARAM
CFSQLTYPE="cf_sql_integer" value="#myMaxValue#">
order by ID ASC
</CFQUERY>
<Cfset rows=q.recordcount>
<cfoutput query="q">
<cfscript>
output.println("#a#,#b#,#c#"); // or whatver u need to
do
</cfscript>
<cfset MyMaxValue="#ID#">
</cfoutput>
</CFLOOP>
Just an idea...
dov
-----Original Message-----
From: Chris Ditty [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 26, 2006 2:53 PM
To: CF-Talk
Subject: Re: Creating text files w/ 1mil + lines
WOW. All I can say is duhhhh. This is way over my little CF head. :)
On 12/26/06, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
> Katz, Dov B \(IT\) wrote:
> > You shouldn't need to take memory into consideration if you use a
> > java.io.BufferedOutputStream and java.io.PrintWriter etc...
>
> Yes you should. Let's say we are writing 1.3 million, 20 character
records from the database to a .csv. First CF queries the database and
buffers the resultset (26 MB). In the proces it needs to convert from
the database charset to its internal charset (26 MB, maybe 52 MB). Then
you loop over the resultset and generate the .csv which you hold in
memory (26 MB). In the process of your 6 million loops you generate 8
bytes of whitespace for each iteration (10.4 MB). Then you write it out.
>
> Of the total memory consumption of 94.4 MB (120.4 MB if you are
unlucky with the charsets), using Java to write it out saves you 26 MB.
And that is when we ignore the overhead of the different datatypes, with
the overhead the numbers would probably be worse.
>
> 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:265034
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
