MDAC 2.5 SDK - Technical Articles
Suresh Kannan
August 1999
This document provides suggestions and guidelines for improving the performance of your MDAC application. The suggestions offered here constitute good coding practice. Performance might not noticeably improve unless accessing data is a significant part of your application processing.
Where possible, sample code has been provided to illustrate key points. In studying the samples, it is important to keep in mind the following considerations:
A strongly typed variable is explicitly declared to represent only one variable type. Once declared, it cannot be used to represent a different variable type, as can weakly typed variables.
Microsoft Visual Basic allows weakly typed variables through the use of the Variant type. The Variant type can store almost any other kind of variable or object. While this gives you automatic type conversions so that you don't have to pay attention to the variable types, it can make debugging difficult. Occasionally, this automatic conversion will transform the data into a type that you didn't expect or intend, and tracking down where that happened is very difficult.
Microsoft Visual Basic defaults to the Variant type for all variables that are declared without a specific type and for all variables that are not declared at all. However, for better performance (unless the Variant type is specifically required for a property or method), avoid using Variant variables. Instead, use the Option Explicit statement to require declarations for all variables, and provide a specific type declaration for all variables that you declare. Alternatively, you can use the Deftype statements to change the default type for variables that are created and not declared or that are declared without a specific type.
A variable that is not declared will be created by Visual Basic with a default type. As discussed above, normally it will be implicitly created as a Variant. However, if you've used the Deftype statements, you can change the default.
Following is an example where the variables are implicitly created:
Public Sub NoExplicitDeclare(cnn)
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rs1!SomeText.Value
Debug.Print txt
End Sub
This code has a problem that the compiler won't catch, so you probably won't find it until you run the code. What is supposed to happen is that rsl should be assigned to a Recordset returned from the open Connection object cnn. However, when you run it, you'll get an error on the line txt = rs1!SomeText.Value. Visual Basic detects that rs1 is an empty Variant and generates a run-time error, "Object Required." In a more complex algorithm, you might be led to believe that the Execute method encountered an error. The real problem is that the variable rsl is misspelled as rs1. This problem is masked because the syntax and the identifiers appear correct at first glance.
If you place the Option Explicit statement at the top of the module, Visual Basic will generate a compiler error, and after you've declared rsl as a Recordset object and txt as a String, Visual Basic will highlight rs1 with the error "Variable not defined."
Next is an example where the variables are explicitly created:
Option Explicit
Public Sub NoExplicitDeclare(cnn As Connection)
Dim rsl As Recordset
Dim txt As String
Set rsl = cnn.Execute("SELECT * FROM Nonsense")
txt = rsl!SomeText.Value
Debug.Print txt
rs1.Close
Set rs1 = Nothing
End Sub
Object variables represent pointers to COM objects. They can be declared in two ways, as follows:
Microsoft Visual Basic must use late binding for all weakly typed object variables. This means Visual Basic must indirectly make every method and property call as each call is encountered at run time. Visual Basic does this by using the IDispatch interface to retrieve the identifier of each method and property function at run time and then calling IDispatch again to actually invoke the method or property. Although Visual Basic caches some of the information from IDispatch, using the late binding approach is still the slowest way to invoke methods and properties.
To create a strongly typed object variable, use the As keyword with the Dim, Private, Public, or Static statements and use the name of an object from one of the object libraries selected in the Project References dialog box. Visual Basic will read the object library at compile time to avoid calling IDispatch to get the identifiers of the methods and properties of the object. This is called early binding. In early binding, Visual Basic still invokes the properties and methods of the object through the IDispatch interface.
If the objects described in the object library use dual interfaces, it means they support not only the IDispatch interface but also a table of function addresses that mirror the properties and methods available through IDispatch. Visual Basic can then call the functions that handle the methods and properties of the object directly, bypassing the IDispatch interface altogether. This is called vtable binding, and it is the fastest way to invoke the properties and methods of an object. A vtable is simply a table of function addresses. If the object does not support dual interfaces, Visual Basic will use early binding instead.
All objects in ADO use dual interfaces, so Visual Basic can work several times faster with your ADO objects if you make your object variables strongly typed with the Dim variable As type statement. The type of the object is typically the name of an object from the object library, although it can also be the name of an interface. If you use the name of the object, Visual Basic silently substitutes the name of the default interface for that object.
Visual Basic chooses early or late binding depending on how you declare the object variable and not on how you create the object. In other words, it doesn't matter whether you use the Set variable = New type statement or the CreateObject function to create the object. What matters is how you declare the object variable. If you declare it as a specific type, Visual Basic will use early binding. If you declare the object variable as Object, Variant, or leave it undeclared, Visual Basic will use late binding.
The code in the StronglyTyped subroutine below uses the faster vtable binding by declaring the variable con as a Connection object:
Sub StronglyTyped()
Dim lngState As Long
Dim con As Connection
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
In the NotStronglyTyped subroutine below, the type of the Connection variable isn't known at compile time, so it's declared As Object. Visual Basic uses the slower late binding method to call the methods and properties of the same Connection object used in the StronglyTyped subroutine.
Sub NotStronglyTyped()
Dim lngState As Long
Dim con As Object
Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
lngState = con.State
con.Close
Set con = Nothing
End Sub
Although object variables represent pointers to COM objects, merely declaring an object variable does not automatically create an instance of a COM object. Visual Basic offers two ways to create an instance of a COM object: implicit and explicit. While implicit creation can save some time in development, it usually costs you much more time in debugging and doesn't help at all in performance.
To implicitly create an object, use the As New keyword of the Dim statement. This permits Visual Basic to create the object automatically when you use the object variable and when the object has not been created already. When you use the Dim...As New... feature, Visual Basic adds code before every object reference to determine at run time whether the object is instantiated. This code automatically creates the object if the object variable is either not initialized or set to Nothing.
By using Dim... As New..., you'll lose some of the control over object references. For simple procedures, this won't be an issue. Visual Basic will automatically release all objects after the procedure has ended. You will take a negligible performance hit for using the automatic object creation feature that As New provides. Only you can decide this trade-off is worthwhile for simple procedures, but once you decide, be very consistent.
The following example shows implicit object creation with the As New keywords:
Sub AutomaticCreation()
Dim rsNS As New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
End Sub
There is an important reason why you might want to avoid Dim...As New in some circumstances, even for simple procedures. There are times when having an object variable set to Nothing is a valid, testable state. The Dim...As New feature prevents an object variable from ever being set to Nothing when you test its state. For example, the following snippet will always print, "n is set" to the Debug window:
Dim n As New Recordset
If n Is Nothing Then
Debug.Print "n is nothing"
Else
Debug.Print "n is set"
End If
In the preceding example, the very act of testing to see whether the object is created creates the object! In cases where the object is set to Nothing to indicate a valid condition, as is the case with the NextRecordset method of the Recordset object, you will never be able to detect that the object is set to Nothing.
To explicitly create an object, do not use the As New feature. If you use the object variable and the object has not been created already, Visual Basic will raise an error. You must use the Set statement to create the object when you will use it.
The following example shows explicit object creation:
Sub ExplicitCreation()
Dim rsNS As Recordset
Set rsNS = New Recordset
rsNS.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rsNS.CursorLocation = adUseServer
Set rsNS = Nothing
End Sub
If you are going to use a particular stored procedure, view, or SQL statement several times, don't create a new Command object each time. Use a static variable or a module-level variable to keep a reference to each Command object that you will reuse.
Note This technique might not work well for VBScript running in Active Server Pages, or for applications written for Microsoft Component Transaction Services.
There are at least two common ways to get the values for fields in a Recordset. One way is to look up each field by its name or ordinal position from the Fields collection of the Recordset object each time you need the value. The other way is to reuse the Field objects in the Fields collection as you iterate through the records in the Recordset.
The code in ColumnsNotBound looks up fields by ordinal position. This incurs the overhead of looking up each field in the Fields collection for each record in the Recordset. For a Recordset with many records, this can get quite expensive.
Sub ColumnsNotBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Do Until rsNS.EOF
strText = rsNS.Fields(0).Value
strMoney = rsNS.Fields(3).Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
The code in ColumnsBound, however, obtains references to the Field objects at the beginning and simply uses those same references when looping through the records.
Sub ColumnsBound()
Dim rsNS As Recordset
Dim strText As String
Dim strMoney As String
Dim fldText As Field
Dim fldMoney As Field
Set rsNS = New Recordset
rsNS.Open "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " _
"FROM Nonsense", "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set fldText = rsNS.Fields(0)
Set fldMoney = rsNS.Fields(3)
Do Until rsNS.EOF
strText = fldText.Value
strMoney = fldMoney.Value
rsNS.MoveNext
Loop
rsNS.Close
Set rsNS = Nothing
End Sub
Try to avoid the use of cursor-based updating. Although using an SQL statement to update data is not feasible in many scenarios, you should use it where possible. Although updating data through the Recordset object is often more convenient, it is also much more expensive. Despite being cumbersome to use, updating data through an SQL statement is well worth the trouble. The routine that uses SQL will update several dozen records in a 5,000 record table about 30 times faster than the routine that uses a cursor.
The following example shows cursor-based updating:
Sub ADOUpdate()
Dim cnNS As Connection
Dim rsNS As Recordset
Dim fldText As Field
Dim fldNumber As Field
Dim lngUpdateEach As Long
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.ConnectionString = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cnNS.Open
Set rsNS = New Recordset
Set rsNS.ActiveConnection = cnNS
rsNS.CursorLocation = adUseClient
rsNS.CursorType = adOpenStatic
rsNS.LockType = adLockBatchOptimistic
rsNS.Open "Nonsense", , , , adCmdTable
Set fldText = rsNS!SomeText
Set fldNumber = rsNS!SomeNumber
rsNS.MoveFirst
Do Until rsNS.EOF
If fldNumber.Value Mod lngUpdateEach = 0 Then
fldText.Value = UCase$(fldText.Value)
End If
rsNS.MoveNext
Loop
rsNS.UpdateBatch
rsNS.Close
cnNS.Close
Set rsNS = Nothing
Set cnNS = Nothing
End Sub
The next example uses an SQL statement to update data:
Sub SQLUpdate()
Dim cnNS As Connection
Dim lngUpdateEach As Long
Dim cmNS As Command
Set cmNS = New Command
lngUpdateEach = Rnd * 99 + 1
Set cnNS = New Connection
cnNS.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set cmNS.ActiveConnection = cnNS
cmNS.CommandText = "UPDATE Nonsense SET SomeText = UPPER(SomeText) " _
& "WHERE SomeNumber % " & lngUpdateEach & " = 0"
cmNS.CommandType = adCmdText
cmNS.Prepared = True
cmNS.Execute , , adExecuteNoRecords
cnNS.Close
Set cnNS = Nothing
Set cmNS = Nothing
End Sub
When you know that the result of your query will yield only a single row of data, instead of opening a Recordset for fetching that data, you can use a stored procedure with output parameters.
When you use a Recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.
The following example shows a singleton select statement:
Sub SingletonSelect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.CursorLocation = adUseServer
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
For n = 1 To 50
rs.Source = "SELECT sometext, sometime FROM nonsense " & _
"WHERE ID = '" & CStr(CLng(Rnd * 5000)) & "'"
rs.Open
strText = rs.Fields(0).Value
timTime = rs.Fields(1).Value
rs.Close
Next
Set rs = Nothing
End Sub
The next example retrieves two output parameters from a stored procedure that selects a single record:
Sub SingletonSp()
Dim cmd As Command
Dim strText As String
Dim timTime As Date
Dim n As Integer
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput, 4)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
For n = 1 To 50
cmd(0).Value = Rnd * 5000
cmd.Execute , , adExecuteNoRecords
strText = cmd(1).Value
timTime = cmd(2).Value
Next
Set cmd = Nothing
End Sub
Here is the code for the stored procedure:
CREATE PROCEDURE [GetTextTimeUsingID]
@inID int = 0,
@outText char(32) OUTPUT,
@outTime datetime OUTPUT
AS
IF @inID = 0 RETURN 2
SELECT @outText = SomeText, @outTime = SomeTime FROM Nonsense
WHERE ID = @inID
RETURN
The Collect method is a hidden method of the Recordset object that lets you quickly get and set the Value property of a Field object without having to obtain a field reference first. This method is appropriate to use when you aren't interested in obtaining or setting any properties other than Field.Value.
Following is an example using the Collect method:
Sub Collect()
Dim rs As Recordset
Dim strText As String
Dim timTime As Date
Set rs = New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
rs.Source = "SELECT SomeText, SomeNumber, SomeTime, SomeMoney " & _
"FROM Nonsense WHERE ID = '2500'"
rs.Open
strText = rs.Collect(0)
timTime = rs.Collect(2)
rs.Close
Set rs = Nothing
End Sub
Although it is easy to fall into the habit of using SELECT * queries, consider asking only for the columns you need. Also, consider adding restrictions, such as WHERE clauses, to your query to limit the records returned.
If you don't need scrolling or updatability, don't ask for it. The ADO defaults of adUseServer, adOpenForwardOnly, and adLockReadOnly offer you the best performance for doing a forward-only scan through the records. Don't ask for a more functional cursor if your application doesn't require it.
If you do want scrolling, don't default to server cursors as ADO does. The ADO CursorLocation default is adUseServer primarily for backward compatibility reasons. However, for most scrolling scenarios, you will be much better off with a client cursor. Only in specific scenarios, such as extremely large data sets, will you be better off with a server cursor. When using a client cursor, don't ask for anything other than a LockType value of adLockReadOnly unless you really need it. If you ask for updatability, the client cursor engine needs to get additional metadata, which can be very expensive to retrieve.
ADO uses the Recordset.CacheSize property to determine the number of rows to fetch and cache. This especially affects server-side cursors. While you are within the range of cached rows, ADO just returns data from the cache. When you scroll out of the range of cached rows, ADO releases the cache and fetches the next CacheSize rows. The default value for the CacheSize property is 1.
How do you determine what value you should use for the CacheSize property in your application? Unfortunately, there isn't a single optimal CacheSize value for all applications. You should try tuning your application with different CacheSize values, and use the value that offers you the best performance. For example, knowing that a small CacheSize value significantly improves performance for fetching data from an Oracle data store might be an important factor for you.
Whenever possible, release ADO objects as soon as you're done with them. This frees up the database and other resources that might be expensive to hold for an extended period. Explicitly close all objects rather than allowing the object to close itself as it is destroyed.
In many data stores, getting command parameter information is often as expensive as executing the command. Describe the command parameters yourself instead of getting the parameter information from the provider.
The following example shows how to get the parameter information from the provider:
Sub ProviderDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
The next example shows how to describe the parameters manually:
Sub UserDescribedParameters()
Dim cmd As Command
Dim lngRetVal As Long
Dim strText As String
Dim timTime As Date
Set cmd = New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
cmd.CommandText = "GetTextTimeUsingID"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append _
cmd.CreateParameter("Return Value", adInteger, adParamReturnValue)
cmd.Parameters.Append _
cmd.CreateParameter("inID", adInteger, adParamInput)
cmd.Parameters.Append _
cmd.CreateParameter("outText", adChar, adParamOutput, 32)
cmd.Parameters.Append _
cmd.CreateParameter("outTime", adDate, adParamOutput, 8)
cmd.Parameters(1).Value = 100
cmd.Execute
lngRetVal = cmd.Parameters(0).Value
strText = cmd.Parameters(2).Value
timTime = cmd.Parameters(3).Value
Set cmd = Nothing
End Sub
MDAC ships with native providers for several data stores, including SQL Server, Oracle, and Microsoft Jet (.mdb). With earlier versions, you had to go through the OLE DB Provider for ODBC, which in turn used the appropriate ODBC driver to access these data stores. The provider used as the default for connections is still the OLE DB Provider for ODBC, but you should use these native OLE DB providers so that you can access your data faster and with lower disk and memory footprint. The SQL Server provider is written to TDS, the Oracle provider to OCI, and the Microsoft Jet provider to the Microsoft Jet Engine API.
Disconnected Recordset objects are supported by the client cursor engine. Use this feature when you are performing a time-consuming operation that doesn't require expensive database resources to be held open. If you need to, you can later reconnect the Recordset to the connection to perform updates.
Following is an example that shows how to disconnect a Recordset:
Sub DisconnectRS()
Dim con As Connection
Dim rs As Recordset
Set con = New Connection
Set rs = New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=persons;" _
& "Initial Catalog=Performance;User ID=sa;Password=;"
Set rs = con.Execute("SELECT SomeText, SomeNumber, SomeTime, " & _
"SomeMoney FROM Nonsense")
Set rs.ActiveConnection = Nothing
Set con = Nothing
'
' Process data
'
rs.Close
Set rs = Nothing
End Sub
ADO includes an ExecuteOptionEnum option called adExecuteNoRecords. Use this option for commands that do not return rows. When this option is specified, ADO does not create a Recordset object, does not set any cursor properties, and specifies IID_NULL REFIID on ICommand::Execute. Also, because IID_NULL is specified on ICommand::Execute, the provider can optimize for this case by not verifying any rowset properties.
The following example demonstrates how the adExecuteNoRecords option is used:
Sub ExecuteNoRecords()
Dim con As Connection
Set con = New Connection
con.Open strConnection
con.Execute "INSERT INTO Nonsense VALUES('" & Greeking(32) & _
"', " & CStr(CLng(Rnd * &H7FFFFFFF)) & _
", " & CStr(CLng(Now)) & _
", " & CStr(CCur(curStart)) & ",DEFAULT ,DEFAULT, DEFAULT, " & _
"DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)", , _
adExecuteNoRecords
con.Close
Set con = Nothing
End Sub
ADO has some optimizations for one-time command executions when the executions are done through the Execute method of the Connection object. This is a common user scenario in Microsoft Internet Information Server (IIS), Active Server Pages (ASP), and Microsoft Component Services (formerly Microsoft Transaction Server) environments, where the code typically opens a connection, executes a row returning or non–row returning command, processes results, and closes the connection. For such scenarios, use Connection.Execute instead of Recordset.Open or Command.Execute. When you use Connection.Execute, ADO doesn't preserve any command state information, which leads to an improvement in performance. However, if you need a more functional cursor or if you need to use Command.Parameters, you might still need to use Recordset.Open or Command.Execute.
ADO is an Automation server, which means it implements Automation interfaces. Therefore, it provides language-independent access to OLE DB. However, although ADO interfaces are accessible from C, C++, and Java, structures such as VARIANTs, which are easy to use from Visual Basic for Applications (VBA), are quite cumbersome to use from C, C++, and Java.
In versions of Microsoft Visual C++ earlier than 5.0, Automation interfaces were hard to use because of lack of language support for COM and VARIANTs. Now, with native compiler support for COM in Visual C++ 5.0, Automation interfaces are much easier to use from Visual C++. However, if you still want to fetch data into C types instead of VARIANTs, you can do so by using the ADO C++ extensions. Besides avoiding the VARIANT overhead, the C++ extensions offer good performance. When you use them, ADO doesn't need to get the column information from the provider. Instead, ADO uses the column information supplied at design time in the form of binding entries.
The following C++ code example shows how to use the ADO C++ extensions to get the values from three VARCHAR fields.
class CAuthor :
public CADORecordBinding
{
BEGIN_ADO_BINDING(CCustomRs1)
ADO_VARIABLE_LENGTH_ENTRY4(1, adVarChar, m_szau_id,
sizeof(m_szau_id), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(2, adVarChar, m_szau_fname,
sizeof(m_szau_fname), FALSE)
ADO_VARIABLE_LENGTH_ENTRY4(3, adVarChar, m_szau_lname,
sizeof(m_szau_lname), FALSE)
END_ADO_BINDING()
protected:
char m_szau_id[12];
char m_szau_fname[21];
char m_szau_lname[41];
};
void FetchAuthorData()
{
CAuthor author;
_RecordsetPtr pRs;
IADORecordBinding *piAdoRecordBinding;
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open("select au_id, au_fname, au_lname from Employees",
"Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;"
"User Id=sa;Password=;",
adOpenForwardOnly, adLockReadOnly, adCmdText);
pRs->QueryInterface(__uuidof(IADORecordBinding),
(LPVOID *)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&author);
while (VARIANT_FALSE == pRs->EOF)
{
printf("%s %s %s", author.m_szau_id, author.m_szau_fname,
author.m_szau_lname);
pRs->MoveNext();
}
piAdoRecordBinding->Release();
}
A database connection is an expensive resource to open and close. Therefore, pooling this resource offers a huge performance improvement for middle-tier applications.
When you use MDAC, you don't have to worry about how to pool your database connections. MDAC takes care of it for you. Pooling is supported at two levels: OLE DB sessions and ODBC connections. If you use ADO, your database connections are pooled automatically by OLE DB session pooling. If you use ODBC, the new Connection Pooling tab in the ODBC Data Source Administrator lets you control the ODBC connection pooling settings and the ODBC Driver Manager takes care of pooling for you.
Although the ADO implementation is free-threaded, don't use it in that way in the middle tier. Don't cache an instance of an ADO object, such as a Connection, globally and invoke methods on it concurrently from multiple threads. If each client request in your application model invokes the Connection.Execute method on a globally cached Connection object on the middle tier, your application will not scale. This is because of synchronization in the Connection.Execute method.
You will get much better throughput by using an application model where each client request creates a new instance of a Connection object, calls Connection.Open and then Connection.Execute, and releases the Connection object on the middle tier. Each request does have the additional overhead of creating a new instance of a Connection object and obtaining a connection from the connection pool. However, because your Connection.Execute call isn't synchronized, the throughput is much better.
Don't cache ADO objects in Global.asa. See "Use ADO Like an Apartment Model" earlier in this document.
Improving the performance of any application is both a science and an art. The recommendations listed here will help with many areas, but every application has different circumstances. The environments may differ from one installation to the next. A setting that makes the application run faster on one machine may make it run slower on a different machine. Even different database schemas will affect many of the suggestions in this article.
There is no substitute for advanced techniques like code profiling, performance monitoring, and good old trial and error. As with any scientific approach, you should vary only one element at a time and note whether performance improves or worsens with each variation. If performance deteriorates, before assuming that the change was the cause of the problem, revert the change and verify that you can reproduce the original behavior.