Improving coding to increase processing speed

[brainonastick]

I am building a training registration system made up of a Course Registration DB and a Course Scheduling DB. The Course scheduler also summarises the registration data (number of student confirmed, withdrawn, waiting list, transferred, substituted) and fees (paid by cheque/cash/credit card, paid by invoice, paid by voucher, paid by transfer, not paid, invoiced etc). There is a payment summary layout in the Registrations DB with a portal that shows the payment methods, amount owing etc. In the same layout there are pipe fields that show the amount summary data from the Course Scheduler DB.

The payment status fields in the portal are calculations based on how much has been paid and what type of payment it is. The portal is slow to refresh (5-10 seconds) and the whole layout is quite slow. i want it to refresh in 3 seconds or less.

Would it be faster if I got rid of the pipe fields from the Course Scheduler and just build summary calcualtions in Course Registrations for the various amounts?

is there a way to speed things up - my IT manager has increased the cache to 512 and that helps a bit when the DBs are hosted. I dont want to change the payment status fields into manual text fields (which would mean they wouldnt have to calculate and would be faster). I have tried adding a script that finds the status data, copies it into some parallel fields which are displayed in the portal instead but it's not any faster.

Would it help if I reduiced the number of relationships and made some composite registration/payment status relationship that only finds certain types of data (confirmed AND invoiced, or withdrawn AND refund paid etc.).

All suggestions gratefully accepted.

[Fenton]

I don't know exactly how you're doing it, but a couple of observations. One of the things that slow down calculations, especially related calculations, is to include other calculation fields as is, rather than the basic calculations which produced them.

I mean, if you have a field with a long calculation, your (and my) tendency is to reference the field in another encompassing calculation, by name, instead of copying the entire calculation of the first field, and pasting it into the 2nd, encompassing one. The first method is more readable, but the second method evaluates faster.

In version 7 a great of confusion can be alleviated by using the Let function to hold the long calculations. Then the remaining calculation can still look fairly simple and readable.

I don't know for sure, but I think that using Sum(relationship::field) is faster that referencing the same kind of field in a child table. The first is only using 1 relationship, while the second is using 2. But whether the extra effort would be worth it for several types of results; not really.

What about those different types of results in the other table? Are they stored fields? If not, why not? That'll definitely be slow, summarizing unstored fields.

If speed is a problem, it is better to create extra calculation fields, so as to get the data into stored fields of their own, rather than building many-level relationships to filter it later. The calculation then happens at data entry rather than later.

Another completely different observation is: don't put totals on data entry or list view screens. Only show when needed.