Request package 1s 8.3. Batch requests for dummies. Methods Run() and RunBatch()

08.09.2024

1C GOODWILL company blog

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

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

In addition, the use of batch queries and temporary tables significantly increases the readability of the entire segment 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, this will not only improve perception, but in most cases lead to increased performance.

Another important detail in favor of batch queries in 1C is that, unlike nested queries, we can separately obtain the result of each query in the batch.

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

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

Let's create a simple batch request. I suggest immediately pasting the request text into the console, and then opening the constructor and seeing how the request package is formed. Add a new request to the console and paste the following text:

Self-supporting.Parent,

Self-supporting. Code,

Self-supporting. Quick Selection Code,

Self-supporting. Name,

Self-supporting.Type,

Self-supporting. Off-balance sheet,

Self-supporting. Quantitative,

Chart of Accounts. Self-supporting AS Self-supporting

////////////////////////////////////////////////////////////////////////////////

Self-supporting TypesSubconto.Line Number AS Line Number,

Self-supportingTypes of Subconto.ViewSubconto AS TypeSubconto,

Self-supportingTypesSubconto.TypeSubconto.Name AS Name,

Self-supportingTypesSubconto.TypeSubconto.ValueType ASValueType,

Self-supportingTypesSubconto.OnlyTurnover AS OnlyTurnover,

Self-supportingTypesSubconto.Summary AS Summative

Chart of Accounts. Self-supporting. Types of Sub-account AS Self-accounting. Types of Sub-conto

ORDER BY

Self-supporting TypesSubconto.NumberLines

For me it looks like this:

Now let's go to the query designer. Here we will be interested in the “Request Package” tab:

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

Click the “Ok” button and try to see the result of the batch request.

Let's set the "Account" parameter. You can select any account from the chart of accounts. As you probably already guessed, this request package must receive account properties. Click “Run” and see the result:

Methods Run() and RunBatch()

In addition to the Execute() method, which will one by one execute all requests in a batch and return the result of the last request, in 1C there is a ExecuteBatch() method. It returns an array of samples of each request in the batch. In the example above, this is exactly the method.

ArrayResults = Query.ExecuteBatch();

Selection1 = ArrayResults.Select();

If Select1.Next() Then

//Actions with selection 1

endIf;

SelectionViewsSubconto = ArrayResults.Select();

The post Working with batch requests in 1C 8.3 and 8.2 first appeared on the 1C GOODWILL company blog.

The query designer in 1C 8.3 and 8.2 is a powerful development tool. It allows you to compose a request text using a special visual environment. Thus, in order to create a 1C request, it is not necessary to know the built-in query language; it is enough to navigate the simple and intuitive interface of the designer.

The query builder is a set of tabs, each of which is responsible for its own part of the query. So filling out the tab Tables and fields We select tables from which the 1C query will receive the data and fields of these tables necessary to solve a specific problem. Filling into the masonry Terms we impose conditions on the selected tables in order to select from them only the data we need, and so on.

Description of the query designer on the official 1C 8 website: v8.1c.ru

Tables and fields; ; ; ; ; ; Nested queries (in development).

In order to call the 1s 8 query designer in the program code you need to:

Create a new request

Request = New Request;

Set an empty request text line

Request.Text = "";

Place the mouse cursor between the quotes and press the right mouse button. In the context menu that opens, select the item Query constructor and answer Yes to the question about creating a new request. If the request text has already been written down, then you need to click anywhere inside it and call the constructor ;

Let's look at all the main tabs of the query builder using small examples of increasing complexity. This approach will allow a novice 1C programmer to more effectively study the constructor and all its capabilities. For examples we will use the configuration Accounting 3.0.

Lesson #1. The query builder is the simplest use case.

Task: write a request to the nomenclature directory, select the entire nomenclature of the directory.

New tabs: Tables and fields.

New mechanisms: viewing and editing the request text using the “Request” button.

To start creating a request, let's create a new request and call the constructor (as written a few paragraphs above). After this, the designer window will open on the tab Tables and fields.

Theoretical part of lesson No. 1

Tab Tables and fields consists of three sections:

Database. This section presents all the database tables that can be used to build a query;

Tables. In this section, the tables necessary for this query are selected. To then move them from the section database need to:

Or double-click on the table;
Or use the “>” or “>>” buttons.

Above section Tables There are a number of buttons. Most of them will be discussed in more detail in the following lessons. For now I will give only brief explanations.

Create a subquery(red line). Designed to create a new subquery;
Create a temporary table description(yellow line). Allows you to specify the name of a temporary table that is located outside of this query; it can also be used to pass a table of values to the query;
Change current element(green line). Allows you to jump to the selected subquery, temporary table, or temporary table description;
Remove current item(blue line). Removes the selected table from the selected tables;
Replace table(blue line). Opens the dialog for replacing the selected table. Useful if you have selected the wrong register virtual table, as positioning occurs on the currently selected table in the list.
Virtual Table Options(purple line). Opens virtual register table parameters.

Fields. This section selects table fields from the previous section. These fields will be the columns of the table or selection obtained as a result of the query. They are needed primarily in order to obtain from the selected tables only the information necessary in a particular case. In order to move them from the section Tables needed:

Or double-click on the field;
Or use the “>” or “>>” buttons;
You can also add a new field yourself, using an arbitrary expression from the fields of selected tables and query language functions.

Above section Fields There are a number of buttons. Creating fields using arbitrary expressions will be discussed in more detail in the following lessons. For now I will give only brief explanations.

Add(green line). Designed to add a new field using the free expression editor;
Change current element(red line). Allows you to change the selected field using the editor;
Delete current(blue line). Removes the selected field from the list.

Practical part of lesson No. 1

We have dealt with the theory necessary to complete the task provided in this lesson. Let me remind you how it sounds: write a request to the nomenclature directory, select the entire nomenclature of the directory.

Let's start creating a request for items:

Let's create a new request and open the constructor using the method specified at the beginning of the lesson;
In the section Database, let's open a thread Directories and we'll find a guide there Nomenclature;
Select it and use the “>” button to move it to the section Tables;
In the section Tables open the nomenclature directory using the “+” icon;
In the list of fields that opens, find the field Link and move it to the section Fields using the ">" button
The item request is ready, click the “OK” button at the bottom of the designer window.

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

In a batch query, you can actually describe several queries, both interconnected by the use of temporary tables, and unrelated (it’s possible, but it’s not clear why?). As a result, you can execute all requests sequentially and receive as a result either an array with the results of each request, or the result of the last one. To obtain an array with query results, use the method ExecuteBatch() request object, and to get the result of the last request RunQuery().

In the request text, package requests are separated by the symbol “;” (semicolon). There is only one namespace for virtual tables per batch request. Using a temporary table manager is not required, but is possible if you want to pass temporary tables from one batch query to another.
Code 1C v 8.x ProcessingProcedure (Failure, Processing Mode)

While Select.Next() Loop
//Checking negative balances
//Passing by register
EndCycle;
End of Procedure

In fact, I removed the definition of the query object and the use of the temporary table manager, combined the query texts (note 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 increased).

After executing a query into a variable ArrayResults we will have 3 elements. The first two will contain a number characterizing the number of records placed in the temporary tables DocTCH and ListProducts, and the third will contain a selection with the fields Nomenclature, Doc_Quantity and Reg_Quantity.

To a variable Query Result only a sample will get in.

Well, that's all for batch requests. A very convenient mechanism both from the point of view of writing queries and from the point of view of reading complex queries.

Information taken from the site

A batch request is needed when the request is too complex to be formulated and made to work effectively. In order to understand what a batch request is, you can use the following diagram:

By the way, batch query options became available only in solution 8.1.11.67.4 - so you may not yet know how this works in practice. To put it simply, it is enough to create a series of queries and connect them through the “;” symbol. For example:

The result is returned through the last one. By the way, batch queries are supported in every regular console. An example of its execution through the query console:

Why can't we do without batch requests?

We put the intermediate result into a temporary table and plan to use it in some future queries. Without the temporary table functionality, such an intermediate query would have to be repeated again. If you don’t know how a batch query works and how to use it, then you can run several queries one after another using data from nested tables - this was the case before, now you don’t need to waste time on this, because we have an effective and simple batch request in 1C. It’s convenient; you no longer have to remember where the temporary table should be. To minimize and speed up use the capabilities of the access control system. The system will select, sort, place. This is how the developers of the popular and reliable solution themselves position this innovation.

Announcement of batch requests from 1C developers

Significant optimization of the program as a whole;
Increasing the scalability of a known solution;
Efficient performance due to the introduction of batch placement capabilities;
Simple and straightforward system administration;
The best opportunities for integration with other solutions.

What is the query functionality in 8.1.11?

It is queries that significantly improve work with temporary tables. The functionality of batch queries implies that any table generated for a specific query from a batch works until the batch query has completely completed its action or until a command to destroy it is reached. Now you can receive both the result of the most recent request - this will be the Execute() command, and an entire array of results using the ExecutePackage() method.

Algorithm for competently creating a batch request

So, we now know that a batch request is several requests that we describe as one general request. They must be executed one after another. Any intermediate result during the execution of the entire chain of included queries can be placed in a temporary table. With this structure, any request can access this intermediate data. It is important that the results of any query placed in a temporary table can be used repeatedly. In this case, you no longer need to register the same thing several times.

Disadvantages of the method

Speaking about the advantages of using batch queries, it is worth mentioning an obvious disadvantage. The fact is that with this algorithm of actions, the system is constantly forced to create tables in which it stores the entire database. It takes time both to create each table and to delete it later. Here's an example:

As a result, we have a table:

Now we use a batch request, remember that we need a semicolon for this, we get:

Please note that the 3rd request combined the results of the first two, let’s see what happened:

Now let's try to add beauty and slightly change the text for the 3rd request, like this:

Now we have this:

All that remains is to combine the columns with the Nomenclature and put zeros in the empty cells.