Recordset.Edit or Update sql vba 语句更新最快的方法?

问题描述

我最近遇到了 vba 更新语句，我一直在使用 Recordset.Edit 和 Recordset.Update 不仅编辑我现有的数据，而且更新它.

I recently came across vba update statements and I have been using Recordset.Edit and Recordset.Update to not only edit my existing data but to update it.

我想知道两者的区别:recordset.update 和Update sql Vba 语句.我认为他们都做同样的事情，但我无法弄清楚哪个更有效以及为什么.

I want to know the difference between the two: recordset.update and Update sql Vba statement. I think they all do the same but I can't figure which one is more efficient and why.

示例代码如下:

'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

推荐答案

假设 WHERE columnc = 20 选择 1000+ 行，正如您在评论中提到的，执行那个 UPDATE 语句应该比循环遍历记录集并一次更新一行的速度明显更快.

Assuming WHERE columnc = 20 selects 1000+ rows, as you mentioned in a comment, executing that UPDATE statement should be noticeably faster than looping through a recordset and updating its rows one at a time.

后一种策略是 RBAR(Row By Agonizing Row)方法.第一个策略，执行单个(有效)UPDATE，是一种基于集合"的方法.一般来说，基于集合的性能优于 RBAR.

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.

但是，您的 2 个示例引发了其他问题.我的第一个建议是使用 DAO 而不是 ADO 来执行您的 UPDATE:

However your 2 examples raise other issues. My first suggestion would be to use DAO instead of ADO to execute your UPDATE:

CurrentDb.Execute stSQL, dbFailonError

无论您选择哪种策略，请确保将 columnc 编入索引.

Whichever of those strategies you choose, make sure columnc is indexed.

这篇关于Recordset.Edit or Update sql vba 语句更新最快的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！