VB Toolkit: Tweaking ADO for Performance

Use ADO to create high-performance Internet applications

Let's talk about how to use ADO to create a high-performance application for the Internet or an intranet. Microsoft presented sessions at Tech Ed 99 that demonstrated how to build a Visual Basic (VB) application that scales to a large number of simultaneous users. This application demonstration addressed performance issues worth looking at.

While testing the ADO application, Microsoft discovered that different cursors directly affect an application's performance (for more information on cursors, see Morris Lewis, "How ADO Uses Cursors," page 59). ADO uses cursors to contain the logical set of rows maintained for a recordset. The cursor also provides the current position in the recordset. Developers use cursors to create a recordset to scroll forward or backward in. And you can use dynamic cursors to have the recordset pick up another user's changes.

ADO supports dynamic, keyset, static, and forward-only cursors. The four cursor types let you tune ADO to meet the needs of your application. The forward-only cursor, the default, is the fastest, and it allows only forward scrolling through the recordset.

To scroll backward through a recordset, you can use one of the other cursor types. Select one of these cursors only if you need a recordset that supports specific features because each of the other cursors requires more overhead on either the server or the client and thereby compromises performance. However, sometimes you need to use a particular cursor. For example, if you need to let users scroll forward and backward in a recordset, you can't use a forward-only cursor.

Likewise, if you need to update a recordset, use a lock type that provides the locking you need. It is important to understand how lock types and other database features, such as stored procedures and triggers, work and what the performance ramifications are. For example, stored procedures are much faster than dynamic SQL.

You can use the Open method to set the recordset's CursorType property before opening the recordset:

rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

This example sets the high-performance forward-only cursor and sets the lock type to read-only. Preventing the database from adding locks to the records contributes to reducing the overhead on the database.

Another parameter you can set is the cursor location. Like the cursor type, the cursor location has an impact on performance. Storing the cursor on the database server places the overhead on that system, whereas using a client cursor places the load on the Web server or other client where ADO code executes. If you use one system for both the database and the Web server, the cursor stays on that system no matter which cursor location you set. A server-side cursor is stored on the system that runs the database server. A client-side cursor is stored on the system that initiated the recordset. For example, if your Active Server Pages (ASP) ADO application requests a recordset with a client-side cursor, the recordset is maintained on the Web server running the ASP ADO code.

You can set the cursor location for ADO by changing the recordset's CursorLocation property as follows:

rs.CursorLocation = adUseClient

The previous examples show how you can change the ADO parameters on the fly to meet your application's requirements, thus increasing performance. If you are using the Data Environ-ment in VB, you can change some cursor settings on the Advanced tab in the Customers Properties dialog box, as Screen 1 shows. However, you cannot use the same settings with the Data Environment that you can use with ADO. For instance, you can't use the Data Environment to set the cursor location to client-side and the cursor type to forward-only.

The two lines of ADO code previously mentioned (the Open method and the CursorLocation property) came directly from the sample application developed for the scalability study that Microsoft presented at Tech Ed. Microsoft demonstrated how you can change ADO parameters to tweak the performance of an application. How did VertigoSoftware, the creators of the sample application from Tech Ed, determine which cursor type and cursor location to use to provide the best performance? In a word, testing. They tested the application and cycled through a tweak-and-test process until they ran out of time. (The time limit was the deadline for getting the application completed for Tech Ed.) Each cycle of the test provided low to high performance gains over the last cycle. The first tests topped out at eight simultaneous users, which is a far cry from the 7500 simultaneous users in the final tests.

How do you deal with the data returned in the recordset? The Tech Ed test revealed that one of the fastest ways to return the data to the calling application is to use a disconnected recordset as the return data type from the method in the COM object.

A disconnected recordset is an ADO recordset that no longer has a connection to the database. The disconnected recordset has several advantages over other ways of returning data. First, the recordset returns the data as a fully functional ADO recordset, which lets the calling application use all of ADO's features. The recordset also provides the return data with meta data such as field names and data types. Finally, you don't need to create any type of special data handler to manage the return data in both the component and client applications.

You might think it's faster to pull the data from the recordset and return it as a delimited string. The developers who tried this approach discovered that using the disconnected recordset is still faster.

To demonstrate these concepts in a complete application, I've created a simple program, based on the Northwind database. My application uses the same techniques as the Vertigo sample did. Listing 1 shows the Customer class, which does the database work. Callout A in Listing 1 shows the ADO code necessary to set the cursor location and then open the recordset with the correct options. The Open method uses a data source name (DSN) of Northwind, which points to the SQL Server database. For the sample code to run as is, you must create this DSN first. Callout B in Listing 1 shows the code to return the recordset as a return value from the function. Note that I explicitly typed the function's return value to return a recordset, which makes the code as efficient as possible.

Listing 2 shows Form1, the client application that uses the Customer object. Callout A in Listing 2 shows the creation of the references to the Customer object and the ADO recordset object. The project references, in Screen 2, are set to the Microsoft ActiveX Data Objects Recordset 2.1 Library, which provides access to the disconnected recordset object.

Callout B in Listing 2 shows the code to execute the RetrieveCustomers method in the Customer object and return the recordset. Callout C in Listing 2 is a VB loop that processes an ADO recordset.

In developing the Tech Ed demonstration application, the VertigoSoftware developers discovered many performance-boosting tips. For more information on building high-performance applications, visit these URLs: http://msdn.microsoft. com/dna and http://msdn.microsoft.com/ vstudio. Studying how other organizations improve application performance can help you tune your programs for peak performance.