Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

This was written in response to the dbEngine(0)(0) vs Currentdb thread raised by Access World Forums member John Clark (NauticalGent).

 

He had found an article by Microsoft MVP Jim Duttman written in Dec 2009:

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative

 

In the article. Jim Duttman 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.

 

 

Although the article was written in 2009, the author Jim Duttman had repeated the same statements in recent replies.

 

Although there is a lot of truth in the article, I was surprised by his advice to mainly use dbEngine(0)(0) as Microsoft have been advising the use of CurrentDB rather than DBEngine(0)(0) from around 2000 onwards! For example, see this Microsoft article dated 06/08/2017.

 

I was also extremely doubtful about the supposed speed increase (up to 5000x) with the use of dbEngine(0)(0).

 

So I decided to compare the two methods and ran the following tests:

 

1.  Set db=CurrentDB followed by db.Execute followed by Set DB = Nothing

    This was done repeatedly - after clearing the data & again after each 1000 record loop

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

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

4.  As test 3 above but with db=DBEngine(0)(0)

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

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

 

I repeated each test 10 times and calculated the averages

The results below are for a desktop i5 processor 2.90GHz ; 4GB RAM ; 32-bit Access:

Speed Comparison Tests                                                  Page 2 (of 8)

Screenshots

Return to Code Samples Page

Click any image to view a larger version ...

As expected, the differences are mostly fairly small but some patterns are clear.

 

Summary:

1.  Setting the variable db=CurrentDB once or repeatedly is faster than using CurrentDB.Execute

2.  Similarly setting db=DBEngine(0)(0) once or repeatedly is faster than using

    DBEngine(0)(0).Execute

3.  It makes little difference whether the variable is set once or repeatedly

4.  Overall CurrentDB is slightly faster than DBEngine(0(0)

 

 

Factoring in the additional time needed to refresh the data if using dbEngine(0)(0), the assertion in the article that dbEngine(0)(0) is preferable and much faster is clearly incorrect.

 

Conclusions:

Using CurrentDB outperforms DBEngine(0)(0).

For best results, use set db=CurrentDB then db.Execute instead of CurrentDB.Execute

 

I contacted the author of the article but he repeatedly tried to invalidate the results.

So I repeated the tests on two other devices

 

Laptop i5 processor 2.60GHz ; 8GB RAM ; 64-bit Access:

 

 

 

 

 

 

 

 

 

 

 

 

Tablet Intel Atom processor 1.33GHz; 2GB RAM ; 32-bit Access

 

 

 

 

 

 

 

 

 

 

 

 

Based on these tests:

a)   Overall the desktop is faster than the tablet despite having less RAM (though it does have a

     slightly faster CPU)

b)   The underpowered tablet struggles on all tests but the last two tests are dramatically slower.

     I only ran these tests twice as they were so painfully slow

c)   CurrentDB is generally faster than DBEngine(0)(0) - the exact opposite of what was said in

     the article by Jim Duttman that triggered this thread.

 

 

Click to download:  CurrentDB vs DBEngine Comparison Tests v5.2        Approx 1.2 MB (zipped)

SpeedTest5 LaptopResults8MB TabletResults2MB DesktopResults4MB 1 2 3 Return To Top Page 2 of 8 Return to Access Articles 4 5 6

2.    CurrentDB vs DBEngine(0)(0)                          Updated 27/02/2019

7 8

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

* Required