Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP -> CSV script, execution speed

Posted on 2010-09-14
2
Medium Priority
?
284 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

927 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