PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled (271128)

SYMPTOMS

When the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) creates implicit connections, these connections are not pooled.

The provider creates implicit connections under the following two situations:

If you create additional sessions from the same data source object while keeping the first session open, the provider creates implicit connections to the SQL Server for each additional session.
If you execute multiple commands from the same connection, and you are specifying read-only, forward-only (default resultset) cursors. Because SQL Server doesn't allow more than one pending resultset per connection per default-resultset (also known as firehose) cursor, the provider will implicitly create another connection if DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE (the default).

In both cases, the implicit connection, which is created by the provider, is not pooled. This can lead to poor performance compared to opening and closing the connection explicitly. For more information on this behavior, see the article "How Consumers Use Resource Pooling" at the following MSDN Web site:

http://msdn.microsoft.com/library/psdk/dasdk/msda3753.htm

CAUSE

Because the SQL Server OLE DB provider doesn't permit more than one set of results to be pending on a connection where the results are being returned by means of a forward-only, read-only (default-resultset) cursor, the provider needs to create an additional SQL Server connection to execute a second command on the connection. The provider will only do this implicitly if the Data Source property DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE.

RESOLUTION

To ensure pooling, you must create additional data source/session pairs, or explicitly create ActiveX Data Objects (ADO) connections. By explicitly creating the ADO connection or OLE DB Session, you can be guaranteed that the connection will be pooled by OLE DB, which means that the next request for an ADO connection or OLE DB Session can reuse an existing session and SQL Server connection.

MORE INFORMATION

The following Microsoft Visual Basic code demonstrates a situation in which an implicit SQL Server connection is created by the SQL Server OLE DB Provider. The implicit connection that is created by the second Execute command is not pooled.

Dim conn As New Connection
Dim cmd1 As New Command
Dim cmd2 As New Command
Dim rs As Recordset

conn.Open "Provider=SQLOLEDB;Data Source=YourDb;Initial Catalog=pubs", "sa"

' NOTE: If the following code were uncommented, the SQL Server Provider would generate an error 
' rather than create any implicit connections.
'conn.Properties("Multiple connections") = False

' Share the connection for the command objects.
cmd1.ActiveConnection = conn
cmd2.ActiveConnection = conn
cmd1.CommandText = "Select * from authors"
cmd2.CommandText = "Select * from titleview"

' Execute the first command and assign results to a recordset.
Set rs = cmd1.Execute

' Because results are pending on the same connection from the first command which
' executed, the next statement will cause the SQL Server OLE DB provider to create a new
' SQL Server connection to execute the SQL statement and get its set of results.
' If the following statement, which clears the results from the first execution, is run before the
' next Execute call, an implicit connection would not be created.
'    Set rs = Nothing
cmd2.Execute