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]  Database Retrieval Speed
     [next Lesson]  IsClientConnected & Stray Tasks

Speed/Optimization: What about the Driver?

Fetching records in an optimized way may have many variations but before you get to the database you interact with a driver. Here we time the difference between arbitrary drivers. We will benchmark with the simplest method: Fetching and displaying all records with a LOOP, .movenext and periodic response.flush commands.

Here is a table display against a SQL server with a OLEDB driver.

   filename=/learn/test/dbtableSQLoledb.asp

<Test Script Below>


<%response.buffer=true%>
<HEAD><TITLE>dbtableSQLoledb.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
<!--#include virtual="/learn/test/lib_dbtablefastv2.asp"-->
<%
server.scripttimeout=240
optimize=optimize_LoopAll
mySQL="select * from authors where au_id<2000 order by author "

myDSN="PROVIDER=SQLOLEDB;DATA SOURCE=sql7.orcsweb.net;"
myDSN=myDSN & "USER ID=student;PASSWORD=magic;"

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

%>
</BODY></HTML>

Here is a table display against a SQL server with a ODBC driver:

   filename=/learn/test/dbtableSQLodbc.asp

<Test Script Below>


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

mySQL="select * from authors where au_id<2000 order by author "
optimize=optimize_LoopAll
myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};"
myDSN=myDSN & "SERVER=sql7.orcsweb.net;UID=student;PWD=magic;"

Call TimerStart
call query2table(mySQL,myDSN,optimize,howmany)
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.