A question about speed, stored and unstored results
My DB is, on the whole, pretty snappy. I have a massive table which will only get bigger and currently there are around 50,000 records on there with around 128 fields. This data is disseminated between customer layouts through sorting and filtering and displayed as I want it.
I need summaries on foreign tables, so have used GetSummary extensively. I also need more advanced calculations on other foreign tables, so for these I have used ExecSQL alongside multiple TO's which filter the results, and this is where things really slow down.
If I flick between foreign records on the screens which have GetSummary to display results, there's a slight lag (I notice it, users may not). If I flick between foreign records using the ExecSQL / multiple TO screens to display results, theres a MASSIVE lag.
Initially, the ExecSQL / multiple TO screens were very quick, but as I've added more data to the main table, these are now super slow.
I need to know in people's experience which is best - GetSummary, SQL or TO's? I have a mix of all three at the moment.
I also need help on storing / not storing results. For the most part, the data won't change (so therefore I think I can store it, therefore making it quicker???) But if it does change, what will trigger a recalculation?? I may have this completely misunderstood
Thanks in advance