Improving MDAC Application Performance

  • Article

Suresh Kannan

August 1999

Sample code update: April 2004

Summary: Get suggestions and guidelines for improving the performance of your MDAC application. The suggestions offered here constitute good coding practice.

Contents:

  • Introduction

  • General Considerations

  • Middle-Tier Considerations

  • Conclusion

Introduction

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:

  • The best way to improve the performance of your application is to understand where your bottlenecks are. If your goal is to improve the speed of an existing application, you should start by profiling and analyzing your application.

  • This document describes general guidelines for improving your application performance. Some suggestions work only under certain circumstances. Where possible, such constraints have been called out. After you implement a change suggested in this document, you should measure your application's performance to validate benefits. Some modifications could be detrimental to your application performance if they are inappropriate for your application scenario.

  • Although Microsoft® Visual Basic® for Applications has been used to illustrate most of the suggestions, you can also apply suggested techniques to clients writing to ADO in other languages, such as C, Microsoft Visual Basic Scripting Edition (VBScript), Microsoft Visual C++®, and Java.

General Considerations

Use Strongly Typed Variables

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.

Declare All Variables

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

Use Strongly Typed Object Variables

Object variables represent pointers to COM objects. They can be declared in two ways, as follows:

  • Use the keywords As Object so that the variable can be used to represent any kind of object; this is a weakly typed object variable.

  • Use the As keyword with the specific type of the object; this is a strongly typed object variable.

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;Integrated Security=SSPI;"
   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; Integrated Security=SSPI;"
   lngState = con.State
   con.Close
   Set con = Nothing
End Sub

Explicitly Create Objects

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;Integrated Security=SSPI;
   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;Integrated Security=SSPI;
   rsNS.CursorLocation = adUseServer
   Set rsNS = Nothing
End Sub

Reuse Command Objects

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.

Bind Columns to Field Objects When Looping Through the Records

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;Integrated Security=SSPI;
   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;Integrated Security=SSPI;

   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

Use Cursor-Based Updating Only If Needed

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;Integrated Security=SSPI;
   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;Integrated Security=SSPI;
   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

Use a Stored Procedure with Output Parameters Instead of Singleton Selects

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;Integrated Security=SSPI;
   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;Integrated Security=SSPI;
   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

If You Must Use a Cursor, Use the Collect Methods for Singleton Selects

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;Integrated Security=SSPI;
   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

Query Only for the Data You Need

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.

Choose CursorLocation, CursorType, and LockType Properties Carefully

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.

Tune the Recordset.CacheSize Property

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.

Release Unused ADO Objects

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.

Describe Command Parameters Yourself

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;Integrated Security=SSPI;

   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;Integrated Security=SSPI;

   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

Use Native OLE DB Providers

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.

Disconnect Your Client Cursor from the Connection for R/O and Long-Use Scenarios

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;Integrated Security=SSPI;
   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

Anticipate Non–Row Returning Commands

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

Use Connection.Execute for Single Execution

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/Transaction 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.

Use ADO C++ Extensions (C++ Users)Subsection Heading

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=MyDataSource;Database=pubs;" 
      "Integrated Security=SSPI;",  
      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();
}

Middle-Tier Considerations

Use Session (OLE DB) or Connection (ODBC) Pooling

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.

Use ADO Like an Apartment Model

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.

ASP Considerations

Don't cache ADO objects in Global.asa. See "Use ADO Like an Apartment Model" earlier in this document.

Conclusion

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.