EXCELvba读取ACCESS库到数组慢，请教优化方案

yf_992258

代码如下，大约近30万条数据,跑下来需要好几十秒，有时候更长，请教一下有没有优化方案
dim xx( 1 to 300000,1 to 5)
tt = Timer
p = 1
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\192.168.1.10\share\test.mdb"
Set rs = New ADODB.Recordset
With rs
  .Open "SELECT * FROM " & sjk & "  order by bm", cn, 1, 1, adCmdText
  'ReDim xx(1 To .RecordCount, 1 To 5)
While Not .EOF
  If Not zd.Exists(UCase(CStr(.Fields("bm")))) Then zd.Add UCase(CStr(.Fields("bm"))), p
   xx(p, 1) = .Fields("a") '
   xx(p, 2) = .Fields("b") '
   xx(p, 3) = .Fields("bm") '
   xx(p, 4) = .Fields("c") '
   xx(p, 5) = .Fields("d") '
   p = p + 1
  .MoveNext
Wend
End With

cn.Close
Set rs = Nothing
Set cn = Nothing
MsgBox Timer - tt

WANT-T

慢的原因是你的代码逻辑问题，既然sql处理数据，为啥不一步到位，需要的字段，筛选的条件全部写在SQL里，为啥要导出全部数据到数组里再配合字典去重？30万的数据量，用字典去重能不慢嘛 ？而且还大量的频繁操作对象。

morpheus126

30W=几十秒。。。vba的话，不算慢了，要再快。。。边缘成本很高的，当然楼上说的也有一定道理

语虚何以言知

建议在access里面做个查询
在access中做个查询new
然后excel中select *  form new

yf_992258

WANT-T 发表于 2023-2-9 11:52
慢的原因是你的代码逻辑问题，既然sql处理数据，为啥不一步到位，需要的字段，筛选的条件全部写在SQL里，为 ...

是我没有说完全，代码也没贴全。本意是想以bm字段排序读取全部数据后，用数组操作，字典是为了后道操作数组时定位bm第一次出现的数组位置，试过了加字典并没影响速度多少。当时因为想循环反复open一张数据库表会慢。sql加条件循环读取我也试了，是快了不少的。那就这样吧，谢谢。附另一段代码

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\192.168.1.10\share\test.mdb"

For i = 2 To UBound(arr)'------这个数组可能会比较大，当时想反复open表会慢，所以想读取全部后数组操作的
Set rs = New ADODB.Recordset
With rs
  .Open "SELECT * FROM " & sjk & " where bm='" & arr(i, 1) & "'", cn, 1, 1, adCmdText
  While Not .EOF
  '----------输出数组
  .MoveNext
  Wend
end with
next

ivccav

我的意见跟2楼有些不同。相反，数组和字典不会拖慢运行速度。你只是代码片段，我不清楚你想做什么，只是建议你用getrows方法获得数据集的数组，不用转置，直接在数组中循环操作，比在ado对象模型中循环快得多。

yf_992258

ivccav 发表于 2023-2-9 19:30
我的意见跟2楼有些不同。相反，数组和字典不会拖慢运行速度。你只是代码片段，我不清楚你想做什么，只是建 ...

谢谢回复和建议。getrows也试过了，跟while循环读取写入数组差不多时间。
另外，应用场景说明一下，看看是否有好的解决方法，access数据库中表主要2列字段（大约不到30万条记录），父子项关系，举例如下
字段1字段2
A        1
A        2
A        3
B        3
C        5
D        6
E        7
E        8
D        7
C        2
E        1

现在EXCEL表格中A列有若干条数据(行数不定，自己输入)，举例如下A列数据
查询数据（A列表头）
3
7
4

需要匹配数据库中的字段2，匹配到的要输出对应的字段1内容。比如查询“3”，输出A和B，查询“7”，输出D和E，查询“4”就无输出。
输出结果（比如输出到sheet2的AB两列）
3 A
3 B
7 D
7 E

jack5d

問題出在While循環上, SQL請求查詢別在環內, 把各種循環和判斷都算好, 最後才呼叫SQL查詢。就是說你把recordset抄到陣列中, 循環判斷就根據陣列內的資料, 不要再問recordset拿數據了。每一次向recordset的訪問, recordset都會向伺服器重新訪問, 而查詢EOF又是這個查詢以外的一個新執行緒

WANT-T

yf_992258 发表于 2023-2-10 10:08
谢谢回复和建议。getrows也试过了，跟while循环读取写入数组差不多时间。
另外，应用场景说明一下，看看 ...

这不就是一句SQL的事嘛，left join 一下就出来了！


"SELECT  A.查询数据 FROM [EXCEL 12.0;DataBase=" & ThisWorkbook.FullName & ";].[表名$] A LEFT JOIN " & sjk & " B ON A.查询数据=B.字段2"


OPEN access，select excel;

lbpp

recordset对象有一个GetRows方法，可以直接返回一个二维变量数组，不知道是不是能用

GetRows 方法将 Recordset 对象的多个记录检索到数组中。
语法array = recordset.GetRows( Rows, Start, Fields )返回值返回 Variant，其值为二维数组。
参数Rows 可选。GetRowsOptionEnum 值，指示要检索的记录数。默认值为 adGetRowsRest。 Start 可选。String 值或 Variant，计算 GetRows 操作开始处的记录的书签。还可以使用 BookmarkEnum 值。 Fields 可选。Variant，表示单个字段名或序号位置，或者字段名数组或序号位置编号。ADO 仅返回这些字段中的数据。 说明使用 GetRows 方法将 Recordset 中的记录复制到二维数组中。第一个下标标识字段，第二个下标标识记录编号。当 GetRows 方法返回数据时，array 变量将自动调整到正确大小。
如果未指定 Rows 参数的值，GetRows 方法将自动检索 Recordset 对象中的所有记录。如果请求的记录多于可用的记录，GetRows 仅返回可用的记录数目。
如果 Recordset 对象支持书签，可以通过在 Start 参数中传递该记录的 Bookmark 属性的值来指定 GetRows 方法从哪一个记录开始检索数据。
如果要限制 GetRows 调用返回的字段，可以在 Fields 参数中传递单个字段名/编号，或者字段名/编号的数组。
在调用 GetRows 后，下一个未读取的记录成为当前记录。如果没有其他记录，EOF 属性将被设置为 True。