Accessing fields from other fields using . (dot)

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.