Period of the calculated totals 1s 8.3. Checks and modes

) and the platform developers have nothing to do with it, this is a fairly well-known architectural solution, not to delete "zero" entries. I would even say that this is an old "holy war".

The most important thing here is to understand that a record with zero amount in the end does not mean at all that this record is not needed.

When designing relational DBMS, it is considered (considered) that CRUD operations (Create, Read, Update, Delete) are distributed in terms of resource costs as follows

1. Light: Read, Update
2. Medium: Create
3. Severe: Delete

And based on the logic of the Register object, which changes frequently; and due to the high cost of deleting records, there is a position that:

the total record does not make sense to delete synchronously at the time of the zero total, because "zero" does not mean "NULL" and because there is a high probability that the next transaction will "want" to increase or decrease the total and it will become non-zero and we will need to incur additional costs and on the insert operation.

hence the message that it makes sense to delete records with a zero total asynchronously, that is, at a certain moment in time - but again, it is not known how to define this very "certain moment". Such a definition should lie with those responsible for the application - most often, as we know, the recalculation of totals occurs at one point in time when closing accounting periods and is presented as a kind of preparatory procedure. This is where there is a problem that has been known for a long time - the business task Closing the period is not a task of ensuring technical stability and business can sometimes "score" on it.
In my practice, I had a table with 400 million records with a zero total.

And here I can say that the platform developers slightly "missed" (from the word "defect") - the fact is that, according to the above-described architectural solution, it clearly becomes clear that:

You need to delete records with a zero total for those keys (sets of dimensions) for which there have been no UPDATE operations for a long time. And this functionality is not in the platform - there is only a global recalculation. In large offices, this is solved by SQL Job, which performs approximately the following work:

1. find 1 set of keys (measurements) for which there were no movements in the last month and which are currently zero
2. for this set of measurements, delete an entry from the table of totals

usually this Job is started every 10 seconds, TOP 1 is selected to reduce the blocking time for an expensive delete operation. Naturally, maintenance plans for recalculating statistics and rebuilding defragmented indexes are already built into such databases. In cases where there are a lot of such "unnecessary" records, then they usually reduce the Job launch period, or abandon the Total Register - because if you have a lot of keys that go to "zero" and are no longer used, most likely you have 2 operations on these keys movements "came" and "left" - why to store such information in the register of totals is completely unclear.

Well, about statistics here, too, everything is worn out - mass operations CREATE and DELETE, as well as UPDATE of a key column lead to a violation of the index search tree (the range of key distributions across data pages) - well, that is, in the search range 1..10 it may well be a key with a value of 23 - this way SQL was more convenient, because the data page was next to the page of key 7, and key 6 instead of which was replaced by key 23 will be in the range 100..134 - which was also more convenient based on the data pages. An example on the fingers - but I think the essence reflects.

In general, about statistics at the time of bulk operations, it is convenient to understand the following: when you do a bulk insert SQL data tries to help you and operates with the proximity of data pages to optimize inserts and completely forgets about optimizing read operations, where the parameter is statistics (key search ranges in the table - key distribution), so that after bulk inserts the read operations are also fast - you need to restore the optimization tool reading by doing UPDATE STATS.

And I also forgot to say - mass deletion leads to a massive occurrence of phantom records: the record is listed as deleted but takes up space - this situation leads to a decrease in the performance of SCAN type selections (viewing).

Anikrion; Albert_2008; Niberu; ser6702; MarchTomCat; olezhe; user598655_ilia-bers; klaus38; LordKim; lmnlmn; spenser123; Monte Carlo; acanta; zaharknyaz; Aggressorak; vesd; Ilya $ n; Waanneek; SkyJack; letarch; aegoncharov; user777757; [email protected]; mytg; Gang031; ice-net; Goga1979; ChessCat; RegrZ; 1cprogr_nsk; Irwin; Paradise.87; KAV2; corum; Roman100; for_questions; ragimi; EugeneMIPT; kai nk; kitaevay; crosby; Noxie41; Alex_grem; nixel; new_user; tdml; NeviD; RimidalV; reboot; denis_aka_wolf; Flashill; marchenko.y; freya-khv; asg.aleks; denis13; adm134; TIS_08; mtv :); soulsteps; shalimski; Anesk; pisarevEV; Silenser; kwazi; engineer74; vadimlp77; Artano; dgolovanov; pchela751; aexeel; artbear; jif; Dmitryiv; Rego1337h; slavap; WizaXxX; IvanBoychuk123; fishca; Evil Beaver; Dach; RodinMax; sanches; mdmdvd; zakakvo; Krio2; jacksonp; adeich; afedor; MaximStav; DoctorRoza; Serg0FFan; sanfoto; kinazarov; Bukaska; theshadowco; oitnur; JesteR; detec; audion; laeg; morok1983; krv2k; Di-dog; sparklemal; awa; KAPACEB.AA; Chif13; sa1m0nn; CratosX; AllexSoft; galich; vlad.frost; igorynets; tormozit; vasiliy_b; vladir; meuses; Poopkeen; Andreynikus; Prad2002; dicwork; JohnyDeath; An-Aleksey; It-developer; rgrisha; Bronislav; 7o2uYXg; HolodZar; adyrshdv; AzagTot; Ramses; DenisCh; PONOM; red80; w-divin; metmetmet; CheBurator; PressaLod; Diversus; sevushka; Aleksey.Bochkov; yuraos;

Currently, a significant part of databases based on 1C-Enterprise 8.3 is used in the "Managed application" mode. Including in this mode, the process takes place routine maintenance, such as, for example, the opening of the next period.

Let's consider the process of opening the next period in more detail. As everyone knows, the accumulation registers and the accounting register are built from several tables. For ease of understanding, we will only consider the main two:

Main table: a table of transactions for an accumulation register or a table of transactions for an accounting ledger.
Totals Table: Stores totals for periods. Serves to accelerate the construction of various reports and selections for periods that are multiples of the storage period of the totals (month).

The totals must be for all registers for all periods that may be requested by users for reports. Based on the specifics of the database operation, the required period for the results may vary. For example, accumulation registers used in trade require some parameters that can vary greatly depending on the specifics of the enterprise. At the same time, accounting requirements for storing totals are much more standardized. In particular, it is a typical situation when in the current year the SALT or SALT report is compiled on an account quarterly, by half-year, or entirely for the past year.

Now let's look at such a common situation. A month has passed, the opening of the next period is required. We go to the menu for managing totals. By default, the Manage Totals - Frequently Used Features form opens. In fact, the form will open in the mode in which we closed it last time, this is either short or full mode. But we are interested in “Outcome Management - Frequently Used Opportunities”. This is very simple form... We need the button "Set the period of the calculated totals" here. If you read the text, you can understand that the system has automatically determined everything and offers to open the correct periods for accumulation registers and accounting registers:

We press the button "Set the period of the calculated totals;", being in full confidence that we have opened the next month, without prejudice to the already existing totals.

As of the moment the button was pressed, totals were calculated for 2016 from January to December. What I would like to see is the calculation of totals for the current moment inclusive, in fact, as suggested in the description for the button. Let's see if this is so. To do this, we need to switch to full capabilities mode.

Now let's look at the screenshot with the result and try to interpret it:

We see that the accumulation registers are not calculated as expected, from 01/01/2016 to 04/30/2017, but for a completely different period: from 04/01/2016 to 04/30/2016. Similarly, the accounting register is calculated not from 01/01/2016 to 05/31/2017, but from 05/01/2016 to 05/31/2017. It is not known whether such a period is suitable for the functioning of trading, but with regard to accounting, there will definitely be a potentially very serious performance problem when building reports for 2016 for the following periods: first quarter, first half of the year, year. Moreover, the performance problem will be directly proportional to the number of transactions carried out by the base per month, since in the absence of calculated total, the data in the request will be obtained from the table of movements for the given period.

Why does it work? It is not known from what moment it started, from what release of the platform, but, apparently, 1C decided to resort to such a solution in terms of optimizing the database size. Typical configurations are quite large, including the structure of the registers contains many dimensions, apparently at some point it was decided that the totals are needed for the current month and plus for the previous year. After all, this is exactly how totals are calculated in "simple" mode, unless the database administrator manually specifies the correct period using the "full" mode.

To correct the situation, we will use the button "Set the period of totals ...".

In the dialog that opens, enter the required start dates separately for the totals of accumulation registers and separately for accounting. My start date of the year was substituted there automatically. Let's press the "OK" button. Let's see what happened:

As you can see, now we got what we originally expected. What conclusion can be drawn from this? Only the one that the opening procedure of the period in managed forms should be carried out using the mode "Totals Management - full possibilities"And nothing else, otherwise you might get a serious performance problem. Accordingly, you need to understand what dates should be set in the field for the start and end dates and control the correctness of these dates. Then the error and the associated performance problem can be avoided!

They have the same task - to perform mathematical or statistical operations on the sample data. But there are also significant differences:

Outcomes add rows to the selection data, while groupings collapse the selection and there are fewer rows.
Outcomes can be calculated for all data in a selection or for individual fields, while, unlike groupings, there may be fields that are neither total nor grouping.
Outcomes can take into account the hierarchy.

First, a little theory. The summary section in 1C queries consists of two sections.

The first one starts with a keyword RESULTS and contains summary fields with an aggregate function applied to them. This section can remain empty, then the result of the query will simply be grouping by the fields of the next section without counting the totals.

There are 6 types of aggregate functions used for groupings:

SUM- sums the values of the grouped column, applies only to numeric values.
THE AVERAGE - calculates the arithmetic mean of the values of the grouped column, applies only to numeric values.
MAXIMUM - can be used for any value type of the grouped column, and the maximum value of all the grouped values is returned. If the values are not numeric, the last is returned when sorted in ascending order.
MINIMUM - can be used for any value type of the grouped column, and it returns the minimum value of all the grouped values. If the values are not numeric, then the first is returned when sorted in ascending order.
NUMBER- returns the number of values in the grouped column, can be used for any types of values. Values like NULL.
QUANTITY DIFFERENT - returns the number of distinct values in the grouped column, can be used for any value type. Values like NULL.

The second section starts with the keyword ON and contains grouping fields in the context of which the result will be calculated according to the total fields. And / or word GENERAL if you need to calculate the totals for the entire sample. The order in which the fields will be located is important here, it also depends on the order in which the totals are calculated in the query result.

If the grouping field contains data that has a hierarchical structure (hierarchical directory), then you can calculate the totals along the entire hierarchical chain. For this it is used keyword HIERARCHY after the name of the grouping field. If the totals need to be calculated along the entire hierarchical chain, except for the grouping field itself, then the keyword is used ONLY HIERARCHY.

Consider as an example a table of goods deliveries.

Task 1. Counting the totals for several fields.

You need to find out how many goods have been delivered in total and how many tables and chairs.

Inquiry. Text = "SELECT
Delivery. Product AS Product,

FROM
Delivery AS Delivery
RESULTS
SUM (Quantity)
ON
GENERAL,
Product";

As a result, we get the following table with totals (the totals rows are highlighted in yellow).

Task 2. Grouping records by field.

It is necessary to group the table records by type of product.

Inquiry. Text = "SELECT
Delivery. Product AS Product,
Supply. Supplier AS Supplier,
Delivery.Quantity AS Quantity,
FROM
Delivery AS Delivery
RESULTS

ON
Product" ;

Task 3. Counting the totals for the field, taking into account the hierarchy.

Inquiry. Text = "SELECT
Delivery. Product AS Product,
Supply. Supplier AS Supplier,
Delivery.Quantity AS Quantity,
FROM
Delivery AS Delivery
RESULTS
SUM (Quantity)
ON
Supplier HIERARCHY ";

As a result, we get the following table.

Task 4. Calculation of totals only by hierarchy.

Inquiry. Text = "SELECT
Delivery. Product AS Product,
Supply. Supplier AS Supplier,
Delivery.Quantity AS Quantity,
FROM
Delivery AS Delivery
RESULTS
SUM (Quantity)
ON
Supplier ONLY HIERARCHY ";

As a result, we get the following table.

How to bypass query result with totals

The result of a query with totals can be bypassed in several ways:

As a normal request. In this case, grouping and detailed records will be displayed sequentially. An example of such a workaround is given in the article.
Separately bypass groupings and detail entries.
As an example, we will take the query from problem 1. But we will not take into account the overall results.
GroupingsProduct = Request. Execute (). Select (QueryResult Bypass. ByGroups, "Product");
While GroupingProduct. Next () Loop
// here we process the grouping rows of the query result
Detailed Entries = Groupings Item. Select() ;
While Detailed Records. Next () Loop
// here we process the lines of detailed records belonging to the groupings
End of Cycle;
End of Cycle;

We continue to study programming in the 1C Enterprise system. Today we'll talk about how to calculate column totals in the tabular section. We want the total to be calculated automatically.

Automatic calculation of totals by column in the 1C tabular section

And so let's get over it, start 1C in the configurator mode. Next, go to the document created earlier. Open it up and go to the form tab and open it.

In the form window, you need to double-click on the tabular section on the right, the table properties window should appear. In it we look for the Basement item and put a tick.

After that, a basement should appear on the form in the tabular section below.

Now you don't need the total to be calculated in the Amount column. To do this, on the Elements tab, look for an item with Name MaterialsAmount and double-click on it. On the right, the field properties appear in them, we are looking for the item
Footer Text and enter Total. And in the Path to data base point, click on three dots.