VBA Update Routine with Varying Processing Times

[skydivetom]

Hello experts:

I'm hopeful I could get some specific ideas/solutions as to why the performance times for 2 different *update routines* vary differently.

Attached zip file contains 3 DBs... each of them containing the same data but with different VBA code (in module "05_NNSY"). I will summarize each of these 3 below:

"00 Update Query 14k Records"
- Upon opening the DB, open form "F01_MainMenu" and click command button "Update Product Tables". Doing so calls function NNSY_Updates.
- The module's function includes a single "DoCmd.RunSQL" line of code.
- The processing time for updating all 14,411 takes approximately 2 seconds.

"01 DoWhile 14k Records -- Not Indexed"
- Again, open the form "F01_MainMenu" and click on the command button.
- Now, this VBA routine uses a DoWhile loop (which is preferred over the UPDATE query).
- Also, in this version the field [MSHP_LEGACY_REPORT_NUMBER] is **not** indexed in tables [tbl_MISHAP] and [tbl_PERSON].
- Now, in contrast to the DB "00 Update...", the function takes approximately 25 seconds until the update routine has been successfully executed.

"02 DoWhile 14k Records -- Indexed"
- This is a copy of the file "01 DoWhile ..."; however, in this version, the field [MSHP_LEGACY_REPORT_NUMBER] has been indexed (Yes | No Duplicates).


- While an improvement (WRT to processing time) can be noticed, it still takes about 10 seconds to execute the record update routine.

In summary:
- Version "00..." takes ~ 2 seconds
- Version "01..." takes ~ 25 seconds
- Version "02..." takes ~ 10 seconds

Here's the desired end-state:
- I would like to use the DoWhile method (either DB "01" or "02") over the Update (DB "00") method.
- Ideally though, the processing time in file "02" should be further reduced (e.g., 1 - 2 seconds -- vs. 10 seconds -- for 14k records).

My question:
1. Can the DoWhile be modified so that it would lead to an improved processing time? If so, how?
2. Please keep in mind that all of the 3 versions only update 1 table (tbl_MISHAP) in these 3 example DBs. In actuality though, I may need to update an additional 4 tables (each of them containing also 14k records). How could the inclusion of other tables (e.g., 5 * 14,000 records) NOT degrade the processing times even further?

Cheers,
Tom

[orange]

Always use Option Explicit as second line in module.

Use CurrentDB.Execute rather than DoCmd.RunSQL for Action queries and you won't need the Set Warnings commands.

SQL Update query will almost always be quicker that recordset processing.

[Gicu]

Hi Tom,

Agree with Orange, update queries would be faster then looping through the recordset. Now there are various ways of running those update statement, have a look at this great article by Colin: http://www.mendipdatasystems.co.uk/s...s-6/4594478795

I am attaching a tweaked version of the Do While Not (= Do Until ) loop that uses dLookup instead of another recordset, not much of a difference between the two approaches.

Cheers,
Vlad

[skydivetom]

Orange/Vlad:

Thank you both for the additional info (and modified function, Vlad). Concur the processing time in the NNSY_UPDATE_LK() doesn't differ much compared to the original NNSY_UPDATE function.

I enjoyed reading Colin's article... thanks for sharing it.

Cheers,
Tom