This document provides tips and guidelines for improving the performance of your Microsoft® Data Access Components (MDAC) application. Sample code is also included to illustrate many of the suggestions. In studying the samples, it is important to keep the following in mind:
CAUTION This document describes general guidelines for improving your application performance. Some of the suggestions work as suggested only under certain circumstances. Where possible, such constraints have been called out. After implementing some of the suggested changes, you should measure application performance to validate the benefit from the changes. Some modifications could be detrimental to the performance of your application if the suggestions are inappropriate to your application scenario.
This section contains the following general tips for improving the performance of your application:
This section provides sample code to use for strongly typing your variables:
Sub StronglyTyped()
Dim con As New Connection
con.Open "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
End Sub
Sub NotStronglyTyped()
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
End Sub
Code in the StronglyTyped() subroutine uses vtable binding. Since the type of the variable is known at compile time, the vtable offset of the Connection::Open function is determined from the typelib at compile time and the generated code simply uses the offset to invoke the function. Since the type of the Connection variable isn’t known at compile time in the NotStronglyTyped() subroutine, the generated code dynamically gets the information to invoke the function at run-time using Idispatch, which is much slower.
This section provides sample code for binding columns to field object when looping through records:
Sub ColumnsBound()
Dim rs As New Recordset
Dim fldId, fldFName, fldLName
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
rs.Source = "select au_id, au_fname, au_lname from authors"
rs.Open
Set fldId = rs.Fields(0)
Set fldFName = rs.Fields(1)
Set fldLName = rs.Fields(2)
While Not rs.EOF
Debug.Print fldId.Value, fldFName.Value, fldLName.Value
rs.MoveNext
Wend
End Sub
Sub ColumnsNotBound()
Dim rs As New Recordset
Dim fldId, fldFName, fldLName
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
rs.Source = "select au_id, au_fname, au_lname from authors"
rs.Open
While Not rs.EOF
Debug.Print rs("au_id"), rs("au_fname"), rs("au_lname")
rs.MoveNext
Wend
End Sub
The code in ColumnsBound() obtains references to the field objects at the beginning and simply uses the references when looping through the records. On the other hand, code in ColumnsNotBound() incurs the overhead of looking up fields in the Recordset::Fields collection for each record in the Recordset. If the Recordset is large, this can get quite expensive.
Use an SQL statement to update data whenever possible. Although updating data through the Recordset object is much more convenient, it is also much more expensive. Using an SQL statement to update date is more cumbersome, but it is well worth the trouble.
This section provides sample code for using stored procedures with output parameters instead of singleton selects:
Sub SingletonSp()
Dim cmd As New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
cmd.CommandText = "GetAuthorName"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Id", adChar, adParamInput, 11)
cmd.Parameters.Append cmd.CreateParameter("FName", adChar, adParamOutput, 30)
cmd.Parameters.Append cmd.CreateParameter("LName", adChar, adParamOutput, 30)
cmd(0) = "172-32-1176"
cmd.Execute
Debug.Print cmd(1), cmd(2)
End Sub
Sub SingletonSelect()
Dim rs As New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
rs.Source = "select au_fname, au_lname from authors where au_id = '172-32-1176'"
rs.Open
Debug.Print rs!au_fname, rs!au_lname
End Sub
When you know that a query will yield only a single row of data, use a stored procedure with output parameters instead of opening a Recordset to fetch that data. When you use a Recordset, the query results returned by the data source consist of data and metadata. The metadata can often be much larger than the data, or be a significant part of the query results. In such cases, using a stored procedure with output parameters is often more efficient.
This section provides sample code for using the collect methods for singleton selects if you must use a cursor:
Sub Collect()
Dim rs As New Recordset
rs.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
rs.Source = "select au_id, au_fname, au_lname from authors where au_lname = 'Green'"
rs.Open
Debug.Print rs.Collect(0), rs.Collect(1), rs.Collect(2)
' VBA shortcut
Debug.Print rs!au_id, rs!au_fname, rs!au_lname
End Sub
The Recordset::get_Collect() and Recordset::put_Collect() methods are shortcuts on the Recordset object for quickly getting and setting the value of a field without first having to obtain a field reference. The collect methods are the most appropriate functions to use for singleton selects when you don’t need to obtain or set any properties other than Field::Value.
Though it is common to use select queries, consider requesting only the columns you need. Also consider adding restrictions to your query to limit the number of records returned.
The ActiveX Data Object (ADO) default of adUseServer, adOpenForwardOnly, and adLockReadOnly offers the best performance for a forward-only scan through records. Don’t use a cursor with more functions if your application doesn’t require it.
If you don’t need scrolling or updating capabilities, don’t ask for them. However, if you do need scrolling capability, don’t default to server cursors like ADO does. The ADO CursorLocation default is adUseServer, primarily for backward-compatibility reasons. However, for most scrolling scenarios, you are much better off using a client cursor. Only in specific scenarios such as extremely large datasets are you better off with a server cursor.
When using a client cursor, don’t use anything other than adLockReadOnly LockType unless you really need it. If you ask for updating, the client-cursor engine must get additional metadata, which can be very expensive to retrieve.
ActiveX Data Object (ADO) uses the Recordset CacheSize property to determine the number of rows to fetch and cache. While you are within the range of cached rows, ADO just returns data from the cache. When you scroll out of range of the cached rows, ADO releases the cache and fetches the next CacheSize rows.
Unfortunately, there isn’t a single optimal CacheSize that is appropriate for all applications. Test your application with different CacheSize values, and use the one that results in the best performance. (Using a large CacheSize provides a significant improvement for fetching data from an Oracle® data source.)
Release your ActiveX Data Objects as soon as possible. This frees the database and other resources that might be expensive to hold for an extended period.
This section provides sample code for describing your own command parameters:
Sub UserDescribedParameters()
Dim cmd As New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
cmd.CommandText = "CreditAccount"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Return Value", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Credit", adInteger, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("Balance", adInteger, adParamOutput)
cmd.Parameters(1).Value = 100
cmd.Execute
Debug.Print cmd.Parameters(0).Value, cmd.Parameters(2).Value
End Sub
Sub ProviderDescribedParameters()
Dim cmd As New Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
cmd.CommandText = "CreditAccount"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 100
cmd.Execute
Debug.Print cmd.Parameters(0).Value, cmd.Parameters(2).Value
End Sub
In many data sources, getting command parameter information is often as expensive as executing the command. When possible, describe the command parameters yourself (as in UserDescribedParameters()), instead of getting the parameter information from the provider (as the code in ProviderDescribedParameters() does).
MDAC 2.0 ships with native providers for three SQL data sources:
In earlier versions, you had to go to the OLE database provider for Open Database Connectivity (ODBC) data that used the appropriate ODBC driver to access these data sources.
With MDAC 2.0, however, you can use the native OLE database providers to 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 Jet provider to Microsoft Jet Engine interfaces.
This section provides sample code for dissociating your client cursor from the connection for R/O and long-use scenarios:
Sub DissociateCon()
Dim con As New Connection
Dim rs As New Recordset
con.CursorLocation = adUseClient
con.Open "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
Set rs = con.Execute("select au_lname, au_fname from authors")
Set rs.ActiveConnection = Nothing
Set con = Nothing
‘
‘ Process data
‘
End Sub
Dissociated Recordset is a feature of the client cursor engine. Use this feature when you are doing a time-consuming operation that doesn’t need to keep expensive database resources open. If necessary, you can reassociate the Recordset back to the connection at a later time to perform updates.
This section provides sample code for using adExecuteNoRecords for non-row-returning commands:
Sub ExecuteNoRecords()
Dim con As New Connection
con.Open "Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;"
con.Execute "insert into AddressBook values(5, 'Kannan', 'Suresh')", , adExecuteNoRecords
End Sub
ActiveX Data Objects 2.0 (ADO) includes a new ExecuteOptionEnum option called adExecuteNoRecords, that you should use for non-row-returning commands. When you specify adExecuteNoRecords, ADO does not create a Recordset object, does not set any cursor properties, and specifies IID_NULL REFIID on ICommand::Execute. Since IID_NULL is specified on ICommand::Execute, the provider can also optimize for this case by not verifying any rowset properties.
ActiveX Data Objects 2.0 (ADO) optimizes one-time command executions done through Connection::Execute. This is a common user scenario in IIS, ASP, and MTS environments, in which the code typically opens a connection, executes a row- 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, thus improving performance. (You might still need to use Recordset::Open or Command::Execute if you need a more functional cursor or if you need to use Command::Parameters.)
This section provides sample code for using ActiveX Data Objects (ADO) C++ extensions if you use C++:
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();
}
ADO is an automation server, so it provides language-independent access to OLE database. Although ADO interfaces are accessible from languages like C/C++ and Java, structures like VARIANTs, which are easy to use from Microsoft Visual Basic for Applications, are quite cumbersome to use from C/C++ and Java.
In earlier releases, automation interfaces were hard to use from Microsoft® Visual C++® due to lack of support for COM and VARIANTs. With the addition of native compiler support for COM in Microsoft Visual C++ 5.0, however, automation servers are much easier to use.
If you still want to fetch data into C-types instead of VARIANTs, you can do so using the ADO C++ extensions. In addition to avoiding the VARIANT overhead, the C++ extensions offer good performance because ADO doesn’t need to get the column information from the provider and instead uses the column information supplied at design-time in the form of binding entries.
This section illustrates how to use ActiveX Data Objects (ADO) for Java classes if you are using Java:
import com.ms.ado.;
public class DisplayAuthors
{
public static void main(String[] args)
{
Recordset rs;
Field fldId, fldFName, fldLName;
rs = new Recordset();
rs.open( "select au_id, au_fname, au_lname from authors",
"Provider=SQLOLEDB;Data Source=sureshk1;Database=pubs;User Id=sa;Password=;");
fldId = rs.getField(0);
fldFName = rs.getField(1);
fldLName = rs.getField(2);
while (!rs.getEOF())
{
System.out.println(fldId.getString() + "\t" +
fldFName.getString() + "\t" +
fldLName.getString() + "\n");
rs.moveNext();
}
}
}
Previously, to use ADO from Java, you ran javatlb on the dll and worked with the generated wrapper classes. Unfortunately, VARIANTs aren’t easy to use from Java, so the generated classes are somewhat cumbersome. Additionally, copying VARIANT data into a native Java type, or vice versa, requires the data to be marshaled across non-garbage-collected/garbage-collected memory boundaries. This is pretty expensive, especially for BSTR/java.lang.String conversions.
ActiveX Data Objects 2.0 addresses these problems with customized Java wrapper classes that make it easier to access ADO with native Java types in the interfaces instead of VARIANTs. The ActiveX Data Object 2.0 implementation also includes some RNI code for copying the data directly to/from the Java type instead of going through VARIANTs, as the javatlb wrappers did.
To access data, you can use the appropriate accessor method (getString, getInteger, and so on) on the Field object instead of calling getValue and having to deal with the resulting VARIANT. This provides a significant performance improvement over the javatlb wrapper classes.
This section contains the following tips for improving middle-tier performance:
A database connection is an expensive resource to open and close, so 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. MDAC supports pooling at two levels: OLE database sessions and Open Database Connectivity (ODBC) connections.
If you use ActiveX Data Object (ADO), your database connections are pooled automatically by OLE database session pooling. If you use ODBC, the new Connection Pooling Tab in the ODBC Data Source Administrator controls the ODBC pooling settings and the ODBC driver manager takes care of pooling for you.
Though the ActiveX Data Object (ADO) implementation is free threaded, don’t use it in such a fashion in middle-tier applications. Don’t cache an instance of an ADO object (such as Connection) globally and invoke methods on it concurrently from multiple threads.
If your application model is such that each client request invokes the Connection::Execute method on a globally cached Connection object on the middle-tier, your application will not scale because of synchronization in the Connection::Execute method. You get much better throughput by having each client request create a new instance of a Connection object, call Connection::Open, Connection::Execute, and release the Connection object on the middle-tier. Even though each request creates a new instance of a Connection object and obtains a connection from the connection pool, the throughput is much better, since your Connection::Execute call isn’t synchronized.
ActiveX Data Object 2.0 includes some optimizations when marked as an Apartment model. It determines the threading model from the registry at dll-load time. If the threading model is Apartment, only the class factories and globals are protected with critical sections. At all other places in code, ADO doesn’t initialize, delete, enter, or leave critical sections. This significantly improves the scaling characteristic of ADO applications. Apartment is the default threading model of ActiveX Data Object 2.0 (as it was in 1.0 and 1.5), so you don’t have to do anything to take advantage of this optimization, other than use ADO in an Apartment-model fashion.
Don’t cache ActiveX Data Objects (ADO) in Global.asa.
Information in this document, including URL and other Internet web site references, is subject to change without notice. The entire risk of the use or the results of the use of this resource kit remains with the user. This resource kit is not supported and is provided as is without warranty of any kind, either express or implied. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
© 1999-2000 Microsoft Corporation. All rights reserved.
Microsoft, Windows, Windows NT, Microsoft Visual Basic, ActiveX, and Visual C++ are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries/regions.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.