Rick,
Wow... 1,800 columns is a lot... 50k rows not so much. But together you end
up with 90 million total loop iterations (50k outer loop times 1.8k inner
loop). That's a bit excessive :). Sine you are writing to the file with each
inner loop iteration (each column value in effect) you are appending to a
file 90,000,000 times.... so you have to mitigate the number of inner loops
or the number of appends or both. I think I would start by building a string
through concatenation that you then append to the file. Something like
this......
<cfloop query="resultSet">
<cfset tmp = ''/>
<!--- write record --->
<cfloop from="1" to="#numFields#" index="i"
step="1">
<Cfset tmp = tmp & delimiter &
resultSet[fieldsArray[i]][resultSet.currentRow].toString()/>
</cfloop>
<cfset fileOutput.write( tmp() )>
<!--- write end of record --->
<cfset fileOutput.endRecord()>
</cfloop>
so you would have 50,000 appends to the file rather than 90,000,000...
although you would still have 90,000,000 loop iterations in total.
I think ultimately you need to "know" the column names - then you could cut
the whole thing down to 50k loops and output the row directly without loops.
<cfloop query="resultset">
<cfset tmp = col1 & delimiter & col2 & .... and on and on through your
colums/>
<cfset fileOutput.write( tmp() )>
<!--- write end of record --->
<cfset fileOutput.endRecord()>
</cfloop>
It's one of those places were CF falls a bit short of the mark - not great
with truly long import/export requests or really large files. I like Perl
for that sort of thing. Or one of the many import/export tools that go with
a RDBMS. For example MSSQL can output to a file using SSIS.
-Mark
Mark Kruger - CFG
CF Webtools
www.cfwebtools.com
www.coldfusionmuse.com
O: 402.408.3733 x105
E: [email protected]
Skype: markakruger
-----Original Message-----
From: Rick Root [mailto:[email protected]]
Sent: Thursday, August 18, 2011 8:50 AM
To: cf-talk
Subject: Improving Performance
Can anyone suggest ways that might incrementally improve the performance of
this code?
I'm using the JavaCSV library to generate a CSV file. It works pretty well,
but has some difficulty outputting extremely large files (50,000+ records,
1800 columns or so)
(formatted pastebin here: http://pastebin.com/zZVAHdPk)
<cfloop query="resultSet">
<!--- write record --->
<cfloop from="1" to="#numFields#" index="i" step="1">
<cfset fileOutput.write(
resultSet[fieldsArray[i]][resultSet.currentRow].toString() )>
</cfloop>
<!--- write end of record --->
<cfset fileOutput.endRecord()>
</cfloop>
Thanks for your suggestions!
Rick
--
*The beatings will continue until morale improves.*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346825
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
