Good morning Stu412,
I hope your day is going well. I'd like to clarify a couple things before I offer any suggestions. Would you be able to post the ExecuteSQL () statement you used and tell me what the "Number" field represents? Thanks and have a great day!
ExecuteSQL shouldn't "Kill your entire system" but it could easily be no better/faster than your summary fields.
What you describe is typical for the design that you have used. It's why summary fields and unstored calculations must be used with care as they only calculate "when needed" and your design requires that a great many calculations all update at the same time--producing that progress bar.
One thing to check before rethinking your entire set up: Are the calculation fields that have the Case Function stored or unstored? I'm wondering if you have a whole bunch of unstored calculations fields in a large number of records that all have to evaluate before the summary fields can even begin to do their evaluations--which would be a major case of "piling on" here.
If they are unstored and you can make them stored values, I'd test that first to see how much improvement that makes. (Calculation fields can be either stored or unstored depending on the storage option specified for them and whether or not they reference data outside the current record (such as in a related record) or a field that cannot be stored/indexed such as a global field, summary field or other unstored calculation field.)
The original Case statements are unstored which would lead to them needing to be resolved each time the calc is called. However, I've left them like this because the values and the selection criteria being referred to in the Case statement can change any time.
If I were to make the Case statements stored, would the change in either the value or criteria fields be sufficient to redo the calcs?
Stored calculations automatically re-evaluate each time a referenced value is modified. But the possible catch here is that some data references--those that refer to data in a related record or in a global field, force the calculation to always be unstored.
There are ways, if done with careful attention to all relevant details, to set up a data field with an auto-entered calculation that is then updated via script when any data from a global field or related record is modified. This can sometimes be a way to get much better performance, but only if certain frequent edits don't require updating all records in your table in a great big "batch update".