Speed/Optimization: What about the Driver? - Performance Source Code in ASP

Special : Web Developer's Collection CD-ROM
50,000+ Web Templates, Logos, Cliparts, Scripts. Order Now!

Add to Favorites Make Home Page

2368 Online

Language Categories

Source Codes Home
Project Ideas New!
Interview Questions
FAQs Home
ASP Home: ASP Source Codes; ASP Script Directory New!; ASP .Net Script Directory New!; ASP Interview Questions; ASP FAQs; ASP How Tos
C Home: C Source Codes; C Script Directory New!; C Interview Questions; C FAQs; C How Tos
C++ Home: C++ Source Codes; C++ Script Directory New!; C++ Interview Questions; C++ FAQs; C++ How Tos
Java Home: Java Source Codes; Java Directory New!; Java Interview Questions; Java FAQs; Java How Tos
JavaScript Home: JavaScript Directory New!; JavaScript Source Codes; JavaScript FAQs; JavaScript How Tos
COBOL Home: COBOL Source Codes; COBOL FAQs; COBOL How Tos
Pascal Home: Pascal Source Codes; Pascal FAQs; Pascal How Tos
PHP Script Directory New!
Python Script Directory New!
Perl & CGI Script Directory New!
Flash Script Directory New!
CFML Script Directory New!
Remotely Hosted Scripts New!
Tools & Utilities Directory New!
XML Script Directory New!
Best Programmers: Amit Mathur; Vishal Bhardwaj; Deepesh Jain
Vyom NetWork

Our Services

Get 9,000 Interview Questions & Answers in an eBook.

9500+ Pages
9000 Question & Answers
All Tech. Categories

14 MB Content

Get it now !!

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

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

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	381500
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.

Free Tutorials - HTML, Java, SQL, PHP, ASP, Perl, SQL Tutorials
Discuss Programming Problems and Freshers Jobs related issues here.
Discuss Final Year Project Ideas here.