Solved

ASP -> CSV script, execution speed

Posted on 2010-09-14
2
272 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:BenthamLtd
2 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 33670889
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
 
LVL 2

Accepted Solution

by:
navinmohanta earned 500 total points
ID: 33671016
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now