Click to return to the Server Technologies home page    
Web Workshop  |  Server Technologies

Improving the Performance of Data Access Components with IIS 4.0


Leland Ahlbeck
Software Design Engineer/Test
Microsoft Corporation

March 12, 1998

Contents
Introduction
Threading Support
Connection Pooling
Connection Pooling and Dead ODBC Connections
Cursor Considerations
Network Issues

Introduction

This paper addresses issues that can affect speed in an Internet Information Server (IIS) 4.0 environment when returning data to a Web client. It assumes that you are familiar with administering your server. The discussion focuses on Microsoft products, but some of the issues raised may pertain to other OLE-DB data providers or open database connectivity (ODBC) drivers. This paper emphasizes Microsoft SQL Server, because we (the Database API Performance group) have done most of our performance testing with it.

Terminology

Data provider -- A data provider is a source for OLE-DB data. For example, the Sample data provider that ships with the OLE-DB software development kit and the SQL Server data provider (which is not yet released), are both OLE-DB data providers. 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. For example, ActiveX™ Data Objects (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 allows OLE-DB consumers, such as ADO, to communicate with ODBC data sources.

Middle tier -- The middle tier is the group of components that run on the Web server. Usually this includes your server software and any components that are used to service Web pages.

How Data Flows from the Database to the Middle Tier

Typically, when a request for data on a Web page is serviced by the Web server, many data components are involved. Keep in mind that all processing is being done on the middle tier, where the end user's computer simply makes the request and receives the results. Processing the request for data consists of these steps:

  1. The Active Server Pages (ASP) or Internet Server Application Programming Interface (ISAPI) DLL 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 then processes the request for data and returns the data to the data provider.
  4. The data provider returns the data to the requesting software. This could be either an ASP or an ISAPI DLL.
  5. The data is then formulated in Web-page format and presented to the user.

TopBack to top

Threading Support

Threading is the technology that enables the middle tier running on the Web server to communicate to other components in one or multiple threads. The threading model chosen can greatly affect performance. For example, if you have the option of communicating through one thread or multiple threads to the server hosting our data, you can provide support for multiple customers simultaneously by using multiple threads.

Threading Models Defined

There are multiple threading models supported; the three we will discuss are:

The default threading model set up by the installation process is the apartment model; this is to accommodate the Microsoft Access database engine by default, because that database engine is limited to apartment threading.

Different components of the system can have different threading models. In the case of IIS 4.0, the component that we are concerned with to 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, you should use the most efficient threading model supported by the OLE-DB data provider you are using, with the exception of an ADO application running with SQL Server. If you are getting your data from either the OLE-DB data provider for Microsoft Access databases or the Microsoft Access ODBC database driver via the OLE-DB data provider for ODBC data sources, you should use the apartment-threading model.

Free-Threading and ADO Objects

By default, all ADO objects are marked in the registry as apartment-threading model. If caching of ADO objects off the Application or Session object is required, you must mark the affected object as free-threaded. To mark the ADO objects as free-threaded in the registry, you'll need to regedit the adofre15.reg file. Here's how to do this:

  1. Locate the ADOFre15.reg file. If you accepted the defaults for program file location when installing IIS, the ADOFre15.reg file will be in c: \Program files\Common files\System\ADO.
  2. Make the directory containing the file your current directory and issue the RegEdit ADOFre15.reg command.
  3. You will need to restart your server before 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 RegEdit the ADOApt15.reg file.

Note that sharing an ADO Connection object across multiple threads will not necessarily increase performance, and could potentially be slower. If the provider supports connection pooling (as does the SQL Server ODBC 3.5 driver), this is a more efficient mechanism. Also, when leaving a cached connection open between operations, it could possibly be dropped or the link to the server could be broken.

TopBack to top

Connection Pooling

When enabled, connection pooling sets up a pool of connections to service database connection requests. Connection pooling allows a middle tier, like IIS, to hold onto the open connections and hand them out to the Web clients making requests from the database. By not closing a connection to the database and holding it for future use, the time required to validate and provide the next connection to the back-end server is reduced. By default, connection pooling is set to on in IIS 4.0 and to off in IIS 3.0; to improve performance we recommend that you enabled connection pooling for IIS 3.0.

When connection pooling is enabled, a request for a connection comes to the driver manager and the connection request is handled through one of three avenues:

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

Enabling Connection Pooling

IIS version 3.0

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

IIS version 4.0

ODBC 3.5 enables connection pooling at two levels: the database driver and the driver manager. This means that IIS 4.0 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, set the driver's CPTimeout value in the Windows NTŪ registry. For more information about setting this value, see the ODBC documentation and on the Microsoft ODBC Web site at http://www.microsoft.com/odbc/ Non-MSDN Online link. The benefit of this structure for IIS 4.0 is the ability to have connection pooling enabled for some ODBC drivers and disabled for others.

Enabling connection pooling from your application

You can enable connection pooling from your application by making a call to the ODBC API. To enable connection pooling, make a call to SQLSetEnvAttr, passing a value for the type of connection pool you wish to enable. For more information on the parameters that can be passed to enable connection pooling, see the ODBC documentation and on the Microsoft ODBC Web site at http://www.microsoft.com/odbc/ Non-MSDN Online link.

Connection Pooling and SQL Server Temporary Stored Procedure

Creating stored procedures to process prepared statements with connection pooling enabled can be a dangerous situation. Before getting into the dangers of connection pooling and temporary stored procedures, we must first understand when temporary stored procedures are created -- and more importantly -- deleted.

Creating temporary stored procedures for prepared SQL statements is an option that is configurable 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 a 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. This feature can improve the performance of your application if it is used properly. If your SQL statement is going to be executed more than two times or contains parameters and will be called multiple times, the statement should be prepared. Keep in mind that there is a price to be paid up front to prepare a SQL statement and that preparation is lost once you disconnect from the database.

When connection pooling is enabled, the issue of when temporary stored procedures should be deleted is raised. With the SQL Server 2.65 driver, they will be released when the connection is released. With the SQL Server 3.5 driver, you have an option of deleting them either when the connection is released, or as appropriate while connected.

The danger with temporary stored procedures and connection pooling is this: If you are using the default setting, you run the risk of running 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 is done with it and releases it, the connection goes back into the pool. When the connection goes back into the pool, the connection is not released and the stored procedures are not deleted from the TempDB. You effectively end up with temporary stored procedures in TempDB that are no longer associated with a client and will never be called again.

Thus, when running the SQL Server 2.65 driver, it may be advisable to disable the creation of stored procedures on preparation when running with connection pooling. With the SQL Server 3.5 driver, the option to create temporary stored procedures should be disabled or set 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 would be released when the ADODB.Recordset and command object are closed.

If you are using ODBC 3.0 and a Data Source Name (DSN)-less connection, 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, consult the driver-specific documentation. The following ADO connection example details how to connect to a SQL Server database with out using a DSN. In addition to specifying the server, user ID, and password, it also 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 Windows NT Performance Monitor (PerfMon.exe). Using Performance Monitor, we can watch connection pooling working. The driver manager will keep statistics on connection pooling, which can then be viewed through Performance Monitor. For Performance Monitor to track changes in connection pooling, some additions need to be made to the system registry on the IIS server.

Registry entries for monitoring connection pooling

When you make changes 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.

Download Download the files to track connection pooling (zipped, 1K).

  1. Download the zipped files (above) and copy them to your Windows NT system directory.
  2. In the Windows NT system directory, run the following command: RegEdit CPMon.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 we can view with Performance Monitor.
  4. Restart you server.

Connection pooling counters and what they mean

After restarting your server, start Performance Monitor (PerfMon.exe). Then go to the Add Counter dialog box, where you'll have an entry for ODBC Connection Pooling in the Process list box. You will now have six new keys that can be monitored by Performance Monitor. The distinction between hard and soft connections/disconnections is used in the naming convention of the keys. Consider hard actions those actions taken by the connection pooling process. Soft actions are actions taken by the application utilizing connection pooling.

The counter that should show the most activity is the ODBC Soft Connection Counter. This 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.

Counter Description
ODBC Hard Connection Counter per Second This is the number of actual connections per second that are made to the server. When you first put your environment under heavy load, this counter will go up very quickly. After a few seconds it will drop to zero. This is the normal behavior when connection pooling is working. Once the connections to the server have been established, they will be used and placed in the pool for re-use.
ODBC Hard Disconnect Counter per Second Displays the number of hard disconnects per second issued to the server. These are actual connections to the server that are being released by connection pooling.
ODBC Soft Connection Counter per Second This counter shows far more activity than the Hard Connections per Second counter. It shows the number of connections made to the database from open connections that were in the pool. Depending on the load on your server, it would not be uncommon for this to show 40 to 60 soft connections per second.
ODBC Soft Disconnection Counter per Second Displays the number of disconnects per second issued by the applications. When the application releases or disconnects, the connection is placed back in the pool.
ODBC Current Active Connection Counter This is the number of connections in the pool that are currently in use. With the version of ODBC that shipped with IIS 4.0, this counter is not working. Consult http://www.microsoft.com/odbc/ Non-MSDN Online link for updates to the ODBC driver manager.
ODBC Current Free Connection Counter Displays the current number of free connections available in the pool. These are live connections available for use.

Other performance counters

These performance counters that can also help you evaluate system performance. The following table details performance counters of interest, what object they are based on, and a description of why they are interesting.

Object Counter Description
SQL Server Max Users This is the maximum number of users that have concurrently connected to the SQL Server.
SQL Server Current Users This is the number of current users connected to the SQL Server. This number should be very close to the maximum number of Hard Connections per Second from the Connection Pooling object.
SQL Server Cache I/O hits This is the percentage of data being read from the SQL Server Cache as opposed to being read from disk. The higher the number the better; it is much faster to pull data from the cache.
Processor Processor utilization This performance key will show you the percentage of utilization on the server.
IIS Currently executing requests per second This is the total number of executing requests per second.
IIS Currently queued requests This is the number of requests that are waiting to be serviced by your server.

TopBack to top

Connection Pooling and Dead ODBC Connections

When a connection becomes severed from the database, it potentially can prevent all applications that are requesting connections from the pool from making a connection to the database. If a dead connection is handed to an application as a working connection, the application would then fail and the dead connection would be placed back into the connection pool. Once this 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 uses a SQL Server-specific driver attribute to detect a dead connection at disconnect time. If the connection was determined to be dead, the connection would be destroyed rather than being placed back into the pool. This fixed the problem of dead connections.

This correction to ODBC revealed another problem with multithreading applications that use ODBC. When the server crashes and no longer responds to the connection requests, all the connections are deleted out of the pool and the multithreading application will then attempt to make new connections to the server (which is dead and not responding). The multithreaded application will try to make new connections to the server. While the first connection is waiting to time out, many other connections are made by the application, and eventually the ODBC client is swamped by connection requests. To fix this, the driver manager can attempt to detect dead server. To detect dead servers, the driver manager will retry the connection to a server only once. If the retry is successful, the connection is handed to the application requesting the connection. If the connection attempt fails, the connection is returned to the connection pool to block future connection attempts.

Dead Server Detection in Detail

There are two ways the driver manager can 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 checking a number of ODBC status codes that contain information about network failure. These are used to determine if the problem was related to network failure or other problems.

The following events are used by the driver manager to determine if the server is not responding. The process is based on a flag and a reconnect time. The reconnect time is representative of the amount of time we will wait prior to attempting the reconnection 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 and that a retry has not been attempted. The time that a reconnect should be attempted is noted on the connection before it is placed back into the pool.
  2. When the driver manger 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 make the connection to the server. In this case, an error is returned to any requesting application.
  3. If the connection is marked as dead and the reconnect time has been reached, it is immediately returned to the pool to prevent other applications from trying to make a connection 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 the pool functions as normal. If the connection to the server fails, the retry time is reset on the dead connection in the pool.

Controlling Dead Server Detection

A new registry setting has been added to control the retry time-out. This registry key will enable you to alter the default value of 60 seconds to whatever you like. It is 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 time-out in seconds.

This setting can also be controlled from your application by making a call to the ODBCSetTryWaitValue ODBC function. The function takes a DWORD as an argument and returns TRUE if successful.

Following is Visual BasicŪ for Applications (VBA) code sample that demonstrates setting the ODBCSetTryWaitValue. In this example the value is being set to 30 for a 30-second time-out 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 was added to the Service Pack 1 release, which is available on the Microsoft ODBC Web site at http://www.microsoft.com/odbc/ Non-MSDN Online link.

TopBack to top

Cursor Considerations

When programming your application, consider the type of cursor you are requesting from the server. In the case of Microsoft SQL Server, you can choose the type of cursor you open; 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 is a special case -- all the results from the query 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 only a Dynamic, Keyset, or Static cursor when needed to update or delete records will improve the overall performance of the application. Special consideration should be given to cases where you are planning to use a dynamic cursor; from a performance standpoint, this is by far the most expensive cursor you can request. In many cases, a Keyset cursor could be implemented and would provide adequate functionality and optimum 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 you are not paying a performance price for functionality you do not need. The following table describes the different types of cursors available from SQL Server and other ODBC data source back-end servers. For specific information on what cursors are supported by your back-end server, consult your server documentation.

Cursor Type Description
Default Result Set
(SQL Server-specific)
This special Forward-only/Read-only cursor is available only with Microsoft SQL Server. When the server is in this mode, it does not wait for the client to request the data to provide it. Once the cursor has been opened, the server loads the wire with data; the only overhead is pulling the data off the wire.
Forward-only Forward-only cursor movement can only be made one row at a time in a forward direction (from the top to the bottom of the result set).
Keyset Keyset cursor membership is fixed, but the data is up-to-date with the underlying database. In other words, new rows added by users will not appear in the recordset but updates to the records will become visible. All types of movement are supported. For your cursor creation to be successful, you will need an index build on your table.
Dynamic Data is up-to-date at all times. All types of movement are supported. Depending on the provider, bookmarks may be supported.
Static This cursor effectively provides a snapshot of the data, but depending on the provider may also enable the user to see and perform inserts and updates to the recordset. Inserts and updates from other users will not be dynamically added to the result set. All types of movement are supported, but providers may or may not support bookmarks.

Setting Cursors

The following table lists the settings required for different clients to achieve the specified server-side cursor type. The following information was tested against SQL Server. If you are using another database server, you may need to verify what cursor types are supported by the server.

Cursor Type ADO OLE-DB Provider for ODBC
Default Result Set* No action required. Set properties:
None

Forward-Only/Read-Only Set the cursor location property on the connection object to adUseServer and the cursor type to adOpenForwardOnly on the execute. Set properties:
-DBPROP_OTHERINSERT = VARIANT_FALSE
-DBPROP_OTHERUPDATEDELETE = VARIANT_FALSE
-DBPROP_OWNINSERT = VARIANT_FALSE
-DBPROP_OWNUPDATEDELETE = VARIANT_FALSE
Dynamic Set the cursor location property on the connection object to adUseServer and the cursor type to adOpenDynamic on the execute. Set properties:
-DBPROP_OTHERINSERT = VARIANT_TRUE
-DBPROP_OTHERUPDATEDELETE = VARIANT_TRUE
-DBPROP_OWNINSERT = VARIANT_TRUE
-DBPROP_OWNUPDATEDELETE = VARIANT_TRUE
Keyset Set the cursor location property on the connection object to adUseServer and the cursor type to adOpenKeyset on the execute. Set properties:
-DBPROP_OTHERINSERT = VARIANT_FALSE
-DBPROP_OTHERUPDATEDELETE = VARIANT_TRUE
-DBPROP_OWNINSERT = VARIANT_TRUE
-DBPROP_OWNUPDATEDELETE = VARIANT_TRUE
Static Set the cursor location property on the connection object to adUseServer and the cursor type to adOpenKeyset on the execute. Set properties:
-DBPROP_OTHERINSERT = VARIANT_FALSE
-DBPROP_OTHERUPDATEDELETE = VARIANT_FALSE
-DBPROP_OWNINSERT = VARIANT_TRUE
-DBPROP_OWNUPDATEDELETE = VARIANT_TRUE
*When setting the cursor to Default Result Set, you need to be aware that rowset size has an effect on the cursor you set. To get the Default Result Set, you need to have the rowset size set to one prior to 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 take care of making 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 sample shows how to request a Dynamic cursor from SQL Server. Note the setting for rs.CacheSize; this 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 lists 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 if a property is available for the cursor, select or call the rs.Supports function. For more information on the Supports function or the properties listed below, see the ADO documentation.

Supports Option adOpen
ForwardOnly
AdOpenDynamic AdOpenKeyset adOpenStatic
adAddNew True True True True
adApproxPosition False False False False
adBookmark False False True True
adDelete True True True True
adHoldRecords False False True True
adMovePrevious False True True True
adResync False False True True
adUpdate True True True True
adUpdateBatch True True True True

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

Method adOpen
ForwardOnly
AdOpenDynamic AdOpenKeyset adOpenStatic
AbsolutePage Not Applicable Not Applicable Write-Only Write-Only
AbsolutePosition Not Applicable Not Applicable Write-Only Write-Only
ActiveConnection Read/Write Read/Write Read/Write Read/Write
BOF Read-Only Read-Only Read-Only Read-Only
Bookmark Not Applicable Not Applicable Read/Write Read/Write
CacheSize Read/Write Read/Write Read/Write Read/Write
CursorType Read/Write Read/Write Read/Write Read/Write
EditMode Read-Only Read-Only Read-Only Read-Only
EOF Read-Only Read-Only Read-Only Read-Only
Filter Read/Write Read/Write Read/Write Read/Write
LockType Read/Write Read/Write Read/Write Read/Write
MaxRecords Read/Write Read/Write Read/Write Read/Write
PageCount Not Applicable Not Applicable Read-Only Read-Only
PageSize Read/Write Read/Write Read/Write Read/Write
RecordCount Not Applicable Not Applicable Read-Only Read-Only
Source Read/Write Read/Write Read/Write Read/Write
Status Read-Only Read-Only Read-Only Read-Only

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

Method adOpen
ForwardOnly
AdOpenDynamic AdOpenKeyset adOpenStatic
AddNew Yes Yes Yes Yes
CancelUpdate Yes Yes Yes Yes
CancelBatch Yes Yes Yes Yes
Clone No No Yes Yes
Close Yes Yes Yes Yes
Delete Yes Yes Yes Yes
GetRows Yes Yes Yes Yes
Move Yes Yes Yes Yes
MoveFirst Yes Yes Yes Yes
MoveLast No No Yes Yes
MovePrevious No Yes Yes Yes
Open Yes Yes Yes Yes
Requery Yes Yes Yes Yes
Resync No No Yes Yes
Supports Yes Yes Yes Yes
Update Yes Yes Yes Yes
UpdateBatch Yes Yes Yes Yes

Additional notes

  1. While 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.
  2. 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 are not supported by SQL Server when requesting a dynamic cursor.

Query Based Update (QBU) Cursors

A QBU cursor is a hybrid cursor, that supports updates via query-based operations. Consequently, you can request a Forward-only/Read-only QBU cursor from the OLE-DB data provider. This cursor really 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 and reopen your rowset. OLE-DB is really taking your update to the rowset and formulating a 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 may use all the column values it has 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 may be advisable to use a Forward-only/Read-only QBU cursor. This would be more efficient than using a Dynamic cursor that would support your updates to the database directly. 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 if this is more efficient for your situation, it's advisable to run the application both ways with a timer.

Scrolling Backwards

This is an area where performance is greatly improved with ODBC 3.5 drivers and version 1.5 of the OLE-DB provider for ODBC data sources. Unfortunately, we have not seen big improvements across all cursor types. Static cursors have shown the most improvement in the area of scrolling backward. The implementation of bookmark technology underneath the ODBC SQLScrollFetch interface has made these performance improvements possible. The OLE-DB provider for ODBC drivers makes calls to SQLScrollFetch using bookmarks to fetch backwards. If you are connecting to SQL Server and need to scroll backwards, your best choice of cursors would be to use Keyset.

TopBack to top

Network Issues

SQL Server Network Libraries

When the SQL Server ODBC driver is installed on a machine, usually the only network library 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 on the machine. This will install the additional network libraries. After being installed, the libraries are available through the ODBC Data Source Administrator. The network library that we have found to have the best performance is TCP/IP.

Configuring Network Libraries

Each DSN can be configured to work with a different network library. When you set up the DSN, you can choose the network library that you want the DSN to use. By default, the SQL Server DSN will be configured to use Named Pipes. To specify a network library other than the default, see the SQL Server 2.65 driver network library that is listed on the DSN configuration screen. Enter the DLL name of the network library you wish 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 button. Choose the Network Library tab from the SQL Server Client Configuration Utility Screen. From this screen, you are presented with a list box of the network libraries that are currently installed on the machine. Be sure you have set up your SQL Server to support the network library you choose. Once you set 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 on network libraries: After configuring the client and server machines to use a network library other than Named Pipes, you should not remove the Named Pipe support on the server. This protocol is used by the SQL Server Enterprise manager to communicate with the server. Removing this protocol will prevent the SQL Server Enterprise manager from communicating with your server.

Networking Bandwidth and Protocols

Many larger corporate networks are running 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 Internetwork Packet Exchange (IPX) will continually announce themselves with a broadcast message. This broadcast message leads to additional network traffic that may adversely affect system performance.

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



Back to topBack to top

Did you find this material useful? Gripes? Compliments? Suggestions for other articles? Write us!

© 1999 Microsoft Corporation. All rights reserved. Terms of use.