Recordset.Edit or Update sql vba 语句更新最快的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30894045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recordset.Edit or Update sql vba statement fastest way to update?
提问by Coding Enthusiast
I recently came across vba update statements and I have been using Recordset.Editand Recordset.Updateto not only edit my existing data but to update it.
我最近遇到了 vba update 语句，我一直在使用Recordset.Edit并且Recordset.Update不仅编辑我现有的数据，而且更新它。
I want to know the difference between the two: recordset.updateand Update sql Vbastatement. I think they all do the same but I can't figure which one is more efficient and why.
我想知道两者之间的区别：recordset.update和Update sql Vba语句。我认为他们都做同样的事情，但我无法弄清楚哪个更有效以及为什么。
Example code below:
示例代码如下：
'this is with sql update statement
dim someVar as string, anotherVar as String, cn As New ADODB.Connection
someVar = "someVar"
anotherVar = "anotherVar"
sqlS = "Update tableOfRec set columna = " &_
someVar & ", colunmb = " & anotherVar &_
" where columnc = 20";
cn.Execute stSQL
This is for recordset (update and Edit):
这是用于记录集（更新和编辑）：
dim thisVar as String, someOthVar as String, rs as recordset
thisVar = "thisVar"
someOthVar = "someOtherVar"
set rs = currentDb.openRecordset("select columna, columnb where columnc = 20")
do While not rs.EOF
rs.Edit
rs!columna = thisVar
rs!columnb = someOthvar
rs.update
rs.MoveNext
loop
采纳答案by HansUp
Assuming WHERE columnc = 20selects 1000+ rows, as you mentioned in a comment, executing that UPDATEstatement should be noticeably faster than looping through a recordset and updating its rows one at a time.
假设WHERE columnc = 20选择 1000 多行，正如您在评论中提到的那样，执行该UPDATE语句应该比循环遍历记录集并一次更新其行明显更快。
The latter strategy is a RBAR (Row By Agonizing Row) approach. The first strategy, executing a single (valid) UPDATE, is a "set-based" approach. In general, set-based trumps RBAR with respect to performance.
后一种策略是 RBAR（Row By Agonizing Row）方法。第一个策略，执行单个（有效）UPDATE，是“基于集合”的方法。一般来说，就性能而言，基于集合的方法胜过 RBAR。
However your 2 examples raise other issues. My first suggestion would be to use DAO instead of ADO to execute your UPDATE:
但是，您的 2 个示例引发了其他问题。我的第一个建议是使用 DAO 而不是 ADO 来执行您的UPDATE：
CurrentDb.Execute stSQL, dbFailonError
Whichever of those strategies you choose, make sure columncis indexed.
无论您选择哪种策略，请确保对columnc进行索引。
回答by Gustav
The SQL method is usually the fastest for bulk updates, but syntax is often clumsy.
SQL 方法通常是批量更新最快的方法，但语法通常很笨拙。
The VBA method, however, has the distinct advantages, that code is cleaner, and the recordset can be used before or after the update/edit without requering the data. This can make a huge difference if you have to do long-winded calculations between updates. Also, the recordset can be passed ByRef to supporting functions or further processing.
然而，VBA 方法具有明显的优势，即代码更简洁，并且可以在更新/编辑之前或之后使用记录集而无需重新查询数据。如果您必须在更新之间进行冗长的计算，这可能会产生巨大的差异。此外，记录集可以通过 ByRef 传递给支持函数或进一步处理。
回答by David Beckman
I have found that when I need to update every record in a table in order, such as adding a sequential ID when using Autonumber is not feasible, adding a running total, or any calculation that is incremental based on some value in the recordset, that the DAO method is much faster.
我发现当我需要按顺序更新表中的每条记录时，例如在使用 Autonumber 时添加顺序 ID 是不可行的，添加运行总计，或者任何基于记录集中某个值的增量计算，那DAO 方法要快得多。
If your data is not in the order you need it processed in, and you instead need to rely on matching values to the data source, then SQL is much more efficient.
如果您的数据没有按照您需要的顺序进行处理，而您需要依赖与数据源的匹配值，那么 SQL 的效率要高得多。
回答by Boris
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select invoice_num from dbo_doc_flow_data where barcode = '" & Me.barcode_f & "'")
Do While Not rs.EOF
rs.Edit
rs!invoice_num = Me!invoice_num_f
rs.Update
rs.MoveNext
Loop
rs.Close
