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

zhaogang1960

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

这个现象早就知道，只要第一次连接就set rs=……，速度很快，否则就很慢，现在讨论的是原因

zhaogang1960

liucqa 发表于 2014-3-12 22:18
我觉得你问的这个问题是内存管理的事情，当一个对象不再被引用的时候，会被vb自动销毁。

arr = cnn. ...

可能是这样，现在不好理解的是仅连接，不做查询速度很慢，加上set rs=………，哪怕只加一次速度就很快

HHAAMM

小小疑问！！以上方式下

只是查询操作，数据源应该是安全的
如果是写入、修改、删除等sql语句，数据源会不不会出问题

HHAAMM

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

呵呵 我在想这么个问题
如果代码是自己或亲朋的一个很重要的工作中要用的，大家会选择哪种方式？？

我的选择是 规规矩矩

lanyuu

zhaogang1960 发表于 2014-3-12 21:43
这个现象早就知道，只要第一次连接就set rs=……，速度很快，否则就很慢，现在讨论的是原因

我推测的是占用时间是由于需要关闭连接，而无需关闭连接的就重新建立新连接的前提条件是存在的不止这一个连接。
这不就是原因吗。

zhaogang1960

HHAAMM 发表于 2014-3-12 22:58
呵呵 我在想这么个问题
如果代码是自己或亲朋的一个很重要的工作中要用的，大家会选择哪种方式？？

规规矩矩：
循环内——实例化cnn变量，连接数据库、查询先放在rs变量中，执行更新、插入等也用Set rs= cnn.Execute(SQL)
循环外——最后一次性：
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing

HHAAMM

本帖到了几十层楼后，我个人对这个问题的关注点转到了这种写法的安全性上。这几天没少百度谷歌的去查资料，因个人相关领域的水平问题，始终也没得到一个确切的答案。但在搜索的过程中看到了这么个说法，“Jet引擎会重复利用以往内存而不会申请新的内存”。
这么说是不是就意味着循环中每次open后不close，只是在代码最后部分关闭释放对象的写法是安全的，不会出现内存泄露问题。

另一个问题

1. Sub x()
   
2.     adostr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     cnn.Open adostr & "E:\1.xls"
   
5.     Sql = "select * from [Sheet1$]"
   
6.     Set rs = cnn.Execute(Sql)
   
7.     Set cnn = CreateObject("ADODB.Connection")
   
8.     cnn.Open adostr & "E:\2.xls"
   
9.     Stop    ''''代码运行到这里，E:\1.xls不能被手动打开也不能被其他程序访问
   
10.     Set rs = cnn.Execute(Sql)
    
11.     Stop    ''''代码运行到这里，E:\1.xls可以正常打开、访问
    
12.     Set cnn = Nothing
    
13. End Sub

复制代码

上面代码，第二次Set rs = cnn.Execute(Sql)后，E:\1.xls正常了。这里面是不是包含有对第一次cnn的关闭，我个人认为是有的。
下面是写入的代码

1. Public Sub y()
   
2.     Dim cn As New ADODB.Connection
   
3.     Dim rs As New ADODB.Recordset
   
4.     adostr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="
   
5.     cn.Open adostr & "E:\1.xls"
   
6.     Sql = "INSERT INTO [Sheet1$](A,B) VALUES (1,2)"
   
7.     Set rs = cn.Execute(Sql)
   
8.     Set cn = Nothing
   
9.     cn.Open adostr & "E:\2.xls"
   
10.     Set rs = cn.Execute(Sql)
    
11. End Sub

复制代码

因个人水平问题，可能在认识理解上都很偏颇。
但就我个人目前所能理解的所看到的现象，这种循环中创建Connection对象，循环结束后关闭销毁对象的写法是安全的！！

百度不到去谷歌

HHAAMM 发表于 2014-3-15 22:59
本帖到了几十层楼后，我个人对这个问题的关注点转到了这种写法的安全性上。这几天没少百度谷歌的去查资料， ...

这个我也发现了 我用测试指针地址的方法 看了 当set rs时候 是2个地址块交替利用
而不用 则是每次都要调用一个新的内存地址  我一直不知道怎么整理这个发现 因为没有相关资料支持 也不好描述 所以没说 测试地址的语句是这样的 “Jet引擎会重复利用以往内存而不会申请新的内存”。 就能与我的测试印证了

1. Public Sub 对象地址测试() '这个过程查看cnn
   
2.     Dim a(1 To 10, 1 To 2) As Object, i, rs As Object, cnn As Object, strCnn$
   
3.     strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0';Data Source=" & ThisWorkbook.FullName
   
4.     Sheet1.[B3:E12].ClearContents
   
5.     For i = 1 To 10
   
6.         Set a(i, 1) = CreateObject("ADODB.Connection") '创建一个连接对象
   
7.         a(i, 1).Open strCnn
   
8.         Set a(i, 2) = a(i, 1).OpenSchema(adSchemaTables) '打开一个系统查询赋值给rs
   
9.         Sheet1.Cells(2 + i, 2) = ObjPtr(ByVal a(i, 1)) 'cnn对象地址
   
10.         Sheet1.Cells(2 + i, 3) = ObjPtr(ByVal a(i, 2)) 'rs对象地址
    
11.         '这时候可以看到每个对象都有有具体的引用时,每次地址都不同,也就是都需要重新分配内存区
    
12.     Next
    
13.     For i = 1 To 10
    
14.         Set a(i, 1) = Nothing: Set a(i, 2) = Nothing
    
15.     Next
    
16.     For i = 1 To 10 '每次打开都调用set rs
    
17.         '创建一个连接对象,每次都用cnn引用,这样上一个就会处于无引用状态,下次set rs就直接引用了这个内存区
    
18.         '这里就会出现2个地址被交替使用的效果,只分配了2次内存,而达到了10次使用rs对象的效果,且后续引用时,前面
    
19.         '对象没有colse操作,而是直接写入新数据
    
20.         Set cnn = CreateObject("ADODB.Connection") '创建一个连接对象
    
21.         cnn.Open strCnn
    
22.         Set rs = cnn.OpenSchema(adSchemaTables) '打开一个系统查询赋值给rs
    
23.         Sheet1.Cells(2 + i, 4) = ObjPtr(ByVal cnn)
    
24.         Sheet1.Cells(2 + i, 5) = ObjPtr(ByVal rs)
    
25.         Sheet1.Cells(2 + i, 6) = ObjPtr(ByVal cnn.OpenSchema(adSchemaTables))
    
26.     Next
    
27.     Call 隐式创建对象测试
    
28.     Set rs = Nothing
    
29.     Set cnn = Nothing
    
30. End Sub
    
31. Public Sub 隐式创建对象测试() '这个过程查看无set rs情况
    
32.     Dim a(1 To 10, 1 To 2) As Object, i, rs As Object, cnn As Object, strCnn$
    
33.     strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0';Data Source=" & ThisWorkbook.FullName
    
34.     Sheet1.[G3:H12].ClearContents
    
35.     For i = 1 To 10 '每次打开都不调用set rs
    
36.         Set cnn = CreateObject("ADODB.Connection") '创建一个连接对象
    
37.         cnn.Open strCnn
    
38.         'Set rs = cnn.OpenSchema(adSchemaTables) '打开一个系统查询赋值给rs
    
39.         Sheet1.Cells(2 + i, 7) = ObjPtr(ByVal cnn)
    
40.         Sheet1.Cells(2 + i, 8) = ObjPtr(ByVal cnn.OpenSchema(adSchemaTables))
    
41.     Next
    
42.     Set rs = Nothing
    
43.     Set cnn = Nothing
    
44. End Sub

复制代码

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

2014-3-15 23:23 上传

点击文件名下载附件

liucqa

HHAAMM 发表于 2014-3-15 22:59
本帖到了几十层楼后，我个人对这个问题的关注点转到了这种写法的安全性上。这几天没少百度谷歌的去查资料， ...

其实，你看原5楼的帖子，测试经过已经说明不会内存泄漏。

但是未经Close就断掉文件连接，会导致windows的文件缓存区里面的数据不能及时写到硬盘上。对于一楼的纯读代码，自然不会有问题，但对于一个存在复杂读写过程的cnn操作代码，则会发生不好预计的效果。

所以我才说，一楼的代码1的书写方式，不是正确的方式。

关于Windows文件缓存区与硬盘数据之间的关系，百度可以找到相关文章，在其他语言中，命令一般是
File::Flush() 或者 File.Flush()

http://bbs.csdn.net/topics/390257901

HHAAMM

我支持118楼附件中的说法 “地址只有一个,说明每次都使用了一个内存区,但下次使用时肯定执行了close操作”