MS Access:与 DBEngine(0)(0) 相比，使用 CurrentDB 时是否有显着的开销? [英] MS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?
问题描述
来自 David W Fenton 的 答案 SU 问题 MS Access 2003包含一个通用的 SQL 控制台
from David W Fenton's answer to the SU question Does MS Access 2003 contain a general purpose SQL console
使用 CurrentDB 作为执行对象的问题是每次调用它都会返回一个新的数据库对象
The problem with using CurrentDB as your execution object is that it returns a new database object each time you call it
我的问题是:使用 CurrentDb 执行 SQL 或打开记录集时是否有开销，我应该避免吗?
My question is: Is there an overhead when using CurrentDb to execute SQL or open a recordset, and should I avoid it?
推荐答案
开销"这个词不是很清楚，所以我不知道有人怎么回答你的问题.
It's not clear what you mean by the term "overhead," so I don't know how anyone could answer your question as worded.
但是 DBEngine(0)(0) 与 CurrentDB 的主题多年来已在 Access 新闻组中进行了相当广泛的讨论.我很久以前就对使用 CurrentDB 感到平静，所以我将总结一下我所看到的情况.
But the subject of DBEngine(0)(0) vs. CurrentDB has been discussed quite extensively over the years in the Access newsgroups. I long ago made my peace with using CurrentDB, so I'll summarize the situation as I see it.
Dim db As DAO.Database
Dim i As Integer
Debug.Print "Start CurrentDB: " & Now()
For i = 1 to 1000
Set db = CurrentDB
Set db = Nothing
Next i
Debug.Print "End CurrentDB: " & Now()
Debug.Print "Start DBEngine(0)(0): " & Now()
For i = 1 to 1000
Set db = DBEngine(0)(0)
Set db = Nothing
Next i
Debug.Print "End DBEngine(0)(0): " & Now()
如果我没记错的话，ADH97 说 DBEngine(0)(0) 大约快 17 倍.
If I recall correctly, the ADH97 said that DBEngine(0)(0) was something like 17 times faster.
但是看看那个代码——它没有测试任何有用的东西.请记住，CurrentDB 和 DBEngine(0)(0) 都返回指向当前在 Access UI 中打开的数据库的指针(以下针对 DBEngine(0)(0) 有一些注意事项).Access 应用程序中没有任何位置这些循环中的任何一个都会以任何方式有用.在实际代码中，您可以这样做:
But look at that code -- it doesn't test anything that is useful. Remember, both CurrentDB and DBEngine(0)(0) return pointers to the database currently open in the Access UI (with certain caveats, below, for DBEngine(0)(0)).There is no place in an Access app where either of those loops is going to be useful in any way. In real code, you do this:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("a SQL SELECT")
[do something with the recordset]
rs.Close
Set rs = db.OpenRecordset("another SQL SELECT")
[do something with this other recordset]
rs.Close
Set rs = Nothing
db.Execute("A SQL DML statement")
Debug.Print db.RecordsAffected
Set db = Nothing
虽然 DBEngine(0)(0) 在循环中可能快 1700%，但这无关紧要，因为您永远不会重复返回对当前在 Access UI 中打开的数据库的引用足够多次差异几乎可以忽略不计(我们在这里谈论的是毫秒，当然，对于具有更多对象的数据库，CurrentDB 将花费更长的时间).
While DBEngine(0)(0) may be 1700% faster in a loop, IT DOESN'T MATTER, because you're never going to repeatedly return a reference to the database currently open in the Access UI enough times for the difference to be anything but completely negligible (we're talking milliseconds here, though, of course, CurrentDB will take longer for databases with more objects).
所以，首先，在我解释为什么存在差异之前，您必须首先认识到性能差异是完全无关紧要的，因为它可以超过最微不足道的差异的唯一情况是这将是脑死亡的愚蠢代码.
So, first off, before I explain why there's a difference, you must first recognize that the performance difference is completely immaterial, as the only circumstances in which it can exceed the most trivial difference is a circumstance that would be brain-dead stupid code.
现在，为什么不同?
嗯，主要有两个原因:
DBEngine(0)(0) 返回在用户界面中当前打开的数据库首次打开时初始化时的集合，除非您手动刷新集合.因此，如果您添加一个新保存的 QueryDef，要使其在使用 DBEngine(0)(0) 的代码中可用，在添加新的 QueryDef 后，您必须调用
DBEngine(0)(0) returns the collections as they were initialized when the database currently open in the user interface was first open, unless you manually refresh the collections. So, if you add a new saved QueryDef, for it to be available in code using DBEngine(0)(0), after adding the new QueryDef you have to call
DBEngine(0)(0).QueryDefs.Refresh在此之前，您的新查询不会出现在 QueryDefs 集合中，但之后会出现.另一方面，CurrentDB 每次调用时都会刷新所有集合，因此您永远不必担心刷新任何集合.
Before that, your new query won't be in the QueryDefs collection, but after it, it will. CurrentDB, on the other hand, refreshes all collections each time it is called, so you never have to worry about refreshing any of your collections.
DBEngine(0)(0) 返回 Access Jet 工作区用于指向当前在 Access UI 中打开的数据库的内部指针.CurrentDB 返回数据库结构的副本，每次调用 CurrentDB 都会创建一个新副本.因此，CurrentDB 将使用更多内存，因为它创建指向当前在 Access UI 中打开的数据库的结构的副本，而 DBEngine(0)(0) 不使用额外的内存，因为它返回的不是副本，而是简单的指向现有内存结构的指针.
DBEngine(0)(0) returns the internal pointer the Access Jet workspace uses to point to the database currently open in the Access UI. CurrentDB returns a copy of the database structure, and each call to CurrentDB creates a new copy. Thus, CurrentDB will use more memory, because it creates a copy of the structure that points to the database currently open in the Access UI, while DBEngine(0)(0) uses no additional memory, because it returns not a copy, but simply a pointer to an existing memory structure.
可能是集合的刷新是 CurrentDB 慢1700%"(或任何数字)的原因，但可能一些额外的时间被设置数据库对象的副本的过程占用，也一样.
Likely the refreshing of the collections is the reason why CurrentDB is "1700%" slower (or whatever the number was), but probably some of the extra time is taken up by the process of setting up the copy of the database object, as well.
同样，这些在实际编码实践中都没有任何区别，因为您不需要经常打开和关闭指向当前在 Access UI 中打开的数据库的指针，因为它不会一直打开和关闭.
Again, none of this makes any difference in actual coding practice, as you just don't need to constantly be opening and closing pointers to the database currently open in the Access UI, as IT'S NOT BEING OPENED AND CLOSED CONSTANTLY.
那么，这是potaeto/potahto吗?
So, is this a potaeto/potahto thing?
不，因为 DBEngine(0)(0) 中有一个错误"可能会导致它返回一个意外的数据库指针(尽管它实际上在技术上是正确的)，并且在访问向导之后立即出现在某些上下文中已运行，DBEngine(0)(0) 将返回一个指向wizard 数据库的指针，而不是指向当前在 Access UI 中打开的数据库.
No, because there's one "bug" in DBEngine(0)(0) that could cause it to return an unexpected database pointer (though it would actually be technically correct), and that is in certain contexts immediately after an Access wizard has run, DBEngine(0)(0) will return a pointer to the wizard database, and not to the database currently open in the Access UI.
那是因为有以下区别:
当前在 Access UI 中打开的数据库，以及
the database currently open in the Access UI, AND
DBEngine 对象的第一个工作区中的第一个数据库.
the first database in the first workspace of the DBEngine object.
另一方面，CurrentDB 总是返回对#1 的引用，而从不返回对#2 的引用.然而，DBEngine(0)(0) 可以返回其他东西，至于在很短的时间内，向导确实是在向导被解除后 DBEngine 对象的第一个工作区中的第一个数据库.
CurrentDB, on the other hand, always returns a reference to #1 and never to #2. DBEngine(0)(0), however, can return something else, as for a very brief moment, the wizard really is the first database in the first workspace of the DBEngine object right after the wizard is dismissed.
现在，生产代码是否可能会遇到此错误?可能不会，因为您不太可能在生产应用程序中使用向导.但这也适用于图书馆数据库，而且这种技术并不少见，特别是对于高级 Access 程序员而言.
Now, is it likely that production code could ever encounter this error? Probably not, since it's unlikely that you'd use a wizard in a production app. But this could also apply to library databases, and that's not so uncommon a technique, particularly for advanced Access programmers.
如果存在实际性能差异，DBEngine(0)(0) 可能是值得的，但由于没有，CurrentDB 更可取，因为它在返回预期的数据库引用方面 100% 可靠.
If there were a practical performance difference, DBEngine(0)(0) might be worth it, but since there isn't, CurrentDB is preferable since it is 100% reliable in returning the expected database reference.
说了这么多，我的应用中都没有使用.
All that said, I don't use either in my apps.
相反，我使用一个函数来缓存一个用 CurrentDB 初始化的数据库变量.这意味着我永远不必初始化任何数据库变量，只需使用我的 dbLocal() 函数代替任何数据库变量.代码如下:
Instead, I use a function that caches a database variable initialized with CurrentDB. This means I never have to initialize any database variables, just use my dbLocal() function in place of any database variable. Here's the code:
Public Function dbLocal(Optional bolCleanup As Boolean = False) As DAO.Database
' This function started life based on a suggestion from
' Michael Kaplan in comp.databases.ms-access back in the early 2000s
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed (3420)
' would then be jumping back into the middle of an If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String
If bolCleanup Then GoTo closeDB
retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing, test if it's Open
strTest = dbCurrent.Name
exitRoutine:
Set dbLocal = dbCurrent
Exit Function
closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close ' this never has any effect
Set dbCurrent = Nothing
End If
GoTo exitRoutine
errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If Not bolCleanup Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in dbLocal()"
Resume exitRoutine
End Select
End Function
在代码中，您可以这样使用:
In code, you use this thus:
Dim rs As DAO.Recordset
Set rs = dbLocal.OpenRecordset("SQL SELECT statement")
[do whatver]
rs.Close
Set rs = Nothing
dbLocal.Execute("SQL INSERT statement")
Debug.Print dbLocal.OpenRecordset("SELECT @@IDENTITY")(0)
dbLocal.Execute("SQL UPDATE statement")
Debug.Print dbLocal.RecordsAffected
第一次调用它时，它会用 CurrentDB 初始化自己并返回缓存的数据库对象.
The first time you call it, it will initialize itself with CurrentDB and return the cached database object.
当您关闭应用程序时，将 bolCleanup 标志设置为 TRUE 来调用它，以便它清理缓存的变量.
When you close the app, you call it with the bolCleanup flag set to TRUE so that it will clean up the cached variable.
如果你添加到集合中，它们不会刷新(因为你不是每次都调用 CurrentDB，只是使用一个用 CurrentDB 初始化的缓存数据库变量)，所以你必须这样做:
If you add to the collections, they don't refresh (because you're not calling CurrentDB each time, just using a cached database variable that was initialized with CurrentDB), so you have to do this:
[add a new QueryDef]
dbLocal.QueryDefs.Refresh
就是这样.没有全局变量，不需要经常用 CurrentDB(或 DBEngine(0)(0))初始化数据库变量.您只需使用它，而不必担心它.唯一的技术细节是确保您应用的关闭例程调用 dbLocal(False).
And that's it. No global variables, no need to constantly initialize database variables with CurrentDB (or DBEngine(0)(0)). You just use it and stop worrying about it. The only technical detail is making sure that your app's shutdown routine calls dbLocal(False).
所以，这就是我对 DBEngine(0)(0) 与 CurrentDB 的看法.
So, that's my take on DBEngine(0)(0) vs. CurrentDB.
关于清理由这两种方法初始化的数据库变量的一个附带问题:
A side issue about cleanup of database variables initialized by these two methods:
如果你用 CurrentDB 初始化一个 db 变量，你不会关闭它，只需将它设置为 Nothing:
If you initialize a db variable with CurrentDB, you don't close it, just set it to Nothing:
Dim db As DAO.Database
Set db = CurrentDB
...
'db.Close <= don't do this
Set db = Nothing
如果你确实发出 db.Close，什么都不会发生，既不好也不好.
If you do issue the db.Close, nothing at all will happen, neither bad nor good.
另一方面，在这种情况下:
On the other hand, in this case:
Dim db As DAO.Database
Set db = DBEngine(0)(0)
...
'db.Close <= don't do this
Set db = Nothing
...发出 db.Close 可能会导致您的应用在某些版本的 Access 中崩溃.
...issuing the db.Close can cause your app to crash in certain versions of Access.
它们都不能实际工作，因为您无法通过数据库对象的 Close 方法关闭当前在 Access UI 中打开的数据库.
Neither of them could actually work, because you can't close the database currently open in the Access UI via the Close method of a database object.
另一方面，如果你这样做:
On the other hand, if you do this:
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase("path to external MDB file")
...
db.Close ' <=you *must* do this
Set db = Nothing
...你真的确实想关闭它，因为它是一个外部数据库.该代码不能用 CurrentDB 完成，因为这是打开对另一个数据库的引用的唯一方法.
...you really do want to close it out, as it is an external database. That code can't be done with CurrentDB, because this is the only way to open a reference to another database.
这篇关于MS Access:与 DBEngine(0)(0) 相比，使用 CurrentDB 时是否有显着的开销?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
