Solved

ASP -> CSV script, execution speed

Posted on 2010-09-14
2
275 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
align Linkedin sign to the right page 5 35
The Best Website Creation Tools 14 55
HTML5 frame 5 25
CSS: Making responsive table look nicer 7 25
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
The viewer will learn how to count occurrences of each item in an array.
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.

776 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