the need for speed

[scottRen]

hi i'm wondering how i can speed up my code, make it more efficient, thanks:

<%
 
 
Set connDB = Server.CreateObject("ADODB.Connection")
connDB.Open "DBQ=" & Server.MapPath("\db\db.mdb") & ";" & _
"DRIVER=Microsoft Access Driver (*.mdb)"

sql = "SELECT * FROM logbook WHERE cusine IN ('CHINESE') AND eat IN ('BOTH','DINE IN') ORDER BY rname ASC"
 
Set rs = connDB.Execute(sql)

While Not rs.EOF
   Ername=rs("rname") 
   Eeat=rs("eat") 
   Eaddress1=rs("address1") 
   Eaddress2=rs("address2") 
   Ehours=rs("hours") 
   Especial=rs("special") 
   Ephone=rs("phone") 
   Eadditional1=rs("additional1") 
   Eadditional2=rs("additional2") 
   Emenu=rs("menu") 
   Edining=rs("dining") 
   Ehours2=rs("hours2") 
   Emenu2=rs("menu2") 
   Ephone2=rs("phone2") 
   
   collect1 =collect1 & Ername&","
   collect2 =collect2 & Eeat&","
   collect3 =collect3 & Eaddress1&","
   collect4 =collect4 & Eaddress2&","
   collect5 =collect5 & Ehours&","
   collect6 =collect6 & Especial&","
   collect7 =collect7 & Ephone&","
   collect8 =collect8 & Eadditional1&","
   collect9 =collect9 & Eadditional2&","
   collect10 =collect10 & Emenu&","
   collect11 =collect11 & Edining&","
   collect12 =collect12 & Ehours2&","
   collect13 =collect13 & Emenu2&","
   collect14 =collect14 & Ephone2&","
   
rs.MoveNext
   Wend
If rs.EOF AND  (Ername = "") Then
collect1 = "None listed.  Please try again."
End If   
output1="&output1="& collect1
Response.Write output1
   Response.Write ""
   output2="&output2="& collect2
   Response.Write output2
      Response.Write ""
output3="&output3="& collect3
Response.Write output3
   Response.Write ""
   output4="&output4="& collect4
   Response.Write output4
      Response.Write ""
output5="&output5="& collect5
Response.Write output5
   Response.Write ""
   output6="&output6="& collect6
   Response.Write output6
      Response.Write ""
output7="&output7="& collect7
Response.Write output7
   Response.Write ""
   output8="&output8="& collect8
   Response.Write output8
      Response.Write ""
output9="&output9="& collect9
Response.Write output9
   Response.Write ""
   output10="&output10="& collect10
   Response.Write output10
      Response.Write ""
         output11="&output11="& collect11
   Response.Write output11
      Response.Write ""
         output12="&output12="& collect12
   Response.Write output12
      Response.Write ""
         output13="&output13="& collect13
   Response.Write output13
      Response.Write ""
   output14="&output14="& collect14
   Response.Write output14
      Response.Write ""
   Counter = 1
   Response.Write "&Counter=" & Counter 


Set rs = Nothing
Set connDB = Nothing 
%>

 

[trollacious]

This will speed things up a little, and will clarify your code some by only using one call to Response.Write:

Dim ostring

ostring = "&output1=" & collect1
ostring = ostring & "&output2=" & collect2
ostring = ostring & "&output3=" & collect3
ostring = ostring & "&output4=" & collect4

'and so on

Response.Write ostring

Lee

 

[RhythmAddict112]

I have seen huge decreases in speed by using the getRows method...If you're craving even more speed, this should explain it pretty well...

http://www.aspdev.org/articles/asp-getrows/

Use your resources, you're on the internet!

 

[ChrisHirst]

Better Performance

Use something other than Access if possible.
Use a fieldlist instead of "SELECT * FROM ..."
Use GetRows() instead of the variable list.
recode the output to use the array from GetRows()



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[Tarwn]

Additionally, do NOT continue concatenating onto a single variable. String concatenation in ASP w/ VBScript is slow. Doing multiple Response.Write's will likely be much faster then continually extending a single string variable (I say likely because there may be a caes I have never seen).

As mentioned above: GetRows() will probably be one of your biggest speed increases (probably 8 to 12 times faster)

One commonly overlooked speed increase is the effect of Dim'ming all your variables ahead f time. Put an Option Epxlicit on there if you have to, but the fewer variables the engine will need to auto-magically create mid-execution, the faster it will be.

Instead of using a * in your SQL statement, list the fields. When you use a star it requires the database to do a pre-query to find out what fields it needs, then it does the real query for those fields. Telling it what fields you want will reduce the work done by the database (or db drivers in this case). Additionally, if there are fields you aren;t planning on using, then not using * will cut down on communications time because you won't be transmitting those extra fields between your db and ASP script, which also means you use a little less memory in both locations, making everything a little more happy.

There are also several FAQs on this subject and if you use the search tab above I think we have a couple fairly long threads on the subject.

-T

 

[trollacious]

Must have been a bad day for me yesterday. You're right about concatenating a string taking so long. Here's an article on faster output of strings:

http://readthefuckingmanual.co.uk/infinitemonkeys/articles/asp/899.asp

Lee

 

[Tarwn]

Yeah, just be careful you test his scripts out ahead of time. I have a couple comments here and there in his site where I found things to not be quite as good as stated
Don't remember how long ago it was, whenever the last time someone pointed at his site I guess.

-T

 

[Sheco]

WHERE cusine IN ('CHINESE')

Why have a list with only one member?

 

[trollacious]

cu[red]i[/red]sine should probably be spelled correctly, too.

Lee

 

[Tarwn]

I was assuming the lists were hard-coded for his example but were coming from form get/post in his real deal, although what bearing my guess has on the reality of the situation I couldn't tell you

-T