Implicit connection to prevent ADO from connecting to SQL Server
Report Date: 2002/9 prepared by: Zheng Wei
Article Last Modified On 2002-9 The Information In this article applies to:
ü Microsoft SQL Server 2000, 7.0
ü Microsoft ADO 2.5
Problem statement:
Database Server: Microsoft SQL Server 2000 and 7.0;
Database Server Patch: Microsoft SQL Server 2000 ServicePack1;
ADO Name: Microsoft Data Access - ActiveX Data Objects 2.5 Type Library
ADO Version: 2.61.7326.0
When performing the following VB code, our developers have issued questions:
CNN.Open "provider = SQLOLEDB.1;
Persist security info = false; user ID = sa;
Initial catalog = freemail; data source = SVR; connectionTIMEOUT = 10 "," ",", -1 sql = "select * from users"
SET RS = cnn.execute (SQL) SET RS2 = cnn.execute (SQL) SET RS3 = cnn.execute (SQL)
When you perform this code, you see in SQL Server Profiler, there is an Audit login event before each SQL statement is executed. The explanation of the Audit Login event is: "Collect all new connection events that occur after tracking startup, such as the client requests to connect to the server running the Microsoft? SQL Server® instance." That is, after connecting SQL Server with the Connection object, the connection is still re-established each time the SQL statement is executed, even if it is the same consNection? !
Establish an event probe record (in time order):
EventClass
TEXT DATA
TraceStart
Audit login
(First connection)
- Network Protocol: LPC
Set quoted_identifier on
Set Implicit_Transactions Off
SET CURSOR_CLOSE_ON_COMMIT OFF
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
Set concat_null_yields_null on
Set Language Simplified Chinese
SET DATEFORMAT YMD
Set DateFirst 7
SQL: STM TSTARTING
SELECT * FROM USERS
Audit login
(2nd connection)
- Network Protocol: LPC
Set quoted_identifier on
SET IMPLICIT_TRANSACTION OFF ... 略
SQL: STM TSTARTING
SELECT * FROM USERS
Audit login
(3rd connection)
- Network Protocol: LPC
Set quoted_identifier on
SET IMPLICIT_TRANSACTION OFF ... 略
SQL: STM TSTARTING
SELECT * FROM USERS
Audit logout
Audit logout
Audit logout
TraceStop
And if each CNN.Execute is later plus rsclose (), each Execute does not have an Audit login event before each execute, but a continuous 3 SQL: StmtStarting event.
This is often generating physical connections, will it affect performance? As usual, should you reuse the same connection?
Cause:
This situation is called implicit login.
When the SET is a Ado.Recordset object receiving the record set returned by Ado.connection.execute, an implicit login occurs, and then establishes a physical connection again and the database server, and this connection has not been reused, and it is not a pool.
This reason is:
Because the SQL Server OLE DB provider does not 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.
Can refer to Microsoft's KB documentation:
Http://support.microsoft.com/default.aspx?scid=kb;n-gb;q271128&gssnb=1
"PRB: Implicit Connections Created By The SQL Server Ole DB Provider (Sqloledb) Are Not Pooled
[Not repeatedly established code snippet of database connection]:
By changing ado.recordset properties to avoid implicitly logging in DIM CN AS New AdoDB.RecordSetdim Rs2 as new adod.com.recordsetcn.open .......... rs.cursortype = adopenStatic Rs.ActiveConnection = cnrs.open "Select * from Orders" rs.cursortype = adopenStaticrs2.activeConnection = cnrs2.open "Select * from Orders" seems to be in Microsoft, only implicit connections only when receiving the default recording point . If you set ado.recordset for other types, such as static set, this problem will not occur. Of course, the default record set attribute forward is the fastest speed.
Writen by zhengyun@tomosoft.com
The information contained in this document represents the current view of the issued question, Zhengyun does not guarantee the accuracy of the information given to the date of the information.
This document is for reference only. For information in this document, Zhengyun does not make any promises or instructions. Author Blog:
http://blog.9cbs.net/zhengyun_ustc/
