This should speed things up some...
It eliminates the IF statement and the count increment - both of which are
slow.
Try changing the value of blockfactor to get different performance results.
-----Code------
<CFQUERY NAME="getUserDetails">
Query database
</CFQUERY>
<cfset blockfactor = 500>
<cfloop from="1" to="#getUserDetails.recordcount#" step="#blockFactor#"
index="i">
<CFOUTPUT QUERY="getUserDetails" startrow="#i#"
maxrows="#blockFactor#">
<CFSET csvstring=csvstring &
"#id#,#replace(first_name,',','ALL')#,#replace(last_name,',','ALL')#,#replac
e(email,',','ALL')#,#replace(postcode_P1,',','ALL')#"& chr(10)>
</CFOUTPUT>
<CFFILE ACTION="APPEND" FILE="thecsvfile.csv" OUTPUT="#csvstring#"
ADDNEWLINE="No">
<cfset csvstring = "">
</cfloop>
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Colin Murphy [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 05, 2002 9:17 AM
To: CF-Talk
Subject: Query to CSV problem
I am looking for a solution to outputing a query to a CSV file.
I am currently querying the database, and running through the results and
storing them in a string. Once I have stored 500 results I append them to
the CSV file and reset the string.
-----Code------
<CFQUERY NAME="getUserDetails">
Query database
</CFQUERY>
<CFOUTPUT QUERY="getUserDetails">
<CFSET csvstring=csvstring &
"#id#,#replace(first_name,',','ALL')#,#replace(last_name,',','ALL')#,#replac
e(email,',','ALL')#,#replace(postcode_P1,',','ALL')#"& chr(10)>
<CFIF #count# EQ 500 OR #getUserDetails.CurrentRow# EQ
#getUserDetails.RecordCount#>
<CFFILE ACTION="APPEND" FILE="thecsvfile.csv" OUTPUT="#csvstring#"
ADDNEWLINE="No">
<CFSET count=0>
<CFSET csvstring="">
</cfif>
<CFSET count=#count#+1>
</CFOUTPUT>
------Code------
The query currently returns 100,000 records, and takes about 5 seconds to
process but the page keeps timing out. The timeout on the server is 2
minutes, which I dont want to increase.
I need this process to be performed as quickly as possible, under 2 minutes.
I know I could increase the timeout for the page by calling the page using
'requesttimeout' in the URL.
Any suggestions would be grateful.
thanks,
Colin Murphy
http://www.colin-murphy.co.uk
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
