Solved

ASP -> CSV script, execution speed

Posted on 2010-09-14

1 solution

Medium Priority

282 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

Comment

Question by:BenthamLtd

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 28

Expert Comment

by:sybe

ID: 336708892010-09-14

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.

LVL 2

Accepted Solution

by:navinmohanta

navinmohanta earned 2000 total points

ID: 336710162010-09-14

You can create the CSV file on the Server side, and then send it to Response as the code snippet.

Thanks !!


<% 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

Featured Post

Question has a verified solution.

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

Easy Step: How to Create Context Sensitive Help

Article by: Roayn

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) , first we need to make the file that contains all topics, which are given exclusive IDs. …

How to Market Any Industry With 4 Easy Steps

Article by: Lexie

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…

Introduction to HTML (Part 1)

Video by: Jason

The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

Introduction to HTML (Part 2)

Video by: Jason

HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…