Problem description: 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 libraryado version: when 2.61.7326.0 executes the following VB code, our developers have doubts: 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 executing this code, you can see in SQL Server Profiler that there is an audit login event before each SQL statement is executed. The audit login event explains: "collect all New Connection events that occur after the self-tracking is started, such as client requests to connect to the server running the Microsoft SQL server instance ". That is to say, after a connection object is used to connect to SQL Server, the connection will be re-established every time the SQL statement is executed, that is, the same connection ?! The connection creation event detection record (in chronological order) is:
| Eventclass |
Text Data |
| Tracestart |
|
| Audit login (first connection) |
-- Network protocol: lpcset quoted_identifier onset implicit_transactions offset when offset ansi_warnings onset ansi_padding onset ansi_nulls onset concat_null_yields_null onset language Simplified Chinese set dateformat ymdset datefirst 7 |
| SQL: STM tstarting |
Select * from users |
| Audit login (2nd connections) |
-- Network protocol: lpcset quoted_identifier onset implicit_transactions off... Omitted |
| SQL: STM tstarting |
Select * from users |
| Audit login (3rd connections) |
-- Network protocol: lpcset quoted_identifier onset implicit_transactions off... Omitted |
| SQL: STM tstarting |
Select * from users |
| Audit logout |
|
| Audit logout |
|
| Audit logout |
|
| Tracestop |
|
If Rs. Close () is added after each CNN. Execute statement, no audit login event exists before each statement, but three consecutive SQL: stmtstarting events. Will frequent physical connections affect performance? As shown in the following example, the same connection should be reused? Cause: This is called implicit logon. When you set an ADO. the recordset object receives ADO. connection. when the record set is returned by execute, an implicit logon occurs, establishing a physical connection with the database server again, and the connection cannot be reused or pooled. The reason is: because the SQL Server OLE DB Provider doesn't permit more than 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 Prope Rty dbprop_multipleconnections is set to variant_true. can refer to Microsoft's kb document: http://support.microsoft.com/default.aspx? SCID = KB; EN-GB; q271128 & gssnb = 1 PRB: Implicit connections created by the SQL Server OLE DB Provider (sqloledb) are not pooled
[Code snippet for repeatedly establishing database connections ]:
Avoid implicit login to dim cn as new ADODB. Connection by changing the properties of ADO. recordset
Dim RS as new ADODB. recordset
Dim rs2 as new ADODB. recordset
CN. Open ..........
Rs. cursortype = adopenstaticRs. activeconnection = Cn
Rs. Open "select * from orders"
Rs. cursortype = adopenstatic
Rs2.activeconnection = Cn
Rs2.open "select * from orders" seems that, as Microsoft said, only the default record set is received. This issue does not occur if ADO. recordset is set to another type, such as a static set. Of course, the default record set attributes forward-only and read-only are the fastest to execute.
