防止ADO连接SQL Server时的隐式连接
Report Date： 2002/9chin a i t p oe er . co mNRaoA
Prepared by： 郑 昀chin a i t p oe er . co mNRaoA
Article last modified on 2002-9chin a i t p oe er . co mNRaoA
The information in this article applies to:chin a i t p oe er . co mNRaoA
ü Microsoft SQL Server 2000,7.0chin a i t p oe er . co mNRaoA
ü Microsoft ADO 2.5chin a i t p oe er . co mNRaoA
问题陈述:
数据库服务器：Microsoft SQL Server 2000以及7.0；chin a i t p oe er . co mNRaoA
数据库服务器补丁：Microsoft SQL Server 2000 ServicePack1；chin a i t p oe er . co mNRaoA
ADO名称：Microsoft Data Access - ActiveX Data Objects 2.5 Type Librarychin a i t p oe er . co mNRaoA
ADO版本：2.61.7326.0chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
执行下面的VB代码时，我们的开发人员产生了疑问：chin a i t p oe er . co mNRaoA
cnn.Open "Provider=SQLOLEDB.1;chin a i t p oe er . co mNRaoA
Persist Security Info=False;User ID=sa;chin a i t p oe er . co mNRaoA
Initial Catalog=freemail;Data Source=svr;ConnectionTimeout=10", "", "", -1
sql = "select * from users"chin a i t p oe er . co mNRaoA
Set rs = cnn.Execute(sql)
Set rs2 = cnn.Execute(sql)
Set rs3 = cnn.Execute(sql)
chin a i t p oe er . co mNRaoA
执行这段代码时，在SQL Server Profiler中看到，每个sql语句执行之前都会有一个Audit Login事件。而Audit Login事件的解释是：“收集自跟踪启动后发生的所有新的连接事件，例如客户端请求连接到运行 Microsoft® SQL Server" 实例的服务器”。也就是说，用Connection对象连接SQL Server之后，每次执行sql语句时仍然会重新建立一次连接，即使用的是同一个Connection？！chin a i t p oe er . co mNRaoA
建立连接的事件探查记录(按时间顺序)为：chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
EventClass chin a i t p oe er . co mNRaoA
Text Datachin a i t p oe er . co mNRaoA
TraceStartchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
Audit Loginchin a i t p oe er . co mNRaoA
(第一次连接)chin a i t p oe er . co mNRaoA
-- network protocol: LPCchin a i t p oe er . co mNRaoA
set quoted_identifier onchin a i t p oe er . co mNRaoA
set implicit_transactions offchin a i t p oe er . co mNRaoA
set cursor_close_on_commit offchin a i t p oe er . co mNRaoA
set ansi_warnings onchin a i t p oe er . co mNRaoA
set ansi_padding onchin a i t p oe er . co mNRaoA
set ansi_nulls onchin a i t p oe er . co mNRaoA
set concat_null_yields_null onchin a i t p oe er . co mNRaoA
set language 简体中文chin a i t p oe er . co mNRaoA
set dateformat ymdchin a i t p oe er . co mNRaoA
set datefirst 7chin a i t p oe er . co mNRaoA
SQL:Stm tStartingchin a i t p oe er . co mNRaoA
Select * from userschin a i t p oe er . co mNRaoA
Audit Loginchin a i t p oe er . co mNRaoA
(第2次连接)chin a i t p oe er . co mNRaoA
-- network protocol: LPCchin a i t p oe er . co mNRaoA
set quoted_identifier onchin a i t p oe er . co mNRaoA
set implicit_transactions off…略chin a i t p oe er . co mNRaoA
SQL:Stm tStartingchin a i t p oe er . co mNRaoA
Select * from userschin a i t p oe er . co mNRaoA
Audit Loginchin a i t p oe er . co mNRaoA
(第3次连接)chin a i t p oe er . co mNRaoA
-- network protocol: LPCchin a i t p oe er . co mNRaoA
set quoted_identifier onchin a i t p oe er . co mNRaoA
set implicit_transactions off…略chin a i t p oe er . co mNRaoA
SQL:Stm tStartingchin a i t p oe er . co mNRaoA
Select * from userschin a i t p oe er . co mNRaoA
Audit LOGOutchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
Audit LOGOutchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
Audit LOGOutchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
TraceStopchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
而如果每句cnn.Execute后面加上rs.close()，则每个execute之前不会有Audit Login事件，而是连续的3个SQL:StmtStarting事件。chin a i t p oe er . co mNRaoA
这样频繁建立物理连接，是否会影响性能？照例说应该重用同一个连接才对呀？chin a i t p oe er . co mNRaoA
Cause:
这种情况叫做隐式登录。chin a i t p oe er . co mNRaoA
当set一个ADO.Recordset对象接收ADO.Connection.Execute返回的记录集时，就会出现隐式登录，再次和数据库服务器建立一次物理连接，而且这个连接还没有办法重用，也不能池化。chin a i t p oe er . co mNRaoA
这个的原因是：chin a i t p oe er . co mNRaoA
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. chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
可以参考微软的KB文档：chin a i t p oe er . co mNRaoA
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q271128&GSSNB=1chin a i t p oe er . co mNRaoA
《PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled》chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
【不会重复建立数据库连接的代码片断】：
chin a i t p oe er . co mNRaoA
通过改变ADO.Recordset的属性避免隐式登录
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
cn.open ..........chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
rs.CursorType = adOpenStaticchin a i t p oe er . co mNRaoA
rs.ActiveConnection = cn
rs.Open "select * from orders"chin a i t p oe er . co mNRaoA
rs.CursorType = adOpenStatic
rs2.ActiveConnection = cn
rs2.Open "select * from orders"chin a i t p oe er . co mNRaoA
看来，确实如微软所说的，只有接收默认的记录集时才会发生隐式连接。如果设置ADO.Recordset为其它类型，如静态集，就不会发生这个问题。chin a i t p oe er . co mNRaoA
当然，默认的记录集的属性forward-only、read-only情况执行速度最快。chin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
Writen by zhengyun@tomosoft.comchin a i t p oe er . co mNRaoA
chin a i t p oe er . co mNRaoA
本文档所包含的信息代表了在发布之日，ZhengYun 对所讨论问题的当前看法，Zhengyun 不保证所给信息在发布之日以后的准确性。 chin a i t p oe er . co mNRaoA本文档仅供参考。对本文档中的信息，Zhengyun 不做任何明示或默示的保证。