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

zhaogang1960

HHAAMM 发表于 2014-3-12 11:17
如果打开的是access，不关闭行吗，可能就行不通了吧
哪位没事儿测试下  20个mdb文件，每个当中都有相同的表 ...

我测试了一次，也是52个accdb文件，效果和52个Excel工作簿完全一致

前期绑定、后期绑定在1楼第二个附件中有
测试得知：
与绑定无关
与数据源类型无关，数据源改为access数据库效果相同
使用CopyFromRecordset rs  比  CopyFromRecordset cnn.Excute(SQL)要快很多

zhaogang1960

没名字了 发表于 2014-3-12 09:21
回复了N条，回复不上来，不知道这次可以不可以。
情况不完全同赵版说的一样，当循环内只设置RS.CLOSE的时 ...

cnn.close设置在循环外不起作用，即如果不重新实例化cnn对象，连接新数据库会出错的

zhaogang1960

lanyuu 发表于 2014-3-12 10:38
通过对创建对象，创建连接，关闭连接等用时的监控可以发现
时间差异主要出现在关闭连接上

前几年讨论过，刚才版主们也有讨论，如果连接前实例化cnn对象变量，就不用再close它了，因为速度太慢，所有教材上都是这样写的，当然好象没有讲为什么

zhaogang1960

guojianlin1985 发表于 2014-3-12 08:23
只连接不查询也是因为没用到set啊。还是直接在硬盘对工作簿进行操作啊。

谢谢，请说明这个说法来自哪里

没名字了

zhaogang1960 发表于 2014-3-12 11:58
cnn.close设置在循环外不起作用，即如果不重新实例化cnn对象，连接新数据库会出错的

我已经明白了，没有设置RS的过程中，cnn对象会在打开下一个连接的时候先关闭前一个连接。而设置了RS的则没有关闭前一个连接的过程。

lanyuu

zhaogang1960 发表于 2014-3-12 12:00
前几年讨论过，刚才版主们也有讨论，如果连接前实例化cnn对象变量，就不用再close它了，因为速度太慢，所 ...

刚才测试了，如果创建了和改连接对象的记录集，下次重新创建连接对象时就不用关闭原来已创建的连接对象，如果为创建和它相连接的记录集，就会先关闭原来的对象才重新创建对象。
这样看应该是重新创建对象的时候会对原来已有的对象作出判断，如果有记录集与其连接就直接清空创建新的对象，如果没有记录集与其相连就会先关闭再创建

没名字了

说来说去，我感觉我们前面说的都错了
看下面的两个实例
Sub 实例1
tt = Timer
    Dim cnn As Object, cnn2 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
        End If
        MyName = Dir()
    Loop
MsgBox  Timer - tt
End Sub

Sub 实例2
tt = Timer
    Dim cnn As Object, cnn2 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
            Set cnn2 = CreateObject("ADODB.Connection")
            cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyName
        End If
        MyName = Dir()
    Loop
MsgBox  Timer - tt
End Sub

这两个的速度差说明了什么？
按照我的理解，这与RS又无关了，而是由于Set cnn = CreateObject("ADODB.Connection")这个语句
当程序中只创建了单一的 Object对象时，在更换目标指向的时候系统会有明确的Object对象目标去关闭和重新连接，但是同时具有多个 Object对象时，就似乎失去了目标，不再去关闭和重连，而是接收指令直接创建新的 Object对象。原来的 Object直接失效或者从内存中注销掉而不是关闭。如果把Set cnn = CreateObject("ADODB.Connection")这句换成dim cnn as new ADODB.Connection，或者把这句移到循环外面，就会出现对象未关闭的错误。

百度不到去谷歌

lanyuu 发表于 2014-3-12 11:44
为什么我的机子打开后那个文件就成为只读状态了，应该是被连接占用了。

我是这样 连接本表的时候  我取出数据到本表临时表t 然后vba用非ado方法往t写入了数据 再sql查询t则还是以前的数据 就算保存了也不行 所以判断cnn建立了一个缓存

1. sql = "select * from [sheet1$a1:c]"
   
2.     Set rs = cnn.Execute(sql)
   
3.     [Sheet2!A2].CopyFromRecordset rs
   
4.    
   
5.     '[Sheet2!A3:C100].Clear '用非ado方法改变sheet2数据后连接不更新,若此时直接查询可得到临时表的数据
   
6.     ThisWorkbook.Save '就算保存工作表也不能从cnn中得到最新数据,必须重新连接
   
7.    
   
8.     sql = "select * from [sheet2$a1:c]'查询sheet2"
   
9.     Set rs = cnn.Execute(sql) '
   
10.     [Sheet3!A2].CopyFromRecordset rs '若非ado方法改变了sheet2数据则不能从sheet2得到任何数据

复制代码

没名字了

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

请解释89楼的原因

lanyuu

zhaogang1960 发表于 2014-3-12 12:00
前几年讨论过，刚才版主们也有讨论，如果连接前实例化cnn对象变量，就不用再close它了，因为速度太慢，所 ...

1. 
   
2. Sub C_3() '设置循环内创建对象，打开记录集
   
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("f3:g100")
   
8.     h = 1
   
9.     tt = Timer
   
10.     arr(h, 1) = Timer - tt  '创建对象时间
    
11.     h = h + 1
    
12.     Do While MyName <> ""
    
13.         If MyName <> ThisWorkbook.Name Then
    
14.             tt = Timer
    
15.             Set cnn = CreateObject("ADODB.Connection")
    
16.             arr(h, 1) = Timer - tt
    
17.             cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath & "000.xlsx"
    
18.             SQL = "select * from [Sheet1$a2:l] where 被拆迁人 is not null"
    
19.             arr(h, 2) = Timer - tt  '创建连接时间
    
20.             Set rs = CreateObject("adodb.recordset")
    
21.             rs.ActiveConnection = cnn
    
22.             rs.ActiveConnection = Nothing   '通过是否执行这一步来判断
    
23.             tt = Timer
    
24.             Set cnn = Nothing
    
25.             arr(h, 1) = Timer - tt
    
26.             h = h + 1
    
27. 
    
28.         End If
    
29.         MyName = Dir()
    
30.     Loop
    
31.     tt = Timer
    
32.     'cnn.Close
    
33.     arr(h, 1) = Timer - tt
    
34.     'Set cnn = Nothing
    
35.     Sheet1.Range("f3:g100") = arr
    
36. End Sub

复制代码

通过测试，我的推断是
创建数据连接对象时，先判断是否存在该对象，如果不存在，直接创建，如果存在，先清空原来的，相当于执行set cnn=notihing，然后再创建连接对象，这个我测试过，清空连接对象有一个判断的过程，如果存在记录集的连接对象为该对象可以直接清空该连接对象，否则就先关闭该连接对象，然后再清空。
我测试过，如果重新创建连接对象和清空原有连接对象时间基本一致，创建对象时间基本可以忽略。