Scope: managed applications, mobile applications, and ordinary applications.
1. If you use virtual tables in queries, pass all conditions related to this virtual table to the table parameters. We do not recommended that you access virtual tables using conditions of WHERE clause, and other.
Such query returns a correct result but it is more difficult for DBMS to select an optimum method to execute that query. In some cases, it can cause DBMS optimizer errors and considerably slow down the query execution.
For example, the following query uses the WHERE section to select data from a virtual table:
| Warehouse = &Warehouse";
Upon executing this query, all records of a virtual table are selected. Then only those that meet the specified condition are sampled.
We recommend that you restrict the number of records to be selected at a very early stage of query processing. To do so, pass conditions to virtual table parameters.
| AccumulationRegister.Stock.Balance(, Warehouse = &Warehouse)";
2.1. When you access a virtual table, pass simple clauses to conditions, for example, "Dimension = Value". We recommend that you do not use subqueries and joins (*) in virtual table parameters as this can delay query execution.
* Note: Both explicit joins in subqueries and implicit joins upon calling fields using "." (dot) from a reference and a join added from data access restrictions (RLS) available in the configuration roles.
Use only one table in a subquery and avoid using joins with other tables.
If a subquery contains a tabular section table (for example, Document.Invoice.GoodsList), avoid accessing header table attributes (Invoice.Posted).
If a subquery includes a table that can have tabular sections (for example, Document.Invoice), avoid accessing tabular sections (for example, WHERE Document.Invoice.GoodsList.Products = "1").
If a subquery includes a temporary table, do not use conditions (the WHERE section).
If a subquery has a permanent table, the condition (the WHERE section) is acceptable only if it is met for at least 80% of the cases. If the condition is unavailable, it means that it is fulfilled for 100% of the cases.
If a subquery has a permanent table, do not use subqueries and joins in data access restrictions (RLS) (only simple conditions of the WHERE Attribute = Value, "WHERE True" type are allowed). For example, if you use standard RLS templates being part of the "Access management" subsystem of Standard Subsystems Library, Exists clause is implicitly added to a query with multiple subqueries and joins. In such cases, rewrite the original query using a temporary table or in the privileged mode.
AND ShipmentDocument.Warehouse = &Warehouse, implicit dot-separated join
OR ShipmentDocument IN
(SELECT -- a subquery with join
Orders.Order AS ShipmentDocument
Document.TransportationJob.Orders AS Orders –- access to this document is restricted using complex RLS that implicitly adds two more joins
INNER JOIN Document.TransportationJob.LoadingWarehouses AS LoadingWarehouses
Orders.Ref = LoadingWarehouses.Ref
AND LoadWarehouses.Warehouse = &Warehouse
AND Orders.Ref.Posted –- here and below access to header attributes
AND Orders.Ref.Status IN (...)))
Warehouse = &Warehouse -- now it is a register attribute
OR ShipmentDocument IN
TempTableOfTransportationJobs AS TransportationJobs)) -- selection of data from a temporary table without conditions
2.3. If you need to use multiple conditions with subqueries, select one condition that meets the above mentioned requirements and filters the maximum number of records. Apply the remaining conditions to an external query.
In some cases, you can avoid moving conditions to an external query if you use temporary tables.
Example of incorrect condition:
Products IN (...) AND Characteristic IN (...) AND Series IN(...)
(Products, Characteristic, Series) IN (SELECT Products, Characteristic, Series FROM GoodsTempTable)