access升迁SQL后的执行效率，如何优化？

shepdog · 发表于 2012-12-19 15:24

我的access有3个表，其中Table1_sql，Table2_sql，各有数十万条记录，已升迁到SQL，Tabel3_local在本机。

当我用以下两种方法运算的时候，产生了很大的效率差异：
方法一 . 3个表直接关联运算，或本地表做子查询，都很慢，长达数分钟，不管本地表的数据有多少。

方法二. 只关联Table1_sql，Table2_sql，本地表Tabel3_local的值取出来，再放到SQL语句，执行时基本可以忽略时间。

这两种方法，得到的结果是一样的。方法一是否用了access自带的数据引擎，只把SQL上的表作为外部表，导致大量的数据传输？而方法二是否用SQL数据引擎，只返回查询结果？
求解惑！

另，如果我的理解正确，该如何解决这个问题？如果先用 ADODB.Recordset调入本地表，是否会达到方法二的效率？如何实现，是逐条调用Recordset的数据，还是用数组变量，如何把recordset的值赋给数组？

谢谢！

shepdog · 发表于 2012-12-20 14:10

本帖最后由 shepdog 于 2012-12-20 14:47 编辑

没有人可以讨论一下吗？

我查了网络资料，貌似SQL语句不支持用数组变量。
可以把本地表Tabel3_local的内容变为字符串变量，作为查询语句的条件，如下以t_SO表为例：
Private Sub Command2_Click()
Dim str As String
Dim a As Long
Dim Rssdb As New adodb.Recordset
Rssdb.Open "select [WBS NO] from t_SO;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Rssdb.EOF Then
Else
Rssdb.MoveFirst
   For i = 0 To Rssdb.RecordCount - 1
         str = str & """" & Rssdb(0) & ""","
   Rssdb.MoveNext
   Next i
'两端加上括号，截去最后一个“，”号
   a = Len(str)
   str = "(" & Left(str, a - 1) & ")"
End If
Debug.Print a, str
Rssdb.Close
Set Rssdb = Nothing
End Sub

后面，在查询SQL时，in 后面加 str变量，就可以了。

Renco · 发表于 2012-12-21 02:52

如果数据量大的情况，一般都采用第二种方式，若干循环得到相关参数变量，形成一定的SQL语句，传给服务器SQL，等回传结果就行了，速度一般是运算的复杂度和结果集的大小，我也采用2楼的方式，这样服务器的安全性也能够提升（做成MDE，代码ADO连接)

shepdog · 发表于 2012-12-21 09:22

本帖最后由 shepdog 于 2012-12-21 09:25 编辑

Renco 发表于 2012-12-21 02:52
如果数据量大的情况，一般都采用第二种方式，若干循环得到相关参数变量，形成一定的SQL语句，传给服务器SQL ...

我上面说的是一个条件的select或delete句型。
假如说，是Update、Insert 句型，上面的方法不能用啊，怎么办呢？
比如说，Access本地表有Col1,Col2两个字段，其中Col1等于SQL表的主键，要把Col2字段的值更新到SQL的表，怎么实现呢？

Renco · 发表于 2012-12-21 13:21

rs.open (本地表)
rs.movefirst
str=""
For i = 0 To rs.RecordCount - 1
str = str & "Update 服务器表 set 服务器表字段='" & rs(【本地表字段Col2】) & "' Where 服务器表.Col1='" & rs(【本地表字段Col1】) & "';" & chr(13)
rs.MoveNext
Next i
'最终形成一个逐条更新的语句
Server服务器ADO.connection.execute str

以上是思路，有些参数及声明，请相应完善即可

shepdog · 发表于 2012-12-21 15:37

本帖最后由 shepdog 于 2012-12-21 15:38 编辑

Renco 发表于 2012-12-21 13:21
rs.open (本地表)
rs.movefirst
str=""

你说的方法，我有想过，但是没有测试过。
我担心，这样会不会由于反复的与服务器交换数据，变得比较慢？

Renco · 发表于 2012-12-21 19:15

不会，你的connection不要用一下就关闭，可以在登录使用程序时就连接上，全局声明，等整个程序关闭时才退出，事实上还很快

shepdog · 发表于 2013-1-21 21:53

本帖最后由 shepdog 于 2013-1-21 21:55 编辑

Renco 发表于 2012-12-21 13:21
rs.open (本地表)
rs.movefirst
str=""

你好！
我现在遇到麻烦了，要求助你了！
我在Access有个连接到SQL的ODBC链接表，假设表名为t_sql，我可以做出来一段字符串：
str="update t_sql set col1=value(0);
   update t_sql set col1=value(1);
   ..............
   update t_sal set col1=value(n);

那么，我可以直接 Docmd.runsql str 吗？还是要先用ADODB.Connection 打开连接？可以是我不知道t_sql表的connectionstring

请帮帮忙，谢谢！

Renco · 发表于 2013-1-22 00:58

在ACCESS里有个隐藏的系统表 MSysObjects
里面有放着链接表的connect，你可以利用一下，
Docmd.runsql str 是本地执行，所以是无法执行你上面的语句的，除非你用传递查询（须要设置ODBC）
ADO方式，与ODBC方式是不同的连接打开方式的，打开后用execute来执行你的语句就行了

shepdog · 发表于 2013-1-22 12:01

本帖最后由 shepdog 于 2013-1-22 12:08 编辑

Renco 发表于 2013-1-22 00:58
在ACCESS里有个隐藏的系统表 MSysObjects
里面有放着链接表的connect，你可以利用一下，
Docmd.runsql st ...

十分感谢你的回复！

		自动登录	找回密码
密码			免费注册

[讨论] access升迁SQL后的执行效率，如何优化？