(16楼提供了参考结论)ADO之Excel自连接会导致性能下降吗?

liu-aguang

接触ADO时间不太长,前两天看到一位版主2009年的一个帖子:<<ADO连接自身不能释放内存的一种解决方案>>
谈论的是如何解决自连接性能下降的问题,不少坛友跟帖反映效果很好.原址:
http://club.excelhome.net/thread-464455-1-1.html

看了版主的解释,有些疑惑.恰巧今天本论坛上有一个自连接的帖子,于是自己亲自测试一下,其结果却非然也!!!
是什么原因呢?
是本人对原帖的理解有误?是系统或Eexcl的版本问题?还是其它?下面把自己的测试情况向大家汇报一下,欢迎指正并讨论:

测试环境:win7 64;Excel 2003 及Excel 2010 32.

这个是测试用的各种文件附件: ADO自连接测试.rar (188.37 KB, 下载次数: 46)

2014-12-17 16:30 上传

点击文件名下载附件

liu-aguang

测试发现2003和2010测试的结论是一致的,所以下面只述及用2003的情况.
在附件中有一个文件名为:自连接测试.xls的工作簿,该工作簿中有一个数据源表,表名为:明细表
另外有一个工作簿123.xls,其中也有一个与上面明细表数据完全相同的明细表.作为比较之用.
在自连接测试.xls工作簿中有两个功能相同代码段TEST1与TEST2,一个是自连接,一个不是.都循环重复执行100次:

1. Sub TEST1()
   
2.      Dim intRow As Integer, t As Single, sql As String
   
3.     t = Timer
   
4.     For i = 1 To 100
   
5.         清空数据
   
6.     Set cnn = CreateObject("adodb.connection")
   
7.     intRow = Sheets("明细表").Range("A65536").End(xlUp).Row
   
8.     cnn.Open "provider=Microsoft.ace.OLEDB.12.0;Extended Properties=""excel 8.0;hdr=yes"";Data Source=" & ThisWorkbook.Path & "\123.xls"
   
9.     sql = "select * from [明细表$A1:D" & intRow & "] where 地点 Like '%" & Range("d2") & "%'"
   
10.     sql = sql & " and 时间 between #" & Range("b2") & "#and#" & Range("b3") & "# and 名称='" & Range("d3") & "'"
    
11.     Sheet1.Range("A6").CopyFromRecordset cnn.Execute(sql, 0, 1)
    
12.     cnn.Close
    
13.     Set cnn = Nothing
    
14.     Next
    
15.     MsgBox "TEST(1)100次时间: " & Timer - t
    
16. End Sub
    
17. 
    
18. Sub TEST2()
    
19.      Dim intRow As Integer, t As Single, sql As String
    
20.      t = Timer
    
21.      For i = 1 To 100
    
22.         清空数据
    
23.     Set cnn = CreateObject("adodb.connection")
    
24.     intRow = Sheets("明细表").Range("A65536").End(xlUp).Row
    
25.     cnn.Open "provider=Microsoft.ace.OLEDB.12.0;Extended Properties=""excel 8.0;hdr=yes"";Data Source=" & ThisWorkbook.FullName
    
26.     sql = "select * from [明细表$A1:D" & intRow & "] where 地点 Like '%" & Range("d2") & "%'"
    
27.     sql = sql & " and 时间 between #" & Range("b2") & "#and#" & Range("b3") & "# and 名称='" & Range("d3") & "'"
    
28.     Sheet1.Range("A6").CopyFromRecordset cnn.Execute(sql, 0, 1)
    
29.     cnn.Close
    
30.     Set cnn = Nothing
    
31.     Next
    
32.     MsgBox "TEST(2)100次时间: " & Timer - t
    
33. End Sub
    
34. Sub 清空数据()
    
35.   If Range("A6") <> "" Then
    
36.     Range("A6:D" & [A65536].End(xlUp).Row).ClearContents
    
37.   End If
    
38. End Sub
    

复制代码

liu-aguang

下面用"资源监视器"得到的执行一次和重复100次的结果:(为了减少相互干扰,在测试自连接与非连接时,都先关闭EXCEL,重新启动再测试另一种模式)
TEST1(非自连接):
打开EXCEL的资源使用情况:

重复执行100次后的情况:


TEST2(自连接):
打开EXCEL的情况:

重复执行100次后的情况:

liu-aguang

结论:
1.不论那种连接方式,也不论执行一次还是100次,在代码执行结束后其EXCEL进程需要占用内存及专用内存都会有所增加,但不是成倍增加.
对TEST1(非自连接)增加(10116-7644=)2472KB
对TEST2(自连接)增加(7776-5312=)2464KB
并且两种方式下,增加的专用内存都不会在EXCEL退出之前释放.
2.两种方式所用时间几乎一致
3.无论用Jet或ACE引擎其结论一致.
4.代码执行期间两种方式都不存在硬盘读写操作.

最后说明:测试发现,不同时间或方式打开同一工作簿,在没有进行任何操作之前,EXCEL的专用内存是不一样.所以测试比较要使用相对增加值.
上面只对专用内存作了比较,事实上,其它的提交(需要占用内存)/工作集(实际影射内存)变化情况是与专用内存一致的.

zhaogang1960

在Win7中确实没有发现内存占用迭加问题，xp中确实存在，楼主可以用xp测试一下

liu-aguang

zhaogang1960 发表于 2014-12-17 17:25
在Win7中确实没有发现内存占用迭加问题，xp中确实存在，楼主可以用xp测试一下

本想测试一下XP,但实在没耐性去安装一个系统.很希望还在使用XP系统的朋友测试按上述方法测试一下.
发本帖的意思是希望得出一个肯定的结论(本人实在不敢下结论),如:在某种环境下,可以不管自连接不自连接,以免在遇到自连接时,心存疑虑.

coby001

TEST1 和 TEST2 的ADO字串都是一样的，怎么比呢？
都是：
provider=Microsoft.ace.OLEDB.12.0;Extended Properties=""excel 8.0;

-
应该是
Microsoft.Jet.OLEDB.4.0','Excel 8.0;
和
Microsoft.ace.OLEDB.12.0;excel 12.0;

liu-aguang

coby001 发表于 2014-12-17 17:49
TEST1 和 TEST2 的ADO字串都是一样的，怎么比呢？
都是：
provider=Microsoft.ace.OLEDB.12.0;Extended P ...

这里讨论的是"自连接"方式与非自连接方式之比较.当然要用同一引擎.

zhaogang1960

liu-aguang 发表于 2014-12-17 17:43
本想测试一下XP,但实在没耐性去安装一个系统.很希望还在使用XP系统的朋友测试按上述方法测试一下.
发本帖 ...

一般使用中不必考虑，因为不会重复执行这个程序，但如果要做成工具程序，就要考虑内存占用问题了，在设计易用宝程序时，我本来考虑使用ADO方法生成新工作簿，以为会加快速度，不想速度特别慢，占用内存很厉害，只好使用常规法了

liu-aguang

zhaogang1960 发表于 2014-12-17 19:00
一般使用中不必考虑，因为不会重复执行这个程序，但如果要做成工具程序，就要考虑内存占用问题了，在设计 ...

谢谢赵版的经验分享.