哪个最好 - 用于更新数据的记录集/ VBA V SQL语句 [英] Which is best - recordsets/VBA V SQL statements for updating data
问题描述
多年来，我一直在广泛使用VBA将数据更新到表格
处理后。我的意思是，如果我必须进行一些密集处理
导致临时表中的数据，我会有VBA代码将
的结果写入到db，创建新记录或更新
现有记录，以相关者为准。这可能还包括删除
记录。
现在我通常通过在源数据上打开记录集然后
来做到这一点尽管在循环中工作并使我的数据发生变化。这是一个简单的视图
的情况，但实际上可能会有计算和许多其他事情。但是对于这个问题假设一个简单的记录创建
和/或更新现有记录。
我的一个新熟人在使用SQL语句时质疑这一点
广泛。他的代码包括构造SQL语句，然后他执行b $ b执行更改。我可以看到这会起作用，它让我想起了这个的优点和缺点。这并不总是一个合适的
解决方案，但在某些情况下可能更简单，更清洁。
我对此方法的任何评论感兴趣。
我假设在
a交易中执行多个SQL语句没有问题，所以一切都成功或失败。
对数据结构的更改需要重新访问所有相关的SQL
语句，但是它可能还需要重新访问我的代码（除了它之外很多情况我很多）通过循环遍历字段来编写数据
目的地集合，并且实际上不用字段名称编码，除非
绝对必要）。
有速度优势吗？我认为有SQL语句。
For years I have been using VBA extensively for updating data to tables
after processing. By this I mean if I had to do some intensive processing
that resulted in data in temp tables, I would have VBA code that wrote the
results of that away to the db, either creating new records or updating
existing records, whichever was relevant. This may also include deleting
records.
Now I generally do this by opening a recordset on the source data and then
work though that in a loop and make my data changes. This is a simple view
of the situation, but in reality there may be calculations going on and many
other things. But for this question assume a simple creation of records
and/or updating existing records.
A new acquaintance of mine has questioned this as he uses SQL statements
extensively. His code includes constructing SQL statements that he then
executes to make the changes. I can see this would work and it has made me
think about the pros and cons of this. This would not always be a suitable
solution, but may be a simpler and cleaner one in some situations.
I am interested in any comments on this approach.
I assume there are no problems with executing multiple SQL statements within
a transaction so that either everything succeeds or fails.
Changes to the data structure would require revisiting all relevant SQL
statements, but then it may also require revisiting my code (except that it
a lot of situations I write the data across by looping through the fields
collection of the destination and don''t actually code in field names except
where absolutely necessary).
Is there a speed advantage? I would think there is with the SQL statements.
推荐答案
Jeff写道：
多年来我一直在广泛使用VBA用于在处理后将数据更新到表格。我的意思是，如果我必须做一些密集的处理导致临时表中的数据，我会有VBA
代码将其结果写入数据库，要么创建
新记录或更新现有记录，以相关者为准。
这可能还包括删除记录。
现在我通常通过在源数据上打开记录集来完成此操作然后工作虽然在循环中，我的数据发生了变化。这是一个简单的情况视图，但实际上可能正在进行计算和许多其他事情。但是对于这个问题，
假设一个简单的记录创建和/或更新现有记录。
我的一个新熟人质疑这一点，因为他广泛使用SQL语句。他的代码包括构造SQL语句然后执行以进行更改。我可以看到这会起作用
它让我想到了这个的优点和缺点。这并不总是一个合适的解决方案，但在某些情况下可能更简单，更清洁。
我对这种方法的任何评论感兴趣。
我假设在一个事务中执行多个SQL语句没有问题，所以一切都成功或失败。
对数据结构的更改需要重新访问所有相关的SQL。语句，但它可能还需要重新访问我的代码
（除了我在很多情况下通过
循环遍历目标的字段集合而不是
）实际上是在字段名称中编码，除非绝对必要。）
是否有速度优势？我认为有SQL语句。
For years I have been using VBA extensively for updating data to
tables after processing. By this I mean if I had to do some intensive
processing that resulted in data in temp tables, I would have VBA
code that wrote the results of that away to the db, either creating
new records or updating existing records, whichever was relevant.
This may also include deleting records.
Now I generally do this by opening a recordset on the source data and
then work though that in a loop and make my data changes. This is a
simple view of the situation, but in reality there may be
calculations going on and many other things. But for this question
assume a simple creation of records and/or updating existing records.
A new acquaintance of mine has questioned this as he uses SQL
statements extensively. His code includes constructing SQL statements
that he then executes to make the changes. I can see this would work
and it has made me think about the pros and cons of this. This would
not always be a suitable solution, but may be a simpler and cleaner
one in some situations.
I am interested in any comments on this approach.
I assume there are no problems with executing multiple SQL statements
within a transaction so that either everything succeeds or fails.
Changes to the data structure would require revisiting all relevant
SQL statements, but then it may also require revisiting my code
(except that it a lot of situations I write the data across by
looping through the fields collection of the destination and don''t
actually code in field names except where absolutely necessary).
Is there a speed advantage? I would think there is with the SQL
statements.
SQL几乎总是更快（并且更可取）到RecordSet更新。如果我需要在其他一些
行的计算中使用一行中的值或结果，我只会使用后者$
-
我没有查看此邮件附带的电子邮件帐户
。发送给... ...
在Hunter dot com的RBrandt
SQL is nearly always faster (and preferable) to RecordSet updates. I would only
use the latter if I needed to use the value or result from one row in some other
row''s calculation.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
" Rick Brandt" < RI ********* @ hotmail.com>在消息中写道
news：nL ****************** @ newssvr21.news.prodigy.c om ...
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:nL******************@newssvr21.news.prodigy.c om...
杰夫写道：
有速度优势吗？我认为有SQL语句。
SQL几乎总是更快（也更可取）RecordSet更新。我
Is there a speed advantage? I would think there is with the SQL
statements.
SQL is nearly always faster (and preferable) to RecordSet updates. I
如果我需要在某个
其他行的计算中使用一行中的值或结果，
将仅使用后者。
-
我没有查看此邮件附带的电子邮件帐户。发送给... ...
在Hunter dot com的RBrandt
would only use the latter if I needed to use the value or result from one row in some
other row''s calculation.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
这是另外一个会引起激烈辩论的问题。
虽然我发现很少与Rick发生矛盾，但我认为该声明是误导的。
误导。如果您正在进行单个插入或单行更新，SQL
可能会更快。但是如果你希望插入多个记录，那么
不一定是这种情况。在某些情况下，记录集可以更快。
私有声明函数GetTickCount Lib" kernel32" （）As Long
Private Sub rh（）
Dim T As Long
Dim I As Integer
Const C As Integer = 1000
Dim rs As New ADODB.Recordset
On Error Resume Next
CurrentProject.Connection.Execute（_
" DROP Table T1"）
On Error GoTo 0
CurrentProject。 Connection.Execute（_
" CREATE Table T1（"& _
" Field1 Text，Field2 Text）"）
>
CurrentProject.Connection.Execute（" Delete * from t1"）
T = GetTickCount
For I = 1 to C
CurrentProject.Connection.Execute（_
" Insert Into T1（Field1，Field2）"& _
" Values（''first''， ''second''）"）
下一页我
Debug.Print GetTickCount - T
CurrentProject.Connection.Execute （删除*来自t1）
T = GetTickCount
rs.Open （T1），CurrentProject.Connection，adOpenForwardOnly，
adLockOptimistic
For I = 1 to C
rs.AddNew
rs（0）=" first"：rs（1）=" Second"
rs.Update
Next I
rs.Close
Debug.Print GetTickCount - T
End Sub
输出：
1610
31
-
兰迪哈里斯
tech at promail dot com
我很确定我知道我记得的一切。
This is another one of those questions that will prompt some heated debate.
While I find it rare to contradict Rick, I think that statement is
misleading. If you are doing a single insert or single row update, SQL
would likely be faster. But if you expect to insert multiple records, that
is not necessarily the case. Under some circumstances recordsets can be
much faster.
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Sub rh()
Dim T As Long
Dim I As Integer
Const C As Integer = 1000
Dim rs As New ADODB.Recordset
On Error Resume Next
CurrentProject.Connection.Execute ( _
"DROP Table T1")
On Error GoTo 0
CurrentProject.Connection.Execute ( _
"CREATE Table T1 ( " & _
"Field1 Text, Field2 Text)")
CurrentProject.Connection.Execute ("Delete * from t1")
T = GetTickCount
For I = 1 To C
CurrentProject.Connection.Execute ( _
"Insert Into T1(Field1, Field2) " & _
"Values (''first'', ''second'')")
Next I
Debug.Print GetTickCount - T
CurrentProject.Connection.Execute ("Delete * from t1")
T = GetTickCount
rs.Open ("T1"), CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
For I = 1 To C
rs.AddNew
rs(0) = "first": rs(1) = "Second"
rs.Update
Next I
rs.Close
Debug.Print GetTickCount - T
End Sub
Output:
1610
31
--
Randy Harris
tech at promail dot com
I''m pretty sure I know everything that I can remember.
Jeff写道：
多年来，我一直在广泛使用VBA来处理表格后更新数据。我的意思是，如果我必须进行一些密集处理，导致临时表中的数据，我会有VBA代码将该结果写入数据库，创建新记录或更新
现有记录，以相关者为准。这可能还包括删除
记录。
现在我通常通过在源数据上打开记录集然后在循环中工作并使我的数据更改来完成此操作。这是一个简单的观点，但实际上可能会有计算和许多其他事情。但是对于这个问题假设一个简单的记录创建和/或更新现有的记录。
我的一个新熟人质疑这一点，因为他广泛使用SQL语句。他的代码包括构造他随后执行以进行更改的SQL语句。我可以看到这会起作用，它让我想到了这个的利弊。这并不总是一个合适的解决方案，但在某些情况下可能更简单，更清洁。
我对这种方法的任何评论感兴趣。
我假设在事务中执行多个SQL语句没有问题，所以一切都成功或失败。
对数据结构的更改需要重新访问所有相关的SQL
语句，但它可能还需要重新访问我的代码（除了它很多情况我通过循环遍历字段
目的地集合并且实际上不在字段中编码来编写数据名称除了绝对必要的地方。
有速度优势吗？我认为有SQL语句。
For years I have been using VBA extensively for updating data to tables
after processing. By this I mean if I had to do some intensive processing
that resulted in data in temp tables, I would have VBA code that wrote the
results of that away to the db, either creating new records or updating
existing records, whichever was relevant. This may also include deleting
records.
Now I generally do this by opening a recordset on the source data and then
work though that in a loop and make my data changes. This is a simple view
of the situation, but in reality there may be calculations going on and many
other things. But for this question assume a simple creation of records
and/or updating existing records.
A new acquaintance of mine has questioned this as he uses SQL statements
extensively. His code includes constructing SQL statements that he then
executes to make the changes. I can see this would work and it has made me
think about the pros and cons of this. This would not always be a suitable
solution, but may be a simpler and cleaner one in some situations.
I am interested in any comments on this approach.
I assume there are no problems with executing multiple SQL statements within
a transaction so that either everything succeeds or fails.
Changes to the data structure would require revisiting all relevant SQL
statements, but then it may also require revisiting my code (except that it
a lot of situations I write the data across by looping through the fields
collection of the destination and don''t actually code in field names except
where absolutely necessary).
Is there a speed advantage? I would think there is with the SQL statements.
这不是是/否问题。真正的问题是，结合代码和SQL以实现最佳性能的最佳方式是什么？还有
除了速度之外还可能是其他考虑因素，例如你可以在备注字段中插入长文本的简单方法（使用
$更容易） b $ b记录集。
组合方法的一个例子可能是你需要使用一个复杂的公式来更新
a特定的记录组。 VBA
功能。从查询中进行VBA函数调用很慢，并且通过循环记录集找到记录的b $ b子集的速度很慢，所以最好的b / b $ b bix是使用SQL来检索要生成的行集，然后循环
通过生成的记录集来应用更新。
This is not a yes/no question. The real question wold be, what is the
best way to combine code and SQL to achieve the best performance. There
also may be other considerations besides speed, such as the ease with
which you can insert long text into a Memo field (much easier with a
recordset).
One example of a combination approach might be where you need to update
a specific group of records using a complex formula implemented as a VBA
function. Making VBA function calls from queries is slow, and finding a
subset of records by looping through a recordset is slow, so the best
bix is to use SQL to retrieve the set of rows to be ubdated, then loop
through that resulting recordset to apply the updates.
这篇关于哪个最好 - 用于更新数据的记录集/ VBA V SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
