I wouldn't use string concatenation, that's very slow. Rather I'd use cfsavecontent surrounding the outermost loop and then write those results to file once rather than with each iteration of the lop. FileIO can be the slowest part of this whole thing.
Just with those two changes you should see a big improvement. regards, larry >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: mkru...@cfwebtools.com >Skype: markakruger > > >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:346840 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
