DoEvents vs DBIdle.RefreshCache vs Sleep


Last Updated 27 Feb 2019                                                Difficulty level :   Moderate


This is the third of 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

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_Doc_Man wrote:

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:


These are the same results as a report




Summary:

Using DoEvents after each record created a significant delay. Unless you need that level of progress detail it is detrimental.

Using Idle dbRefreshCache after each record produced a smaller performance hit (for the reasons explained so clearly by The_Doc_Man)

Using Idle dbRefreshCache after each loop was the fastest of all BUT both tests 3 & 4 made the progress indicator perform erratically - the CPU just didn't have time to keep up.

Using DoEvents after each loop was almost as fast and the progress indicator behaved perfectly.

As expected, having a fixed delay using the Sleep API was much slower as it means the processor may be paused longer than needed.

Perhaps surprisingly, having no delay wasn'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.



Conclusions:

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.

NOTE:
The attached example database also includes code to give detailed computer information.





Click to download :     CPU Pause Comparison Tests v4.2     Approx 1.3 MB (zipped)



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 27 Feb 2019



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