Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  Speed: Database Percieved Speed
     [next Lesson]  OLEDB & ODBC Drivers differences

Speed/Optimization: All Variations

Fetching records in an optimized way actually has many variations. We will list most of them here, provide code sample and typical timings for fetching and displaying records. These timings reveal an interesting behavior. Even if a script reports it ran in say, 7 seconds, that refers to the time that script received from the CPU. So that if 7 scripts take 8 seconds each there may be hundreds or thousands of scripts running on the server that are sharing the CPU. User #1 may see a 7 second result in 21 seconds, so their 7 second report reflects the time spent on the server/CPU for the script and the fact that 14 seconds of other stuff was executed round-robin with the rest of the scripts, not the time since the script started.

Method: LOOP, .movenext, periodic response.flush

Query took 6 seconds.
Query processed 10835 records.
Speed =1805.83333333333 records per second

Method: LOOP, .movenext and periodic response.flush commands. String is assembled with & operator and writen periodically.

Query took 52 seconds.
Query processed 10835 records.
Speed =208.365384615385 records per second.

Observation: The & operator is VERY expensive! Unbelievably so.

Method: Single GetString command

Query took 4 seconds
Query processed 10837 records.
Speed =2709.25 records per second.

Method: GetRows command with no LOOP + movenext but loop through the array with periodic response flushes.

Query took 3 seconds.
Query processed 10834 records.
Speed =3611.33333333333 records per second.

Note: Getstring often wins in many tests but it depends on many factors. I have seen a straight loop take 18 secs where GetRows takes 9 seconds and getstring take 4 secs.

Method: GetRows command of a fixed row count (say 500 record clusters for example) with a reponse.flush after each array is read!

Query took 3 seconds.
Query processed 10782 records.
Speed =3594 records per second.

Method: Displaying a portion of the data (say the first 500 records) only.

Query took 7 seconds.
Query processed 500 records.
Speed =71.4285714285714 records per second.

Note: This was recorded as 7 seconds but actually was much faster actually due to speed the browser reported the results.

Now here is the code that was used to gather all the data.

Here is a table display with a simple LOOP:

   filename=/learn/test/dbtableloopall.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtableLoopAll.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_LoopAll

myDSN = "DSN=student;UID=student;pwd=magic"
'mySQL="select * from authors order by author "
mySQL="select * from authors order by author "

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
Call TimerEnd
%>
</BODY></HTML>

Here is a table display with a simple LOOP assembling a string:

   filename=/learn/test/dbtableloopallstring.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtableLoopAllstring.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_LoopAll_string

myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author "

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
Call TimerEnd
%>
</BODY></HTML>

Here is a table display with a GetString call and no LOOP:

   filename=/learn/test/dbtablegetstringall.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtablegetstringall.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_GetStringAll
myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author"

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
response.write OptimizationName(optimize) & "<br>"
Call TimerEnd
%>
</BODY></HTML>

Here is a table display with a GetString call (buffered):

   filename=/learn/test/dbtablegetstringbuffered.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtablegetstringbuffered.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_GetStringBuffered
myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author"

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
response.write OptimizationName(optimize) & "<br>"
Call TimerEnd
%>
</BODY></HTML>

Here is a table display with a GetRows call and no LOOP:

   filename=/learn/test/dbtablegetrowsall.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtablegetrowsall.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_getrowsall
response.write OptimizationName(optimize) & "<p>"

myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author "


call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
call TimerEnd

%>
</BODY></HTML>

Here is a table display with a GetRows (buffered):

   filename=/learn/test/dbtablegetrowsbuffered.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtableGetRowsBuffered.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_GetRowsBuffered

myDSN = "DSN=student;UID=student;pwd=magic"
'mySQL="select * from authors order by author "
mySQL="select * from authors order by author "

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
Call TimerEnd
%>
</BODY></HTML>

Here is a table display of a portion of the records:

   filename=/learn/test/dbtablelimitrows.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtablelimitrows.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_LimitRows
myDSN = "DSN=student;UID=student;pwd=magic"
mySQL="select * from authors order by author"

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
response.write OptimizationName(optimize) & "<br>"
Call TimerEnd
%>
</BODY></HTML>

Here is the optimized library lib_dbtablefastv2.asp which achieves this speed:

   filename=/learn/test/lib_dbtablefastv2.asp

<Test Script Below>


<%
Const optimize_LoopAll = 1
Const optimize_GetstringAll = 2
Const optimize_GetrowsAll = 3
Const optimize_GetrowsBuffered = 4
Const optimize_GetStringBuffered = 5
Const optimize_LimitRows = 6
Const optimize_LoopAll_String = 7

dim optimize_buffersize
dim optimize_started
dim optimize_ended
dim optimize_SQL
dim optimize_DSN
dim optimize_howmany
dim optimize_cursorlocation
dim optimize_maxrecs
dim optimize_disconnectRS

optimize_started=0

' performance stuff
optimize_buffersize=200
'optimize_cursorlocation=aduseclient
optimize_maxrecs=500
optimize_cursorlocation=aduseserver
optimize_disconnectRS=false
optimize_stringwrite=false

SUB TimerStart()
    optimize_started=now()
END SUB

SUB TimerEnd()
    optimize_ended=now()
    elapsed=DateDiff("s", optimize_started, optimize_ended)
    response.write "SQL=<b>" & optimize_SQL & "</b><br>"
    response.write "DSN=<b>" & optimize_DSN & "</b><br>"
    response.write "Query took <b>" & elapsed & " seconds.</b><br>"
    IF optimize_howmany=-1 THEN
        optimize_howmany=querycount(optimize_DSN,optimize_SQL)
    END IF
    response.write "Query processed <b>" & optimize_howmany & " records.</b><br>"
    response.write "Speed =<b>" & optimize_howmany/elapsed & " records per second.</b><br>"
    response.write "Notes:<br>"
    pad="&nbsp;&nbsp;&nbsp;&nbsp;"
    response.write pad & "buffersize=<b>" & optimize_buffersize & "</b><br>"
    IF optimize_cursorlocation=adUseClient THEN
        response.write pad & "cursorlocation=<b>adUseClient</b><br>"
    END IF
    IF optimize_cursorlocation=adUseServer THEN
        response.write pad & "cursorlocation=<b>adUseServer</b><br>"
    END IF
END SUB

sub query2table(parmQuery, parmDSN,parmMethod,parmcount)
    ' method 1 = standard
    ' method 2 = getrows
    ' method 3 = getstring
    dim howmany
    SELECT CASE parmMethod
        CASE 1
            Call loopStandard(parmQuery,parmDSN,howmany)
        CASE 2
            Call loopGetString(parmQuery,parmDSN,howmany)
        CASE 3
            Call loopGetRows(parmQuery,parmDSN,howmany)
        CASE 4
            Call loopGetRowsBuffered(parmQuery,parmDSN,howmany)
        CASE 5 
            Call loopGetStringBuffered(parmQuery,parmDSN,howmany)
        CASE 6
            Call LimitRows(parmQuery,parmDSN,howmany)
        CASE 7
            Call loopStandardStringWrite(parmQuery,parmDSN,howmany)
        CASE ELSE
            response.write "1, 2 or 3 are only valid speedmethods"
    END SELECT
    parmcount=howmany
    If optimize_started<>0 THEN
        optimize_DSN=parmDSN
        optimize_SQL=parmquery
        optimize_howmany=parmcount
    END IF
END SUB

FUNCTION querycount(parmDSN,parmQuery)
    set rstemp=Server.CreateObject("adodb.Recordset")
    rstemp.open parmQuery, parmDSN, adopenstatic
    querycount=rstemp.recordcount
    rstemp.close
    set rstemp=nothing
END FUNCTION


SUB loopstandard(inputquery, inputDSN,inputcount)
    dim conntemp, rstemp
    set conntemp=server.createobject("adodb.connection")
    ' 0 seconds means wait forever, default is 15
    conntemp.connectiontimeout=0
    conntemp.cursorlocation=optimize_cursorlocation
    conntemp.open inputDSN
    set rstemp=conntemp.execute(inputquery)
    IF optimize_disconnectRS=true THEN
        conntemp.close
    END IF
    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="&nbsp;"
            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
        END IF

    loop%>
    </table>
    <%
    inputcount=counter
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB%>

<%SUB loopstandardStringWrite(inputquery, inputDSN,inputcount)
    dim conntemp, rstemp
    set conntemp=server.createobject("adodb.connection")
    ' 0 seconds means wait forever, default is 15
    conntemp.connectiontimeout=0
    conntemp.cursorlocation=optimize_cursorlocation
    conntemp.open inputDSN
    set rstemp=conntemp.execute(inputquery)
    IF optimize_disconnectRS=true THEN
        conntemp.close
    END IF
    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
    tempSTR=""
    DO UNTIL rstemp.eof 
        counter=counter+1
        tempSTR=tempSTR & "<tr>"
        for i = 0 to howmanyfields
            thisvalue=rstemp(i)
            If isnull(thisvalue) then
                thisvalue="&nbsp;"
            end if
        tempSTR=tempSTR & "<td valign=top>" & thisvalue & "</td>" & vbcrlf
        next
        tempSTR=tempSTR & "</tr>"
        rstemp.movenext
        IF counter mod 50=0 THEN
            If response.isclientconnected()=false THEN
                EXIT DO
            END IF
            tempSTR=tempSTR & "</table>" & TableStart
            response.write tempSTR
            response.flush
            tempSTR=""
        END IF
    loop%>
    </table>
    <%
    inputcount=counter
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB%>

<%SUB loopGetstring(inputquery, inputDSN,inputcount)
    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
    tempSTR=rstemp.getstring(,, "</td><td>", "</td></tr><TR><TD>", "&nbsp;")
    response.write tempSTR
    response.write "</table>" 
    inputcount=-1
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB%>


<%SUB loopGetstringbuffered(inputquery, inputDSN,inputcount)
    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
        tempSTR=rstemp.getstring(,optimize_buffersize, "</td><td>", "</td></tr><TR><TD>", "&nbsp;")
        response.write tempSTR
        If response.isclientconnected()=false THEN
                EXIT SUB
        END IF
        response.write "</table>" & TableStart
    LOOP UNTIL rstemp.eof
    response.write "</table>" 
    inputcount=-1
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB

SUB loopGetRows(inputquery, inputDSN,inputcount)
    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
    alldata=rstemp.getrows
    numcols=ubound(alldata,1)
    numrows=ubound(alldata,2)
    
    FOR rowcounter= 0 TO numrows
        FOR colcounter=0 to numcols
            response.write "<td valign=top>" 
            response.write alldata(colcounter,rowcounter)
            response.write "</td>" 
        NEXT
        response.write "</tr>" & vbcrlf
        IF rowcounter mod 50=0 THEN
            If response.isclientconnected()=false THEN
                    EXIT FOR
                END IF
            response.write "</table>" & TableStart
        END IF
    NEXT
    response.write "</table>" 
    inputcount=numrows
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB

SUB loopGetRowsBuffered(inputquery, inputDSN,inputcount)
    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
        alldata=rstemp.getrows(optimize_buffersize)
        numcols=ubound(alldata,1)
        numrows=ubound(alldata,2)
    
        FOR rowcounter= 0 TO numrows
            FOR colcounter=0 to numcols
                response.write "<td valign=top>" 
                response.write alldata(colcounter,rowcounter)
                response.write "</td>" 
            NEXT
            response.write "</tr>" & vbcrlf
        NEXT
        howmany=howmany+numrows
        If response.isclientconnected()=false THEN
                EXIT SUB
        END IF
        response.write "</table>" & TableStart
    LOOP UNTIL rstemp.eof
    response.write "</table>" 
    inputcount=howmany
    rstemp.close
    set rstemp=nothing
    conntemp.close
    set conntemp=nothing
END SUB

SUB LimitRows(inputquery, inputDSN,inputcount)
    set rstemp=Server.CreateObject("adodb.Recordset")
    rstemp.maxrecords=optimize_maxrecs
    'rstemp.open inputquery, inputDSN, adopenforwardonly, adlockReadOnly
    rstemp.open inputquery, inputDSN,adopenstatic
    
    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>
    <%
    response.flush
    tempSTR=rstemp.getstring(,, "</td><td>", "</td></tr><TR><TD>", "&nbsp;")
    response.write tempSTR
    response.write "</td></tr></table>" 
    
    inputcount=optimize_maxrecs
    rstemp.close
    set rstemp=nothing
END SUB

FUNCTION optimizationName(parmNum)
    SELECT CASE parmnum
    CASE optimize_LoopAll
        optimizationName="LoopAll"
    CASE optimize_GetstringAll
        optimizationName="GetstringAll"
    CASE optimize_GetrowsAll
        optimizationName="GetrowsAll"
    CASE optimize_GetrowsBuffered
        optimizationName="GetrowsBuffered"
    CASE optimize_GetStringBuffered
        optimizationName="GetStringBuffered"
    CASE optimize_LimitRows
        optimizationName="LimitRows"
    CASE ELSE
        optimizationName="undefined"
    END SELECT
END FUNCTION
%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.