How not to dance on a rake with a tambourine while doing CRM?

Question

PHP

I would like to reduce the load on the system in a multi-user cloud CRM.

The most costly in terms of execution time is the calculation of balances for the entire period on the specified date in the context of the nomenclature.

Each time you have to sum up all income and expenses.

There was an idea to make a "closing of the month", as in 1C, in order to count the balances in the accumulative table and then take from it and then do the calculation only for the current month.

In the case of posting documents for the old period on the fly, recalculate the balances only for those nomenclatures that are in the document, perhaps simply adding, subtracting the amount from the current document with a cumulative amount.

1. Will it give an increase in speed, is there any point in writing this program code or will the output be negligible.

2. Will the selection on the table with all the months and the nomenclature be even more difficult? Or keep only the last month, i.k. the majority of requests for the current date, and leave the rest of the requests without "caching"?

Answer 1

for this increase, you will pay with crap for calculating balances and turnovers for an arbitrary date. for example, a report from the last month's rotation
Full recalculation does not present any problems. it is much cheaper to buy more memory and processor time than to unnecessarily complicate the system. Odintse just slows down godlessly because of its architecture.
In my accounting system, for example, everything is recalculated and the balances in warehouses and cash accounts, and there are no problems.

Answer 2

The most costly in terms of execution time is the calculation of balances for the entire period on the specified date in the context of the nomenclature.

1) Is this your assumption or did you measure the performance of queries and code?
2) If the performance really sags, then how do you calculate? SQL with aggregation and count?
3) How many rows are there in the sampling tables?
4) What does explain show?
5) Indexes?
If you can’t optimize by selections and everything depends on the query, then you can of course display the amounts monthly, and then do the aggregation by amounts + additional selection from incomplete months, but there are some nuances, in addition to the obvious complication of the code and denormalization, at least not always all turnovers are firmly nailed to the end of the month and can be changed retroactively, which will also need to be taken into account and recalculated when making such changes.
Some additional speed can be given simply by a "long" cache for such requests, with which, in principle, the same problems may arise - for some time the data will be inconsistent.

Answer 3

The most costly in terms of execution time is the calculation of balances for the entire period on the specified date in the context of the nomenclature.

First, why is it in crm? Secondly - why is it every time on a given date, and not at the current moment?