Page #71 (Chapter 10 - Retrieving and Storing Data in Databases)

	Chapter 10 - Retrieving and Storing Data in Databases
	Visual Basic Developers Guide to ASP and IIS
	A. Russell Jones

Copyright 1999 SYBEX Inc.

---

	Retrieving Data with ADO

	You retrieve data from a data store into a Recordset object. In ADO, there are often several ways to accomplish the same task. For example, you can retrieve data by using a Connection object, a Command object, or the Open method of a Recordset object. Sometimes, to accomplish your goals you must use only one of these methods. Other times, you may freely choose between any of them. The principal difference lies in how much code you want to write.

	In general, you want to minimize the number of objects you have to create and you want to specify exactly what data you want to retrieve, how you want to retrieve it, and how you want the recordset to act. This leads to the rather odd fact that the more code you write in ADO, the faster your data access is likely to be.

	Retrieving Data with the ADO Connection Object

	The simplest way to retrieve data from a database is to create a Connection object, open it, then use it to retrieve the data into a Recordset object. For example, to retrieve all the book titles from the pubs sample database:

	Dim R As ADODB.Recordset

Dim conn as new ADODB.Connection

conn.Open  "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs","sa",""

Set R = conn.Execute("SELECT * FROM Titles")

	When you open a recordset in this manner, you get a read-only, forward-only recordset. This is the most efficient type of recordset, and you'll use it more than other types in Web applications. However, it has several deficiencies that make it unsuitable for many purposes. For example, you can't update the records, you can't determine how many records were returned (without looping through the recordset), you can only move forward through the recordset, and you can't control whether or how the server locks the retrieved records. If you want to access earlier records again, you have to retrieve a new copy of the recordset.

	Using ADO Cursor Types

	Each recordset that you open contains a pointer to the current record, assuming the query returned any records. By default, when you open a recordset, the pointer points to the first record. ADO allows several types of record pointers (called cursors, because you can move through the data with them). Many people call the read-only, forward-only type of cursor a firehose cursor, or sometimes, a cursorless recordset. The firehose cursor is the fastest type of cursor.

	There are four types of cursors. All except the adForwardOnly cursors allow both forward and backward movement; however, the keyset cursor provides the best level of backward movement performance. The four types are described in the following list:

	adOpenForwardOnly  This is the firehose cursor. You use this type of cursor when you want to display records.

	adOpenStatic  This cursor takes a "snapshot" of the records at the time when you retrieve the recordset—you can't see any changes that other people make to the records. You use this type of cursor when you need to cache records.

	adOpenKeyset  Each record has a unique key. When you use this cursor type, the recordset retrieves the set of keys to the requested records and buffers the first few records. As you request more records, the cursor retrieves the data from the database. You can see changes that others make to the records in the recordset, but you can't see new or deleted records. You use this type of cursor when you need to make multiple updates or when the retrieved recordset is large.

	adOpenDynamic  This cursor is like adOpenKeyset, but you can see new and deleted records. Dynamic cursors use lots of resources and aren't particularly useful in a Web environment. You should not usually need to use dynamic recordsets with WebClasses.

	Retrieving Data with the Recordset Object

	If you need anything other than a forward-only cursor, if you want to know how many records were returned, or if you want to control how the server locks records, you can't use the Connection object's Open method. You must use the Recordset.Open method instead. To do this, set the Recordset's CursorType property to the cursor type you need, then open it using the Recordset.Open method. For example, to obtain the list of titles from the pubs database using a static cursor, you would write

	Dim R As ADODB.Recordset

Set conn = New ADODB.connection

conn.Open  "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs","sa",""

Set R = new ADODB.Recordset

R.Open "SELECT * FROM Titles", conn, adOpenStatic

	The Recordset.Open method accepts several arguments, most of which are optional:

	Source  Required. An SQL query, name of a table, name of a query/ stored procedure, or name of a Command object

	ActiveConnection  Optional. An open Connection object

	CursorType  Optional. An ADO CursorTypeEnum constant

	LockType  Optional. An ADO LockTypeEnum constant

	Options  Optional. An ADO CommandTypeEnum value (may require combined values)

	Don't worry about LockType and Options right now; we'll get to those soon.

	Most of the ADO properties and arguments have default values. ADO often lets you substitute one type of value for another. For example, rather than opening a Connection object, you can substitute a connection string for the second parameter of the Recordset.Open method.

	Dim R As ADODB.Recordset

Set R = new ADODB.Recordset

R.Open "SELECT * FROM Titles", "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs;UID=sa;PWD=", adOpenStatic

	If you do this, ADO creates and opens a connection for you. As soon as you close the recordset, the connection closes as well. However, it's not a good idea to open recordsets this way because ADO will create a Connection object anyway and use that to retrieve the data—and ADO does not pool connections created in this manner. In general, despite the availability of default values, you're much better off if you specify all the properties and methods. When you don't provide them, ADO makes the decision for you—and it doesn't always make the best decision.

	Using Disconnected Recordsets

	Since ADO 2, you can "disconnect" or disassociate recordsets from the connection with which you opened them. To disconnect a recordset, you must set the CursorLocation property to adUseClient. Both the Connection object and the Recordset object support the CursorLocation property. The default value is adUseServer, which places the burden of cursor movement on the server rather than the client (the recordset). The adUseClient setting is better. If you set the Connection object's CursorLocation property, recordsets that you open using that connection inherit the setting. You can override the setting for individual recordsets by setting the CursorLocation property for the Recordset object.

	Disconnecting a recordset frees its associated connection to the pool so that the freed Connection object can be reused. Freeing connections is a major performance enhancement when you are likely to keep a recordset open for any significant length of time. For example, when you're formatting a large table with data from a recordset, you should disconnect the recordset first, to free the connection. If you cache Recordset objects by storing them in Session variables (which I don't recommend), the associated connection is unavailable to any other user unless you first disconnect the recordset.

	When you open and disconnect a recordset by using the adLockBatchUpdate locking option, you can update the disconnected records, then save the data to the database later by reconnecting the recordset to a valid connection. If you plan to do this (which is convenient), you need to be aware of two issues:

	•		ADO cannot update values from multiple tables. If your recordset contains data from multiple tables, you should update the data yourself.

	•		You must include the key field(s) from the table in order to update the data properly. ADO cannot match the disconnected records to their proper rows if the key values are missing.

	Using ADO Record-Locking Options

	ADO provides four record-locking options. The result they provide depends on the database you're using. Some databases lock rows, others lock pages. The options are as follows:

	adLockReadOnly  Database does not place locks on data

	adlockOptimistic  Database locks data only during actual updates

	adLockPessimistic  Database locks data until you explicitly release the locks

	adLockBatchOptimistic  Like adLockOptimistic, but you use this flag to batch updates rather than to perform single record updates

	In general, you want to use the locking scheme that will place the least restrictions on other people using your application. The least restrictive option is adLockReadOnly. I highly recommend that you use this option exclusively if possible. That means that you should not use recordsets to update data—use stored procedures instead. If you follow that recommendation, you won't need to worry about locks. Use timestamp fields to determine whether the underlying data has changed since you retrieved it.

	If you insist on using recordsets to update data, try to use the adLockOptimistic option or adLockBatchOptimistic option whenever possible—preferably with disconnected recordsets. The reason you should avoid adLockPessimistic is that the data is unavailable to other users while you have the record locked. The data stays locked until you have finished updating it. Sometimes it's convenient to lock the data exclusively, but you don't ever have to; you can use timestamp fields instead.

	Understanding ADO's Unfortunate Options Argument

	Most areas of ADO are well thought out, but not all. One area that needs some work is the ubiquitous Options argument.

	For example, the Connection.Execute method takes three arguments: an SQL statement or query, a variable to hold the number of rows affected, and an Options argument. When calling Connection.Execute, the Options argument is a CommandTypeEnum value. The CommandTypeEnum value isn't required, but leaving it out affects how quickly the ADO can interpret the command. The possible values of the Options argument are:

	adCmdText  Interprets the CommandText argument as an SQL statement.

	adCmdTable  Interprets CommandText as a table name.

	adCmdTableDirect  Interprets CommandText as a table name whose columns are all returned.

	adCmdStoredProc  Interprets CommandText as a stored procedure name.

	adCmdUnknown  Default. If you don't specify the CommandType, ADO sets it to adCmdUnknown. For unknown command types, ADO checks CommandText against the names of stored procedures and tables. If it finds a match, it executes CommandText as a table or stored procedure; if not, it attempts to parse CommandText as a SQL statement.

	adCommandFile  Use this value when you're reconstituting a persisted recordset from a file.

	adExecuteNoRecords  When you execute a command that doesn't return records, or for which you don't want the returned records, use this value. Unlike all the other values, you combine this with adCmdText or adCmdStoredProc.

	In other cases, the Options argument requires a different value. For example, the Connection.Open method (which the MSDN documentation says takes three arguments) can actually take four; the fourth one, Options, accepts a Connect-OptionEnum value. Currently (up to ADO 2.1) the ConnectOptionEnum has only one value: adAsyncConnect (16).

	It's easy to confuse the required Option type values because of the common name. Using the Object Browser to find the required values is also easier (and more accurate) than using the documentation.

	Retrieving Data with the Command Object

	You've seen how to open recordsets from connections and how to use the Recordset.Open method. You can also use the Command object to retrieve data. You don't typically need to use a Command object unless you want to perform a query multiple times or unless your query has parameters. Remember, creating unnecessary objects carries a performance penalty.

	To use a Command object, you:

	1.		Create the Command object

	2.		Set its CommandText property to a table name, query/stored procedure name, or an SQL statement

	3.		Set its ActiveConnection property to an open Connection object

	4.		Use the Execute method to retrieve a recordset

	Here's an example:

	Dim conn as Connection

Dim cm as Command

Dim R as Recordset

Set conn = New ADODB.connection

conn.Open  "Provider=SQLOLEDB;" & _ "SERVER=myServer;DATABASE=pubs","sa",""

Set cm = New Command

cm.CommandText="SELECT * FROM Titles"

set cm.ActiveConnection = conn

set R = cm.Execute

	Setting up and executing a Command object takes more code, but when you need to run a query multiple times, it's much faster than retrieving data via a Connection object. The initial execution is no faster than using the Execute method of a Connection object with an SQL statement. However, like a stored procedure or Access query, subsequent executions contain pre-compiled SQL and, therefore, they execute very quickly.

	When you specify SQL as a string, it's called dynamic SQL. Dynamic SQL is relatively slow compared to queries or stored procedures because the server has to parse and compile the SQL, create a query execution plan, then retrieve the data. In contrast, when you use queries and stored procedures, both the SQL and the query execution plan are pre-compiled and immediately ready for execution—all the database server has to do is retrieve the data.

	I've been using the terms query and stored procedure interchangeably so far, but they're not the same at all. The generic term query means any SQL statement that returns data. For most VB programmers, a query means a Microsoft Access pre-compiled SQL statement. Access queries can contain only a single SQL statement. A stored procedure is a term used with ODBC databases such as SQL Server and Oracle. Stored procedures can contain not only multiple SQL statements but also code. This feature is extremely important because it can mean the difference between a successful and unsuccessful application.

	Note		For the rest of this book, I'm going to use the term stored procedure to mean either an Access query or a stored procedure. This is partly to avoid having to write query/stored procedure whenever I need to use the term and partly to encourage you to move away from Access and toward an ODBC database as quickly as possible. I'll use the term query generically to mean any dynamic SQL request to the database server except stored procedures.

	Executing code on the database server results in three benefits for your application:

	•		You can move program logic into the database where you can update it easily by replacing the text of the stored procedure. You don't have to recompile your application.

	•		Code compiled in the database acts directly on the data without moving that data back and forth through the network. Not only does this reduce network traffic, it also frees your Web server from performing routine database updates.

	•		You can partition your program to take advantage of the database's built-in security—restricting access to data and stored procedures based on a person's identity.

	In SQL Server, you use a Basic-like language called Transact SQL to write stored procedures. Transact SQL has a full set of operators, built-in functions, and If… Then…Else, For…Next, Case, and looping constructs that let you write complex programs that execute within the context of the database server.

	Besides running queries multiple times, the other reason to use a Command object is to retrieve data by using stored procedures that require parameters. Often, these two needs go hand in hand. For example, to retrieve a single record from the Authors table in the pubs database, you need to specify a condition that determines which record to select. In SQL, you specify conditions in a WHERE or HAVING clause:

	SELECT * FROM Authors WHERE au_ID = '238-95-7766'

	The query will select a single record (not all fields are shown here):

	au_id

au_lname

au_fname

phone

	238-95-7766

Carson

Cheryl

415 548-7723

	Now suppose you have a need to run that same query, but with a different author ID, once for every request to your Web site. You can't store the SQL as written because it will always return a specific record. Instead, you store it as a parameter query or stored procedure. The following stored procedure returns a single Author record. To use the stored procedure, you must specify one parameter: the Author ID (au_id).

	In Access the stored procedure looks like this:

	PARAMETERS au_id Text;

SELECT Authors.* FROM Authors

WHERE (((Authors.au_id)=[au_id]));

	To create the stored procedure in Access, click the Queries tab, then click New. Click OK to accept Design View, then click the Close button without selecting any tables from the Show Table list. Click the SQL button on the toolbar and select the SQL view. Paste the text of the procedure into the SQL window to define the stored procedure. Save it as getAuthor.

	In Transact SQL, the stored procedure looks like this:

	CREATE PROCEDURE getAuthor @au_id varChar(11)

AS SELECT * FROM Authors WHERE Authors.au_id = @au_id

	To create the stored procedure in SQL Server, paste this script into the iSQL window and run it.

	if exists (select * from sysobjects where id = object_id('dbo.getAuthor') and sysstat & 0xf = 4)

   drop procedure dbo.getAuthor

GO

CREATE PROCEDURE getAuthor @au_id varChar(11)

AS SELECT * FROM Authors WHERE Authors.au_id = @au_id

GO

	Despite the difference in the procedure's definitions in the two databases, you use them from ADO as if they were identical. Assume you have an open connection called conn. You create the Command object and assign the name of the query or stored procedure to the CommandText property, set the ActiveConnection to your open connection, then use the Execute method to fetch the data.

	Dim cm as Command

Dim R as Recordset

Set cm = New Command

cm.commandText="getAuthor"

set cm.ActiveConnection = conn

set R = cm.Execute(Set R = cm.Execute _

    (reccount, Array("238-95-7766"), adCmdStoredProc)

	The Command.Execute method takes several arguments. The first argument is a long integer value called RecordsAffected (the variable recCount in the fragment). You pass the variable in by reference and the database server fills it with the number of records affected by the query.

	Note		The RecordsAffected argument always receives a value of -1 for queries that return records. It's only useful for queries that alter data (such as UPDATE, INSERT, and DELETE queries).

	The second argument to the Command.Execute method is a Variant array of parameter values—in this case, the au_id of the record to retrieve. The third argument, Options, contains an ADO CommandTypeEnum value. The database server substitutes the values in the Variant array for the declared parameters in the stored procedure. The type and sequence of the parameter values in the array must match the declared type and sequence of the expected parameters declared in the query definition.

	Getting the types and sequences to match by using Variants is not only difficult, it's an invitation to disaster. Luckily, the Command object has an alternate syntax that, while longer, is faster and unambiguous. When you use a Variant array to pass parameters, the Command object converts the array into individual Parameter objects and appends them to its Parameters collection. (OK, there is one more ADO object type that you need to know about, but it's very simple.)

	To create a Parameter object, use the Command object's CreateParameter method, then append the new Parameter to the Parameters collection:

	Dim cm as Command

Dim R as Recordset

Set cm = New Command

cm.commandText="getAuthor"

set cm.ActiveConnection = conn

cm.Parameters.Append cm.CreateParameter _

    ("au_id", adVarChar, adParamInput, 11, "238-95-7766")

Set R = cm.Execute(reccount, , adCmdStoredProc)

	To create a Parameter object, you specify the name, data type, direction (input or output), defined size, and value of the parameter. Typically, you'll create and append Parameter objects in one statement, as shown.

	The difference in speed between dynamic SQL and stored procedures can be dramatic. It's always best to experiment with such issues yourself to get a feel for the difference. After you've done this, you'll probably agree that the extra effort required to create and call stored procedures is worthwhile. The best way to experiment is to create a noncritical project that isolates the problem. In the next section, you'll create a project that illustrates the speed difference between dynamic SQL and stored procedures.

	Investigating Stored Procedures

	For this project, you'll build a database with a large number of rows and populate the rows in code, using SQL statements. You will measure how long it takes to fill the table. Next, you will delete the data and populate the table again using a stored procedure, measuring how long that method takes.

	Create a database and call it WebData. If you're using SQL Server 6.5 or earlier, you'll need to create the database on a device with at least 20MB of free space. Create the database log with a minimum of 10MB of free space. If you're creating the database in Access, you don't have to worry about the database size (yet). The Access database is available for download from the Sybex Web site. If you purchased this book in a set, the Access database is on the set's CD.

	Note		To download code, navigate to http://www.sybex.com. Click Catalog and search for this book's title. Click the Downloads button and accept the licensing agreement. Accepting the agreement grants you access to the downloads page for the book.

	Create one table in the database, called Products, that contains these fields:

	Microsoft Access

	FieldName

FieldType

Size

	ID

AutoNumber

4

	Name

Text

100

	Description

Text

255

	Price

Currency

8

	Quantity

Long Integer

4

	SQL Server/MSDE

	FieldName

FieldType

Size

	ID

Identity

Integer

	Name

VarChar

100

	Description

VarChar

255

	Price

Currency

8

	Quantity

Integer

4

	Here's an SQL Data Definition Language (DDL) statement to drop and re-create the database. Load this script into the iSQL utility in SQL Server Enterprise Manager and run it to create the Products table.

	if exists (select * from sysobjects where id =    object_id('dbo.Products') and sysstat & 0xf = 3)

   drop table dbo.Products

GO

CREATE TABLE dbo.Products (

   ID int IDENTITY (1, 1) NOT NULL ,

   Name varchar (100) NOT NULL ,

   Description varchar (255) NOT NULL ,

   Price money NOT NULL ,

   Quantity int NOT NULL

)

GO

	Create a new IIS project and rename it WebDataAccess. Rename the default WebClass to WebData. Add a reference to the Microsoft ActiveX Data Objects 2x Library (not the ActiveX Data Objects Recordset Library or the multi-dimensional library). Enter the code from Listing 10.1 into the WebClass_Start event procedure (overwriting the default Start event code), and run the project.

	Listing 10.1: Code for Investigating Stored Procedures (WebDataAccess.dsr)

	Private Sub fillProductsSQL(whichDB As String, _

    withTran As Boolean)

    Dim SQL As String

    Dim conn As Connection

    Dim i As Integer

    Dim aName As String

    Dim aDescription As String

    Dim aPrice As Currency

    Dim aQuantity As Long

    Randomize

    Set conn = New Connection

    conn.Mode = adModeReadWrite

    If whichDB = "Access" Then

        conn.Open "WebData", "Admin", ""

        conn.Execute "DELETE FROM Products"

        Response.Write "Started Access Dynamic SQL Loop " _

        & "at: " & FormatDateTime(Now(), vbGeneralDate) _

        & "<BR>"

    Else

        conn.Open "Provider=SQLOLEDB;SERVER=Russell;DATABASE=WebData", "sa", ""

        conn.Execute "TRUNCATE TABLE Products"

        Response.Write "Started SQL Server Dynamic SQL " _

        & "Loop at: " & FormatDateTime _

        (Now(), vbGeneralDate) & "<BR>"

    End If

    If withTran Then

        conn.BeginTrans

    End If

    For i = 1 To 1000

        aName = "Product" & CStr(i)

        aDescription = "This is a test description " _

        & "of a non-existent product."

        aPrice = CCur(100 * Rnd)

        aQuantity = Int(1000 * Rnd) + 1

        SQL = "INSERT INTO Products "

        SQL = SQL & "(Name, Description, Price, Quantity) "

        SQL = SQL & "VALUES ('" & aName & "', '" & _

            aDescription & "', " & aPrice & ", " & _

            aQuantity & ")"

        conn.Execute SQL

    Next

    If whichDB = "Access" Then

        Response.Write "Finished Access Dynamic SQL " & _

            "Loop at: " & FormatDateTime _

            (Now(), vbGeneralDate) & "<BR>"

    Else

        Response.Write "Finished SQL Server Dynamic " _

           & "SQL Loop at: " & FormatDateTime(Now(), _

           vbGeneralDate) & "<BR>"

    End If

    If withTran Then

        conn.CommitTrans

    End If

    conn.Close

End Sub

Private Sub fillProductsSP(whichDB As String, withTran As Boolean)

    Dim SQL As String

    Dim conn As Connection

    Dim cm As Command

    Dim i As Integer

    Dim aName As String

    Dim aDescription As String

    Dim aPrice As Currency

    Dim aQuantity As Long

    Randomize

    Set conn = New Connection

    conn.Mode = adModeReadWrite

    If whichDB = "Access" Then

        conn.Open "WebData", "Admin", ""

        conn.Execute "DELETE FROM Products"

        Response.Write "Started Access Stored " _

           & "Procedure Loop at: " & FormatDateTime _

           (Now(), vbGeneralDate) & "<BR>"

    Else

        conn.Open "Provider=SQLOLEDB;SERVER=Russell;" & _

           "DATABASE=WebData", "sa", ""

        conn.Execute "TRUNCATE TABLE Products"

        Response.Write "Started SQL Server Stored " _

           & Procedure Loop at: " & FormatDateTime(Now(), _

           vbGeneralDate) & "<BR>"

    End If

    Set cm = New Command

    cm.CommandType = adCmdStoredProc

    cm.Parameters.Append cm.CreateParameter _

       ("Name", adVarChar, adParamInput, 100, "")

    cm.Parameters.Append cm.CreateParameter("Description", _

       adVarChar, adParamInput, 255, "")

    cm.Parameters.Append cm.CreateParameter("Price", _

       adCurrency, adParamInput, , 0)

    cm.Parameters.Append cm.CreateParameter("Quantity", _

       adInteger, adParamInput, 4, 0)

    Set cm.ActiveConnection = conn

    cm.CommandText = "InsertProduct"

    cm.CommandType = adCmdStoredProc

    If withTran Then

        conn.BeginTrans

    End If

    For i = 1 To 1000

        cm.Parameters(0) = "Product" & CStr(i)

        cm.Parameters(1) = "This is a test description of " _

            & "a non-existent product."

        cm.Parameters(2) = CCur(100 * Rnd)

        cm.Parameters(3) = CLng((1000 * Rnd) + 1)

        cm.Execute

    Next

    If whichDB = "Access" Then

        Response.Write "Finished Access Stored Procedure Loop " _

        & "at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

    Else

        Response.Write "Finished SQL Server Stored Procedure " _

        & "Loop at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

    End If

    If withTran Then

        conn.CommitTrans

    End If

    conn.Close

End Sub

Private Sub WebClass_Start()

    ' call the dynamic SQL version with no transaction

    Call fillProductsSQL("Access", False)

    Call fillProductsSQL("SQLServer", False)

    ' call the stored procedure version with no transaction

    Call fillProductsSP("Access", False)

    Call fillProductsSP("SQLServer", False)

    ' call the dynamic SQL version with a transaction

    Call fillProductsSQL("Access", True)

    Call fillProductsSQL("SQLServer", True)

    ' call the stored procedure version with a transaction

    Call fillProductsSP("Access", True)

    Call fillProductsSP("SQLServer", True)

End Sub

	The WebClass_Start event calls each function four times: twice for Access and twice for SQL Server, once with withTran (the second parameter) set to False (no transaction), and once with it set to True (use a transaction). If you have both Access and SQL Server, you will see something interesting—the Access version is over twice as fast as the SQL Server version when no transaction is in effect! When you use a transaction, however, the SQL Server version is over twice as fast as Access!

	On my computer, which has a Pentium II 400Mhz processor, it takes roughly five seconds to insert a thousand records into Access without a transaction, and twelve seconds to insert the same number of records into SQL Server. With a transaction, it still takes five seconds in Access, but less than two seconds in SQL Server.

	When you run the code, you will notice that there is little (if any) difference between the dynamic SQL version and the stored procedure version. That's because the insert operation takes so much time that the process of parsing and compiling the SQL is not apparent. Retrieving data is a different story, though.

	Listing 10.2 contains the code for another test that retrieves records from the table data you just created. To run the test, you'll need to create a stored procedure called getProduct in both Access and SQL Server. The stored procedure requires one argument: the ID of the product row to retrieve. It returns all the fields in that row. In Access the procedure (query) definition is

	PARAMETERS ID Long;

SELECT Products.*, Products.ID

FROM Products

WHERE (((Products.ID)=[ID]));

	In SQL Server, the stored procedure definition is

	CREATE PROCEDURE getProduct @ID int AS

SELECT * FROM Products WHERE ID=@ID

	Create another WebClass and set it as the start-up object, or replace the code from the previous test. On the Sybex Web site (or the CD, if you purchased this book in a set), the WebData.dsr contains a combination of the code from Listing 10.2 and Listing 10.1. Enter the code from Listing 10.2, then run the project.

	Note		If you ran the insert procedure in Listing 10.1 more than once, you'll need to adjust the random ID generation code inside the Access loop in Listing 10.2, because the AutoNumber ID in Access won't start at 1. Use the formula Int((upperbound - lowerbound + 1) * Rnd + lowerbound) where upperbound is the highest ID value and lowerbound is the lowest ID value in the Products table.

	Listing 10.2: More Code to Investigate Stored Procedures (WebData.dsr)

	Private Sub WebClass_Start()

    ' call the dynamic SQL version for each server

    Call getProductSQL("Access")

    Call getProductSQL("SQLServer")

    ' call the Stored Procedure version for each server

    Call getProductSP("Access")

    Call getProductSP("SQLServer")

End Sub

Private Sub getProductSP(whichDB As String)

    Dim SQL As String

    Dim conn As Connection

    Dim anID As Long

    Dim s As String

    Dim i As Integer

    Dim R As Recordset

    Dim cm As Command

    Randomize

    Set conn = New Connection

    conn.Mode = adModeReadWrite

    Set cm = New Command

    cm.CommandText = "getProduct"

    If whichDB = "Access" Then

        conn.Open "WebData", "Admin", ""

        Set cm.ActiveConnection = conn

        Response.Write "Started Access SQL Retrieval Loop " _

           & "at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

        For i = 1 To 1000

            anID = CLng((1000 - 1 + 1) * Rnd + 1)

            Set R = cm.Execute(, Array(anID), adCmdStoredProc)

            R.Close

        Next

    Else

        conn.Open "Provider=SQLOLEDB;SERVER=Russell;" & _

            "DATABASE=WebData", "sa", ""

        Set cm.ActiveConnection = conn

        Response.Write "Started SQL Server SQL Retrieval Loop " _

            & "at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

        For i = 1 To 1000

            anID = CLng((1000 - 1 + 1) * Rnd + 1)

            Set R = cm.Execute(, Array(anID), adCmdStoredProc)

            R.Close

        Next

    End If

    If whichDB = "Access" Then

        Response.Write "Finished Access SQL Retrieval Loop " & _

           "at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

    Else

        Response.Write "Finished SQL Server SQL Retrieval Loop " & _

           "at: " & FormatDateTime(Now(), vbGeneralDate) & "<BR>"

    End If

    Set R = Nothing

    conn.Close

    Set conn = Nothing

End Sub

	This time, the results are a little different. Each procedure generates a random value between 1 and 1,000, then retrieves the record from the Products table that corresponds to the random ID. The procedure getProductSQL retrieves the records by using dynamic SQL. The getProductSP retrieves the records by using a stored procedure. Each procedure loops 1,000 times, so each retrieves 1,000 records.

	On my computer (a 400Mhz Pentium II running NT Workstation), the getProductSQL procedure takes about five seconds running against Access and about two seconds running against SQL Server. The stored procedure version still takes five seconds running against Access (no improvement) but takes less than one second running against SQL Server.

	The results of these two tests should show you several things:

	•		You're better off running SQL Server in Web applications.

	•		Use transactions when inserting data.

	•		Use stored procedures when retrieving data.