INFO: ADO Spawns Additional Connections to SQL Server (194979)
The information in this article applies to:
- ActiveX Data Objects (ADO) 1.5
- ActiveX Data Objects (ADO) 2.0
- ActiveX Data Objects (ADO) 2.1 SP2
- ActiveX Data Objects (ADO) 2.5
- ActiveX Data Objects (ADO) 2.6
- ActiveX Data Objects (ADO) 2.7
This article was previously published under Q194979 SUMMARY
ActiveX Data Objects (ADO) hides many the complexities of communicating
with your database and makes writing code to query your database easy. For
example, if you run two asynchronous queries that generate cursors against
the same ADO connection object, those queries are queued. After the first
asynchronous query completes, the second one executes. Requesting two
firehose cursors on the same ADO connection object creates two actual
connections to the database.
Actually, this behavior is controlled by the OLE DB provider used to
communicate with SQL Server, not by ADO itself. ADO asks the provider
(either the native SQL Server OLE DB provider or the default provider that
communicates to ODBC, depending on how you are connecting to SQL Server) to
run the query and the provider determines whether another connection to the
database is required. If so, the provider creates that new connection. It
is possible for you to use one ADO connection object but have multiple
connections to your SQL Server database open.
The more you understand this behavior, the better you will be able to
optimize your ADO code. For example, if you open two recordset objects by
setting the connection string in the ActiveConnection property, you create
two separate connections to your database.
NOTE: This behavior is not specific to SQL Server or the providers for SQL Server.
The best way to determine how many connections your application is actually
making to SQL Server is to use SQL Server's utilities such as SQL Trace or
Performance Monitor while you run your application.
MORE INFORMATION
Following are some guidelines:
Client-Side Cursors
The ADO client cursor engine uses firehose cursors when retrieving data
from the server for optimal performance. The client cursor engine maintains
this data in its own cursor rather than taking up resources from the server
or the provider. If you perform an asynchronous query using the ADO client
cursor engine, the actual connection to SQL Server is essentially blocked
until that query completes and ADO retrieves all of the results. Therefore,
if you try to use the ADO connection object while this query is still
running you will be creating a second connection.
Server-Side Cursors
You can have multiple non-firehose cursors open on a connection and
retrieve data from any of them. However, if you have a non-firehose cursor
open and you then open a firehose cursor, you receive a second connection
to your database. The provider establishes the second connection to provide
you with as much flexibility as possible for working with your recordset
objects. This way you can still work with your non-firehose cursor and your
firehose cursors. If the provider creates the firehose cursor on the same
actual connection to the database, you would not be able to retrieve any
more information from SQL Server until you retrieve all of the data from
the firehose cursor.
In addition, if you open a firehose cursor, that connection to the database
is now tied up until you retrieve all of the data from that cursor. This
means that if you use a single ADO connection object to open a firehose
cursor and then a non-firehose cursor, the second (non-firehose) cursor
opens on a second connection to the database. If you first retrieve all of
the data from the firehose cursor, then the second (non-firehose) cursor is
opens on the same connection to the database.
Modification Type: | Minor | Last Reviewed: | 3/14/2005 |
---|
Keywords: | kbDatabase kbDSupport kbinfo kbProvider KB194979 |
---|
|