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

百度不到去谷歌

百度不到去谷歌 发表于 2014-3-12 13:39
我是这样 连接本表的时候  我取出数据到本表临时表t 然后vba用非ado方法往t写入了数据 再sql查询t则还是以 ...

哎哟 还没结果呢 受不起啊 我有新的发现 不过还没完整的测试模型 可能有突破

zhaogang1960

百度不到去谷歌 发表于 2014-3-12 17:14
哎哟 还没结果呢 受不起啊 我有新的发现 不过还没完整的测试模型 可能有突破

现在可以把问题集中在问题3上面第一种情况，第一次连接数据库时，Set rs =……随便一个简单查询，速度就很快

1. Sub ADO加数组已知工作表名_31() '第一次连接工作簿，把查询赋值给变量
   
2. tt = Timer
   
3.     Dim cnn As Object, SQL$, Mypath$, MyName$, rs As Object, m As Byte
   
4.     Application.ScreenUpdating = False
   
5.     Mypath = ThisWorkbook.Path & ""
   
6.     MyName = Dir(Mypath & "*.xls")
   
7.     Do While MyName <> ""
   
8.         If MyName <> ThisWorkbook.Name Then
   
9.             Set cnn = CreateObject("ADODB.Connection")
   
10.             cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
    
11.             If m = 0 Then
    
12. '                Set rs = cnn.OpenSchema(20) 'adSchemaTables
    
13.                 Set rs = cnn.Execute("select * from [Sheet1$a2:l] where 1=2")
    
14.                 m = 1
    
15.             End If
    
16.         End If
    
17.         MyName = Dir()
    
18.     Loop
    
19.     cnn.Close
    
20.     Set cnn = Nothing
    
21.     MsgBox Timer - tt
    
22. End Sub
    

复制代码

第二种情况，仅有连接：

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

复制代码

百度不到去谷歌

zhaogang1960 发表于 2014-3-12 17:22
现在可以把问题集中在问题3上面第一种情况，第一次连接数据库时，Set rs =……随便一个简单查询，速度就很 ...

这个我在3楼就发现了后面一直在研究的就是为什么导致了这个情况

没名字了

zhaogang1960 发表于 2014-3-12 17:22
现在可以把问题集中在问题3上面第一种情况，第一次连接数据库时，Set rs =……随便一个简单查询，速度就很 ...

我想赵版可能没有看我89楼说的现象，在你现在列出的第二例里面，不加RS，只要添加一个OBJ控件并且激活它，就能起到加速的作用。

zhaogang1960

没名字了 发表于 2014-3-12 18:44
我想赵版可能没有看我89楼说的现象，在你现在列出的第二例里面，不加RS，只要添加一个OBJ控件并且激活它， ...

我看到了，跟HHAAMM版主提到的一致，再添加一个cnn连接速度可以加快，只能是又多了一个疑问

hehex

lanyuu 发表于 2014-3-12 13:48
通过测试，我的推断是
创建数据连接对象时，先判断是否存在该对象，如果不存在，直接创建，如果存在， ...

这是不可能的，如果能这么智能化进行内存管理，就没有.Net FrameWork 的开发应用取代COM 和传统的程序内存泄漏那一说了。
要知道Excel 是基于.Com 技术的，直接调用的ADO 对象也是，不是ADO.Net，是不可能自动去清内存等等的。

hehex

zhaogang1960 发表于 2014-3-10 23:22
谢谢回复，我也是这样想的：
只要连接数据库，就会自动产生一个rs对象，不管该对象本质是什么，都要赋给 ...

赵老师，如果确认是因为Connection 没有正常关闭的话，你的速度最快的模块1 是有问题的，因为它的高速其实来自于Unsafe 的操作。

任何程序连续打开数十个数据库的数据表，再操作完之后保持着IO 通道都是不安全的。应该在操作完即刻关闭Connection 以保证源数据表的数据安全性，这是第一位的。远比所谓的效率要重要的多。

就好象一个普通IO , 循环Workbooks.Open 几十个文件，每个只开不关，那一定比又开又关的速度要快。但是明显这是不良的编程习惯，一旦出现问题可能出现不可预知的后果。

如果完全按照那种教条式的编程习惯和内存管理要求，应该是你每次循环都应该在循环内生成Connection 和rs 并且在该次循环结束前先关rs 再关Connection, 然后对这两个对象Set xxx = nothing ，以确保没有一点内存泄漏的产生。 即使不考虑内存管理问题，也应该在数据源文件不再使用的情况下，关闭它的IOStream 通道以确保数据的完整性和安全性。当然这样的程序应该是在您的模块1 的基础上添加若干语句。效率不会高于您的模块2，但是这是完整的，安全的。

如果一个高效的，但是可能产生未知后果的程序和一个安全的但是效率低的程序，应该如何来选择呢？

zhaogang1960

hehex 发表于 2014-3-12 19:40
赵老师，如果确认是因为Connection 没有正常关闭的话，你的速度最快的模块1 是有问题的，因为它的高速其实 ...

本讨论不涉及安全问题

我看到的有关VBA+SQL的书籍中，都是先实例化Connection对象，再打开数据库连接，最后一次性关闭连接
实践证明这种方式速度最快

用Set cnn = CreateObject("ADODB.Connection")或Set cnn = New ADODB.Connection实例化Connection对象后，都会关闭上一个连接

lanyuu

1. 
   
2. Sub C_7() '设置循环内创建对象，
   
3.     Dim cnn As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
   
4.     Application.ScreenUpdating = False
   
5.     Mypath = ThisWorkbook.Path & ""
   
6.     MyName = Dir(Mypath & "*.xls")
   
7.     arr = Sheet1.Range("n3:q100")
   
8.     h = 1
   
9.     tt = Timer
   
10.     arr(h, 1) = Timer - tt  '创建对象时间
    
11.     h = h + 1
    
12.     Set cnn2 = CreateObject("ADODB.Connection")
    
13.     cnn2.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath & "000.xlsx"
    
14.     SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
    
15.     Set rs = cnn2.Execute(SQL)
    
16.     rs.Close
    
17.     'Set rs = Nothing
    
18.     'cnn2.Close
    
19.     'Set cnn2 = Nothing
    
20.     Do While MyName <> ""
    
21.         If MyName <> ThisWorkbook.Name Then
    
22.             tt = Timer
    
23.             Set cnn = CreateObject("ADODB.Connection")
    
24.             arr(h, 1) = Timer - tt
    
25.             tt = Timer
    
26.             cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath & MyName
    
27.             SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
    
28.             arr(h, 2) = Timer - tt  '创建连接时间
    
29.             tt = Timer
    
30.             Set cnn = Nothing
    
31.             arr(h, 1) = Timer - tt
    
32.             h = h + 1
    
33.         End If
    
34.         MyName = Dir()
    
35.     Loop
    
36.     tt = Timer
    
37.     'cnn.Close
    
38.     arr(h, 1) = Timer - tt
    
39.     'Set cnn2 = Nothing
    
40.     Sheet1.Range("n3:q100") = arr
    
41. End Sub
    
42. 
    

复制代码

根据测试：
a、建立连接对象1，打开连接1，并建立连接对象2 ，不打开连接2，重新建立连接对象1时，需要关闭连接1后重新建立。

b、建立连接对象1，打开连接1，并建立连接对象2 ，打开连接2，重新建立连接对象1时，无需关闭连接1，直接清空连接对象后重新建立。

c、建立连接对象1，打开连接1，并建立记录集 ，以连接1打开记录集，无论是否关闭该记录集，只要不清空该记录集对象（调试时发现，关闭了记录集，其activaconnection也存在），重新建立连接对象1时，无需关闭连接1，直接清空连接对象后重新建立。

d、建立连接对象1，打开连接1，建立连接对象2，打开连接2，以连接2打开记录集 ，关闭连接2，无论是否关闭该记录集（调试时发现，关闭了连接2，其activaconnection为nothing），重新建立连接对象1时，需先关闭连接1，然后清空连接对象后重新建立。

通过以上得出推论，当清空一个连接对象时会判断程序中有几个对外的连接，如果当前的连接对象不是唯一的一个，即直接清空，不进行强制关闭的过程。
以上仅为个人推论。

没名字了

lanyuu 发表于 2014-3-12 21:35
根据测试：
a、建立连接对象1，打开连接1，并建立连接对象2 ，不打开连接2，重新建立连接对象1时，需要关闭 ...

你的这个猜测和我89楼的猜测是一样的。我也统计了各项的时间，确实是省略了关闭的时间，但没有理论去支持。我甚至用WPE去跟踪设置了网络连接的过程，三种方式的连接都是发送和接受完全相同（四步发送，四步接收，数据完全相同）。但在内部销毁的过程中无法跟踪。