Wendy, My initial guess is the delay in the CFFILE, disk IO.
Try buffering the rows.. Along with this suggestion below you could try converting it to a CFSCRIPT. MX apparently runs CFScript quicker too (I think ? ) Though you would have to write a function for cffile. http://www.macromedia.com/devnet/mx/coldfusion/extreme/cftags_cfscript.html Anyway .. try this (i just wrote it in the email, haven't tested it yet but you will get the drift). maybe play with the buffer a bit (trading system memory (holding X lines to disk IO from CFFILE) eg: <cfset int_row_count = 0> <cfset buffer_len = 500> <cfset outputLine = ""> <cfoutput query="online_reg"> <cfset int_row_count = int_row_count + 1> <cfset outputLine = outputLine & chr(13) & "#ID#,#FirstName#,#LastName#,#Address1#,#Address2#,#City#,#State#,#Zip#"> <cfif int_row_count eq buffer_len> <cffile action="append" file="#exportFilePath#" output='#outputLine#' addnewline="yes"> <cfset outputLine = ""> <cfset int_row_count = 0> </cfif> </cfoutput> <!--- output any last lines hanging about ---> <cfif len(outputLine) gt 0> <cffile action="append" file="#exportFilePath#" output='#outputLine#' addnewline="yes"> </cfif> We had a simlar issue where the server just pushed the content out as a CSV via CFCONTENT We had something like <cfoutput query="qry_results"> <cfset csvStuff = csvStuff & "'#col1','#col2#',..."> </cfoutput> <CFHEADER NAME="content-disposition" VALUE="attachment;filename=output.csv"> <cfoutput>#csvStuff#</cfoutput> issue above was we were building our CSV content in a variable (ie, in memory) .. I got our 40min CSV download down to 6 mins (intranet so we don't care) by 'streaming the content out' eg: <CFHEADER NAME="content-disposition" VALUE="attachment;filename=output.csv"> <cfoutput query="qry_results">... </cfoutput> hope that helps. > -----Original Message----- > From: Wendy Copley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 12 March 2003 9:49 AM > To: CF-Talk > Subject: Writing Query Results to a CSV using CFFILE > > > Hi All! > > I've inherited a reporting application that allows users to dump the > results of a query into a CSV file (via the CFFILE tag) and > either save it > or open it. This works pretty well most of the time, but > there are some > circumstances where the results set is over 150K rows. In > those cases it > can take 30+ minutes to build the file and my users usually > get impatient > and close their browsers before it is done building. Does > anyone have any > ideas about how I can speed up the process of building this > file? This is > the code used to build the file: > > <cfset export_header="Record > ID,FirstName,LastName,Address1,Address2,City,State,Zip"> > > <!--- export table header ---> > <cfoutput> > <cffile action="write" > file="#exportFilePath#" > output="#export_header#" > addnewline="yes"> > </cfoutput> > > > <!--- append the search query to the file ---> > <cfoutput query="online_reg"> > <cfset > outputLine="#ID#,#FirstName#,#LastName#,#Address1#,#Address2#, > #City#,#State#,#Zip#"> > > <cffile action="append" > file="#exportFilePath#" > output='#outputLine#' > addnewline="yes"> > </cfoutput> > > I've tried building one big variable and appending that all at once > instead of appending each record one at a time and that takes > about twice > the time to execute that this code does. I'm beginning to > wonder if CFFILE > is the best way to go. Any other ideas? > I'm running ColdFusion 4.5. > > Thanks for any help you can give! > _______________ > Wendy Copley > Web Engineer > [EMAIL PROTECTED] > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
