1s 8 batch request. Simple requests. Reasons for suboptimal query performance

Let's analyze how the syntax of the texts of requests for simple example: Held document Consumable containing in the tabular part Goods list of goods for sale and quantity. When conducting such a document, it is necessary to ensure control of negative balances stored in the balance accumulation register Remains of Goods.

The configuration structure is shown in the figure.

(16.22 kilobytes) Number of downloads: 64

Let's form a query to the tabular part of the document and the virtual table Remains accumulation register. We will take into account possible duplicate lines in the document, for this we will group the records.

// Pass through the register

EndCycle;

EndProcedure

Naturally the resulted request is absolutely not optimal. Let's optimize it with the help of nested queries: Let's group the tabular part of the document before connecting to the balance table, pass the list of goods to the parameters of the virtual table as the value of the condition for calculating the balance. As a result, our request will take the following form:

If in a query it would be necessary to obtain data from the remains of different registers, then the filter value, and therefore our second subquery, would be repeated in all parameters of virtual tables, it is natural that the system re-accesses the database for each subquery to obtain data.

Temporary tables

I don’t remember from which release it became possible to use temporary tables in queries. For this, the "Temporary Table Manager" object is used. In fact, the temporary table manager describes the namespace of temporary tables and is responsible for creating and destroying them in the database.

Temporary tables themselves are actually physically created in the database, therefore, they should be treated with caution, since the disk subsystem is currently the slowest part of the technology, and the speed of creating and destroying tables directly depends on it.

Let's rewrite the query to use temporary tables. Let's place the grouped tabular part of the document and the list of products for the filter of virtual tables into temporary tables:

Procedure Handling Posting(Failure, Posting Mode)

MBT = New TemporaryTable Manager;

QueryResult = Query.Execute();
Selection = QueryResult.Select();

While Selection.Next() Loop

//Check for negative balances

// Pass through the register

EndCycle;

EndProcedure

When using temporary tables in the query text, use the statement Post to create a new temporary table, in this case the system does not send the contents of this table to the query result (see note 1 and note 2 in the text above), but the number of records placed in the temporary table, you can not accept this value if you wish.

You can also use the instruction Destroy in this case the temporary table is destroyed, otherwise the temporary tables are destroyed along with the temporary table manager object.

In our main query, I used the names of temporary tables as an indication of the source of data acquisition (they must be assigned a synonym, which we see in the text). You can use temporary tables as a source more than once, which, if used skillfully, will both reduce the query text (improve the readability of complex queries) and increase speed (when using temporary table data in several places in the query).

batch requests

Batch queries logically complement the functionality of temporary tables and provide more options when working with queries.

In a batch query, in fact, you can describe several queries, both related to each other using temporary tables, and not related (it is possible, but it is not clear why?). As a result, you can execute all queries sequentially and receive as a result either an array with the results of each query execution, or the result of the last one. To get an array with query results, use the method ExecutePackage() request object, and to get the result of the last request ExecuteRequest().

In the request text, package requests are separated by the symbol ";" (semicolon). There is only one virtual table namespace per batch query. The use of a temporary table manager is not required, but is possible if you want to transfer temporary tables from one batch query to another.

Let's rewrite the procedure to use batch queries:

Procedure Handling Posting(Failure, Posting Mode)

While Selection.Next() Loop

//Check for negative balances

// Pass through the register

EndCycle;

EndProcedure

In fact, I removed the definition of the query object and the use of the temporary table manager, combined the query texts (pay attention to the separator ";" between the texts). As a result, the query text has become more readable (and when using the query builder, the readability of the query is greatly improved).

After executing the request to the variable ArrayResults we have 3 elements. The first two will contain a number characterizing the number of records placed in temporary tables DocPM And Product List, and the third will contain a selection with fields Nomenclature, Doc_ Quantity and Reg_ Quantity.

into a variable Request Result only a sample will be included.

Well, that's all for batch requests. A very convenient mechanism both in terms of writing queries and in terms of reading complex queries.

The 1C Enterprise platform allows you to execute several queries sequentially at a time. In 1C, this is called a batch of requests. Within one package, each request is separated by a "semicolon".

To achieve staged execution of queries in a batch, as a rule, temporary tables are initially created, then the conditions for their joint use, such as filters, joins, joins, are formed. Thanks to this, the final result is achieved. Temporary tables obtained as a result of any queries in the batch continue to exist until the end of the execution of the package as a whole or until the execution of a query that destroys the temporary tables.

In addition, the use of batch queries and temporary tables greatly improves the readability of the entire piece of this code. Complex queries that also contain nested queries can be very difficult to understand. However, if you break a long complex query into several, and even use temporary tables, then this will not only improve perception, but also in most cases leads to increased performance.

Another important detail in favor of batch requests in 1C is that, unlike we can get the result of each request in a batch separately.

An example of creating a batch of requests in 1C language

To see an example of how to create a batch of queries, we will use the query builder, which we will call for clarity from the query console. Thus, we can immediately see the result of the package execution.

Let's create a simple batch request. I suggest immediately inserting the request text into , and then open it and see how the request package is formed. Add a new request to the console and paste the following text:

Get 267 1C video lessons for free:

Self-supporting.Link,
Self-supporting. Parent,
Self-supporting.Code,
Self-supporting.QuickChoice Code,
Self-supporting. Name,
Self-supporting.View,
Self-supporting. Off-balance sheet,
Self-supporting. Quantitative,
FROM
Chart of Accounts. Self-supporting AS Self-supporting
WHERE
Self-supporting.Link = &Account
;
////////////////////////////////////////////////////////////////////////////////

SELECT
Self-supportingTypesSubconto.LineNumber AS LineNumber,
Self-supporting Subconto types. Subconto type AS Subconto type,
Self-supporting Types of Subconto.Type of Subconto.Description AS Name,
Self-supportingSubconto Types.Subconto Type.ValueType AS ValueType,
Self-supportingTypes of Subconto.Only Turnovers AS OnlyTurnovers,
Self-supportingTypesSubconto.Sum AS Sum
FROM
Chart of Accounts. Self-supporting. Types of Subconto AS Self-supporting
WHERE
Self-supportingTypesSubconto.Reference = &Account
SORT BY
Self-supportingTypesSubconto.LineNumber

For me it looks like this:

Now let's move on to the query builder. Here we will be interested in the "Package Requests" tab:

As you can see, we have a batch of two requests. By double-clicking on any of them, you can proceed to edit it:

Let's press the "OK" button and try to see the result of the batch query.

Set the "Account" parameter. You can select any account from the Chart of Accounts. As you probably already guessed, this batch of requests should get the properties of the account. Click "Run" and see the result:

Methods Execute() and ExecutePackage()

When my query became so complex that it exceeded my understanding, I decided to use batch queries.

But faced with the fact that I know nothing about them. It turned out that everything is very simple. In 5 minutes you will be able to use batch queries. Start reading.

As it turned out, everything is very simple. You just need to write several queries separated by semicolons. The result will be returned in the last request.

Batch requests appeared only in version 8.1.11.67.4.

Here is the text of the request:

SELECT T1.Zn PUT WTBletters FROM (CHOOSE "A" AS ZN COMBINE ALL SELECT "B") AS T1;

SELECT T1.Zn PUT WTCigures FROM (SELECT "1" AS Zn COMBINE ALL SELECT "2") AS T1;

SELECT TB.Zn, TTs.Zn, TB.Zn+TP.Zn FROM VTBletters AS TB, VTSigers AS TP

Batch queries are supported in any regular query console.

The figure shows a sample query execution:

And now a little from experience. Why batch requests are needed.

The fact is that you can put some intermediate result into a temporary table, which may then be needed in several subsequent queries.

Previously, when there were no temporary tables, you would have to duplicate the query text.

Of course, you can do without a batch query by sequentially executing several queries and manipulating nested tables. But with batch requests it is more convenient. You just write a query and don't think about placing temporary tables. Everything happens by itself.

In addition, if a data composition system (DCS) is used, it intelligently selects the required fields and minimizes the entire batch of queries.

If requests had a method Request.Execute() now there is a method Request.ExecutePackage(), which returns all tables from the batch, as an array.

The announcement of batch requests on the 1c website is here: http://v8.1c.ru/overview/release_8_1_11/#Functional

History from life

Let me explain what prompted me to batch requests.

So, imagine there is a document, it has tabular part. In a collumn " Mistake» sign, whether there is an error when filling out the document. In a collumn " TextErrors» there can be one or more sentences with error texts. The types of errors contained in sentences are known in advance.

So, we enter a list of all errors in the table Error Codes- it contains the error code and the search substring.

We get one, two or more errors for each line. Because There can be multiple errors on one line.

But the error may not be recognized, i.e. flag " Mistake” stands, but the error text did not give us an error code.

We make a left join, where the error code is NULL, we give the error code " Other errors» .

But the problem was that there were about 200 error codes, so the left connection worked for a very long time. I had to replace it with an internal connection that flew. But at the same time, lines were lost for which the error was not found. I still could not figure out how to pull these lines into the result.

The query was written for the linking system, i.e. no tables of values or temporary tables can be used in principle. This is where batch requests come in handy.

I simply connected all the lines with errors again with all the lines for which errors were found, and still added the type of error "Other errors".

The article describes the mechanism of batch requests implemented in the 1C:Enterprise platform. After reading the article, you will learn:

What are batch requests and what are they for?
How to create a query package using the query builder?
How to return an array of results for each request from a batch?

Applicability

The material is relevant for current versions of the 1C:Enterprise platform, edition 8.3

Purpose of a request package

The platform allows you to work with batches of requests. We get the opportunity to execute several requests "at a time". In a batch request, the request texts are separated by the symbol ";" (semicolon).

Queries are executed sequentially, while temporary tables that were created during the execution of any query will exist until the end of the execution of the entire query package or until the execution of the query in the package that destroys this temporary table. Important difference from a nested query is that the results of each query of the package are available separately.

Query batches allow you to achieve staged query execution. To do this, in a batch query, first temporary tables are created, then they are shared (join, union, filters) to obtain the final result of the query. It is also important to note that using temporary tables in batch queries improves the readability of the query body.

Large queries with nested queries wrapped inside each other are often quite difficult to understand. But if you rewrite such a query using temporary tables, the visibility of the query can increase quite a lot. Using a query package with temporary tables can also improve query performance.

There are query performance optimization techniques based on replacing nested queries with temporary tables.

A temporary table can be useful when you need to use the same data multiple times in a large query, such as joining or joining with other tables. When using nested queries, such data would have to be obtained several times using the same nested queries, which, of course, would affect both the readability of the text and performance.

Create a query package using the constructor

Separate requests included in the package are separated in the text by the symbol ";" (semicolon). To avoid splitting the query body manually, you can use the Query Builder for this.
The query builder has a separate tab for query packages. Requests can be added to the package using the corresponding button on the command bar, as well as moved up or down.

Visual display of individual queries - tabs on the right side of the constructor, with which you can proceed to editing the text of a particular query. Names are displayed on these tabs for temporary tables, for requests for data selection – “Package 2 Request”, etc., for deletion – “– NameVT”.

Also in the list of database tables appear temporary tables created within this package. However, this does not mean that temporary tables are stored in the database along with all other infobase tables.

Executing Package Requests

If the object Inquiry executing the batch query has a temporary table manager installed, temporary tables that were not destroyed as part of the batch query will be saved in the installed manager.

In the text of a batch query, it is possible to use and destroy temporary tables that existed in the installed temporary table manager at the time the batch was launched for execution.

Besides method Execute(), which sequentially executes all requests of the package and returns the result of the last request in the package, there is another method in the platform - ExecutePackage().

This method executes all queries in sequence and returns an array of results for each query in the package, in the order in which the queries appear in the body of the package.

The result of executing a request to destroy a temporary table is the value Undefined, which is also placed in the result array.

This article is intended for readers who are familiar with the SQL language.

The query language in 1C, which has been used since version 8, has now become useful tool for working with databases, which allows you to read from them, but not write. Syntactically, the query language is very similar to the SQL language, but in Russian.

Below is a table of correspondence between the main operators of the query language and SQL:

1C query language operators	SQL statement


VARIOUS



COMPOUND
GROUP BY
UNITE
SORT BY

And it's far from full list. More complete background information on available operators query language can be obtained in the query builder, which will be discussed below.

Execution of a request 1C from program code is carried out using the built-in language object "Request". An example of writing a database query using the built-in programming language:

Request = New Request; Query.Text = "CHOOSE | Synonym.Reference AS Reference |FROM | Catalog.Directory1 AS Synonym"; Selection = Query.Execute().Select(); While Selection.Next() Loop // Insert selection processing SelectionDetailRecords End of Loop;

The "Execute" method executes the query, the "Select" method returns a value of the "SelectionFromQueryResult" type. You can also use the Unload method, which returns a table of values.

The query parameters are stored in the "Parameters" property (in this case, it is a structure, so all the methods of the structure are applicable here - insert, delete, etc.).

An example of setting the "Query.Parameters.Insert" parameter ("Directory", ReferenceReference). In the query, you can access the parameters through the ampersand "&Reference". Below is an example request using parameters:

Request = New Request; Query.Text = "SELECT | Users.Reference AS Link, | Users.Parent AS Parent, | Users.Name AS Name |FROM | Directory.Users AS Users |WHERE | Users.Reference = &Directory"; Query.Parameters.Insert("Catalog", DirectoryReference); Selection = Query.Execute().Select(); While Selection.Next() Loop // Insert selection processing SelectionDetailRecords End of Loop;

Recall that the query language is intended only for reading data from the database, so there are no analogues of such queries in it. SQL statements like INS ERT and UPDATE. Data can only be modified via object model built-in programming language 1C. Also, in the 1C query language, there are operators that have no analogues in SQL, for example:

IN THE HIERARCHY
PUT
INDEX BY

IN THE HIERARCHY– allows you to select all elements of the hierarchical dictionary that are included in the hierarchy of the passed link. Sample request using IN THE HIERARCHY:

SELECT Goods.Link, Goods.Article FROM Directory.Goods AS Goods WHERE Goods.Link IN HIERARCHY(&Citrus)"

In this case, all subordinate elements of the Citrus nomenclature catalog will be returned to the result, no matter how many hierarchy levels this catalog has.

Also, for example, the task is to find a product with the name "Pen". The product must be included in the hierarchy of “Stationery. Goods”, that is, we do not need to look for a doorknob. The structure of the nomenclature in this case is as follows:

office

accessories

|_ Door handles |_ Simple door handle |_ Luxury door handle

We write a query like this:

SELECT Goods.Link, Goods.Article FROM Directory.Goods AS Goods WHERE Goods.Name Similar to "Pen%" And Goods.Link IN HIERARCHY(&Office)"

When using the structure IN THE HIERARCHY keep in mind that if you pass an empty reference to the “Office” parameter, the request execution will slow down, since the platform will check each element for belonging to the root.

PUT– This statement places the result in a temporary table. Request example:

SELECT Users.Reference AS Reference, Users.Parent AS Parent, Users.Name AS Name PUT SelectedUsers FROM Directory.Users AS Users WHERE Users.Reference = &Directory; SELECT SelectedUsers.Reference AS Link, SelectedUsers.Parent AS Parent, SelectedUsers.Name AS Name FROM SelectedUsers AS SelectedUsers

This SQL query will be executed by several queries:

Creating a temporary table (the platform can "reuse" previously created temporary tables, so the creation does not always occur);
Putting data in a temporary table;
Execution of the main query, namely SEL ECT from this temporary table;
Destruction/clearing of the temporary table.

A temporary table can be explicitly destroyed using the construct DESTROY, or implicitly - when closing the temporary table manager.

The "Request" object of the built-in programming language has the "TemporaryTable Manager" property, which is designed to work with temporary tables. Code example:

MBT = NewTempTableManager(); Request = New Request; Query.TemporaryTable Manager = MBT;

After a query is executed, the MBT variable can be used a second time in another query, which is undoubtedly another plus of using temporary tables. In this case, the temporary table will be deleted from the database when the “Close” method is called…

MVT.Close();

...or when clearing a variable from memory, that is, when executing the method in which the variable was declared. Temporary tables increase the load on the disk subsystem, so you should not create too many temporary subsystems (in a loop, for example), or large subsystems.

INDEX BY– this operator is used in conjunction with the operator PUT. When creating a temporary table, this operator can index the created table, which significantly speeds up work with it (but only if the index matches your query).

Free expert advice

Thank you for your feedback!

A 1C specialist will contact you within 15 minutes.

Features of some query language operators

FOR CHANGE – given operator is designed to lock a specific query table (or all tables that participate in the query). Locking is done by placing a U lock on the table. In SQL, this is implemented via hint UPDLOCK. This construction is necessary to prevent deadlocks. Example of a query with a construct FOR CHANGE:

SELECT Users.Reference AS Link, Users.Parent AS Parent, Users.Name AS Name FROM Directory.Users AS Users

IN this example U The lock will be placed on the "Users" table. If you do not specify a table to lock, it will be imposed on all tables participating in the query. It is important to note that this construction only works in configurations in which the auto mode blocking management.

COMPOUND- query supports connections LEFT/RIGHT, FULL, INNER, which corresponds to joins in SQL - LEFT/RIGHT JOIN, OUTER JOIN, INNER JOIN.

However, when using the query builder, you won't be able to do RIGHT JOIN. The constructor will simply swap the tables, but the operator will always be left. For this reason, in 1C you will never find the use of a right join.

Syntactically, the connection looks like this:

SELECT Table1.Reference AS Reference FROM Reference.Reference1 AS Table1 LEFT JOIN Reference.Reference2 AS Table2 BY Table1.Attributes = Table2.Attributes

The 1C query language does not have an operator for joining the Cartesian product (CROSS JOIN). However, the absence of an operator does not mean that the query language does not support such a join. You can join tables if necessary in the following way:

SELECT Table1.Reference AS Reference FROM Reference.Reference1 AS Table1 LEFT JOIN Reference.Reference2 AS Table2 ON TRUE

As you can see from the example, the connection key is set ON TRUE, that is, each row of one table corresponds to a row of another. The join type (LEFT, RIGHT, FULL, INNER) doesn't matter if you have rows in both tables, but if either table has no rows (letting the table go) the result will be different. For example, when using INTERNAL connection result will be empty. Using LEFT/RIGHT the join will or won't result in data depending on whether we're joining a table with data or not. Using COMPLETE connection data will always be (naturally, only one table, since the other is empty), the choice of connection type depends on the specific application task.

A small visual clue how they work different types connections:

LIKE. Unlike the similar operator SQL language– LIKE, template for LIKE can be specified using only some special characters:

% (percentage): a sequence containing any number of arbitrary characters;
_ (underscore): one arbitrary character;
/ - the next character should be interpreted as a regular character.

RESULTS ON the SQL equivalent is the ROLLUP operator. Operator usage example RESULTS:

SELECT Goods.Price AS Price, Goods.Item AS Goods FROM Directory.Nomenclature AS Goods RESULTS AVERAGE(Price) BY Goods

The result will be like this:


Bed	9833,333



Iron





Pen

That is, an additional line is added to the result, containing the value of the field by which the grouping is performed and the value of the aggregating function.

Working with batch requests

1C allows you to work with batches of requests. In a batch request, the request texts are separated by a semicolon (;). Execution of a batch request 1C is carried out sequentially. Batch request text example:

SELECT Users.Link AS Link, Users.Parent AS Parent, Users.Name AS Name FROM Directory.Users AS Users;
SELECT Work Schedule.User AS User, Work Schedule.Date AS Date, Work Schedule. Work Hours AS Work Hours FROM Data Register. Work Schedule AS Work Schedule

To get the result of all requests included in the package, you must use the method of the request object "ExecutePackage", instead of "Execute". This method executes all requests sequentially. The result of the query is an array of results for each query from the package, and the order in the array is the same as the order of the queries in the body of the package.

Considering the query language, it is worth mentioning such a feature as virtual tables. Virtual tables are not present in the database; this is a kind of wrapper that is executed on the DBMS side as a query using subqueries. An example of a 1C query using virtual tables:

SELECT Register of LiabilitiesTurnovers.Liabilities AS Obligation FROM Accumulation Register.Register of Obligations.Turnovers() AS Register of ObligationsTurnovers

Such a query to the DBMS will look like this:

SEL ECT T1.Fld25931RRef FR OM (SELECT T2._Fld25931RRef AS Fld25931RRef, CAST(SUM(T2._Fld25936) AS NUMERIC(38, 8)) AS Fld25936Turnover_, CAST(SUM(T2._Fld25937) AS NUMERIC(38, 8)) AS Fld25937Turnover_ FR OM dbo._AccumRgTn25938 T2 WH ERE ((T2._Fld949 = @P1)) AND ((T2._Fld25936 @P2 OR T2._Fld25937 @P3)) GROUP BY T2._Fld25931RRef HAVING (CAST(SUM(T2._Fld25936) ) AS NUMERIC(38, 8))) 0.0 OR (CAST(SUM(T2._Fld25937) AS NUMERIC(38, 8))) 0.0) T1>>>>

It can be seen that it does not look like SQL, since there is a subquery, a grouping. Virtual tables, by and large, are "syntactic sugar", that is, created, in general, for the convenience of developing queries, so that queries are more compact and more readable.

Only registers have virtual tables, but which virtual tables are available for a register can be seen in the query builder.

When using virtual tables, you must always give a selection condition. Otherwise, performance issues may occur.

In the request body it looks like this:

Accumulation Register.RegisterLiabilities.Turnovers(, Operation = &Operation) AS RegisterObligationsTurnovers

For the convenience of writing queries, that is, creating query texts, there is a constructor in 1C that can be called through the context menu (right-click):

In the Query Builder, you can see a complete list of supported query language functions and operators.

The Query Builder is a very flexible visual tool for creating queries of any complexity. It is only available in the configurator mode. In Enterprise mode, there is a so-called "Query Console" - this is external processing supplied on the ITS disk. For a managed application, the request console can be downloaded from its.1c.ru website.

The description of work in the query builder is beyond the scope of this article, so it will not be considered in detail.

Reasons for suboptimal query performance

Below is a list of the main reasons (but not all) that lead to slow query execution.

Using join with subqueries

It is not recommended to join with subqueries, subqueries should be replaced with temporary tables. Joining subqueries can lead to a significant loss in performance, while the execution of a query on different DBMS can vary significantly in speed. The execution speed of such queries is also sensitive to the statistics in the DBMS. The reason for this behavior is that the DBMS optimizer cannot always correctly determine the optimal query execution plan, since the optimizer does not know anything about how many rows the subquery will return after its execution.

Using Virtual Tables in Query Joins

Virtual tables at the DBMS level are executed as subqueries, so the reasons are the same as in the first paragraph.

Using conditions in a query that do not match existing indexes

If in the query conditions (in the operator WHERE or in virtual table conditions) uses fields that are not all included in the index, given request will be done with using SQL table scan or index scan constructs (in whole or in part). This will affect not only the query execution time, but also an excessive S lock will be imposed on extra rows, which in turn can lead to lock escalation, that is, the entire table will be locked.

Using OR in query conditions

Usage logical operator OR in construction WHERE may also result in a table scan. This is due to the fact that the DBMS cannot use the index correctly. Instead of OR construction can be applied COMBINE ALL.

Getting data through a dot for fields of a composite type

It is not recommended to receive values through a dot (in the construction CHOOSE WHERE), because if the object attribute is a composite type, the join will occur with each table included in this composite type. As a result, the query to the DBMS will be significantly more complicated, which may prevent the optimizer from choosing the correct query execution plan.