Just saw bug in my code re: the location of a newline.
change
<cfset outputLine = outputLine & chr(13) &
"#ID#,#FirstName#,#LastName#,#Address1#,#Address2#,#City#,#State#,#Zip#">
to
<cfset outputLine = outputLine &
"#ID#,#FirstName#,#LastName#,#Address1#,#Address2#,#City#,#State#,#Zip# & chr(13) ">
and the cffiles to
<cffile action="append" file="#exportFilePath#" output='#outputLine#'
addnewline="no">
With regards to building the CSV string in memory.
As we found, it absolutely killed poor old CF4.5 and thus my
solutions was stream it as much as possible.
Your issue (since SQL 7 is purring as it should) is CFFILE.
As Barney and Dave mentioned, reducing the CFFILE's would be your goal
and I would caution holding the whole string in memory as that too produced a
bottleneck for us.
YMMV.
cheers
ramon
> -----Original Message-----
> From: Buckland, Ramon [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, 12 March 2003 10:34 AM
> To: CF-Talk
> Subject: RE: Writing Query Results to a CSV using CFFILE
>
>
> 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#,#Sta
> te#,#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
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
