CurrentDB vs DBEngine(0)(0) vs ThisDb vs CurDb

First Published 27 Feb 2019                         Last Updated 10 May 2023                         Difficulty level :   Moderate


This is the second in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.

Example databases are provided in each case so the same tests can be done on your own workstations

UPDATED 10 May 2023
This article has been completely rewritten with additional tests and updated conclusions


My original article written in 2019 was in response to the dbEngine(0)(0) vs Currentdb thread started by Access World Forums member John Clark (NauticalGent).

John had found an article by former Access MVP Jim Duttman written in Dec 2009: CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative

In his article, Jim Dettman wrote:

CurrentDB() is an Access function and is processed by the UI layer, which returns a new reference to the current database. As a result, it's always up to date with current information (i.e. it would include a form that was just added) in its collections. Using dbEngine(0)(0) on the other hand is getting a pointer to the DAO (Data Access Object) reference maintained by JET. In using this reference you are bypassing the UI layer, which means that you won't know if something has been done in the UI unless you do a refresh of a collection. That refresh can be quite expensive in terms of performance.

So why would you want to use a dbEngine(0)(0) reference at all instead of CurrentDB()? Because it is considerably faster; as much as five thousand times faster. When you look at all the CurrentDB() calls in your application, you will begin to realize that this can add up.

The reason for this and which is not at all obvious (and you may have caught in the statement above), is that each call to CurrentDB() returns a new database object.

At this point, you may be thinking "Great, I'll use dbEngine(0)(0) everywhere and if I really need updated collections, I'll just refresh when I need to." After all, a lot of applications don't add objects at runtime, so the refresh issue may not seem like such a big deal.

However dbEngine(0)(0) does have one other problem that you need to be aware of; in certain situations, it may not point to the same database that CurrentDB() does. Keep in mind that one is the current database the UI is working with, while the other is the current database that JET is working with. The situations would be fairly rare where they might be different (they may occur when using wizards, libraries, or compacting DBs accomplished by closing and re-opening db's in code), but they can occur. So how might one get the best of both worlds and yet still be safe? With one small and easy to routine:

'code courtesy of Jim Dettman at
'https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

Private objCurDB As DAO.Database
Public Function CurDb() As DAO.Database

      If objCurDB Is Nothing Then
            Set objCurDB = CurrentDb()
      End If

Set CurDb = objCurDB
End Function

Paste the above into a general module and now in place of CurrentDB(), you can use CurDB(). Since CurDB() uses a variable to hold a reference to the current DB, it is fast like using dbEngine, but it is being done through the UI, so the reference will always be to the correct DB.


The article quoted above was written in 2009 but the author had repeated the same statements in recent replies to the article.

Although there is a lot of good advice in the article, I was surprised by his suggestion thatdbEngine(0)(0) was up to 5000x faster than CurrentDb

After all, Microsoft have been advising the use of CurrentDB rather than DBEngine(0)(0) from around 2000 onwards!
For example, see this Microsoft article which was last updated 8 Feb 2022.

I therefore decided to compare the two methods and initially largely ignored the later section in which Jim recommended the use of the CurDb function.

Typical Usage: Set db = CurrentDb or Set db = DBEngine(0)(0)

Long after completing my original tests, I found another method using a custom property called ThisDb as proposed by nsenor in a discussion at Bytes.com forum:

'code courtesy of @nsenor at
'https://bytes.com/topic/access/answers/929334-problem-referring-existing-tabledef-object#post3832880

Public Property Get ThisDb() As DAO.Database

      If m_dbThis Is Nothing Then Set m_dbThis = CurrentDb
      Set ThisDb = m_dbThis

End Property

Typical Usage: Set db = ThisDb

In my initial tests on each of the various methods, I found little difference between the times required in each case.

However, fellow Access developer, Josef Pöztl from Austria, recently informed me of an error in one of my original tests.

As will be obvious when comparing the two code snippets above, there is a lot of similarity between the ThisDb property and the CurDb function

It therefore seemed an ideal opportunity to review all 4 methods by repeating the tests. I also carried out some additional tests.



Tests

All the tests were performed on an old desktop PC with an i5 processor 2.90GHz and 16GB RAM running 32-bit Access 365 on 64-bit Windows:


I ran the following tests on the 4 methods for comparison:

1.   Set db=CurrentDB followed by db.Execute followed by Set db = Nothing at the end
      This was done repeatedly - both after clearing the data & again after each 1000 record loop

2.   As test 1 above but using db=DBEngine(0)(0)

3.   As test 1 above but using db=ThisDb

4.   As test 1 above but using db=CurDb

5.   Set db=CurrentDB ONCE at the start and Set db = Nothing ONCE at the end

6.   As test 5 above but with db=DBEngine(0)(0)

7.   As test 5 above but using db=ThisDb

8.   As test 5 above but using db=CurDb

9.   Used CurrentDB.Execute in each step. No variable set/destroyed.

10.   Used DBEngine(0)(0).Execute in each step. No variable set/destroyed.

11.   Used ThisDb.Execute in each step. No variable set/destroyed.

12.   Used CurDb.Execute in each step. No variable set/destroyed.

This is a typical set of results for each test:




Results

I repeated each test 10 times and calculated the average times for each. The average results obtained were:


These are the same results as a report


With two SIGNFICANT exceptions, the time differences were mostly fairly small but some patterns are clear.



Summary

1.   Setting the variable db=CurrentDB once is significantly faster than setting and clearing it repeatedly during a loop procedure
      Using CurrentDB.Execute for each iteration is also very slow

2.   There is very little difference in the times when using db=DBEngine(0)(0) once or when using DBEngine(0)(0).Execute
      It makes almost no difference whether the db=DBEngine(0)(0) variable is set once or repeatedly

3.   Similarly, there is very little difference in the times when using db=ThisDb once or repeatedly or when using ThisDb.Execute

4.   Likewise, there is very little difference in the times when using db=CurDb once or repeatedly or when using CurDb.Execute

Taken together, these results suggest that setting a variable db to any of CurrentDb, DBEngine(0)(0), ThisDb or CurDb is beneficial in terms of speed.

However, doing so within a loop is detrimental, particularly in the case of CurrentDb

It also suggests that the time needed to set db=CurrentDb is far longer than either DBEngine(0)(0), ThisDb or CurDb as the times for the last three methods were hardly impacted by being run within a loop

Overall, the tests indicated that as long as the variable wasn't set within a loop (which is clearly bad practice), it made little difference which method is actually used to run a query in code

However, I had finally realised that I had overlooked something very important.
The article was mainly referring to the time taken to set the variable rather than its use when running a query

I decided to run some additional tests to investigate this further



Further Tests

In normal circumstances, for any standard set of queries such as those used above, the largest proportion of the time taken is, of course, actually running the query rather than setting/clearing the variable db.

I repeated the tests to measure the average time to run Set db = 'something' (CurrentDb/DBEngine(0)(0)/ThisDb/CurDb) without running any subsequent queries.

In these tests, I measured the time to set the db variable 1000 times in succession and the average time was then calculated after each test. Each test was run 30 times.

As these times were all very fast, I used a high resolution timer capable of measuring to a precision of 0.1 microseconds

I also compared those times with those obtained for Set db = 'something' followed by Set db = Nothing

As you might expect, the additional time needed to 'unset' (clear) the variable was negligible in each case

Here are the results of the additional 'set time' tests:


From these results, it is clear that each of the methods is very fast with the slowest being CurrentDb which takes about 0.35 milliseconds / 3500 microseconds

Next fastest is the use of DBEngine(0)(0) which took about 0.00087 milliseconds / 870 microseconds (almost 400x faster)

Faster still was the use of ThisDb which took just under 0.00015 milliseconds / 150 microseconds (about 2300x faster than CurrentDb)

However, by a whisker, the 'winner' was the use of CurDb which took just over 0.00014 milliseconds / 140 microseconds (almost 2500x faster than CurrentDb)

Interestingly, it takes about 100 microseconds longer to just set db=DBEngine(0)(0) than it does to set & reset the variable.
I have repeated these tests many more times and ALWAYS get the same results for DBEngine(0)(0), which I am unable to explain.

NOTE: The additional tests are in the module modMoreTests with the results saved in tblSetTimes. I used the query qryAvgSetTimes to calculate the average times.

These tests indicate that if you want to obtain the shortest possible time when running a query using code, it is definitely worth setting db=ThisDb or db=CurDb

The results also confirm the points made by both Jim Dettman and nsenor. I was clearly wrong in my assertions.

However, these figures should be put into perspective.

As long as the variable is not set within a loop, the time needed to set the variable is negligible compared to the total time to run the query itself

For that reason, it is very unlikely that any significant difference will be noticed whichever of the four methods is used in a real life situation.



Conclusions

1.   All four methods of setting a variable to denote e.g. the current database are very fast
      Both Jim Dettman and nsenor were correct in stating that using CurrentDb is slower than DBEngine(0)(0) or CurDb / ThisDb

2.   However, in all cases the time required is likely to be only a very small proportion of the total time needed.
      Overall, it is likely to make little difference which method is used

3.   Setting a variable repeatedly within a loop is poor practice and will cause the process to run more slowly.
      The effect will be noticeable if using CurrentDb repeatedly in a loop



Download

Click to download:  Speed Comparison Tests v2.7        Approx 1.6 MB (zipped)       The zip file contains both my test results and an empty datafile



Feedback

Please use the contact form below to let me know whether you found this article useful or if you have any questions/comments.

Do let me know if there are any errors

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 10 May 2023



Return to Speed Test List Page 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Return to Top