ADO加快打开表访问的速度 [英] ADO speed up to open table access

问题描述

根据写入数据的速度，对于rs.AddNew和rs.Update，用ADO和Access打开表的最佳方法是什么?

What is the best method to open a table with ADO and Access, for rs.AddNew and rs.Update, based on the speed for writing data?

我需要使用特定的游标或特定的方法吗?

Do I need to use a particular cursor or a particular method?

我使用ADO连接从VB6到Jet.

I use an ADO connection to Jet from VB6.

推荐答案

我的两个建议是:

1. 以adOpenStatic打开Recordset，以最大程度地减少试图跟踪其他用户可能对表所做的更改的开销.

1. Open the Recordset as adOpenStatic to minimize the overhead of trying to keep track of changes to the table that might be made by other users.

通过在批量插入之前执行cn.BeginTrans，然后在之后执行cn.CommitTrans，在事务中包装多个.AddNew操作.

Wrap multiple .AddNew operations in a transaction by doing cn.BeginTrans before the batch of inserts, and cn.CommitTrans afterwards.

编辑

在回应@ Bob77的评论时，他说:

Edit

In response to the comment from @Bob77, in which he said:

围绕一系列任意更新操作进行Begin/End Trans抛出并没有提供任何内在的性能优势，并且在大多数情况下应该使情况更糟.

Throwing Begin/End Trans around a series of arbitrary update operations doesn't provide any intrinsic performance benefit, and in most cases should make matters worse.

以下VBScript测试结果清楚地表明，在使用Jet/ACE数据库时，在事务中包装一批插入内容可以大大提高性能.

The following VBScript test results clearly show that wrapping a batch of insertions in a Transaction can greatly improve performance when working with Jet/ACE databases.

Option Explicit
Dim con, rst, t0, i, n, s
Const adUseClient = 3
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const useTransaction = False

t0 = Timer
n = 1000
Set con = CreateObject("ADODB.Connection")
con.CursorLocation = adUseClient
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\adoTimeTest.accdb;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT * FROM tbl", con, adOpenStatic, adLockOptimistic
If useTransaction Then
    con.BeginTrans
End If
For i = 1 to n
    rst.AddNew
    rst("ItemName").Value = "Item_" & i
    rst("SeqNo").Value = i
    rst.Update
Next
If useTransaction Then
    con.CommitTrans
End If
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
s = "Added " & n & " rows in " & Round(Timer - t0, 1) & " seconds with"
If Not useTransaction Then
    s = s & "out"
End If
s = s & " transaction."
Wscript.Echo s

表[tbl]的结构为

ID       - AutoNumber, Primary Key
ItemName - Text(255), Indexed (duplicates OK)
SeqNo    - Long Integer, Indexed (no duplicates)

测试1:useTransaction = False

[tbl]表为空，并且.addcb文件已重新压缩.

The [tbl] table is empty and the .addcb file has been freshly compacted.

Y:\>cscript /nologo adoTimeTest.vbs
Added 1000 rows in 103.9 seconds without transaction.

测试2:useTransaction = True

[tbl]表已清空，而.addcb文件再次被重新压缩.

The [tbl] table has been emptied and the .addcb file has been freshly compacted again.

Y:\>cscript /nologo adoTimeTest.vbs
Added 1000 rows in 4.9 seconds with transaction.

编辑

响应@ Bob77的后续评论:

Edit

In response to the follow-up comment from @Bob77:

我将再次尝试打开数据库以进行独占访问.

I'd try again opening the database for exclusive access.

使用ODBC和互斥访问的其他测试:

Additional tests using ODBC and Exclusive access:

con.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Y:\adoTimeTest.accdb;Exclusive=1;Uid=admin;Pwd=;"

测试3:useTransaction = False

[tbl]表为空，并且.addcb文件已重新压缩.

The [tbl] table is empty and the .addcb file has been freshly compacted.

Y:\>cscript /nologo adoTimeTest.vbs
Added 1000 rows in 26.5 seconds without transaction.

测试4:useTransaction = True

[tbl]表已清空，而.addcb文件再次被重新压缩.

The [tbl] table has been emptied and the .addcb file has been freshly compacted again.

Y:\>cscript /nologo adoTimeTest.vbs
Added 1000 rows in 6.1 seconds with transaction.

这篇关于ADO加快打开表访问的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！