Temporary tables 1s 8.3. Performance Impact

When building queries, in the 1c language it is possible to obtain the result of executing a query using the command: "Place<ИмяВременнойТаблицы>", Where<ИмяВременнойТаблицы>- the name of the “temporary table”, which is a property of the “Query” object and is an object of the TemporaryTableManager type, which describes the namespace of temporary tables and is responsible for their creation and destruction in the database. This mechanism is used to obtain intermediate query data for further use in other queries, which makes it more convenient to debug them, optimize the code, and make queries more readable.

Let's say you have a query with several temporary tables and you need to debug the contents of each of them. Typically, such tasks are solved using the query console, but if this is not possible (for example, lists of values, tables, etc. are passed to the query), use the method below.

We add the following function to our module (server function, if this is a managed application):

Function VT Data(Query, VT Name) Table Data = New Query; DataTables. Text = "Select * | From " + NameVT+ " |" ; ReturnTableData. Execute ( ) . Unload() ; EndFunction

Let's call it after the request text, for example:

Request = New Request; Request. Text = "SELECT |TZItotal.Name |PLACE Final |FROM |&TZItotal AS TZItotal |; |/////////////////////////////// //////////////////////// |SELECT |* |PLACE No. |FROM |Directory.Nomenclature AS Nomenclature |; //////////////////////////////////////////////// // |SELECT |* |From |Final AS Total | LEFT JOIN Nom AS Nom | BY Total.Name = Nom.Name"; Request. TemporaryTableManager = new TemporalTableManager; Request. execute ( ) ; Table = DataVT(Query, "Final" ) ;

Now the Table variable contains the temporary table Total, which can be viewed in debugging or output to a spreadsheet document.

Lifetime

When a query is executed, the platform destroys all temporary tables that were created as part of its execution. That is, the lifetime of temporary tables created within one request from the 1C:Enterprise platform begins from the moment the table is created in the request and ends when it is executed.

In this case, it is possible to destroy a temporary table programmatically by executing the "DESTROY" instruction in one of the query packages<ИмяВременнойТаблицы>". Then the platform executes the SQL command "TRUNCATE" to destroy the temporary table.

When using a temporary table manager, if the tables have not been explicitly destroyed by the developer (query statement "DESTROY", resetting the temporary table manager to "Undefined" or using the "Close()" method), then the platform destroys them independently after the context has been destroyed, within which they were formed. In other words, the object will be destroyed when the procedure or function in which it was created ends.

Performance Impact

Using temporary tables can significantly reduce the load on the SQL server by reusing a previously generated sample from the table in other queries. Of course, creating and storing temporary tables takes additional resources, but compared to wasting resources on repeated queries to tables, this option is more optimal.

Creating temporary tables based on the values ​​table can have a negative impact on performance, since transferring the value table and populating the temporary table on the SQL server based on it can take a long time and create additional load on the system. This feature should only be used if future queries intensively use data from the values ​​table. Otherwise, a more optimal step would be to process this table programmatically after executing the database query. The exact answer depends on the specific problem.

Conclusion

Support for temporary tables by the 1C:Enterprise platform allows you to write more optimal queries to the database, while the query text itself is also simplified and becomes more readable.

The most important argument in favor of using temporary tables is that their use allows the SQL server to build more optimal query plans.

Requests are designed to extract and process information from the database to provide it to the user in the required form. Processing here means grouping fields, sorting rows, calculating totals, etc. You cannot change data using queries in 1C!

The request is executed as per the given instructions − request text. The request text is compiled in accordance with the syntax and rules query language. The 1C:Enterprise 8 query language is based on the standard SQL, but has some differences and extensions.

Scheme of working with a request

The general scheme of working with a request consists of several successive stages:

1. Creating a Request object and setting the request text;
2. Setting request parameters;
3. Executing a request and getting the result;
4. Bypassing the request result and processing the received data.

1. Object Request has the property Text, to which you need to assign the request text.

// Option 1
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM

|WHERE
;

// Option 2
Request = New Request("CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency");

2. Setting parameter values ​​is carried out using the method SetParameter(< Имя>, < Значение>) . Parameters in the request text are indicated by the symbol “ & " and are usually used in selection conditions (WHERE section) and in virtual table parameters.

Request);

3. After assigning the text and setting the parameters, the request must be executed and the execution result obtained. Execution is performed by the Execute() method, which returns an object Query Result. From the query result you can:

• get a selection using the Select method (< ТипОбхода>, < Группировки>, < ГруппировкиДляЗначенийГруппировок>) ;
• upload values ​​to a value table or value tree using the Upload method (< ТипОбхода>) .

// Receive a sample

Sample = Query Result. Choose();

// Get table of values
RequestResult = Request. Run();
Table = Query Result. Unload();

4. You can bypass the query result selection using a loop:

Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

A complete example of working with a request might look like this:

// Stage 1. Creating a request and setting the request text
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency";

// Stage 2. Setting parameters
Request . SetParameter("Currency" , SelectedCurrency);

// Stage 3. Executing the query and getting the sample
RequestResult = Request. Run();
Sample = Query Result. Choose();

// Traversing the selection
Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

Composition of the request text

The request text consists of several sections:

1. Request Description— list of selectable fields and data sources;
2. Merging queries— expressions “UNITE” and “UNITE ALL”;
3. Organizing results— the expression “ORDER BY...”;
4. Auto-order— the expression “AUTO ORDERING”;
5. Description of results- the expression “RESULTS ... BY …”.

Only the first section is mandatory.

Temporary tables and batch queries

1C query language supports the use temporary tables— tables obtained as a result of executing a query and stored on a temporary basis.

You can often encounter a situation where you need to use not database tables as the source of a query, but the result of executing another query. This problem can be solved using nested queries or temporary tables. The use of temporary tables allows you to simplify the text of a complex query by dividing it into its component parts, and also, in some cases, speed up query execution and reduce the number of locks. To work with temporary tables, use the object TimeTable Manager. A temporary table is created using the PLACE keyword followed by the name of the temporary table.

ManagerVT = New TemporaryTablesManager;
Request = New Request;
Request . ManagerTemporaryTables = ManagerVT;

Request . Text =
"CHOOSE
| Currencies.Code,
| Currencies.Name
|Place in Currency
|FROM
| Directory.Currencies AS Currencies";

RequestResult = Request. Execute();

To use the VTVcurrency temporary table in other queries, you need to assign a common temporary table manager to these queries—VT Manager.

Batch request is a request that contains several requests separated by the “;” character. When executing a batch query, all queries included in it are executed sequentially, and the results of all temporary tables are available to all subsequent queries. Explicitly assigning a temporary table manager to batch queries is not necessary. If a temporary table manager is not assigned, then all temporary tables will be deleted immediately after the query is executed.

For batch queries, the ExecuteBatch() method is available, which executes all queries and returns an array of results. Temporary tables in a batch query will be represented by a table with one row and one column “Count”, which stores the number of records. To debug batch requests, you can use the method Execute Batch WITH INTERMEDIATE DATA() : It returns the actual contents of temporary tables, not the number of records.

// Example of working with a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Currencies.Name
|FROM
| Directory.Currencies AS Currencies
|;
|SELECT
| Nomenclature.Name
|FROM
| Directory. Nomenclature AS Nomenclature";

BatchResult = Request. ExecuteBatch();

TZCurrencies =PacketResult[ 0 ]. Unload();
TZNomenclature = Package Result[ 1 ]. Unload();

// An example of using temporary tables in a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Products. Link HOW TO Product
|PLACE WTT Products
|FROM
| Directory.Nomenclature HOW Products
|WHERE
| Products.Manufacturer = &Manufacturer
|;
|SELECT
| VTTProducts.Product,
| Vocational school. Quantity,
| Vocational school.Price,
| Vocational school.Link AS DocumentReceipts
|FROM
| VT Products AS VT Products
| LEFT CONNECTION Document. Receipt of Goods and Services. Goods AS PTU
| Software VTProducts.Product = PTU.Nomenclature";

Request . SetParameter( "Manufacturer", Manufacturer);

RequestResult = Request. Run();
Sample = Query Result. Choose();

Bye Sample.Next() Loop

EndCycle;

Virtual tables

Virtual tables- these are tables that are not stored in the database, but are generated by the platform. At their core, these are nested queries against one or more physical tables executed by the platform. Virtual tables receive information only from registers and are mainly intended for solving highly specialized problems.

The following virtual tables exist (possible parameters are indicated in parentheses):

• For information registers:
  • SliceFirst(<Период>, <Условие>) — the earliest records for the specified date;
  • SliceLast(<Период>, <Условие>) — the latest records for the specified date;
• For accumulation registers:
  • Leftovers(<Период>, <Условие>) — balances as of the specified date;
  • Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <Условие>) - Period transactions;
  • RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <Условие>) — balances and turnover for the period;
• For accounting registers:
  • Leftovers(<Период>, <УсловиеСчета>, <Субконто>, <Условие>) — balances as of the specified date in the context of account, dimensions and sub-accounts;
  • Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчета>, <Субконто>, <Условие>, <УсловиеКорСчета>, <КорСубконто>) — turnover for the period in the context of accounts, measurements, corr. accounts, subconto, cor. subconto;
  • RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <УсловиеСчета>, <Субконто>, <Условие>) — balances and turnover in the context of accounts, measurements and sub-accounts;
  • TurnoverDtKt(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчетаДт>, <СубконтоДт>, <УсловиеСчетаКт>, <СубконтоКт>, <Условие>) — turnover for the period by account Dt, account Kt, Subconto Dt, Subconto Kt;
  • MovementsSubconto(<НачалоПериода>, <КонецПериода>, <Условие>, <Порядок>, <Первые>) — movements together with subconto values;
• For calculation registers:
  • Base(<ИзмеренияОсновногоРегистра>, <ИзмеренияБазовогоРегистра>, <Разрезы>, <Условие>) — basic data of the calculation register;
  • DataGraphics(<Условие>)—graph data;
  • ActualActionPeriod(<Условие>) is the actual period of validity.

When working with virtual tables, you should apply selections in the parameters of virtual tables, and not in the WHERE condition. The query execution time greatly depends on this.

Query constructor

To speed up the input of query texts, the platform has special tools: Query constructor And Query constructor with result processing. To call constructors, you need to right-click and select the required item:

Constructors can also be called from the main menu Text.

Using the query builder, the programmer can interactively construct the query text. To do this, select the necessary tables and fields with the mouse, establish relationships, groupings, totals, etc. This approach saves time and eliminates possible errors. As a result of its work, the query constructor generates the query text.

The query constructor with result processing, in addition to generating the query text, creates a ready-made code fragment for receiving and processing data.

RequestSchema object

The platform allows you to programmatically create and edit the request text using the object Request Schema. An object has a single property Batch of Requests, in which the object stores the properties of all queries currently being edited. The RequestSchema object supports the following methods:

• SetQueryText(< Текст>) — fills the Request Packet property based on the transmitted request text;
• GetQueryText() - returns the request text generated based on the Request Package property;

FindParameters() - returns the request parameters.

Let's look at an example of working with the RequestSchema object. To programmatically generate the request text

SORT BY
Currencies.Code

The embedded language code might look like this:

RequestScheme = New RequestScheme;
Package 1 = RequestScheme. RequestBatch[ 0 ];
Operator1 = Package1. Operators[ 0 ];
// adding source
RegisterTable = Operator1. Sources. Add( "Directory.Currencies", "Currencies" );
// adding fields
FieldLink = Operator1. SelectableFields. Add("Currencies.Link" , 0 );
FieldCode = Operator1. SelectableFields. Add("Currencies.Code", 1);
// specifying field aliases
Package 1 . Columns[ 0 ]. Alias ​​= "Currency" ;
Package 1 . Columns[ 1 ]. Alias ​​= "Code" ;
// adding a condition
Operator1 . Selection. Add( "NOT FlagDeletion");
// add ordering
Package 1 . Order. Add(FieldCode);
RequestText = RequestScheme. GetQueryText();

Hi all! Or rather, to those who still sometimes look at this blog :)

After quite a long absence due to full-time work, I finally decided to write another post.

I recently learned that some people do not know that the 1C 8.3 platform has a built-in tool for debugging temporary tables. It appeared relatively recently, in one of the releases of 1C 8.3.8 - "To get data"() applied to To the Temporary Table Manager.

Meanwhile, this tool greatly facilitates the ability to study problems, for example, when analyzing typical mechanisms.

Everything is quite simple.

1. Get temporary query tables

When using debugging, we first calculate the list of temporary tables, Query.TemporaryTableManager.Tables. This way we can get the list of tables generated by the query being executed:

2. Get a temporary table for debugging

Then we access the temporary table we need for debugging by adding Get(<Индекс таблицы>)

3. We receive the data directly

Using the GetData() method allows you to get the Query Result collection directly for the selected temporary table.

In the case of directly debugging a table with index 0 (as you know, indexes and numbering in 1C start from zero), data is obtained by calculating the following line:

Query.TemporaryTableManager.Tables.Get(0).GetData().Unload()

Debugging this way will take less time than using different “crutches” 😀

That's all, good developments to you and happy holidays!

PS. And for those who support payroll calculation at an enterprise (not only programmers), I remind you that a separate resource Pro-Zup.info has been allocated for this topic.

If you have questions on this topic, are interested in expanding the capabilities of the standard program, or have suggestions for troubleshooting, welcome to the resource https://pro-zup.info/

The query mechanism, which emerged in version 7 of the 1C program, became increasingly widespread and ever-growing in popularity with the release of the first versions of the 8th platform. The advent of managed forms and data composition systems has significantly increased the scope of this tool. However, many novice programmers find it quite difficult to master it.

Using an object called “Temporary Table Manager” allows you to:

• Significantly simplify the request text;
• Break it down into simpler blocks;
• Increase its readability and structure.

A few words about how it works

In principle, the work of a temporary table manager can be divided into four stages of use:

1. Creation of a manager;
2. Filling it out;
3. Reading data from tables;
4. Destroying the manager and clearing the tables.

Let's talk about each stage in more detail.

Creating a Temporary Table Manager

In order to define this object, you need to execute the code shown in Fig. 1

Here it should be noted that the definition of the temporary table manager is given before the Execute() operator, otherwise the execution of the code is guaranteed to be interrupted by an error, the information window of which is indicated in Fig. 2.

Fig.2

Filling out the manager

In the same Figure 1 there is a line that transfers the selection to a temporary table. It starts with the “Place” operator. The destination is the name of the destination table.

Using the “Query Builder”, this line can be created on the “Advanced” tab Fig. 3.

Fig.3

To do this you need:

1. Set the “Query type” switch to the “Create temporary table” position;
2. Specify the name of the receiver.

If you use a debugger to check the manager's filling sequence, you will find that the data will appear in it only after the Execute() request method is executed.

You can determine whether a manager is full using the Quantity() operator. Example line: MVT.Tables.Quantity().

Reading manager tables

The next step is to read data from existing tables in another query. There is one problem here: the new query that is created does not know about the existence of the populated table, so it will not appear in the “Database” window of the console.

It must be registered and created manually.

To do this, on the “Tables and Fields” tab (Fig. 4), you need to perform a certain sequence of actions:

Fig.4

1. In the “Tables” window menu of this tab, click the “Create temporary table description” button;
2. In the window that opens, you must specify the name of the field and its description (field type), as it is specified in the existing table;
3. The necessary fields or functions with them must be moved to the third window of the tab.

During the entire execution time, temporary tables are stored in the RAM of the user’s computer when working in a file mode or clog up the server’s memory in client-server mode until the function or procedure that called the manager is completely completed. Large volumes of data can significantly reduce the performance of even the weakest hardware.

Removing tables

In general, experienced specialists, in order not to clog up memory too much, recommend deleting temporary tables immediately after using them, if they will not be called anywhere else.

Removing tables from the manager can be done in two main ways:

• By specifying the keyword Destroy directly in the request text;
• Using the Close() method applied directly to the manager.

In the second case, all tables created by various queries will be forcibly destroyed.

The use of the first option for deleting data can be specified explicitly by writing a line like “Destroy TableName” in the query text, or using the “Advanced” tab (Fig. 2) of the “Query Designer” window.

By switching the switch to the “Destruction” position and specifying the name of the object that needs to be deleted, you can complete this action without any problems.

Transfer (TK) to request

One of the most common uses of this manager is passing a table of values ​​as one of the data sources to a query. As you know, direct use of this collection in a request in 1C is not provided.

To do this, the request text must contain a line like “Select * Place MVT from &Tz As Tab”. By passing an existing table of values ​​as the “TZ” parameter to the request, we will receive an object suitable for further processing in other requests.

The only condition that prevents passing a TK as a parameter is the implicitly declared types of its columns. That is, when creating TK columns, you need to fill in the second parameter with a line like “New TypeDescription(“”)).

There are situations when it is necessary to combine several queries in one query, and table joins cannot help with this. The easiest way to show it is with an example.

Suppose in our system the facts of purchase and sale of goods are recorded by the documents Income and Expense, respectively. The counterparty can be either a buyer or a supplier. Debt can be offset by delivery of goods:

To calculate the total debt of a counterparty, you need to add up the sum of all expenses for this counterparty and subtract the sum of all receipts from the same counterparty; the easiest way to do this is using the COMBINE ALL operator:

Request.Text =
"
//calculate the amount we shipped to contractors
|SELECT
| Expense. Counterparty,
|FROM
| Document.Expense AS Expense
|GROUP BY
| Expense.Counterparty
| COMBINE ALL
//calculate the amount of the counterparties
//delivered goods to us
|SELECT
| Parish. Counterparty,
//take the amount with a negative sign,
//that when combined it was deducted from the expense amount
| SUM(-Receipt.Amount)
|FROM
| Document. Arrival AS Arrival
|GROUP BY
| Arrival.Counterparty";

In the first request, we calculate the amount of expenses for each counterparty, in the second - the amount for which each counterparty supplied us with goods. The amount in the second request is taken with a minus sign, so that when collapsing the resulting table, it is subtracted from the amount of shipment to this counterparty. As a result, we get a table like:

It's not exactly what we wanted, but it's close. To achieve the required result, all that remains is to group by counterparty. To do this, the query must be placed in a temporary table (working with temporary tables is discussed in closed part of the course ) and select and group fields from it:

Request = New Request;
Request.Text =
"CHOOSE
| Expense. Counterparty,
| AMOUNT(Expenditure.Amount) AS Debt
|Place VT_Incoming Expense
|FROM
| Document.Expense AS Expense
|GROUP BY
| Expense.Counterparty
| COMBINE ALL
|SELECT
| Parish. Counterparty,
| SUM(-Receipt.Amount)
|FROM
| Document. Arrival AS Arrival
|GROUP BY
| Parish.Counterparty
|;
|////////////////////////////////////////////////////////////////////////////////
|SELECT
| VT_Incoming Expense. Counterparty,
| SUM(VT_IncomeExpenditure.Debt) AS Debt
|FROM
| VT_IncomingConsumption AS VT_IncomingConsumption
|GROUP BY
| VT_Incoming Expense. Counterparty";

Requirements for merging queries

When combining two queries, the number of fields must be the same; if any of the queries lacks fields, then they must be added as constants. Let's look at the example above, let the expense document also have a discount field that reduces the amount of the counterparty's debt, but there are no discounts in the receipt document. How to be in this case? So:

Request = New Request;
Request.Text =
"CHOOSE
| Expense. Counterparty,

|FROM
| Document.Expense AS Expense
|GROUP BY
| Expense.Counterparty
| COMBINE ALL
|SELECT
| Parish. Counterparty,
| SUM(-Receipt.Amount),
//add a null field discount
| 0
|FROM
| Document. Arrival AS Arrival
|GROUP BY
| Arrival.Counterparty";

All that remains is to subtract the discount and group.

The order is also important. The fields will be merged exactly in the order in which they are specified in the SELECT sections of both queries. In relation to the previous example, let's swap the discount and amount fields in the sample of receipts:

Request = New Request;
Request.Text =
"CHOOSE
| Expense. Counterparty,
| AMOUNT(Expenditure.Amount) AS Debt,
| AMOUNT(Expense.Discount) AS Discount
|FROM
| Document.Expense AS Expense
|GROUP BY
| Expense.Counterparty
| COMBINE ALL
|SELECT
| Parish. Counterparty,
//switch places
| 0,
| SUM(-Receipt.Amount)
|FROM
| Document. Arrival AS Arrival
|GROUP BY
| Arrival.Counterparty";