Your first task in this lesson is eliminating the CurRowMaterialsAndServices.MaterialOrService.MaterialServiceType statement, which impacts the performance.

Open the module of the Services document.

The current posting procedure is shown in listing 14.3.

Listing 14.3. Posting() procedure

RegisterRecords.BalanceOfMaterials.Write = True;
RegisterRecords.CostOfMaterials.Write = True;
RegisterRecords.Sales.Write = True;
 
For Each CurRowMaterialsAndServices In MaterialsAndServices Do
    If CurRowMaterialsAndServices.MaterialOrService.MaterialServiceType = 
        Enums.MaterialServiceTypes.Material Then
   
        // register BalanceOfMaterials Expense
        Record = RegisterRecords.BalanceOfMaterials.Add();
        Record.RecordType = AccumulationRecordType.Expense;
        Record.Period = Date;
        Record.Material = CurRowMaterialsAndServices.MaterialOrService;
        Record.Warehouse = Warehouse;
        Record.Quantity = CurRowMaterialsAndServices.Quantity;
   
        // register CostOfMaterials Expense
        Record = RegisterRecords.CostOfMaterials.Add();
        Record.RecordType = AccumulationRecordType.Expense;
        Record.Period = Date;
        Record.Material = CurRowMaterialsAndServices.MaterialOrService;
        Record.Cost = CurRowMaterialsAndServices.Quantity * CurRowMaterialsAndServices.Cost;
    EndIf;
  
    // register Sales
    Record = RegisterRecords.Sales.Add();
    Record.Period = Date;
    Record.MaterialOrService = CurRowMaterialsAndServices.MaterialOrService;
    Record.Customer = Customer;
    Record.Technician = Technician;
    Record.Quantity = CurRowMaterialsAndServices.Quantity;
    Record.Revenue = CurRowMaterialsAndServices.Total;
    Record.Cost = CurRowMaterialsAndServices.Quantity * CurRowMaterialsAndServices.Cost;
  
EndDo;

All the data required for posting the document is taken from the document itself. The platform accesses the database and reads the data of the entire MaterialsAndServices object only to determine whether an item is a material or a service (listing 14.4). 

Listing 14.4. Accessing the MaterialsAndServices object

If CurRowMaterialsAndServices.MaterialOrService.MaterialServiceType

Leaping ahead, we will mention that the correct document posting requires some other data that is not stored in the document itself.

So let us implement this as follows: all the material or service-related data contained in the document tabular section is retrieved using a database query. And the data related to the document itself (such as document date or warehouse) is still obtained from the document. This approach focuses on reading only the required data and therefore reaching the maximum document posting performance.

The following data is retrieved using a query:

• MaterialOrService
• Quantity
• Total
• Cost

The following data is retrieved from the document:

• Date
• Customer
• Technician
• Warehouse

Let us proceed to creating the query.

1. Right-click in the module window, in the line before the loop that iterates through the document tabular section, and then click Query Wizard with result processing (fig. 14.4).
   
    
   Fig. 14.4. Opening the query wizard
2. Confirm that you want to create a new query.
3. In the query wizard, click the Tables and fields tab and select the ServicesMaterialsAndServices table.
   
   It is the tabular section of the Services document.
4. Select the following fields from the table (fig. 14.5):
   • MaterialOrService
   • MaterialOrService.MaterialServiceType
   • Quantity
   • Total
   • Cost
   
   Fig. 14.5. Selected fields
   
   But you do not need all the records of this table. You only need the records related to your document, so let us set a filter condition for selecting only the rows of the document being posted from the document table.
5. Click the Conditions tab, drag the Ref field to the list of filter criteria, and set the condition as shown in listing 14.5.
   
   Listing 14.5. Filter condition for the document table
   
   

   ServicesMaterialsAndServices.Ref = &Ref

   A reference to this document is passed to the Ref query parameter (fig. 14.6).
   
   
   Fig. 14.6. Filter condition for the document table
   
   Also note that a single material or service might have multiple occurrences in a document tabular section. Let us reflect this in the query.
6. On the Grouping tab, group the records by the MaterialOrService and MaterialOrService.MaterialServiceType fields.
7. Specify that if the document tabular section contains multiple rows with a single product or service, totals are calculated by the Quantity and Total fields.
   
   This ensures that each result row contains a unique material or service, and if a material or service is encountered in multiple document rows, the corresponding result row stores its total by the Quantity and Total fields.
8. Move the Cost field to the list of totaled fields.
   
   This field will be used to calculate the Max function.
   
   We imply that the cost is the same for all document rows that contain the same material or service, so the Max function is only needed to obtain one of the available cost values (fig. 14.7).
   
   Fig. 14.7. Grouping document table rows
9. On the Unions/Aliases tab, specify the aliases for the Quantity and Total fields as QuantityInDocument and TotalInDocument respectively.
10. For the MaterialOrServiceMaterialServiceType field, specify MaterialServiceType as an alias (fig. 14.8).
    
    This alias makes the query easier to read.
    
    Fig. 14.8. Field aliases
11. Click OK.
    
    The query text generated by the wizard is shown in listing 14.6.
    
    Listing 14.6. Query text
    
    

    //{{QUERY_BUILDER_WITH_RESULT_PROCESSING
    // This fragment was built by the wizard.
    // Warning! All manually made changes will be lost next time you use the wizard.
    	
    Query = New Query;
    Query.Text = 
        "SELECT
        |	ServicesMaterialsAndServices.MaterialOrService,
        |	ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType,
        |	SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument,
        |	SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument,
        |	MAX(ServicesMaterialsAndServices.Cost) AS Cost
        |FROM
        |	Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
        |WHERE
        |	ServicesMaterialsAndServices.Ref = &Ref
        |
        |GROUP BY
        |	ServicesMaterialsAndServices.MaterialOrService,
        |	ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType";
    	
    Query.SetParameter("Ref", Ref);
    	
    QueryResult = Query.Execute();
    	
    SelectionDetailRecords = QueryResult.Select();
    	
    While SelectionDetailRecords.Next() Do
        // Insert selection processing SelectionDetailRecords
    EndDo;
    	
    //}}QUERY_BUILDER_WITH_RESULT_PROCESSING

You can delete the comments generated by the query wizard at the beginning and at the end of the script fragment.

Since you used the query wizard with result processing to generate the query, the wizard has also generated the script that executes the query and iterates through the query records. Let us analyze the generated script.
As you already know, the query processing involves the use of the 1C:Enterprise script object named Query. First, a new Query object is created and stored to the Query variable. Then the query text is stored to the Text property of the Query object (Query.Text = …).

Then a reference to the document whose module is being executed is stored to the &Ref query parameter (listing 14.7).

Listing 14.7. Specifying the query parameter

Query.SetParameter("Ref", Ref);

Then the query is executed (Query.Execute()), the QueryResult object is obtained, and its method Select() is executed. This method generates a selection of records from the query result.

This is how the QueryResultSelection object is generated. This object is stored to the SelectionDetailRecords variable.

Then the Next() method of this object (SelectionDetailRecords.Next()) is used to iterate through the selection of query records within a loop.

Each execution of the query selection method SelectionDetailRecords.Next() moves the pointer to the next selection record until the end of the selection is reached.

You can get a value of a field that belongs to the selection from the query result using the SelectionDetailRecords variable, which contains the current row of the query selection, followed by a dot. Example: SelectionDetailRecords.MaterialOrService.

Now you only need to move the lines that generate the register records into the loop that iterates through the query result (listing 14.8).

Listing 14.8. Loop that iterates through the query records

While SelectionDetailRecords.Next() Do
    // Insert selection processing SelectionDetailRecords
EndDo;

1. Delete the comment "// Insert selection processing SelectionDetailRecords" and move the condition that checks whether an item is a material or a service and the script that generates records for the BalanceOfMaterials and CostOfMaterials registers into the loop (listing 14.9).
   
   Listing 14.9. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If CurRowMaterialsAndServices.MaterialOrService.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = CurRowMaterialsAndServices.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = CurRowMaterialsAndServices.Quantity;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = CurRowMaterialsAndServices.MaterialOrService;
           Record.Cost = CurRowMaterialsAndServices.Quantity * CurRowMaterialsAndServices.Cost;
   
      EndIf;
        
   EndDo; 

2. In the condition replace CurRowMaterialsAndServices.MaterialOrService with SelectionDetailRecords because the material or service type is now obtained from the query.
3. In the register records also replace CurRowMaterialsAndServices with SelectionDetailRecords (listing 14.10).
   
   Listing 14.10. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = SelectionDetailRecords.Quantity;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Cost = SelectionDetailRecords.Quantity * SelectionDetailRecords.Cost;
   
       EndIf;
        
   EndDo;

4. Replace Quantity with QuantityInDocument because you use that alias in the query (listing 14.11).
   
   Listing 14.11. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = SelectionDetailRecords.QuantityInDocument;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
   
       EndIf;
        
   EndDo;

5. Move the script that creates Sales register records into the loop (listing 14.12).
   
   Listing 14.12. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = SelectionDetailRecords.QuantityInDocument;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
   
       EndIf;
       
       // register Sales
       Record = RegisterRecords.Sales.Add();
       Record.Period = Date;
       Record.MaterialOrService = CurRowMaterialsAndServices.MaterialOrService;
       Record.Customer = Customer;
       Record.Technician = Technician;
       Record.Quantity = CurRowMaterialsAndServices.Quantity;
       Record.Revenue = CurRowMaterialsAndServices.Total;
       Record.Cost = CurRowMaterialsAndServices.Quantity * CurRowMaterialsAndServices.Cost;
       
   EndDo;

   This part requires the same replacements.
6. Replace CurRowMaterialsAndServices with SelectionDetailRecords (listing 14.13).
   
   Listing 14.13. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = SelectionDetailRecords.QuantityInDocument;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
   
       EndIf;
       
       // register Sales
       Record = RegisterRecords.Sales.Add();
       Record.Period = Date;
       Record.MaterialOrService = SelectionDetailRecords.MaterialOrService;
       Record.Customer = Customer;
       Record.Technician = Technician;
       Record.Quantity = SelectionDetailRecords.Quantity;
       Record.Revenue = SelectionDetailRecords.Total;
       Record.Cost = SelectionDetailRecords.Quantity * SelectionDetailRecords.Cost;
       
   EndDo;

7. Replace the Total and Quantity query fields with TotalInDocument and QuantityInDocument respectively (listing 14.14).
   
   Listing 14.14. Creating register records
   
   

   While SelectionDetailRecords.Next() Do
       
       If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
       
           // register BalanceOfMaterials Expense
           Record = RegisterRecords.BalanceOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Warehouse = Warehouse;
           Record.Quantity = SelectionDetailRecords.QuantityInDocument;
      
           // register CostOfMaterials Expense
           Record = RegisterRecords.CostOfMaterials.Add();
           Record.RecordType = AccumulationRecordType.Expense;
           Record.Period = Date;
           Record.Material = SelectionDetailRecords.MaterialOrService;
           Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
   
       EndIf;
       
       // register Sales
       Record = RegisterRecords.Sales.Add();
       Record.Period = Date;
       Record.MaterialOrService = SelectionDetailRecords.MaterialOrService;
       Record.Customer = Customer;
       Record.Technician = Technician;
       Record.Quantity = SelectionDetailRecords.QuantityInDocument;
       Record.Revenue = SelectionDetailRecords.TotalInDocument;
       Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
       
   EndDo;

8. Remove the remaining part of the loop that iterates through the tabular section (listing 14.15).
   
   Listing 14.15. Lines to remove
   
   

   For Each CurRowMaterialsAndServices In MaterialsAndServices Do
    
   EndDo;

The resulting posting procedure should look as shown in listing 14.16.

Listing 14.16. Posting() procedure

Procedure Posting(Cancel, Mode)
 
    RegisterRecords.BalanceOfMaterials.Write = True;
    RegisterRecords.CostOfMaterials.Write = True;
    RegisterRecords.Sales.Write = True;
 
    Query = New Query;
    Query.Text = 
        "SELECT
        | ServicesMaterialsAndServices.MaterialOrService,
        | ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType,
        | SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument,
        | SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument,
        | MAX(ServicesMaterialsAndServices.Cost) AS Cost
        |FROM
        | Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
        |WHERE
        | ServicesMaterialsAndServices.Ref = &Ref
        |
        |GROUP BY
        | ServicesMaterialsAndServices.MaterialOrService,
        | ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType";
 
    Query.SetParameter("Ref", Ref);
 
    QueryResult = Query.Execute();
 
    SelectionDetailRecords = QueryResult.Select();
 
    While SelectionDetailRecords.Next() Do
    
        If SelectionDetailRecords.MaterialServiceType = Enums.MaterialServiceTypes.Material Then
    
            // register BalanceOfMaterials Expense
            Record = RegisterRecords.BalanceOfMaterials.Add();
            Record.RecordType = AccumulationRecordType.Expense;
            Record.Period = Date;
            Record.Material = SelectionDetailRecords.MaterialOrService;
            Record.Warehouse = Warehouse;
            Record.Quantity = SelectionDetailRecords.QuantityInDocument;
   
            // register CostOfMaterials Expense
            Record = RegisterRecords.CostOfMaterials.Add();
            Record.RecordType = AccumulationRecordType.Expense;
            Record.Period = Date;
            Record.Material = SelectionDetailRecords.MaterialOrService;
            Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
   
        EndIf;
    
        // register Sales
        Record = RegisterRecords.Sales.Add();
        Record.Period = Date;
        Record.MaterialOrService = SelectionDetailRecords.MaterialOrService;
        Record.Customer = Customer;
        Record.Technician = Technician;
        Record.Quantity = SelectionDetailRecords.QuantityInDocument;
        Record.Revenue = SelectionDetailRecords.TotalInDocument;
        Record.Cost = SelectionDetailRecords.QuantityInDocument * SelectionDetailRecords.Cost;
    
    EndDo;
   
EndProcedure

Run 1C:Enterprise in the debug mode, repost the Services documents, and ensure that nothing has changed.

You have accomplished the first step: eliminated the reading of all the data of the MaterialsAndServices object and thus streamlined the posting procedure.