1C:Professional training course. Lesson 3-1

Lesson 3-1

Balance control (sequel)

As it was mentioned in Lesson 2, we have a problem with our balance control query. Here is a query text we use: 

SELECT
 BalanceOfMaterialsBalance.Material AS Material,
 BalanceOfMaterialsBalance.Warehouse AS Warehouse,
 BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
FROM
 AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
WHERE
 BalanceOfMaterialsBalance.QuantityBalance < 0

The only condition here is BalanceOfMaterialsBalance.QuantityBalance < 0. It means that the query will return all records that have negative balance whether they have anything to do with the current document or not. This could cause various issues depending on the 1C:Enterprise mode (file mode or client/server mode), type of DBMS you use and even the number of concurrent users working with the system.

Most of the issues are far beyond the scope of the course but here is a relatively simple example: to execute the query the DBMS will have to scan all the Balance table, i.e. to browse through its records, checking them one by one. Balance table contains one record for each Material in each Warehouse for each month: it’s going to be thousands of records, and the number will grow over a course of time. This happens because the accumulation register’s Balance table is perfectly fit to search by dimensions but not by other fields.

So, we definitely need to add Material and Warehouse into the query’s conditions, i.e. filter out the Balance records that have nothing to do with the document we are posting. Here is a picture showing the principle:


We need to read the first two records (painted with green) of the Balance table and we need to filter out the last two (red) records.

Here are three ways of doing this:

  • Using WHERE;
  • Using JOIN;
  • Using virtual table’s parameters.

As you might have guessed, only one of this methods is recommended, but we discuss all of them learning new things down the road.

Filtering the Balance table with WHERE

WHERE clause in 1C can contain any number of conditions joined by AND, OR and NOT operators and divided by parentheses. Here are the condition types that can be useful for our purposes:

  • <Expression> <Comparison operation> <Expression>
  • <Expression> [NOT] IN (<List or Subquery>)

<Expression> <Comparison operation> <Expression>

There is the list of all valid comparison operations we can use in this type of conditions:

  • >
  • <
  • =
  • >=
  • <=
  • <>

Here is what you can use as an expression (left or right - doesn’t matter):

  • Any field of the BalanceTab. Examples:
    • BalanceTab.QuantityBalance
    • BalanceTab.Material
  • Any field of an object whose reference is a value of some BalanceTab field:
    • BalanceTab.Material.MaterialOrService
    • BalanceTab.Warehouse.Description
  • Literal (explicitly specified value) of any type:
    • 100
    • “Main”
  • Named parameter whose value will be set later (but before the query is executed):
    • &BalanceLimit
    • &DefaultWarehouse
  • Any valid operation (arithmetical or not) returning one value:
    • BalanceTab.QuantityBalance / 2 + 10
    • “PRE:” + BalanceTab.Material.Code

Note that these expressions can be used in any combinations and any order on the both sides of the comparison.

Also, note that you can compare string expressions using all operators - not only = and <>. In this case, expressions will be compared according to the alphabetic order. Here are few examples of expressions returning TRUE as a result:

  • “abc” < “bcd”
  • “23” < “qqq”

Here are a few examples of valid WHERE conditions: 

// Returns all of the Balance table records with a balance not equal to 5
WHERE BalanceTab.QuantityBalance  5
// Returns nothing
WHERE 2 * 2 = 5
// Returns all rows
WHERE "abc" + "def" = "abcdef"
// Returns balances of all materials that are in "Plumber stuff" group
BalanceTab.Material.Parent.Description = "Plumber stuff"

Can we use this condition type to filter out the Balance table records that we don’t need? Well, let’s try. We can rewrite the Posting handler query like this: 

    SELECT
 BalanceOfMaterialsBalance.Material AS Material,
 BalanceOfMaterialsBalance.Warehouse AS Warehouse,
 BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
FROM
 AccumulationRegister.BalanceOfMaterials.Balance AS BalanceOfMaterialsBalance
WHERE
 QuantityBalance < 0 AND
 Warehouse = &Warehouse AND
 (
  Material = &Material1 OR
  Material = &Material2 OR
  Material = &Material3 OR
  ...
 )

The problem is that we don’t know how many records are in the tabular section, so we cannot write the static query text that fits any document. But the query text is just a string variable and, as such, it can be built in runtime from as many pieces as we need to the document we are posting.

Such a query called a dynamic one, and it’s used by 1C developers pretty extensively. If you open any 1C application, you can hardly find a single static query - most of them will be the dynamic ones. Aside from its obvious advantages, this approach has one noticeable drawback: when you look at the application source code, you often don’t know what query will be eventually executed. But here is a tip: use the Debugger, a breakpoint and “Evaluate expression” function to get the query text at runtime just before it’s executed.

Let’s try to build the dynamic query that filters the Balance table the way we need.


Here is a new version of the Posting handler we just used: 

Procedure Posting(Cancel, Mode)
	
	// 1. Filling out BalanceOfMaterials recordset
	RegisterRecords.BalanceOfMaterials.Write = True;
	For Each CurRowMaterialsAndServices In MaterialsAndServices Do
	If CurRowMaterialsAndServices.MaterialOrService.MaterialOrService =
Enums.MaterialOrService.Service Then Continue;
		EndIf;
		Record = RegisterRecords.BalanceOfMaterials.Add();
		Record.RecordType = AccumulationRecordType.Expense;
		Record.Period = Date;
		Record.Material = CurRowMaterialsAndServices.MaterialOrService;
		Record.Warehouse = Warehouse;
		Record.Quantity = CurRowMaterialsAndServices.Quantity;
	EndDo;
	
	//2. Writing down BalanceOfMaterials recordset
	RegisterRecords.BalanceOfMaterials.LockForUpdate = True;
	RegisterRecords.Write();
		
	//3. Checking if there any negative balance records
	Query = New Query;
	Query.Text = 
		"SELECT
		|	BalanceOfMaterialsBalance.Material AS Material,
		|	BalanceOfMaterialsBalance.Warehouse AS Warehouse,
		|	BalanceOfMaterialsBalance.QuantityBalance AS QuantityBalance
		|FROM
		|	AccumulationRegister.BalanceOfMaterials.Balance AS
|	BalanceOfMaterialsBalance
		|WHERE
		|	BalanceOfMaterialsBalance.QuantityBalance  1 Then
			MaterialsCondition = MaterialsCondition + " OR " + Chars.CR;
		EndIf;
		MaterialsCondition = MaterialsCondition + Chars.Tab + Chars.Tab +
			"BalanceOfMaterialsBalance.Material = &Material" + Counter;
		Query.SetParameter("Material" + Counter,
CurRowMaterialsAndServices.MaterialOrService);
	EndDo;
	If MaterialsCondition  "" Then
		Query.Text = Query.Text + " AND" + Chars.CR + 
			Chars.Tab + "Warehouse = &Warehouse" + Chars.CR +
			Chars.Tab + "AND (" + Chars.CR +
			MaterialsCondition + Chars.CR + 
			Chars.Tab + ")";
	EndIf;
	Query.SetParameter("Warehouse", ThisObject.Warehouse);
		
	//5. Execute the query
	QueryResult = Query.Execute();
	SelectionDetailRecords = QueryResult.Select();
	
	//6. If the result is not empty, cancel the document's posting
	If SelectionDetailRecords.Count() = 0 Then
		Return;
	EndIf;
	Cancel = True;
	
	//7. Prepare the error message and display it.
	MessageText = 
		"There is not enough materials on the " + 
		ThisObject.Warehouse.Description + 
		". See the list of deficits below:" +
		MessageText;
	While SelectionDetailRecords.Next() Do
		MessageText = 
			MessageText + Chars.CR + Chars.LF + 
			SelectionDetailRecords.Material.Description + ": " +
			SelectionDetailRecords.QuantityBalance;
	EndDo;
	Message(MessageText);
	
EndProcedure

So, functionally we got what we needed - the unwanted Balance table records are filtered out. But how good this decision is? Well, imagine a Service document with hundreds of records and you will know the answer. To check such a document’s balance we are going to need a really massive query text even sending which to a DBMS can be a resource-consuming task.

One more problem is that the query is likely to be slightly different on every execution (because the number of parameters varies depending on the number of lines in the document’s tabular part). This will cause frequent query recompilation and further slowing down of the application.

Another obvious problem is a complexity of the source code. It can take a while to write it, it will be a nightmare to read it, and the risk of missing a bug is higher in comparison with static queries.

All in all, we don’t recommend you using this balance control approach in real-world systems.

Parameters vs. Literals

A few more words about query compilation. You should remember, that using literals you make the query to be recompiled every time the literal value is changed. For example, these three queries return the same results:

//Query 1
Query.Text = "SELECT * FROM Catalog.Clients AS C WHERE C.Name = ""Sigizmund""";
//Query 2
Name = "Sigizmund";
Query.Text = "SELECT * FROM Catalog.Clients AS C WHERE C.Name = """ + Name + """";
//Query 3
Query.Text = "SELECT * FROM Catalog.Clients AS C WHERE C.Name = &Name";
Query.SetParameter("Name", "Sigizmund");

But the first and the second queries will be recompiled every time you change the client’s name. This won’t be the case for the third query. It will be compiled once (on the first run), and then a DBMS will be using the ready-to-go query plan, even if you change the parameter value. This won’t save you a lot of time for the simple queries like these. But it can be significant for real-life queries, which can be as large as a few hundreds of lines.

Therefore, you should never use literals in 1C queries. Use parameters instead.



 Lesson 2-5 | Course description | Lesson 3-2

1C:Enterprise Developer's Community