ASP -> CSV script, execution speed

Dear EE,

I have an ASP script which queries a MySQL database and turns the results of this into a CSV file. Just wondering if there was any way of speeding up the creation of the file as it is taking well over 3 minutes+ to bosch out a 7500 row file.

I've attached the code below, if any of you guys have any ideas on on speed improvements, I'm all ears. I haven't bothered including the SQL syntax as I know this isn't the problem (the query on it's own doesn't take this long to run).

Many thanks as always.
<!--#include file="connstr.asp"-->
<% Response.Buffer = True
 Server.ScriptTimeout = 3000 
' Response.ContentType="text/csv"
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment; filename=JSOutdial.csv" 

' <SQL syntax and connector strings go here>


LinesForOutput = LinesForOutput & """Customer_Telephone""" & ","
LinesForOutput = LinesForOutput & """Customer_Forename""" & ","
LinesForOutput = LinesForOutput & """Customer_Surname""" & ","
LinesForOutput = LinesForOutput & """Query_Name""" & ","
LinesForOutput = LinesForOutput & """URL""" & vbCrLf
While Not csvQuery.EOF
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Telephone") & """" & ","
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Forename") & """" & ","
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Surname") & """" & ","
LinesForOutput = LinesForOutput & """" & rsQuery("QueryName") & """" & ","
LinesForOutput = LinesForOutput & """http://outcall.********.co.uk/jsoutcall/customerdetails.asp?CURN=" & csvQuery("Customer_URN") & """" & vbCrLf
csvQuery.MoveNext
Wend


Response.Write(LinesForOutput)
Response.End


%>

Open in new window

BenthamLtdAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
navinmohantaConnect With a Mentor Commented:
You can create the CSV file on the Server side, and then send it to Response as the code snippet.

Thanks !!

<!--#include file="connstr.asp"-->
<% Response.Buffer = True
 Server.ScriptTimeout = 3000
' Response.ContentType="text/csv"
'

SET fso = Server.CreateObject("Scripting.FileSystemObject")
SET CSVFile = fso.CreateTextFile (Server.MapPath("test.csv"),2)

LinesForOutput = LinesForOutput & """Customer_Telephone""" & ","
LinesForOutput = LinesForOutput & """Customer_Forename""" & ","
LinesForOutput = LinesForOutput & """Customer_Surname""" & ","
LinesForOutput = LinesForOutput & """Query_Name""" & ","
LinesForOutput = LinesForOutput & """URL""" & vbCrLf

CSVFile.WriteLine LinesForOutPut

LinesForOutput = ""

While Not csvQuery.EOF
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Telephone") & """" & ","
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Forename") & """" & ","
LinesForOutput = LinesForOutput & """" & csvQuery("Customer_Surname") & """" & ","
LinesForOutput = LinesForOutput & """" & rsQuery("QueryName") & """" & ","
LinesForOutput = LinesForOutput & """http://outcall.********.co.uk/jsoutcall/customerdetails.asp?CURN=" & csvQuery("Customer_URN") & """" & vbCrLf

CSVFile.WriteLine LinesForOutPut

csvQuery.MoveNext
Wend

Response.AddHeader "content-disposition", "attachment; filename=JSOutdial.csv"
Response.AddHeader "Content-Length", CSVFile.Size
Response.ContentType = "application/octet-stream"

CSVFile.Close

SET objStream = Server.CreateObject("ADODB.Stream")

objStream.Open
objStream.Type = 1
objStream.LoadFromFile(Server.MapPath("test.csv")

Response.BinaryWrite(objStream.Read)
objStream.Close

SET objStream = Nothing
SET CSVFile = Nothing
SET fso = nothing
0
 
sybeCommented:
Forget about storing your output in a string variable first, just use Response.Write.
Creating a very long string costs performance, and in your case you already use Response.Buffer = True, so there is no difference in output. Your string variable "LinesForOutput" is just sucking up performance for no reason.
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.