Speed/Optimization: What about the Driver?

--------------------------------------------------------------------------------

Speed/Optimization: What about the Driver?

--------------------------------------------------------------------------------

Description : 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.

<%response.buffer=true%>
<HEAD><TITLE>dbtableSQLoledb.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">


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

<%response.buffer=true%>
<HEAD><TITLE>dbtableSQLODBC.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">


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

<%
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=" "
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=" "
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=" "
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>", " ")
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>", " ")
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>", " ")
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
%>