避免ADO衔接SQLServer时的隐式衔接[MSSQL防范]
本文“避免ADO衔接SQLServer时的隐式衔接[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
避免ADO衔接sql server时的隐式衔接
|
Report Date: 2002/9chin a i t p oe er . co mNRaoA |
Prepared by: 郑 昀 |
|
Article last modified on 2002-9chin a i t p oe er . co mNRaoA | |
|
The information in this article applies to: ü Microsoft SQL Server 2000,7.0 ü Microsoft ADO 2.5 | |
问题报告:
数据库服务器:Microsoft SQL Server 2000以及7.0;
数据库服务器补钉:Microsoft SQL Server 2000 ServicePack1;
ADO名称:Microsoft Data Access - ActiveX Data Objects 2.5 Type Library
ADO版本:2.61.7326.0
履行下面的VB代码时,我们的开辟人员产生了疑问:
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)
履行这段代码时,在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
|
EventClass |
Text Data |
|
TraceStart |
|
|
Audit Login (第一次衔接) |
-- 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 简体中文 set dateformat ymd set datefirst 7 |
|
SQL:Stm tStarting |
Select * from users |
|
Audit Login (第2次衔接) |
-- network protocol: LPC set quoted_identifier on set implicit_transactions off…略 |
|
SQL:Stm tStarting |
Select * from users |
|
Audit Login (第3次衔接) |
-- network protocol: LPC set quoted_identifier on set implicit_transactions off…略 |
|
SQL:Stm tStarting |
Select * from users |
|
Audit LOGOut |
|
|
Audit Logout |
|
|
Audit Logout |
|
|
TraceStop |
|
而假如每句cnn.Execute背面加上rs.close(),则每个execute之前不会有Audit Login事件,而是持续的3个SQL:StmtStarting事件.
这样频繁成立物理衔接,能否会影响性能?按例说应当重用同一个衔接才对呀?
Cause:
这种情形叫做隐式登录.
当set一个ADO.Recordset对象接纳ADO.Connection.Execute返回的记录集时,就会呈现隐式登录,再次和数据库服务器成立一次物理衔接,并且这个衔接还没有办法重用,也不能池化.
这个的缘由是:
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.
可以参考微软的KB文档:
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》
【不会反复成立数据库衔接的代码片断】:
通过改变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情形履行速度最快.
Writen by zhengyun@tomosoft.com
本文档所包含的信息代表了在公布之日,ZhengYun 对所谈论问题的当前见解,Zhengyun 不保证所给信息在公布之日今后的精确性.
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“避免ADO衔接SQLServer时的隐式衔接”更多相关内容
中查找“避免ADO衔接SQLServer时的隐式衔接”更多相关内容