I am currently using a workbook where I connect to a MySQL database, download a recordset and then loop through 100,000s of unique IDs. The workbook has 31 sheets. Excel is ran in AWS where we open 5 copies and 13 instances of Excel per copy. I have found this to be the optimal number of copies and instances.
The workbook design is as follows:
Application.ScreenUpdating = False, Application.Calculation = xlManualDownload MySQL recordset
Loop through each unique ID in the recordset:
3.1 Loop through each of 31 sheets, clear the mysql data range in entirety, clear the calculation range in entirety except for the first row
3.2 Copy the mysql data from the record set to the sheet
3.3 Update Defined Names related to SQL data to new ranges
3.4 Use .FillDown on calculations
3.5 Update Defined Names related to Calculations to new ranges
3.6 Calculate worksheet
When loop gone through all IDs,
Application.ScreenUpdating = True, Application.Calculation = xlCalculationAutomatic
What I have noticed however, is that calculation time per record progressively slows down over time. For example, the first 10,000 records might calculate in 0.7sec/record and by record 140,000 this will grow to 1.5sec/record. This problem is exacerbated the more records there are, for example 70,000 vs 140,000 records.
- Is there anything regarding the Excel calculation engine that could potentially be causing this progressive slowdown in performance?
- I am assuming that the current setup of calculating one worksheet is inefficient as dependencies between the worksheets are not being resolved. Should the workbook as a whole be calculated as opposed to by sheets
- With regard to the Defined Names, each unique ID on each sheet may have a different number of rows. Would it be better to set a sensible range for each of the Defined Names, and then only update the range if this is exceeded? I have timed that updating the names can take as long as .FillDown