Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
TestName NumQueries Start End Elapsed Avg/Record
PreTest1 50 1/21/2003 20:00 1/21/2003 20:01 21 0.42
PreTest2 50 1/21/2003 20:01 1/21/2003 20:02 67 1.34
Test1 50 1/21/2003 20:02 1/21/2003 20:02 2 0.04
Test2 50 1/21/2003 20:02 1/21/2003 20:02 1 0.02
Test1 100 1/21/2003 20:03 1/21/2003 20:03 4 0.04
Test2 100 1/21/2003 20:03 1/21/2003 20:03 1 0.01
Test1 200 1/21/2003 20:03 1/21/2003 20:03 8 0.04
Test2 200 1/21/2003 20:03 1/21/2003 20:03 2 0.01
Test1 500 1/21/2003 20:04 1/21/2003 20:04 19 0.038
Test2 500 1/21/2003 20:04 1/21/2003 20:05 6 0.012
Test1 1000 1/21/2003 20:05 1/21/2003 20:05 39 0.039
Test2 1000 1/21/2003 20:05 1/21/2003 20:05 12 0.012
<%
Option Explicit
Server.ScriptTimeout = 600
Dim strConnect
strConnect = "Provider=SQLOLEDB.1;Password=----;" & _
"Persist Security Info=False;User ID=----;" & _
"Initial Catalog=----;Data Source=----;"
%><!--#INCLUDE FILE="adovbs.inc" --><%
Dim objCommand
Dim sql_pretest, sql_newid, sql_count, sql_randcount, sql_all
Dim rs_pretest, rs_newid, rs_count, rs_randcount, rs_all
sql_pretest = "SELECT * FROM Main"
Dim numTestLoops, loopCounter
Dim start_time, end_time
Dim dummy_key, dummy_int, dummy_string
numTestLoops = 50 'default value, will be changed dependant on workhorse call
'---------------------------- PreTests --------------------------------------------------
'The pretests are timed on executing a query, looping through the rows accessing the primary_id (integer),
' a non-key numeric field, and a text (varchar) field
' all objects will be set to nothing in each loop and re-instantiated
'-------- PreTest 1: objCommand to select *
Function PreTest1()
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_pretest
objCommand.CommandType=adCmdText
Set rs_pretest = objCommand.Execute
rs_pretest.MoveFirst
Do Until rs_pretest.EOF
dummy_key = rs_pretest("rid")
dummy_int = rs_pretest("main_size")
dummy_string = rs_pretest("title")
rs_pretest.MoveNext
Loop
Set rs_pretest = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- PreTest 2: rs.Open to select *
Function PreTest2
'Grab the Start time
start_time = Now()
'Do The Test
For loopCounter = 0 to numTestLoops - 1
Set rs_pretest = Server.CreateObject("ADODB.Recordset")
rs_pretest.Open sql_pretest, strConnect, adOpenStatic, adLockOptimistic, adCmdText
rs_pretest.MoveFirst
Do Until rs_pretest.EOF
dummy_key = rs_pretest("rid")
dummy_int = rs_pretest("main_size")
dummy_string = rs_pretest("title")
rs_pretest.MoveNext
Loop
Next
'Grab the end time
end_time = Now()
End Function
'---------------------------- Random Record Tests ---------------------------------------
'-------- Test 1: Select a random record from a table using NewID() function
Function Test1
'Prep the sql statement
sql_newid = "SELECT Top 1 * FROM Main Order By NewID()"
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_newid
objCommand.CommandType=adCmdText
Set rs_newid = objCommand.Execute
dummy_key = rs_newid("rid")
dummy_int = rs_newid("main_size")
dummy_string = rs_newid("title")
Set rs_newid = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- Test 2: Select a random record using 2 queries
Function Test2
'Prep the sql statement
Dim recCount
sql_count = "SELECT Count(*) as num FROM Main"
Randomize
'Grab the start time
start_time = Now()
'Do the test
For loopCounter = 0 to numTestLoops - 1
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText=sql_count
objCommand.CommandType=adCmdText
Set rs_count = objCommand.Execute
recCount = rs_count("num")
Set rs_count = Nothing
sql_randcount = "SELECT TOP 1 * FROM (SELECT TOP " & cInt(Rnd * recCount) & " * FROM Main ORDER BY rid DESC) DERIVEDTBL"
'response.write sql_randcount
objCommand.CommandText=sql_randcount
Set rs_randcount = objCommand.Execute
dummy_key = rs_randcount("rid")
dummy_int = rs_randcount("main_size")
dummy_string = rs_randcount("title")
Set rs_randcount = Nothing
Set objCommand = Nothing
Next
'Grab the end time
end_time = Now()
End Function
'-------- Test 3: Select a random record by selecting * then moving x number of records forward
Function Test3
'prep the sql
'sql_all = "SELECT * FROM MA"
'there is no need to do this test because it can't even select everything from the table fast enough, let alone move through it
End Function
'------------------------------------------------ Workhorse 0----------------------------
Function workhorse(testname,numloops)
numTestLoops = numLoops
Response.Write "Starting " & testname & ":<br>"
Response.Flush
Select Case lcase(testname)
case "pretest1"
PreTest1
case "pretest2"
PreTest2
case "test1"
Test1
case "test2"
Test2
case "test3"
Test3
End Select
Dim sec, avg
sec = DateDiff("s",start_time, end_time)
avg = sec/numTestLoops
Response.Write testname & " has completed " & numTestLoops & " queries against the database.<br>"
Response.Write "Start: " & start_time & "<br>"
Response.Write "End: " & end_time & "<br>"
Response.Write "Total Time: " & sec & "s<br>"
Response.Write "Average: " & avg & "<br><hr>"
'append the results to our text file
Dim fso, fil
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set fil = fso.OpenTextFile(Server.MapPath("dbEfficLog.txt"), 8)
fil.WriteLine(testname & vbTab & numTestLoops & vbTab & FormatDateTime(start_time,2) & " " & FormatDateTime(start_time,4) & vbTab & FormatDateTime(end_time,2) & " " & FormatDateTime(end_time,4) & vbTab & sec & vbTab & avg)
fil.close
Set fil = Nothing
Set fso = Nothing
End Function
workhorse "PreTest1",50
workhorse "PreTest2",50
workhorse "Test1",50
workhorse "Test2",50
workhorse "Test1",100
workhorse "Test2",100
workhorse "Test1",200
workhorse "Test2",200
workhorse "Test1",500
workhorse "Test2",500
workhorse "Test1",1000
workhorse "Test2",1000
%>