dbEngine(0)(0) vs Currentdb

[isladogs]

Thought it might be worth placing results from my 3 devices together
Results for each are consistent in tems of rank order but the effect of the last two tests differs

1. Desktop i5 processor 2.90GHz ; 4GB RAM ; 32-bit Access

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

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

For info, clicking the View Summary button displays the results in a report with a summary chart (example attached)

As you can see(on these tests):
a) Overall the desktop is faster than tablet despite having less RAM (though a slightly faster CPU)
b) The underpowered tablet struggles on all tests but the last two tests are dramatically different. I only ran these tests twice as they were so painfully slow
c) CurrentDB is generally faster than DBEngine(0)(0) - the opposite of what was said in the article by Jim Duttman at EE that triggered this thread.
After repeatedly trying to rubbish the results, he's finally gone quiet on the subject!

My belief is that using CurrentDB each time is slower as Access has to interpret what it means ...each time
Whereas setting db=CurrentDB at the start 'optimises' the process as it means Access has that read into memory.
A bit like using stored queries is meant to do compared to using SQL statements (though NG has been checking that & will I believe be reporting back on those tests in the near future)

As for the variation between devices, perhaps a different level of priority is put on processing tasks depending on the specifications.
So if a device is underpowered (2GB tablet) or, if I can put it like this, possibly 'overpowered' for the task like Minty's 32GB dream machine, the discrepancy is more marked

For info, the times are calculated using the system clock.
This gives output in milliseconds but I've rounded all values to 2 d.p.
The reason I've done is that the system clock updates typically 60 times per second so each individual result is only accurate to about 0.016 seconds.
For that reason, doing the tests repeatedly is necessary for greater accuracy.

For anyone interested, last year I did an example to measure reaction times that uses the same method of calculating times: https://www.access-programmers.co.uk/forums/showthread.php?t=298140

@Minty
You might also be interested in comparing different methods of pausing the processor actions - DoEvents vs Idle RefreshCache - see post 33

 

[shadow9449]

After reading this thread, here's my non-scientific contribution:

I have a routine in my program that was calls a function that was declaring db as CurrentDB at the beginning of the function. I tested this function setting the routine to do a task that would call the function about 2600 times. It took 45 seconds. Then I tried it by calling a global function that declared a public variable pointing to CurrentDB only once and it took about 31 seconds. This was just done on my local workstation, not on a network. But it does show a big difference at least anecdotally.

 

[NauticalGent]

I realize this thread is old and the marks deep on this dead horse. But I came across this article ( https://nolongerset.com/demystifying-doevents/ ) from one of our newest members, @NoLongerSet.

I think it adds a nice finish to this subject.

 

[Galaxiom]

I have a routine in my program that was calls a function that was declaring db as CurrentDB at the beginning of the function. I tested this function setting the routine to do a task that would call the function about 2600 times. It took 45 seconds. Then I tried it by calling a global function that declared a public variable pointing to CurrentDB only once and it took about 31 seconds. This was just done on my local workstation, not on a network. But it does show a big difference at least anecdotally.

Big difference? 5 ms per loop?

BTW The "overheads" from running CurrentDb() are not just about about setting and destroying variables. My understanding is that CurrentDb() also refreshes the database Collections before creating the pointer so that any changes made to the schema in Access are reflected in calls to it.

Hence setting a single persistent variable and reusing it skips this step, in effect going back to being the same as using DbEngine(0)(0).

In most cases it isn't going to matter because the schema isn't altered but the tiny delay is inconsequential. Mircosoft recommends always using CurrentDb().

 

[shadow9449]

Big difference? 5 ms per loop?

Percentage-wise it's a significant difference. Perhaps per iteration it's small but when your scope is big enough it will add up.

I did say that it's non-scientific

 

[isladogs]

I realize this thread is old and the marks deep on this dead horse. But I came across this article ( https://nolongerset.com/demystifying-doevents/ ) from one of our newest members, @NoLongerSet.

I think it adds a nice finish to this subject.

Actually I think it fits really well with this article on my website: DoEvents vs DBIdle.RefreshCache

 

[CJ_London]

Mircosoft recommends always using CurrentDb().

And what about if your want to know the recordsaffected value?

 

[The_Doc_Man]

I think many of us understand that Microsoft's recommendations are to be taken cum grano. They recommend the Edge browser, too, and for a while they even recommended Internet Explorer before Edge was a thing. It's hard to tell when M$ makes a recommendation whether it really IS a good utility - or merely good for their bottom line.