In the query language you can access a field by specifying it after another field, separated by . (dot). On the one hand, this allows you to create short queries. But on the other hand, you should always understand the background of every such action. This section covers accessing fields using . (dot) and provides tips on how to optimize access to fields.
Accessing a field using . (dot)
Let us consider the following query:
SELECT Items.Description, QuantityTurnover FROM AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers
In this query the Description field is accessed by adding it after . (dot) to the Items field. When such a query is executed, 1C:Enterprise query language generates an implicit connection to the Items catalog and gets the Description field from it. Therefore, the query that is actually executed has the following format:
SELECT CatalogItems.Description, QuantityTurnover FROM AccumulationRegister.ItemsAccounting.Turnovers AS ItemsAccountingTurnovers LEFT JOIN Catalog.Items AS CatalogItems ON ItemsAccountingTurnovers.Items = CatalogItems.Ref
Note that if a query gets multiple fields from a single field using . (dot), the platform creates a single connection to the table referenced by these fields. For instance, if in the first example you get the Description and Code fields from the Items field using . (dot), a single connection to the Items table is created instead of two connections.
Accessing a Ref field using . (dot)
Do not get the Ref field from another field using . (dot). When you get the Ref field using this method, an additional connection is created, which will most likely impact the query performance.
For example, in the following query the Ref field is accessed from the Counterparty field using . (dot).
SELECT PurchaseOrder.Date, PurchaseOrder.Number FROM Document.PurchaseOrder AS PurchaseOrder WHERE PurchaseOrder.Counterparty.Ref = &Counterparty
This results in the creation of a redundant connection to the Counterparties table, which will probably slow down the query execution. This query should be modified as follows:
SELECT PurchaseOrder.Date, PurchaseOrder.Number FROM Document.PurchaseOrder AS PurchaseOrder WHERE PurchaseOrder.Counterparty = &Counterparty
Using . (dot) to access fields from the fields that reference multiple objects
If a field used to access another field is a reference to multiple tables, the number of connections established during the query execution is equal to the number of reference tables that contain the required field.
Example: Suppose the Recorder field of the accumulation register references two documents: PurchaseOrder and SalesOrder. In this case the following query:
SELECT ItemsAccounting.Recorder.Number, ItemsAccounting.Count FROM AccumulationRegister.ItemsAccounting AS ItemsAccounting
will result in an implicit connection to both document tables, i.e. two connections will be established. The executed query will look as follows:
SELECT CASE WHEN ItemsAccounting.Recorder REFS Document.SalesOrder THEN SalesOrder.Number WHEN ItemsAccounting.Recorder REFS Document.PurchaseOrder THEN PurchaseOrder.Number END, ItemsAccounting.Count FROM AccumulationRegister.ItemsAccounting AS ItemsAccounting LEFT JOIN Document.SalesOrder AS SalesOrder ON ItemsAccounting.Recorder = SalesOrder.Ref LEFT JOIN Document.PurchaseOrder AS PurchaseOrder ON ItemsAccounting.Recorder = PurchaseOrder.Ref
But if the field used to access another field has AnyRef type, in some cases an implicit connection to all the object tables in the configuration might be established. So, for example, if you use a field of the AnyRef type to get the Presentation field and the configuration has 50 catalogs and 100 documents, 150 connections to different tables will be established.
You should keep this in mind when you design your data structure, as well as when you create queries.
Using CAST to limit the number of connections when accesing fields using . (dot)
When you generate a query, you sometimes know which reference will be present in a specific field that references multiple tables. In this case you can explicitly specify the required table in the query so that the implicit connection is established only with the specified table rather than all the tables referenced by this field.
Example: Suppose the Recorder field of the accumulation register references two documents: PurchaseOrder and SalesOrder, and the WHERE statement narrow the selection to recorder values that reference PurchaseOrder:
SELECT ItemsAccounting.Recorder.Number, ItemsAccounting.Count FROM AccumulationRegister.ItemsAccounting AS ItemsAccounting WHERE ItemsAccounting.Recorder REFS Document.PurchaseOrder
Regarding this query, as we know for certain that the result will include only the documents with the recorder referencing the table of purchase orders, you can modify the query to improve its performance:
SELECT CAST(ItemsAccounting.Recorder AS Document.PurchaseOrder).Number, ItemsAccounting.Count FROM AccumulationRegister.ItemsAccounting AS ItemsAccounting WHERE ItemsAccounting.Recorder REFS Document.PurchaseOrder
When this query is executed, only the connection to the Document.PurchaseOrder table is established instead of connecting to both tables referenced by the Recorder field. This improves query performance.Next page: Bitness of results of expressions and aggregate functions in query language