dbEngine(0)(0) vs Currentdb

I found this article a few days back and re-read it again today when I had some time to actually read it.

https://www.experts-exchange.com/ar...kspaces-0-Databases-0-and-an-alternative.html

The author obviously knows what he is talking about but it never hurts to hear another prospective.

If I understand him correctly, EVERYTIME you create the Currentdb object, there is an overhead “hit” on proformace even though you destroy the object when you exit your Sub/Function. Do I have that correctly?

Welcome any thoughts on this article, thanks for taking the time to read it.

Yep, that's a big reason why if I need to call the database object more than once in a code run, I create a db Object, assign CurrentDB to it, and then refer to the db object for the rest of the run.

Also, 99% of the time, you won't actually notice the performance hit, at least in my experience. The bigger threat is pulling the wrong database if you are working with more than one at the same time.

I always (well mostly) set a reference to Currentdb. Using Currentdb not only means overhead issues but also problems if you need to refer to what has happened.


Eg
will always give zero, no matter how many records were deleted in the previous line because it's a new instance.

Which is why I always had a "support" module for which the declaration area held all of my Public variables that were, effectively, project-global. My DAO database object was there. When the switchboard form loaded, one of its steps was to set that database object to CurrentDB. After that, it stayed that way for the life of the object. Since I very rarely allowed a RESET to occur, that meant it was long-lived.

I also ran a quick test on every form load to see if the object was Nothing. If so, it got reconstituted. However, my logging told me that it happened SO rarely that it was not an issue. Once I got the last of my untrapped errors properly trapped and contained, I don't recall it EVER happening again that I needed to reconstitute the object.

Thanks folks. What is making me scratch my bald head is this...I’ve been reading Access “stuff” for three years straight, at a minimum checking this forum at least once every day and reading random posts.

Along the way I have picked up some very cool nuggets in the form of tips and best practices. With the exception of setting your Recordsets and Database objects to Nothing as you exit your routines, I have seen nothing on this subject. The only time I have ever seen dbEngine (0)(0) was in a book by Andrew Couch which made me wonder what the difference was. A google search led me to that 2009 article.

Makes me wonder what else I don’t know...

I quote a line from "Civilization: Beyond Earth"

"What you don't know can't hurt you. This does not apply to fungi."

Good question, and good answers!

Thanks everyone!

Sent from my SM-G925F using Tapatalk

I have occasionally used both methods in the same application. It makes sense to use CurrentDb() for the code database but dbengine to get a reference to the other database I need to reference. My documenter uses the second db reference to get tables from the BE I selected to document them. In other cases, I use the dbengine reference to actually modify table definitions in the BE.

An interesting thread. Thanks NG

I almost always just use CurrentDB.Execute so I rarely bother with the Set db commands

Having read the article & the various comments, I thought I'd test the time taken for different scenarios to complete

I used a large postcodes table of 2.6 million records and measured the time taken to update null records in a text field to 'OK' and to clear those same records.

I used 4 methods
1. Set db=CurrentDB before each update statement, used db.Execute & Set db=Nothing afterwards
2. Set db=DBEngine(0)(0) before each update statement, used db.Execute & Set db=Nothing afterwards
3. Omitted the Set db and used CurrentDB.Execute
4. Omitted the Set db and used DBEngine(0)(0).Execute

I repeated each test several times compacting then restarting before each group of tests. Outcomes were very similar in each case

Results:
As expected, methods 1 & 2 were MUCH slower due to the time needed to set & clear db twice in each case
Methods 3 & 4 completed in about 20% of the time for the other two methods

OOPS. Sorry! It wasn't a fair test!!!! I've since redone these - see next post!



In some tests, DBEngine(0)(0) was faster but in other cases it was the other way round. The differences were rarely significant.



Bearing in mind issues touched on in the article about the need to refresh using DBEngine(0)(0) and the often significant time that would need, I think these limited tests have confirmed method 3 to be the best approach overall

Where I need to refer to an external database, I just specify it for that section of code

NOTE:
Due to the size of the data table, the test app is MUCH too big to upload (25MB zipped). If the code may be useful to anyone, I can remove the table & upload it

OOPS. Sorry! It wasn't a fair test!!!! Mea culpa

Methods 1 & 2 were measuring the time to clear then populate the table
Methods 3 & 4 were only measuring the time to populate the table.

I've now amended it so all methods are identical (i.e. time to clear then populate the table)

NEW results:
These show there is negligible difference between the 4 methods in terms of time ... at least for this test



For now at least, I stick by my previous comments about using method 3

Perhaps some further tests would be useful e.g. append and make table

Well I wish I would have waited until you did this test...I did a quick find and replace trick (47 of them) and got a "28 -Out if Stack Space" error and a system exit for my efforts. I got it figured out quickly enough and set about this morning doing so additional clean-up of my code.Did a quick C&R and a decompile and prepared myself to be amazed. As you have demonstrated, the show was underwhelming. At first I chalked it up to my tables being relatively small but then you posted your more accurate results and I felt quite deflated...All it not lost, I did get some house-keeping done and even fixed some nagging issues I had been meaning to get at so all is not lost. Not to mention I get paid to play with Access. There are worse things in life, unless your name is NFK!

Like I said, I use Currentdb.Execute if I'm only going to need to reference the database object once, sometimes twice. If I have to access it a lot for some reason (usually because I'm running a series of queries in sequence), I'll create a db object, assign CurrentDb, and go from there. I'll also do it if I need to know the number of records affected by a query, as a couple times I've tried qdf.RecordsAffected and received 0 when I knew damned well the query had updated records.

Still, nice to see that processing time doesn't really seem to be affected, especially considering the size of the tables I work with.

The tests I did certainly don't seem to support the notion expressed in the article that using DBEngine(0)(0) is

about 5000 times faster

Click to expand...

than CurrentDB.

@NG
In case I can save you time before another set of possibly unnecessary code changes, below are the results of another test I did a couple of months ago

In a thread at UA, someone asked which was the Best Way To Check For 'nothing' In A Form Control. Various people suggested using:
- Trim([FieldName] & "")=""
- Len([FieldName] & ""=0
- Nz([FieldName],"")=""

I normally use Nz but more out of habit than any other reason.
One particular user was adamant that Len would be noticeably faster as

VBA handles numbers better then strings especially in comparing, so Len(...) <> 0 is easier for VBA then "" = ""

Click to expand...

So I put that to the test using the same large dataset & timing populating any 'empty' records then clearing those records again

In each test, Nz was SLIGHTLY faster than the other methods but not enough to make a huge difference. All results were similar but consistently in the order shown:



These are all on my 7 year old underpowered desktop PC
Testing on my 6 year old laptop shaved 10 seconds off each time.

So the only significant result in my view was that I need a new desktop PC

HTH

Following a request by PM, I've just uploaded my 25MB zipped test file to Dropbox.
If anyone wants to try the tests on their own PCs, send me a PM or email and I'll share the link.
I expect many of you will then feel smug when you get noticeably faster times than I got on my old PC

NOTE: As my Dropbox folder is close to full I'll only leave the file there for a couple of days.

Are you kidding me? This ancient laptop they have me working on is probably slower than your PC! And my home PC is a gaming rig, so it damned well BETTER be faster.

One easy way to find out!
The person who contacted me by PM has a

'very shiny new i7 32gb Ram M2 SSD Lenovo P51 kindly provided by my employers'

Click to expand...

Presumably its still in mint condition!

I just asked my employer for something similar and he laughed hysterically.
As you know, I'm self employed

Out of curiosity how many times did you use the Set dB = Currentdb line?

The EE article also alleges that the number of times it is used has the effect on overhead.

Edit : I confess it was me !
Okay slightly smug time, but it is new and pretty whizzy for a laptop - Core i7 vPro 7th Gen 32Gb Ram, M2
And

So coders of the world. Forget all that cool mega code optimisation. Throw shed loads of money at your hardware instead !

Edit 2 : Just looked at the "Hard Drive" (it's a glorified memory stick!) specifications;

Sequential Read 3,000 MB/s
Sequential Write 1,800 MB/s
Random Read 270,000 IOPS
Random Write 420,000 IOPS

No wonder it moves along a fair old lick...

So the only significant result in my view was that I need a new desktop PC

Click to expand...

This, in scientific terms, is called a "secondary finding" or "serendipitous finding." It's amazing what you can find when you were really looking for something else. Louis Pasteur's Nobel Prize on polarization of light through organic crystals was a serendipitous finding. All he wanted to do was a supplemental experiment for bonus credit to pull up a bad grade in Chemistry while he was studying for his M.D. equivalent.

I also noted, Colin, that you published your findings in Expert's Exchange (EE).

The original EE article suggests the the CurrentDB function is part of the user interface (UI) and that it creates a dynamic object that somehow encapsulates the database actually opened by the underlying engine. Whereas using the DBEngine.Workspaces(0).Databases(0) reference bypasses the UI.

Then the comment is made that the two databases implied by these paths might not be the same. It is interesting to note that Microsoft DOES NOT SUGGEST that you use this kind of pointer as the EE article suggests.

https://docs.microsoft.com/en-us/office/vba/api/access.application.currentdb

Relevant quote from article:

In previous versions of Microsoft Access, you may have used the syntax DBEngine.Workspaces(0).Databases(0)or DBEngine(0)(0)to return a pointer to the current database. In Microsoft Access 2000, you should use the CurrentDb method instead. The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0)syntax refers to the open copy of the current database. The CurrentDb method enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0)syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser database.

Click to expand...

I understand that DBEngine(0)(0) is traversing the component object model to reach the database in question and should be a matter of just indexing into a couple of arrays (of pointers, probably) to find the current database (as in, the .Databases(0) object.) My question, which is unlikely to be answered in this forum, is what is the difference between the .Databases object that is the "end-object" of this collection specification and the database object returned by CurrentDB?

The implication of the EE article is that the database object is a data structure of pointers to objects and that using CurrentDB runs the risk of becoming outdated if new new objects are created. The idea is, I infer, that CurrentDB is a local copy of the pointers to the objects of the database, which to me says that it is somehow a local copy of all or part of MSysObjects(?).

But what I don't understand is that if we are talking shared BE server, there IS no BE equivalent to that. The JET engine or ACE engine is on the same box as the FE app and so the DBEngine(0)(0) object is ALSO a local copy of those pointers and COM structures. Seems to me that the difference between the two is the difference in overhead of copying whatever it is that the CurrentDB function copies.

Which is why I'm surprised to see THAT MUCH of a difference in your findings, Colin. The time required for you to do updates of 2.6 million records should totally hide any minor overhead involved in the setup of that reference, because once you do the .Execute method, your code is out of the picture until JET/ACE has finished. Doing 2.6 million of anything using SQL should CLEARLY take a long time due to disk I/O speed, and the amount of time to do a copy of something that is in MSACCESS.EXE's memory or at worst is one table from the BE file (that has to be read from the network) should be nearly invisible.

I am NOT asking you to repeat the experiment but I wonder if you would get greater variation in times from one try to the next for the same method than the differences you got from the different setups.

@NG
In both methods 1 & 2, I set & destroyed the db variable TWICE.
Once whilst populating the fields then again when emptying them again.
The latter takes about 4x as long in these tests

@Minty
You outed yourself - I was keeping your identity quiet!
Apart from saying your PC was probably in mint condition
Anyway, your PC is definitely better than mine! By about a factor of 3

My rather more modest desktop spec is:


As I said via PM, better lock up your shiny new PC this bank holiday weekend as I know where you work!!!!

DocMan
I already knew that I need a new PC but its always good to have more evidence

Not sure whether you saw that I corrected my original findings in post #10 (as well as the results of a different test in post #13)
As you say, I have since published the corrected results to this topic at Experts Exchange.

I think you may have misread part of the EE article:

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.

Click to expand...

The emphases are mine

That's why I talked about needing to factor in the time for a refresh if you used DBEngine. Both my results & Minty's show its no more than negligibly faster even without a refresh.

All my tests were done on a local table. Whilst I could test using a linked Access or SQL BE, I KNOW the results will be slower so there's little point