We often hear the recommendation that the recalculation of totals should be performed regularly and this operation results in the improvement of performance, but what lies behind this procedure and what problems are solved exactly?
Let’s try to examine…
What are the totals on registers?
These are the aggregated values from the table of register records that are used to quickly calculate the residue and turnover on any measurements. They are stored in the separate tables.
What totals can be?
By default, there are the monthly totals for the registers and the actual totals (only for the registers of residues).
Why «by default»? Because the base administrator can manage the presence of results using the methods «SetTotalsUsing» and «SetPresentTotalsUsing».
What happens when the document writes the record on register?
Imagine that we conduct the document «Sales of goods and services» in a standard Trade 10.3 with 1 row in a table part post factum for 01.01.2013, and the date for calculating the results — 28.02.2013. During conduction an accumulation register «Goods on warehouses» is written.
In this case, in DBMS:
1) a new record is added to the table of accumulation register records
2) in the table of totals the actual results are updated – from the residue for 01.11.3999 a written off number is subtracted.
3) if the document date is less than the deadline for calculating the totals, then the totals are updated by the months (and as many times as how many months the document date is less the calculating date of results) – i.e. the totals will be updated for 01.02.2013 and 01.03.2013.
Altogether, due to the document writing 4 rows were updated only on this register.
And where is a problem?
And the problem is that over the time in the table of totals the records with zero values are accumulated. Imagine that in the previous example the implementation wrote fully off all the residue of the goods at the time. It turns out that 2 monthly totals and the actual totals will be reset to zero – firstly the arrival added the positive value in the totals, and the implementation zeroed it. In the virtual tables the value will disappear, but the zero records in the physical tables remain.
Let’s look at the table of totals for this register in DBMS – we will get on periods the general number of records and a number of records for which all the registers are equal to 0:
It turns out that we have some number of «junk» records in the monthly results and almost 30% of the «junk» records in the actual results.
But the actual results are the most frequently used data for operatively conducted documents. And the extra rows in this data array result in slowdown of query execution, i.e. the documents are conducted longer, more risk to appear the locks.
How to fix situation?
Note that it should be done regularly. The frequency of conduction is determined individually depending on the intensity of working with base.
There are a few options.
The easiest and most obvious – to launch in the Designer a full recalculation of results. Drawbacks – if the base is big, this process will take a long time.
Another option – recalculation of the results using the built-in language tools.
For example, to recalculate the actual results (as the most important in terms of operative user work), it is possible to use the following code:
For Each Reg In Metadata.AccumulationRegisters Do If Reg.RegisterType = Metadata.ObjectProperties.AccumulationRegisterType.Balance Then AccumulationRegisters[Reg.Name].RecalcPresentTotals(); EndIf; EndDo; For Each Reg In Metadata.AccountingRegisters Do AccountingRegisters[Reg.Name].RecalcPresentTotals(); EndDo;
If your base has been running for more than one year and its volume is large enough, this code will be executed many times faster than the full recalculation of results. Therefore, this code can be converted to the scheduled job and executed each night or on weekends.
Perform this procedure and see in DBMS the same table:
It can be seen that in the actual results (first row) there are no more zero records.
Similarly the monthly results are recalculated.
It is required to use method «SetTotalsPeriod()» – it is possible to recalculate, for example, only few last months and do not load the system with unnecessary recalculation of previous years.
What else should be kept in mind?
Recalculation of results strongly affects the statistics – it becomes not relevant. Therefore, after recalculation of the results you can get not an improvement, but, inversely, a sharp degradation in performance. So, it is required not to forget to launch the complete update of statistics in DBMS and cleaning a procedural cache after recalculation.