|
Speed/Optimization Example: A Table
Display
Speeding up your scripts involves many big and small script
changes. We have prepared this "before" and "after" example to
illustrate the point. This example assumes you truly have to display this many records
(for example, a corporate report). Alternatively:
Side note: If anyone you know believes Access queries are
done asynchronously, running a couple of these scripts will prove them wrong. Access
queries execute one web user at a time sequentially.
After Optimization
Here is a very fast table display going against an identical
huge SQL Server Table:
filename=/learn/test/dbtablefast.asp
<%response.buffer=true%>
<HEAD><TITLE>dbtablefast.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtablefast.asp"-->
</BODY></HTML>
Here is the optimized library lib_dbtablefast.asp
which achieves this speed:
filename=/learn/test/lib_dbtablefast.asp
<%
sub query2table(inputquery, inputDSN)
dim conntemp, rstemp
set conntemp=server.createobject("adodb.connection")
' 0 seconds means wait forever, default is 15
conntemp.connectiontimeout=0
conntemp.open inputDSN
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1
tablestart="<table border=1 cols=3><col width='15%'><col width='70%'><col width='15%'><tr>"
response.write tablestart
for i=0 to howmanyfields %>
<td><b><%=rstemp(i).name%></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
DO UNTIL rstemp.eof
counter=counter+1
response.write "<tr>"
for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue=" "
end if
response.write "<td valign=top>" & thisvalue & "</td>" & vbcrlf
next
response.write "</tr>"
rstemp.movenext
IF counter mod 50=0 THEN
If response.isclientconnected()=false THEN
EXIT DO
END IF
response.write "</table>" & TableStart
response.flush
END IF
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
Before Optimization
Here is the original slow script which basically demonstrates
techniques that may work if your data and concurrency load is light, but the script above
demonstrates the typical changes made to speed up a script when it becomes needed or you
just want to wring every ounce of speed from your site. This script will probably timeout
before it's completion!
filename=/learn/test/dbtableslow.asp
<HEAD><TITLE>dbtableslow.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
myDSN = "DSN=student;uid=student;pwd=magic"
mySQL= "SELECT * from authors order by Author"
call query2table(mySQL,myDSN)
%>
<!--#include virtual="/learn/test/lib_dbtableslow.asp"-->
</BODY></HTML>
Here is the original slow library lib_dbtableslow.asp
:
filename=/learn/test/lib_dbtableslow.asp
<%
sub query2table(inputquery, inputDSN)
dim conntemp, rstemp
set conntemp=server.createobject("adodb.connection")
conntemp.open inputDSN
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border=1><tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
<td><b><%=rstemp(i).name%></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
DO UNTIL rstemp.eof and response.isclientconnected()%>
<tr>
<% for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue=" "
end if%>
<td valign=top><%=thisvalue%></td>
<% next %>
</tr>
<%rstemp.movenext
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
|