This page contains testing notes, book recommendations, references, links, and other information for the ASP Speed Tricks article.
The tests were run by reloading the page five to ten times and estimating the average running time. The goal was not to be precise in the absolute running times, but to get an idea of the relative running times for each example. Here are specifications of the test system.
The script below was used to create the test data table for the MDB file and in SQL Server. The script creates a single table called "tblData" with four fields and 1000 rows. Obvious modifications to the script can be used to create tables with more rows.
<%@ Language=VBScript %> <% Option Explicit %> <% Dim objCN Dim strsql, objRS Dim i, FieldArray Set objRS = Server.CreateObject("ADODB.Recordset") Set objCN = Server.CreateObject("AdoDB.Connection") ' Create test data table If True Then ' A Jet SQL Database objCN.ConnectionString = "DSN=datasource" strsql = "CREATE TABLE tblData "&_ "(Field1 AUTOINCREMENT UNIQUE NOT NULL PRIMARY KEY,"&_ "Field2 INTEGER, Field3 TEXT(50), Field4 TEXT(50))" Else ' A SQL Server Database objCN.ConnectionString = "Provider=SQLOLEDB.1;"&_ "User ID=sa;Data Source=localhost;"&_ "Initial Catalog=sqltest;Password=;" strsql = "CREATE TABLE tblData "&_ "(Field1 INTEGER IDENTITY PRIMARY KEY,"&_ "Field2 INTEGER, Field3 NVARCHAR(50), Field4 NVARCHAR(50))" End If objCN.Open objRS.Open strsql,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText ' Populate the test data table with test data. objRS.Open "tblData", objCN, adOpenStatic, adLockOptimistic, adCmdTable FieldArray = Array("Field2","Field3","Field4") For i = 1 To 100 objRS.AddNew FieldArray, Array(879,"This is test data","abc") objRS.AddNew FieldArray, Array(458,"more test data.","def") objRS.AddNew FieldArray, Array(77,"another test","ghi") objRS.AddNew FieldArray, Array(66,"test test test","jkl") objRS.AddNew FieldArray, Array(54,"testing, 123","mno") objRS.AddNew FieldArray, Array(88,"test data test data","pqr") objRS.AddNew FieldArray, Array(498,"testing again","stu") objRS.AddNew FieldArray, Array(64,"test again","vwx") objRS.AddNew FieldArray, Array(8,"done testing","yz") objRS.AddNew FieldArray, Array(58,"again","xxx") Next objRS.Update Response.write "added 1000 rows to new table tblData" objRS.Close objCN.Close Set objRS = Nothing Set objCN = Nothing %>
Click the links to see the books at Amazon.com. You can also use the AddALL Book Search and Price Comparison for finding good prices on books.
Acknowledgements for some of the techniques go to commentators in the microsoft.public.inetserver.asp.general. I must also give acknowledgment to all the technical writers who have posted helpful ASP articles across the web.
I just wanted to express my EXTREME gratitude for this article - I've spent the past several hours trying to optimise a query and have been scouring probably around 30 different sources. This article provided EVERYTHING I needed to make my query fast (retrieving upwards of 50000 records and 30 fields with SQL strings being programmatically generated, and dumping them into an Excel table using ASP). I can't tell you how APPRECIATIVE I am - I've managed to get my query down from 7 minutes to less than 20 seconds because of your fantastic article!!
Rich - Sep. 18, 2003
Thank you for the article. I found it informative and useful for my project. ... You article, along with a few others, was particularly easy for me to read, and had quantitative proof.
Chris Zwemke - Nov. 25, 2003