Improving Performance of the Data Access Components When Running with Internet Information Server 4.0

 November 1997

by Leland Ahlbeck

Introduction

This paper addresses issues that may affect speed when returning data to a Web client running in an Internet Information Server^® (IIS) environment. This document focuses on Microsoft^® products, but some of the issues raised may relate to other OLE DB data providers or ODBC drivers. Most of the performance testing on these issues used Microsoft^® SQL Server™; therefore, the document emphasizes SQL Server.

Terminology

Data provider

A data provider is a source for OLE DB data. Examples of OLE DB data providers include the OLE DB Sample data provider that ships with the Data Access SDK, and the SQL Server data provider. Another example of a data provider is the Microsoft OLE DB data provider for ODBC data sources.

Consumer

A consumer is a user of a data provider. ADO is a consumer of OLE DB data sources.

OLE DB data provider for ODBC data sources

This data provider is effectively a translation layer between OLE DB and ODBC. It enables OLE DB consumers such as ADO to communicate with ODBC data sources.

Middle tier

The group of components that are running on the Web server. Usually, this includes server software and any components that are being used to service Web pages.

How Data Flows from the Database to the Middle Tier

Typically, when a Web server services a request for data on a Web page, many data components are involved. Keep in mind that all the processing is being done on the middle tier, where the end user’s computer is just making the request and receiving the results. Data flow follows this process:

1. Processing the request for data starts with the ASP or ISAPI DLL. This starts the process and makes the call to the OLE DB data provider.
   
   
2. The OLE DB data provider services the request and performs the query on the database. Or, in the case of the OLE DB data provider for ODBC data sources, a call is made to ODBC.
   
   
3. The server processes the request for data and returns the data to the data provider.
   
   
4. The data provider returns the data to the requesting software—either an ASP or an ISAPI DLL.
   
   
5. The data is formulated into Web page format and presented to the user.

Threading Support

Threading is the ability of the middle tier running on the Web server to communicate to other components in one thread or multiple threads. The chosen threading model can have a large impact on performance. For example, you can choose to communicate through one thread to the server hosting your data, or through multiple threads. If you choose multiple threads, you can provide support for multiple customers simultaneously.

Threading Models Defined

Multiple threading models are supported. This paper discusses only three: apartment threading, free-threading, and objects marked as both. (The default threading model set up by the installation process is the apartment model. This accommodates Microsoft Jet by default because the Microsoft Jet database engine is limited to apartment threading.) 

• In the case of apartment threading, only one thread from each object communicates with the back-end server, but the Web server can support multiple objects entered simultaneously by different threads. Apartment threading is the default threading model, not because of efficiency but because of compatibility with other Microsoft products. Microsoft Jet, the Microsoft Access database engine, supports this threading model only.
  
  
• For free-threaded objects, there are no restrictions on the number of threads that can access an object. Therefore, multiple threads access the same object, which makes calls to the database (as opposed to multiple copies of the object making calls to the database).
  
  
• For objects marked as both, the object can be accessed as either apartment or free-threaded. This offers the most flexibility for your Web server but may not be supported by all back ends.

Different components of the system can have different threading models. In the case of IIS 4.0, the component that can improve database performance is the threading model applied to and supported by the data access components. This could be either an ISAPI DLL or the ADO data access component. In either case, use the most efficient threading model supported by your OLE DB data provider.

Note   An exception to this is an ADO application running against SQL Server. You should use the apartment threading model if you will get your data either from the OLE DB data provider for Access databases, or from the Microsoft Jet ODBC database driver by way of the OLE DB data provider for ODBC data sources.

Free Threading and ADO Objects

By default, all ADO objects are marked in the registry as apartment model. If caching of ADO objects off the Application or Session object is required, you must mark the affected object as free-threaded in the registry. To do so, you will need to edit the ADOFre15.reg file. Follow these steps:

1. Locate the ADOFre15.reg file. If you accepted the default program file location when installing IIS, ADOFre15.reg will be located in C:\Program Files\Common Files\System\ADO.
   
   
2. Make the directory containing the file your current directory and issue the command RegEdit ADOFre15.reg.
   
   
3. Restart your server and the change will take effect.

If you determine you need to switch the ADO threading model back to apartment threading, follow the above instructions but edit the ADOApt15.reg file.

Sharing an ADO Connection object across multiple threads will not necessarily increase performance, and could potentially decrease it. If the provider supports connection pooling (as does running against the SQL Server ODBC 3.5 driver), this is a more efficient mechanism. Also, if you leave a cached connection open between operations, it could be dropped or the link to the server could be broken.

Connection Pooling

Connection pooling, when enabled, sets up a pool of connections to service database connection requests. Enabling this feature allows a middle tier such as IIS to hold on to the open connections and hand them out to Web clients making requests from the database. Keeping these connections open saves time when validating subsequent requests and making the connection to the back-end server. By default, connection pooling is set to On in IIS 4.0 and Off in IIS 3.0; to improve performance, it is recommended that you enable connection pooling.

When connection pooling is enabled, a request for a connection comes to the driver manager and is handled in one of three ways: If there are no connections in the pool, a new connection is created and passed to the user. If connections are available in the pool and the connection properties (User ID, Password, and so on) requested by the client match the connection properties of the pooled connection, the client is given the open connection in the pool. If connections are available but the connection properties do not match, a new connection with the appropriate properties will be created for the client.

When using connection pooling with SQL Server or any database system that limits user logins to a specified number, keep in mind that each connection uses up one of the licensed logins. Depending on your situation, this may or may not be beneficial. The driver manager will hold on to the connection, possibly preventing another user from logging on to the server if you are running. On the other hand, when connection pooling is enabled you may have fewer user connections in the pool servicing more users.

Enabling Connection Pooling

 Version 3.0 of IIS

To enable connection pooling using IIS 3.0, follow these steps:

1. From the Start menu, click Run. In the command line, type RegEdit and click OK.
   
   
2. In the left pane of the Registry Editor, open the following tree (the backslashes indicate subdirectories):
   HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\ W3SVC\ASP\Parameters
   
   
3. Double-click StartConnectionPool in the right pane. This opens an Edit String dialog box. Change the 0 (zero—the value for Off) to 1 (the value for On).
   
   
4. Click OK to save changes.

IIS Version 4.0

ODBC 3.5 now enables connection pooling at two levels: the database driver and the driver manager. This means that Internet Information Server automatically supports connection pooling at the driver-manager level and enables connection pooling for Microsoft SQL Server. Additionally, if you want your ODBC driver to participate in connection pooling, you must set the driver's CPTimeout value in the Windows NT^® registry. For more information about setting this value, see the ODBC documentation or visit the Microsoft ODBC Web site at http://www.microsoft.com/odbc. The benefit of the IIS 4.0 structure is the ability to enable connection pooling for some ODBC drivers and disable it for others.

Enabling Connection Pooling from Your Application

You can enable connection pooling from your application by calling the ODBC API. To enable connection pooling, call SQLSetEnvAttr, passing a value for the type of connection pool you want to enable. For more information about the parameters you can pass to enable connection pooling, see the ODBC documentation or visit the Microsoft ODBC Web site at http://www.microsoft.com/odbc/.

Connection Pooling and SQL Server Temporary Stored Procedures

It can be dangerous to create stored procedures to process prepared statements while connection pooling is enabled. Before getting into the dangers of connection pooling and temporary stored procedures, you must first understand when temporary stored procedures are created and, more importantly, when they are deleted.

Creating temporary stored procedures for prepared SQL statements is an option you can configure from the ODBC Data Source Administrator. The default for this setting is On for the SQL Server 2.65 and 3.5 drivers. This means that when an SQL statement is prepared, a temporary stored procedure is created and compiled. When the prepared command is called by ICommand.Execute or through one of the ADO methods, the temporary stored procedure is executed, saving the overhead of parsing and compiling the SQL statement. If used properly, this feature can potentially improve the performance of your application. If your SQL statement will be executed more than twice, or if it contains parameters and will be called multiple times, you should prepare the statement. Keep in mind that preparing an SQL statement carries a price, and that preparation is lost once you disconnect from the database.

When connection pooling is enabled, you must decide when to delete the temporary stored procedures. With the SQL Server 2.65 driver, temporary stored procedures are released when the connection is released. With the SQL Server 3.5 driver, you can choose to delete them either when the connection is released, or when the connection is released and as appropriate while you’re connected.

The danger with temporary stored procedures and connection pooling is that if you are using the default setting, you may run out of room in TempDB, where temporary stored procedures are created and stored. When connection pooling is enabled, a connection is made to the database; but when the client releases it, the connection goes back into the pool. When this happens, the connection is not released and the stored procedures are not deleted from TempDB. You end up with temporary stored procedures in TempDB that are no longer associated with a client and will never be called again. 

If you use the SQL Server 2.65 driver, it is advisable to disable the creation of stored procedures on prepare when connection pooling is enabled. With the SQL Server 3.5 driver, you should disable the Create Temporary Stored Procedures option or set it to “disconnect and as appropriate” when running connection pooling. Setting this option to “disconnect and as appropriate” means that the ODBC SQL Server driver will drop the connection when the OLE DB ICommand object that created the temporary stored procedure is released. If your client code is ADO, the stored procedure will be released when the ADODB.Recordset and command object are closed.

If you are using ODBC 3.0 and a connection without DSN, or a file DSN, to connect to your server, you can set the additional connection properties supported by your driver. For a listing of keywords supported by your driver, see the driver-specific documentation. The following ADO Connection example shows how to connect to an SQL Server database without using a DSN. In addition to specifying the server, user ID, and password, it specifies the default database.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString ="DRIVER=SQL Server;
SERVER=DAPIPERF03;UID=sa;PWD=;DATABASE=ssNSTL"
cn.Open

Monitoring Connection Pooling

With ODBC 3.5 you can monitor connection pooling using the NT Performance Monitor (PerfMon.exe). Using Performance Monitor, you can watch connection pooling at work. The driver manager will keep statistics on connection pooling, which you can then view through the NT Performance Monitor. For Performance Monitor to track changes in connection pooling, you need to make some additions to the system registry on the IIS server.

Registry Entries for Monitoring Connection Pooling

As with any changes you make to the registry, it is extremely important to back up the registry first. To do this, use the backup facilities provided in the registry editor.

To change the registry to monitor connection pooling, follow these steps:

1. Save the attached files and copy them to your Windows NT system directory.
   
   
2. In the Windows NT system directory, run this command: RegEdit CPMonitor.reg. This step adds the necessary keys to the registry to track the connection pooling performance counters.
   
   
3. In the Windows NT system directory, run lodctr odbcperf.ini. This step loads the information about the performance counters that you will view with Performance Monitor.
   
   
4. Restart your server.

Connection Pooling Counters and What They Mean

After restarting your server, start Performance Monitor (PerfMon.exe). In the Add Counter dialog box, there will be an entry for ODBC Connection Pooling under the process list box, with six new keys that you can monitor. The naming convention of the keys makes a distinction between hard and soft connections and disconnections. The connection pooling process takes hard actions. The application utilizing connection pooling takes soft actions.

You should see the most activity from the ODBC Soft Connection Counter, which shows you the number of connections that are satisfied by the connection pool. In this case, a live connection is coming from the pool rather than having to make a connection to the database.

Other Performance Counters

Other performance counters also can be useful for evaluating system performance. The following table details additional performance counters, the objects they are based on, and a description of why they are interesting.

Connection Pooling and Dead ODBC Connections

When a connection is severed from the database, it potentially could prevent all applications requesting connections from connecting to the database. If a dead connection were handed to an application as a working connection, the application would fail and the dead connection would be placed back into the connection pool. Once the dead connection was back in the pool, the driver manager would keep handing it to other applications requesting a connection.

In an effort to fix this problem, ODBC released a fix to the driver manager that would use an SQL Server-specific driver attribute to detect a dead connection at disconnect time. If the connection were dead, it would be destroyed rather than being placed back into the pool. This behavior fixed the problem of dead connections.

However, this correction introduced another problem with multithreaded applications that use ODBC. When the server crashes and no longer responds to the connection requests, all the connections are deleted from the pool. The multithreaded application attempts to make a new connection to the server, which does not respond. While the first connection is waiting to time out, the application makes many more connections. Eventually, the ODBC client will be swamped by connection requests. In an effort to detect dead servers, the driver manager will retry the connection to the server only once. If it is successful, the connection will be handed to the application requesting the connection. If the connection attempt fails, the connection is returned to the pool to block future connection attempts.

Dead Server Detection in Detail

There are two ways the driver manager attempts to determine if the server is dead. One technique involves checking the read-only connection attribute SQL_ATTR_CONNECTION_DEAD to determine if the server is working prior to returning the real connection to the connection pool. The second involves a number of ODBC status codes that contain information about network failure. These codes are then used to determine whether the problem was related to network failure or other problems.

The following procedure shows the series of events used by the driver manager to determine if the server is not responding. The process is based on a flag and a reconnect time, which is representative of the amount of time you will wait prior to attempting to reconnect to the server.

1. The first time a connection is determined to be dead, it is placed back in the pool and marked as dead. The time that a reconnection should be attempted is noted on the connection before it is placed back into the pool.
   
   
2. When the driver manager retrieves a connection from the pool that has been marked as dead, and the reconnect time has not yet been reached, it is immediately returned to the pool and returns an error to the application. This blocks other applications from trying to connect to the server. In this case, an error is returned to the requesting application.
   
   
3. When a connection marked as dead is retrieved from the pool at the reconnect time, the connection is immediately returned to the pool to prevent other applications from trying to connect to the server. The connection to the server is then retried. If the connection is successful, the dead connection is removed from the pool and you allow the pool to function as normal. If the connection to the server fails, you reset the retry time on the dead connection in the pool.

Controlling Dead Server Detection

A new registry setting controls the retry timeout. This registry key allows you to alter the default value of 60 seconds to any time interval. You can find it in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling\Retry Wait. The key is of data type REG_SZ and is the timeout in seconds.

You can also control this setting from your application by calling the ODBC function ODBCSetTryWaitValue. The function takes a DWORD as an argument and returns TRUE if successful.

Following is a Visual Basic for Applications code sample that demonstrates setting ODBCSetTryWaitValue. In this example the value is set to 30, for a 30-second timeout on a dead connection.

Declare Function ODBCSetTryWaitValue Lib "ODBC32.dll" (ByVal dwTimeOut As Integer) As Boolean
Sub SetServerWait()
    Dim fRetValue As Boolean
    fRetValue = ODBCSetTryWaitValue(30)
End Sub

This setting was added to the Service Pack 1 release, which is available at the Microsoft ODBC Web site at http://www.microsoft.com/odbc/. 

Cursor Considerations

When programming your application, you should consider the type of cursor you are requesting from the server. In the case of Microsoft SQL Server, you have a choice; each cursor carries specific functionality, benefits, and limitations. If your application is returning data from the server in a forward-only manner, the most efficient cursor to use will be Default Result Set. The default result set in SQL Server translates to a  forward-only, read-only cursor. But in this case, all the query results are pushed from the server to the workstation without any further requests from the workstation. There are some limitations associated with this cursor; see the SQL Server documentation for a more complete description of this cursor type. Coding your application so it opens a dynamic, keyset, or static cursor only when necessary to update or delete records will improve the overall performance of the application. You should give special consideration to cases where you are planning to use a dynamic cursor, because it is by far the most expensive cursor you can request from a performance standpoint. In many cases, a keyset cursor would provide adequate functionality and optimal performance.

Cursor Summary

To get optimal performance from your application, you need to select the appropriate cursor for your needs. Each cursor contains a specific set of functionality, so choosing your cursor carefully will ensure that you aren't paying a performance price for functionality you do not need. The following table describes cursor types available from SQL Server and other ODBC data source back-end servers. For specific information about which cursors are supported by your back-end server, see your server documentation.

Setting Cursors under ADO, and OLE DB Provider for ODBC Drivers and ODBC

The following table details the settings required for different clients to achieve the specified cursor server side type. This information was tested against SQL Server. If you are using a different database server, check to see which cursor types it supports.

^*When setting the cursor, to get the default result set you need to be aware that rowset size has an effect on the cursor you set. In order to get the default result set, you need to set the rowset size to 1 before executing the SQL statement. For efficiency when returning data, set the rowset size to a higher value after executing the SQL statement. If you are making your call from ADO, you do not need to be concerned with this setting, because the OLE DB provider for ODBC will make the appropriate settings for you.

The following code sample demonstrates how to open a default result set cursor using ADO:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
cn.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Lots_O_Rows”,cn

The following code example shows how to request a dynamic cursor from SQL Server.  Notice the setting for rs.CacheSize, which controls the ODBC rowset size:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
cn.Open
Set rs = New ADODB.Recordset
rs.CacheSize = 100
rs.Open "SELECT * FROM Lots_O_Rows”, cn, adOpenDynamic, adLockBatchOptimistic 

Cursors and Supported Properties

The following table details the properties the cursor will exhibit, providing the concurrency is set to adLockBatchOptimistic and the table the cursor is open against contains an index. To test whether a property is available for the selected cursor, call the rs.Supports function. For more information about the Supports function or the properties listed below, see the ADO documentation.

On a table with an index, opened with adLockBatchOptimistic, the following CursorTypes are applicable as indicated per property.

On a table with an index, opened with the concurrency option set to adLockBatchOptimistic, the following CursorTypes are applicable as indicated per method.

Additional Notes

1. Although you can request batch updating with a forward-only and dynamic cursor, you cannot update more than one row at a time. Users should use keyset or static cursors with batch updating. If you are using SQL Server as your back-end server, the only cursor type that will do true batch updating is keyset.

IRowsetScroll is not implemented in the OLE DB provider for ODBC drivers; thus the AbsolutePosition and AbsolutePage properties are write-only. Furthermore, these properties require bookmarks and will not be supported by SQL Server when requesting a dynamic cursor.

QBU Cursors

QBU cursors are high-breed cursors that support updates via query-based operations. Consequently, you can request a forward-only/read-only QBU cursor from the OLE DB data provider. This gives you a forward-only/read-only cursor that supports updates to the result set, but you will not be able to see the updates unless you close your rowset and reopen it. OLE DB actually takes your update to the rowset and formulates an SQL statement, creating a new command object and executing the SQL statement against your database. The problem with this technique is that when the OLE DB code formulates the SQL statement, it might use all of its column values to formulate the WHERE clause of the statement, depending on your database design and the version of the ODBC driver you are running.

If your application is doing just a few updates to the database, it is advisable to use a  forward-only/read-only QBU cursor. This is more efficient than using a dynamic cursor  that would directly support your updates to the database. This cursor type's biggest limitation is that you will not be able to see your updates to the database until you close and reopen your recordset. To determine whether this is more efficient, you should run the application both ways with a timer.

Scrolling Backward

In this area, performance improved greatly with ODBC 3.5 drivers and version 1.5 of the OLE DB provider for ODBC data sources. Unfortunately, big improvements did not occur across all cursor types. Static cursors have shown the most improvements in scrolling backward. These performance improvements have made it possible to implement bookmark technology underneath the ODBC SQLScrollFetch interface. The OLE DB provider for ODBC drivers makes calls to SQLScrollFetch using bookmarks to fetch backward. If you are connecting to SQL Server and need to scroll backward, the keyset cursor would be your best choice.

Network Issues

SQL Server Network Libraries

When you install the SQL Server ODBC driver on a computer, the only network library usually installed is Named Pipes; for performance reasons, you should install additional libraries. The most direct way to get the additional network libraries is to install the SQL Server client utilities, which will install the additional network libraries. After installation, they are available through the ODBC Data Source Administrator. The network library with the best performance is TCP/IP.

Configuring Network Libraries

You can configure each DSN to work with a different network library. When you set up the DSN you can optionally choose the network library that you want the DSN to use.  By default the SQL Server DSN will be configured to use Named Pipes. If you are using the SQL Server 2.65 driver, the network library is listed on the DSN configuration screen. Enter the DLL name of the network library you want to use. The process for the SQL Server 3.5 driver is simpler: On the second screen of the DSN Wizard, click the Client Configuration command button. Choose the Network Library tab from the SQL Server client configuration utility screen. Then you will see a list box of the network libraries that are currently installed on your computer. Be sure you have set up your SQL Server to support the network library you have chosen. Once you have set up a library for one connection, it will become the default for all subsequent connections, as well as any DSN-less connections that your application may use.

Another point about network libraries: After configuring the client and server computers to use a network library other than Named Pipes, you should not remove Named Pipe support from the server. The SQL Server Enterprise Manager uses this protocol to communicate with the server. Removing it will prevent SQL Server Enterprise Manager from communicating with your server.

Networking Bandwidth and Protocols

Many larger corporate networks run multiple protocols to accommodate different generations of network expansion. Some consideration should be given to the interaction of these protocols. For example, some servers running IPX will continually announce themselves with a broadcast message. This message leads to additional network traffic that may have an adverse effect on system performance.

TCP/IP currently provides the best performance. However, multiple network protocols are necessary for some corporate environments; eliminating other protocols and just running TCP/IP may not be practical. With this in mind, it might be possible to isolate the Web server and database server from the other protocols by not routing those protocols to the portion of your network that contains the IIS components. This would create a somewhat isolated network environment for your Web-based applications. Eliminating this additional network traffic may have a positive impact on overall performance.