This was written in response to a side issue raised by Access World Forums member The_Doc_Man in the dbEngine(0)(0) vs Currentdb thread. Both of the methods can be used to pause code giving the processor time to complete the previous task before continuing
The difference between DbEngine.Idle dbRefreshCache and DoEvents is that the former is a DBEngine-only event, whereas DoEvents allows any pending Windows events from ANY PROCESS a shot at the system resources.
Stated another way, the .Idle method waits for a specific and narrowly-crafted event to occur whereas the DoEvents action allows even lower priority processes a shot at the CPU resource. As such, even if nothing is pending, it involves an O/S non-I/O call. To be clear, the difference is the nature of the O/S process scheduler event. The .Idle call is a voluntary wait state based on your process priority but the DoEvents is not.
Granted, on any multi-CPU or multi-threaded system, the odds of having to wait very long will drop significantly with the number of CPU threads. I have almost NEVER seen my system with 3 CPUs busy at once. (It has four CPU threads.)
Therefore, the .Idle method is faster because less can happen while you are waiting AND because less code has to execute to get to and then back out of the wait state.
I mainly use DoEvents to build in a processing delay to allow the display to update progress.
This can be in the form of a progress bar and/or a message like 'Test 3 ; Loop 4 of 10'.
Doing either is useful to show users something is happening but it does create a performance 'hit'. In other words it makes the processing a bit slower.
Until now, I've rarely used DbEngine.Idle dbRefreshCache so I adapted my speed test database to do so.
Once again, I used 6 tests to compare the effect of using
1. DoEvents after each record added
2. DoEvents after each loop
3. dbEngine.Idle dbRefreshCache after each record
4. dbEngine.Idle dbRefreshCache after each loop
5. Using the Sleep API to build in a fixed 10 millisecond delay after each loop
6. No delay between events
I used Set db=CurrentDB and db.Execute for each test.
Each test was repeated 10 times and average times calculated.
These are the average results on a slow desktop PC with 4 GB RAM:
Click any image to view a larger version ...
Using DoEvents after each record creates a significant delay. Unless you need that level of progress detail it is detrimental.
Using Idle dbRefreshCache after each record produces a smaller performance hit (for the reasons explained so clearly by The_Doc_Man)
Usng Idle dbRefreshCache after each loop is the fastest of all BUT both tests 3 & 4 make the progress indicator perform erratically - the CPU just doesn't have time to keep up.
Usng DoEvents after each loop is almost as fast and the progress indicator behaved perfectly
As expected, having a fixed delay using the Sleep API is slow as it means the processor may be paused longer than needed.
Perhaps surprisingly, having no delay isn't the fastest method even though I switched off the progress indicator for that test
I also tested this on a laptop (faster as 8GB RAM) & a tablet (slow - 2GB RAM).
Whilst the times were different, the relative order was the same on each device
If you want a progress indicator, use DoEvents at appropriate intervals (e.g. after each loop) which allow the display to be updated without a significant performance hit.
If progress displays aren't important, use dbEngine.Idle dbRefreshCache instead ... at suitable intervals. You can always use the hourglass and/or a fixed message 'Updating records....' so the user knows something is happening.
The attached database also includes code to give detailed computer information.
Click to download : CPU Pause Comparison Tests v4.2 Approx 1.3 MB (zipped)
3. DoEvents vs DBIdle.RefreshCache Updated 27/02/2019
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