ADO学习笔记之二从连接的内部机制来理解和创建安全高效的代码结构

liu-aguang

    在VBA中使用ADO,目的是为了利用强大的SQL查询语言访问外部数据源.如:查询,提取,修改,增删外部数据源中的数据.然而,无论进行何种操作,首先必须创建并打开一个由VBA到数据源的链接. VBA中通常用打开连接对象,即用Connection.Open语句来显式建立一个到数据源链接.如果没有显式建立链接,那么在使用其它对象时都会自动隐式建立一个临时链接.

    建立一个完整的链接在软硬件资源和时间资源方面开销很大.所以官方称链接一个昂贵的资源.然而在实际应用环境中,有时需要频繁链接与断开操作. 如对同一数据源的并发操作或对不同数据源的遍历操作. 设想在分布式的网络环境中,也许有数百万数据要求者并发操作,有什么电脑能承受这链接断开之重呢?所以ADO(其实是OLEDB)设计了一些链接机制来应对频繁链接与断开的情况,尽量减少建立一个完整链接的机会.
    然而,要让这种机制发挥其功能,它要求编程者遵循一些编程规则. 也许您已从大量实践中或从前辈的代码样本中获得了一些经验, 然,如果你希望从更高的角度去认识它们,做一个"知其然,并知其所以然"的编程者,并能在不同应用场境中灵活以对,这篇文章也许值得你去参阅.
    首先我们测试几段宏代码的运行时间差别来明确讨论的问题. 本楼层最后给出了4个代码段及它们的附件,它们都是完成同一个任务:对文件名为db1.mdb的数据库,连接/断开100次.
    测试说明:
    1. 为了使用测试更趋于真实,我们用API函数来测试经历时间,它可以精确到毫秒.注意在32位和64位的系统中,Declare语句选择提示(代码中有具体提示).
    2. 测试环境需求: 安装2007以上EXCEL; 如果是excel2003+XP sp3,你需要另外安装AccessDatabaseEngine.exe(32位版).
    下面是在笔者电脑上各段代码所用时间:
    TEST1代码段:3500 ms. 在100次的数据连接中,每次按标准的连接/断开方式编制代码结构,共有100次完整的连接与断开操作;
    TEST2代码段:210 ms. 只是在TEST1代码段的循环体外增加了一个连接;
    TEST3代码段:55 ms. 与TEST2唯一不同的是修改了一个配置;
    TEST4代码段:260 ms. 该代码结构来自赵刚老师曾经发的一个英雄帖: <<SQL顺序连接多个工作簿速度的疑问>>
    问题:
    1. TEST2比TEST1多一个连接对象, 为什么速度快10倍左右, 并且相差倍数随循环次数而增加.它给我们有什么指导意义?
    2. 同样的代码段,为什么TEST3比TEST2又快若干倍? 修改的配置是什么意思?
    3. TEST4不但打开了100次连接,并且还产生的记录集,而速度却相当于TEST2,为什么?

1. 'Private Declare Function timeGetTime Lib "winmm.dll" () As Long             '假如你的系统是32位版本请使用该语句.
   
2. Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As LongLong  '假如你的系统是64位版本请使用该语句.
   
3. Function aa() As Double
   
4. aa = timeGetTime
   
5. End Function
   
6. '以上代码段是用作测试经历时间.Sub Test1()
   
7.     tt = aa
   
8.     Dim cnn As ADODB.Connection
   
9.     For i = 1 To 100
   
10.         Set cnn = New ADODB.Connection
    
11.         cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\db1.mdb;"
    
12.         '...............
    
13.         cnn.Close
    
14.         Set cnn = Nothing
    
15.     Next
    
16.         MsgBox "TEST1经历时间: " & aa - tt & " 毫秒"
    
17. End SubSub Test2()
    
18.     tt = aa
    
19.     Dim cnn1 As New ADODB.Connection
    
20.     Dim cnn As ADODB.Connection
    
21.     myPath$ = ThisWorkbook.Path & ""
    
22.     cnn1.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "db1.mdb"
    
23.         For i = 1 To 100
    
24.         Set cnn = New ADODB.Connection
    
25.         cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "db1.mdb"
    
26.         '.............
    
27.         cnn.Close
    
28.         Set cnn = Nothing
    
29.     Next
    
30.     cnn1.Close
    
31.     Set cnn1 = Nothing
    
32.     MsgBox "TEST2经历时间: " & aa - tt & " 毫秒"
    
33. End SubSub Test3() '该段代码与TEST2完全相同.
    
34.     Rem 测试该代码段请修改注册表.位置:
    
35.     Rem HKEY_LOCAL_MACHINE/SOFTWARE/Classes/CLSID/{3BE786A0-0366-4F5C-9434-25CF162E475E}
    
36.     Rem 将该位置项目的OLEDB_SERVICES对应数据由0xfffffffe修改为0xffffffff(前面是7个F一个e;后面8个f)
    
37.     tt = aa
    
38.     Dim cnn1 As New ADODB.Connection
    
39.     Dim cnn As ADODB.Connection
    
40.     myPath$ = ThisWorkbook.Path & ""
    
41.     cnn1.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "db1.mdb"
    
42.         For i = 1 To 100
    
43.         Set cnn = New ADODB.Connection
    
44.         cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "db1.mdb"
    
45.         '.............
    
46.         cnn.Close
    
47.         Set cnn = Nothing
    
48.     Next
    
49.     cnn1.Close
    
50.     Set cnn1 = Nothing
    
51.     MsgBox "TEST3经历时间: " & aa - tt & " 毫秒"
    
52. End SubSub Test4()
    
53.     tt = aa
    
54.     Dim rst As ADODB.Recordset
    
55.     Dim cnn As ADODB.Connection
    
56.     Dim myPath$
    
57.     myPath = ThisWorkbook.Path & ""
    
58.     For i = 1 To 100
    
59.         Set cnn = New ADODB.Connection
    
60.         cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "db1.mdb;"
    
61.         Set rst = cnn.Execute("select * from 123")
    
62.     Next
    
63.     cnn.Close
    
64.     Set cnn = Nothing
    
65.     MsgBox "TEST4经历时间: " & aa - tt & " 毫秒"
    
66. End Sub
    
67. 
    

复制代码

    测试附件.rar (272.61 KB, 下载次数: 100)

2015-1-9 15:48 上传

点击文件名下载附件

     下面我们分三个部分来讨论.叙述中涉及ADO数据访问的底层技术,如果您对一些专业术语/概念不太明白,可参考下方链接的一个点击率很低的技术帖:
http://club.excelhome.net/thread-1176608-1-1.html
      一. 链接的重用与共享特性--它解释了TEST1与TEST2速度差
      二. 在ADO中应用池(Pooling)--它解释了TEST2与TEST3速度差
      三. 解析TEST4中隐含的链接特性
     

liu-aguang

    一. 连接的重用与共享
   
    在ADO出现之前,我们的应用程序是通过OLEDB去访问数据源的.OLEDB象积木,玩积木,不同的构建模块组合可以搭建功能各异的"作品"; OLEDB内部也有若干功能组件和服务. 应用程序调用不同组件或服务让它们协调工作即可访问不同的数据源,或实现不同的功能.
    ADO是OLEDB的包装,简化了对OLEDB的使用.使用ADO访问数据源,不再需要一条条语句分别去调用若干功能组件或服务,而往往是通过设置对象属性或参数来达到调用各种组件或服务的目的. 所以讨论ADO,实际是在讨论OLEDB的运行机制.在OLEDB中有两个重要的概念:数据消费者和数据提供者.
    数据消费者: 指想要得到数据源中的数据的程序或对象.如在VBA中使用ADO,可以认为VBA是数据消费者.
    数据提供者: 也叫数据提供程序(Provider).它是ADO数据访问链条中直接与数据源打交道的部分.现在一般都由数据库生产商提供,如访问Microsoft Office数据源(Excel/Access/txt...)的数据提供者为:Microsoft Ace OLEDB.12.0. 不同类型的数据库往往其数据提供者是不同的.
    用ADO(OLEDB)访问数据源有两种方式:一是在相关组件或服务的协作下直接向数据提供者要数据,由数据提供者去与数据源交互. 二是使用OLEDB提供的默认提供者(MSDASQL)调用ODBC数据源管理器,通过ODBC驱动程序与数据源交互. 上面的代码即是采用的第一种方式,如采用第二方式,其结论是一致的.本文在不特别提示时,讨论的是第一种方式.
    当我们执行Connection.open语句的时候,从VBA的角度上看是创建一个链接实例. 而从内部运行机制来看,它是要求创建并打开一个到数据存储区(Data store)的连接. ADO中连接(Connection)对象有多个属性,其中有Provider和ConnectionString属性. 它们既可在OPEN前分别设置,也可以为一个整体作为Connection打开(Open)的参数. Provider属性值(如上面示例代码中的Microsoft Ace OLEDB.12.0),是连接中要使用的数据提供者,有了它就能创建一个连接.创建的过程是对相关组件和提供者初始化的过程,也是一个开销很大的过程.ConnectionString属性,是提供者与数据源交互,即打开连接的依据.
    到这里有一个重要的概念,即重用和共享: OLEDB的组件或服务一当被调用或初始化,它是可以重用或共享的. 换句话说,创建一个连接,只要没有关闭它, 就可以被重用或共享. 在TEST2中,一个在循环体外的连接Conn1一当创建,循环体内的连接cnn使用的是同一个数据提供者,因此可以对它的重用和共享. 所以cnn.open并没有建立一个完整的连接. 从而大大节省了的运行的时间.
    可见,创建与打开连接是两个不同的过程. 只不过被ADO整合在OPEN里了.假如我们把TEST2中循环外的连接Cnn1后面的数据库换为db2.mdb;而循环体内不改变,我们会得到一致的结论.

   再看一个例子,附件中有若干不同的Excel工作簿,现在我们用重用的方式建立下面代码,对它们进行遍历:

1. 
   
2. Sub Test5()
   
3.     tt = aa
   
4.     Dim cnn1 As New ADODB.Connection
   
5.     Dim cnn As ADODB.Connection
   
6.     myPath$ = ThisWorkbook.Path & ""
   
7.     myFile$ = Dir(myPath & "*.xls")
   
8.     cnn1.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 8.0;" & _
   
9.                "Data Source=" & myPath & "1111.xls" '这是一个不存在的工作簿.
   
10.     Do While myFile <> ""
    
11.         If myFile <> ThisWorkbook.Name Then
    
12.         Set cnn = New ADODB.Connection
    
13.         cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 8.0;" & _
    
14.                  "Data Source=" & myPath & myFile
    
15.         '.............
    
16.         cnn.Close
    
17.         Set cnn = Nothing
    
18.         End If
    
19.         myFile = Dir
    
20.     Loop
    
21.     cnn1.Close
    
22.     Set cnn1 = Nothing
    
23.     MsgBox aa - tt
    
24. End Sub
    
25. 
    

复制代码

   我们惊讶地发现,被重用的Cnn1连接,在它的ConnectionString属性中指定了一个并不在的工作簿, 奇怪的是连接同样成功建立,并且也达到了重用提效的结果.这里又涉及到另外一个问题:Connecton.open是打开一个到实际数据源的连接吗?
    这个说法出现在很多资料,包括一些官方帮助里. 而在MSDN技术库中却是这样表述的：方法Open是--"Opens a connection to a data store."; 连接对象是--"A Connection object represents a physical connection to a data store". 他们使用的是数据存储区(Data
store),而非数据源(data source). 进而在基于OLEDB编程的论述中说到:当消费者向数据提供者请求一个数据源对象时,OLEDB的服务会从中拦截这一请求,并根据指定的提供者和连接属性返回给消费者一个代理数据源对象,而并不是一个实际的数据源对象.
    由此,我们是否可以这样来认为:ADO中当执行OPEN命令时,首先激活相关组件和服务,并初始化,建立连接,然后根据字符串属性构建一个与实际数据源相匹配的数据存储区,数据存储区在物理上是一个具有某种结构的缓存区. 至于与实际数据源交互是其它对象或方法的事情(如execute).
    不过,在打开连接时是否与实际数据源实现第一次握手,跟数据提供者有关,也和数据源类型有关. 比如,TEST5中,当数据源为Access时,就不能在连接字符中提供一个不存在的数据源.关于以上两点是笔者的猜测,不一定准确.
     无论如何,从上面的分析中我们得到如下结论:
     在需要频繁连接/断开的场境中,可以利用连接的重用和共享来提高效率. 它的编程技巧是: 定义并建立一个全局或模块级的可以重用的连接对象.

liu-aguang

二. 在ADO中应用池(Pooling)
   
    在TEST3中我们在注册表中修改了一个值,实际上是启用OLEDB的会话池(Session Pooling),也叫资源池(Resource Pooling).使用ADO访问数据源,即可以应用OLEDB的会话池,也可以应用ODBC的连接池(Connection Pooling).这取决于我们代码的访问方式: 如果我们的连接字符串使用ODBC方式,则可应用连接池;反之,如果用数据提供者直接与数据源交互,则可应用会话池.两种池功能基本一样,启用方式略有不同,本文主要谈会话池的使用.
     池是什么?有什么作用? 池可以存放应用程序与数据源建立的连接.在频繁连接/断开的场境中,应用程序不必每次重新建立一个完整连接.一个连接一当创建,它可以放在池中,以后有相同连接请求则可从池中寻找复用,从而提高了运行效率.下图描述了ADO数据访问体系,可以看到两种池,它们其实是OLEDB或ODBC接口内的一个核心服务.

     谈池的机制有很多细节. 简单来说,池的运作机制是:启用了池功能后,当我们open时,上面谈及的代理数据源首先会在池中寻找是否有可用连接,在可用连接中是否与请求的连接相匹配;如果有则返回消费者一个会话对象,利用这个连接;如果无则重新完整建立. 而当我们Close连接时,它把这个连接又放回池中.池的机制里还有一个超时机制和处理不健全的连接机制(指非正常释放的无用连接),到设定时间后(一般默认值为60秒)由这个机制自动从池中清理.
     使用池有几个条件:
    1. 必须启用.池是与数据提供者相联系的.有的数据提供者默认是启用池的(如,涉及到系统数据库的数据提供者);而有的数据提供者默认是不启用的(如,我们前面用到的Microsoft Ace OLEDB.12.0). 启用和禁用池的方法:对ODBC的DSN方式连接可以通过ODBC数据源管理器手动修改;对基于OLEDB编程的应用程序可以通过代码命令修改;在VBA中使用ADO的数据提供者方式,笔者发现只有通过注册表修改.
    2. 必须有配套的代码结构. 即与上一部分所述要求一样,建立一个全局或模块级的连接对象.TEST1的代码结构,就不符合该要求,如即使启用池,也不会发挥池的功能,反而会起相反作用.
    3. 必须连接字符串必须完全相同. 笔者没有对过多的数据提供者作测试,有人说连接字符串中关键字/值对顺序都必须一致,并且空格也要一致.
     值得一提的是,在遍历不同数据源时(每次连接字符串肯定不一样),启用池服务,反而会拖慢连接速度.这也许是有些数据提供者默认禁用池的原因.
     

liu-aguang

三. TEST4代码段解析

         该代码段从形式上没有满足连接复用的条件: 必须有一个全局的连接对象变量. 然而, 我们注意到, 该代码段有如下两个特征:

        1.  每次显式地打开一个连接后,并没有显式断开它.

        2.   在循环体内,每一次链接都使用了Execute方法建立了一个记录集, 并将之赋与一个记录集变量. 并且也没有设置记录集的ActiveConnection为Null.

在这种情况下, 第一次建立的连接始终存在.客观上起到了连接复用的目的.在这段代码中, 假如增加一个记录集的显式断开,并设置记录集对象为Nothing. 那么它运行的情况变得与TEST1一样.

笔者对该代码的看法:

按MSDN建义的代码编写规则: 尽可能晚打开连接,尽可早关闭连接; 如果显式打开连接, 则必须显式地关闭连接; 在关闭连接的同时,必须设置其对象为Nothing,从而让其从内存中释放. 这段代码是与之相背的.另外,如果在一个分布式的环境中,这种结构的代码, 不能使断开的连接放入池中; 记录集使用的连接也没有正常释放, 这可能带来安全的风险及性能的损失. 所以, 笔者建议可以改为MSDN建义的TEST2的方式: 通过一个全局的连接变量来实现连接的复用与共享.

29151429

学习                             

闻启学

liu-aguang 发表于 2015-1-10 09:56
三. TEST4代码段解析         该代码段从形式上没有满足连接复用的条件: 必须有一个全局的连接对象变量. 然 ...

貌似 C# 或者java 里面的连接池

liu-aguang

闻启学 发表于 2015-1-10 23:03
貌似 C# 或者java 里面的连接池

Test4代码段速度快的原因不是池的作用. 即使禁用池,也会有同样结果.
无论VBA/C等编程语言,只要利用ADO或直接基于OLEDB编程,都可以启用或禁用会话池或连接池;有些资料把会话池和连接池混为一谈,实际上是两回事.
   只有直接基于ODBC编程, 才只能使用连接池.

ivccav

楼主的专研精神可嘉，侃侃而谈的能力也很强，语文一定是学得很好的那种。

看完全文，有用的就一条：重用连接对象从而提高连接效率。

我来划一下重点吧：
1.重用连接对象，提高效率
在需要频繁连接/断开的场境中,可以利用连接的重用和共享来提高效率， 技巧是: 定义一个全局或模块级的连接对象；
2.连接池（会话池）中看不中用
连接字符串必须完全相同，连接字符串中关键字/值对顺序都必须一致,并且空格数量、标点符号都要一致。在遍历不同数据源时(每次连接字符串肯定不一样),启用池服务,会严重拖慢连接速度。因此这是很鸡肋的一条，如果数据源相同，且我还需要这个连接，我干嘛频繁断开/连接？提高效率，参考第一条总结的做法即可。

Dyx5

很喜欢这种技术贴！

sjwxsdj

学习