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

HHAAMM

没名字了 发表于 2014-3-11 14:40
三个过程设断比较

你这是完成一次的时间哦

If MyName <> ThisWorkbook.Name Then
            t = Timer
            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)
            Set rs = cnn.Execute(SQL)
            Debug.Print Timer - t

HHAAMM

或者这样

If MyName <> ThisWorkbook.Name Then
            Debug.Print Timer - t
            t = Timer

没名字了

去除RS的整个循环比较，也就是说，其实是set rs 起到了作用

没名字了

HHAAMM 发表于 2014-3-11 14:59
你这是完成一次的时间哦

If MyName  ThisWorkbook.Name Then

整循环的比较我在前面就已经比较过了。数据已经贴在10楼

zhaogang1960

liucqa 发表于 2014-3-11 15:52
把查询文件的行数增加到1000行，1、2的时间就差不多了，而3的时间会非常短（因为没查询）

说明在文件小的 ...

同意你最后说的书写方法，即使是运行不产生查询的SQL语句，最好也用Set rs=cnn.Execute(SQL)的方式

其实我们讨论的是有无set rs……的问题，与查询文件的行数无关，请看下面两段，后者速度非常快：

1. 
   
2. Sub aaa() '仅连接工作簿，不做任何操作
   
3. tt = Timer
   
4.     Dim cnn As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
   
5.     Application.ScreenUpdating = False
   
6.     Mypath = ThisWorkbook.Path & ""
   
7.     MyName = Dir(Mypath & "*.xls")
   
8.     Do While MyName <> ""
   
9.         If MyName <> ThisWorkbook.Name Then
   
10.             Set cnn = CreateObject("ADODB.Connection")
    
11.             cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
    
12.         End If
    
13.         MyName = Dir()
    
14.     Loop
    
15.     cnn.Close
    
16.     Set cnn = Nothing
    
17.     MsgBox Timer - tt
    
18. End Sub
    
19. 
    
20. Sub bbb() '第一次连接时,创建一个不可能有记录的查询SQL = "select * from [Sheet1$a2:l] where 1=2"
    
21. tt = Timer
    
22.     Dim cnn As Object, SQL$, Mypath$, MyName$, arr, brr(1 To 60000, -1 To 11), i&, j&, m&, n&
    
23.     Application.ScreenUpdating = False
    
24.     Mypath = ThisWorkbook.Path & ""
    
25.     MyName = Dir(Mypath & "*.xls")
    
26.     Do While MyName <> ""
    
27.         If MyName <> ThisWorkbook.Name Then
    
28.             Set cnn = CreateObject("ADODB.Connection")
    
29.             cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
    
30.             If m = 0 Then
    
31.                 SQL = "select * from [Sheet1$a2:l] where 1=2"
    
32.                 Set rs = cnn.Execute(SQL)
    
33.                 m = 1
    
34.             End If
    
35.         End If
    
36.         MyName = Dir()
    
37.     Loop
    
38.     cnn.Close
    
39.     Set cnn = Nothing
    
40.     MsgBox Timer - tt
    
41. End Sub
    

复制代码

说明：我把52个待查询工作簿数据增加到了1000行

guojianlin1985

第一种  用 set是把文件一次性设置成对象并放在内存读取操作
第二种没用 set是直接对数据库（工作簿）进行读取操作

如果不用set系统并不知道你的数据库（工作簿）有多大，不可能都放到内存里。

这应该跟vb和sql天生运行机制有关系

所以养成一个好的习惯很重要。

zhaogang1960

guojianlin1985 发表于 2014-3-11 22:01
第一种  用 set是把文件一次性设置成对象并放在内存读取操作
第二种没用 set是直接对数据库（工作簿）进 ...

同意“养成一个好的习惯很重要”，那么第三种情况——只连接，不查询也很慢，不好解释

百度不到去谷歌

zhaogang1960 发表于 2014-3-11 22:03
同意“养成一个好的习惯很重要”，那么第三种情况——只连接，不查询也很慢，不好解释

转一大圈得出结论差不多 只有微软能解释了啊

百度不到去谷歌

没名字了 发表于 2014-3-11 15:14
去除RS的整个循环比较，也就是说，其实是set rs 起到了作用

哥  我在3楼 赵版在4楼就说的是这个问题啊 敢情你一直没看这个 亏我还和你讨论半天的说 泪奔

HHAAMM

循环前规规矩矩的写上
Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
循环内规规矩矩的写上
rs.Close
            cnn.Close

这样反而是最慢的，没天理了啊。要想快就的萝卜不去泥、管杀不管埋才行

测试了 rs.Open SQL, cnn 和上面的效果一样