SQL顺序连接多个工作簿速度的疑问，解释合理加技术分2分

zhaogang1960 · 发表于 2014-3-10 19:10

本帖最后由 zhaogang1960 于 2014-3-10 23:41 编辑

在解决使用SQL合并多个工作簿数据时发现：采用顺序连接每个工作簿，用GetRows方法把查询数据写入数组，采用下面两种方式速度上有很大差异，如果不加类似Set rs = cnn.Execute(SQL)语句，即使仅保留连接数据库语句，其他什么也不做，速度也会变得很慢，见第3种情况，原题目见下面链接：

跨工作簿汇总

http://club.excelhome.net/thread-1101782-1-1.html

1、

Set rs = cnn.Execute(SQL)

arr = rs.GetRows

Sub ADO加数组已知工作表名_1() '使用Set rs = cnn.Execute(SQL)，arr = rs.GetRows
tt = Timer
Dim cnn As Object, rs As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
Application.ScreenUpdating = False
Mypath = ThisWorkbook.Path & ""
MyName = Dir(Mypath & "*.xls")
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
Set rs = cnn.Execute(SQL)
arr = rs.GetRows
For i = 0 To UBound(arr, 2)
m = m + 1
brr(m, -1) = m
For j = 0 To 11
brr(m, j) = arr(j, i)
Next
Next
End If
MyName = Dir()
Loop
[a1].CurrentRegion.Offset(2).ClearContents
[a3].Resize(m, 13) = brr
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
MsgBox Timer - tt
End Sub

复制代码

2、

arr = cnn.Execute(SQL).GetRows

Sub ADO加数组已知工作表名_2() 'arr = cnn.Execute(SQL).GetRows
tt = Timer
Dim cnn As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
Application.ScreenUpdating = False
Mypath = ThisWorkbook.Path & ""
MyName = Dir(Mypath & "*.xls")
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
arr = cnn.Execute(SQL).GetRows
For i = 0 To UBound(arr, 2)
m = m + 1
brr(m, -1) = m
For j = 0 To 11
brr(m, j) = arr(j, i)
Next
Next
End If
MyName = Dir()
Loop
[a1].CurrentRegion.Offset(2).ClearContents
[a3].Resize(m, 13) = brr
cnn.Close
Set cnn = Nothing
MsgBox Timer - tt
End Sub

复制代码

3、第一种情况速度很快，第二种情况后者速度很慢，甚至仅保留连接工作簿语句，删除查询语句时，速度也很慢的奇怪现象：

Sub ADO加数组已知工作表名_3() '仅连接工作簿，不做任何操作
tt = Timer
Dim cnn As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
Application.ScreenUpdating = False
Mypath = ThisWorkbook.Path & ""
MyName = Dir(Mypath & "*.xls")
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
' arr = cnn.Execute(SQL).GetRows
' For i = 0 To UBound(arr, 2)
' m = m + 1
' brr(m, -1) = m
' For j = 0 To 11
' brr(m, j) = arr(j, i)
' Next
' Next
End If
MyName = Dir()
Loop
' [a1].CurrentRegion.Offset(2).ClearContents
' [a3].Resize(m, 13) = brr
cnn.Close
Set cnn = Nothing
MsgBox Timer - tt
End Sub

复制代码

请看附件

SQL顺序连接多个工作簿速度的疑问.rar (305.58 KB, 下载次数: 213)

Excel2007或2010附件：

SQL顺序连接多个工作簿速度的疑问20072010.rar (443.85 KB, 下载次数: 227)

该贴已经同步到 zhaogang1960的微博

jsxjd · 发表于 2014-3-10 21:09

很怪异，BUG？

百度不到去谷歌 · 发表于 2014-3-10 23:15

多处放置time做了测试发现关键在这里
Set rs = cnn.Execute(SQL)'1里注释掉这句就会一样速度了
2方法中没有显示创建rs,系统总是会隐式创建rs对象,看测试的结果就是隐式创建的速度很慢,当我把1中set rs这句注释掉速度就和2一样了所以我觉得bug应该就是这个了

zhaogang1960 · 发表于 2014-3-10 23:22

本帖最后由 zhaogang1960 于 2014-3-10 23:25 编辑

百度不到去谷歌发表于 2014-3-10 23:15
多处放置time做了测试发现关键在这里
Set rs = cnn.Execute(SQL)'1里注释掉这句就会一样速度了 ...

谢谢回复，我也是这样想的：
只要连接数据库，就会自动产生一个rs对象，不管该对象本质是什么，都要赋给一个对象变量rs,如Set rs = cnn.OpenSchema(adSchemaTables)取得工作表名、Set rs =cnn.Execute(SQL)查询等，否则如果继续连接下一个数据库，又会产生一个rs对象，会连续占用内存，从而导致速度变慢。
但没有理论根据啊

zhaogang1960 · 发表于 2014-3-11 00:05

liucqa 发表于 2014-3-10 23:41
加上这个看看

cnn.CursorLocation = adUseClient

谢谢回复，经测试：
第一、cnn.CursorLocation = adUseClient没有任何影响
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & myPath & MyName
改为
         With cnn
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .ConnectionString = "Data Source=" & myPath & MyName & ";Extended Properties=Excel 12.0"
            .CursorLocation = adUseClient
            .Open
         End With

第二、
Set rs = cnn.Execute(SQL)
arr = rs.GetRows
或
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic'1，3
arr = rs.GetRows
速度一样，都很快

mmwwdd · 发表于 2014-3-11 07:07

学习，看了很长时间

没名字了 · 发表于 2014-3-11 08:59

个人感觉没有依据也仅仅是猜测：
excel打开的时候是会在系统temp里面增加两个缓存文件，word则是在当前目录根据修改次数创建多个。(关闭时自动删除)
所以有这样的猜测，但未必准确
当我们预设了RS的时候，读取内容的存储是利用硬件内存空间。没有预设的时候，它会找一个缓存查询数据的硬盘物理空间去存储，以防大量消耗内存造成系统崩溃。这个时间差异可能就是由此产生。但是我刚才跟踪了一下它的缓存文件，发现大小并没有改变。也可能说明我的想法是错的。
不过我又实验了一下文件数量的影响，当我们只用一个数据源表的时候，速度基本上完全是一致。这又让我觉得这其中一定涉及到一个写入和清除的过程时间。

百度不到去谷歌 · 发表于 2014-3-11 09:26

本帖最后由百度不到去谷歌于 2014-3-11 09:32 编辑

没名字了发表于 2014-3-11 08:59
个人感觉没有依据也仅仅是猜测：
excel打开的时候是会在系统temp里面增加两个缓存文件，word则是在当前目录 ...

我觉得可能是隐式创建的rs会预分配比较大的内存空间导致速度变慢,而执行sql语句显式赋值的rs有确定的结果集,其空间占用反而小一些又或者是隐式创建的机制本身就慢一些不能知ado的内部原理只能猜测了

没名字了 · 发表于 2014-3-11 09:38

百度不到去谷歌发表于 2014-3-11 09:26
我觉得可能是隐式创建的rs会预分配比较大的内存空间导致速度变慢,而执行sql语句显式赋值的rs有确定的结果 ...

也有这个可能，而且比我的猜测更靠谱些：以下是我的测试，很有规律的
文件数量时间1 时间2 时间1上下差|时间2上下差|两时间差
1 0.046875 0.046875
2 0.0625 0.09375 0.015625 0.046875 0.03125
3 0.078125 0.140625 0.015625 0.046875 0.03125
4 0.09375 0.171875 0.015625 0.03125 0.015625
5 0.078125 0.21875 -0.015625 0.046875 0.0625
6 0.09375 0.25 0.015625 0.03125 0.015625
7 0.109375 0.28125 0.015625 0.03125 0.015625
8 0.125 0.328125 0.015625 0.046875 0.03125

百度不到去谷歌 · 发表于 2014-3-11 09:52

重新测试了一下想起rs对象在1中声明他以后在循环中每次都是赋值给他新的sql结果这样每次rs都没有关闭,也就是说使用的是同一个rs对象 ,只是每次接收不同的结果
而2隐式创建rs的话每次执行sql的时候,实际上都需要重新创建一个rs对象这就是为什么只执行一次的话,效率差不多
我在1代码中 loop前加上这个语句速度就明显变慢了
Set rs = Nothing
Loop
应该就是2中每次rs都需要重新创建耗费了时间!

		自动登录	找回密码
密码			免费注册

[讨论] SQL顺序连接多个工作簿速度的疑问，解释合理加技术分2分