Add to Favorites    Make Home Page 28080 Online  
 Language Categories  
 Our Services  

Home » ASP Home » Performance Home » Speed/Optimization: What about the Driver?

A D V E R T I S E M E N T

Search Projects & Source Codes:

Title 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.
Category ASP » Performance
Hits 4660
Code Select and Copy the Code
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"> <!--#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: <%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: <% 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 %>

Related Source Codes

Script Name Author
ııııııııııııııııııııı VyomWorld
Resistor color code reader A.Chermarajan.
Telephone Directory dhivya
card swapping game (Mini Project) nityanand
simple hangman-pascalsource Seabert
college dirtectory (Mini Project) msridhar
Poll Application John van Meter
ASP Daily Hit Counter. Tejaskumar Gandhi
To avoid null in asp environment using sql Sami
Maklumbalas webmaster
poll John van Meter
EasyASP Template Engine. TjoekBezoer
Basic Calculator using HTML & Javascript. Patrick M. D Souza
What servers support ASP ? VyomWorld
What is ASP? VyomWorld

A D V E R T I S E M E N T




Google Groups Subscribe to SourceCodesWorld - Techies Talk
Email:

Free eBook - Interview Questions: Get over 1,000 Interview Questions in an eBook for free when you join JobsAssist. Just click on the button below to join JobsAssist and you will immediately receive the Free eBook with thousands of Interview Questions in an ebook when you join.

New! Click here to Add your Code!


ASP Home | C Home | C++ Home | COBOL Home | Java Home | Pascal Home
Source Codes Home Page

 Advertisements  

Google Search

Google

Source Codes World.com is a part of Vyom Network.

Vyom Network : Web Hosting | Dedicated Server | Free SMS, GRE, GMAT, MBA | Online Exams | Freshers Jobs | Software Downloads | Interview Questions | Jobs, Discussions | Placement Papers | Free eBooks | Free eBooks | Free Business Info | Interview Questions | Free Tutorials | Arabic, French, German | IAS Preparation | Jokes, Songs, Fun | Free Classifieds | Free Recipes | Free Downloads | Bangalore Info | Tech Solutions | Project Outsourcing, Web Hosting | GATE Preparation | MBA Preparation | SAP Info | Software Testing | Google Logo Maker | Freshers Jobs

Sitemap | Privacy Policy | Terms and Conditions
Copyright ©2003-2019 SourceCodesWorld.com, All Rights Reserved.
Page URL: http://www.sourcecodesworld.com/source/show.asp?ScriptID=263


Download Yahoo Messenger | Placement Papers | Free SMS | C Interview Questions | C++ Interview Questions | Quick2Host Review