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

百度不到去谷歌

HHAAMM 发表于 2014-3-11 13:16
弄不明白！！

不过对于这种循环打开，建议 set cnn 这些语句应该写在循环外面，或许Provider都应该写在外 ...

你放外面试试 速度更慢的!

没名字了

回复又丢了，坑爹的验证。白写了那么多。
只有连接的那个过程不是最慢的，而是比过程2快。这也正说明，当系统遇到没有声明的执行语句，需要通过自己默认的方式去处理。之所以比过程2快是快在循环的执行上。

zhaogang1960

HHAAMM 发表于 2014-3-11 13:16
弄不明白！！

不过对于这种循环打开，建议 set cnn 这些语句应该写在循环外面，或许Provider都应该写在外 ...

谢谢郝版参与讨论
循环打开有其他用途，当我们不知道工作簿(或数据库)的工作表名时，我们会用ADOX、或OpenSchema方法取得工作表名，后者比较方便，速度也较快，这样就必须循环打开每个工作簿

另外，请看19楼，仅在第一次连接时使用   Set rs = cnn.Execute(SQL)速度更快  
If i <1 Then
i = i + 1
Set rs = cnn.Execute(SQL)
End If

没名字了

zhaogang1960 发表于 2014-3-11 13:17
程序1和程序3比较，前者有查询，速度很快，后者只有连接，没有查询，速度很慢
程序3没有查询，当然就不可 ...

是过程1和过程2
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

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

这两个过程比较

百度不到去谷歌

没名字了 发表于 2014-3-11 13:20
回复又丢了，坑爹的验证。白写了那么多。
只有连接的那个过程不是最慢的，而是过程2快。这也正说明，当系统 ...

一直在说set rs这句决定快慢的啊 现在就是讨论为什么这一句加不加会对效率产生影响 和执行循环都没关系的
最后都用arr = cnn.Execute(SQL).GetRows来赋值数组的情况下
set rs加与不加也
也就是说rs就算不在后续语句中用 只要这么执行过 就能加速

zhaogang1960

百度不到去谷歌 发表于 2014-3-11 13:28
一直在说set rs这句决定快慢的啊 现在就是讨论为什么这一句加不加会对效率产生影响 和执行循环都没关系的 ...

是这样，正如19楼所说的，只有第一次使用了set rs就可以加速了，而且是要稍微更快些

没名字了

这是分别运行过程1和过程2，然后删除查询结果后保存的文件大小

HHAAMM

百度不到去谷歌 发表于 2014-3-11 13:17
你放外面试试 速度更慢的!

如果每次创建对象和创建一次如果没差别或差距是反过来的，那真是没天理了！！

百度不到去谷歌

HHAAMM 发表于 2014-3-11 13:33
如果每次创建对象和创建一次如果没差别或差距是反过来的，那真是没天理了！！

是啊 所以太奇葩了 cnn和rs的使用在这里完全违反常规了

百度不到去谷歌

我的测试结果
序号        仅一次set rs        循环set rs        无set rs
1        0.2890625        0.32421875        2.00390625
2        0.296875        0.3515625        2.0234375
3        0.28515625        0.328125        2.0234375
4        0.27734375        0.328125        2.0078125
5        0.296875        0.328125        2.015625
6        0.3359375        0.3984375        2.02734375
7        0.296875        0.33984375        2.01953125
8        0.28125        0.328125        2.02734375
9        0.3203125        0.34765625        2.0703125
10        0.2890625        0.3359375        2.10546875
均值        0.296875        0.341015625        2.032421875
有兴趣可以运行test宏 在本机上测试一下 百思不得其解了