Queries in dynamic lists

Scope: a managed application, a mobile application.

Best practices

When you design dynamic lists in forms, keep in mind that dynamic lists require a higher query execution speed than in reports, data processing procedures, and other. Data in dynamic lists is displayed directly to users. Speed of data output and update is critical. Below you will find recommendations that supplement information in a group of chapters "Query optimization".

1. Try to use simple queries for dynamic lists. To simplify, first optimize the data storage architecture to easily display it in dynamic lists.

Example

In dynamic lists of shipment references, you need to display a shipment state. The state depends on accumulation register balance and statuses of two documents of another type.

INCORRECT EXAMPLE

Try to develop a query for a dynamic list considering a complicated logic of shipment state calculation.

CORRECT EXAMPLE:

Create the "Shipment states" information register to store the calculated shipment state. The calculation can be made either when posting documents that might affect the shipment state or as a separate scheduled job.

2. Select one of the following modes of a dynamic list:

  • Dynamic data reading is enabled (recommended). Queries that select the number of records roughly equivalent to the number of rows displayed in a table are used.
  • Dynamic data reading is disabled, a non-virtual main table or one of the following tables is specified: SliceFirst, SliceLast, TasksByExecutive, FilterCriteria, and ExtDimensionRegisterRecord. Queries that select by 1,000 records to the server buffer are used. When required, this data is transferred to the client.  It is less efficient than dynamic reading.
  • Dynamic reading of data is disabled, the main table is not specified. The query is executed "as is". The buffer accumulates data starting from 1,000 records. The closer to the end of the list, the more the records.  You can use this mode only to small selected data.

3. When developing dynamic lists, note that a query to DBMS depends on predefined DCS filter settings, order, and grouping. In particular, it means that you need to consider indexing of the following fields:

  • Fields used for joins in queries.
  • Fields with imposed conditions in queries.
  • Fields displayed as quick filters.
  • Fields used for ordering or grouping.
  • Fields expected to be frequently ordered or grouped by users.

Avoid indexing all fields as excessive indexes create unreasonable load upon writing data.

See also: Inconsistency of indexes and query conditions, Dynamic list restrictions.

4. We do not recommend that you use statements that make queries more complicated:

  • DISTINCT and GROUP BY statements.
  • CASE in WHERE statement or join conditions.
  • Ordering by the field received using the CASE statement, including user-initiated one.

See also: General requirements for developing optimal queries

5.1 Join queries with a small number of existing tables only. In an optimal scenario, a dynamic list includes only one table that is assigned as default.

Avoid using joins with:

  • A great number of existing tables. The maximum value is 4 tables.
  • Nested queries.
  • Virtual tables. 

See also: Queries joined with nested queries or virtual tables

5.2. Joins with virtual tables are allowed if a query and data architecture meet the following conditions:

  • Joins with virtual tables SliceLast (SliceFirst) are allowed if an information register contains
    a small number of records. For example, to get the current exchange rate using data of the ExchangeRates information register.
  • When you access a virtual table, totals stored in an information register are used (see Allowing totals for periodic information registers).
  • A query addressing the Balance virtual table is converted by the platform into simple reading of a stored table with totals without grouping (see Effective access to the Balance virtual table).

5.3. Use temporary tables in dynamic lists according to the below requirements and the requirements of the standard for using temporary tables.

5.3.1. Use temporary tables in dynamic lists only when they contain a small number of records. Otherwise, their use is inefficient since temporary table values are NOT cached in a dynamic list and generated every time data is read to fill in a list.

5.3.2. In particular, if you cannot redesign a dynamic list query using virtual tables with restrictions (see cl. 5.2) or choose not to use them, use joins with temporary tables instead of joins with the above tables.

5.3.3. If the last dynamic list query selects data only from an earlier created temporary table, it is not a dynamic list. Redevelop its query and metadata used by the query.

5.4. When you consider the number of query tables, keep in mind that implicit joins with additional tables are created upon calling fields using "." (dot). For more information, see Dereferencing reference fields of a composite type in the query language

5.5. When you use a list under restricted rights where RLS data access restrictions apply to tables involved in a query*, data access restrictions are joined automatically, thus slowing down list operations. In this mode, test the dynamic list speed.

* Note: to tables, for which RLS is provided in a configuration.

6. Provide optimization for fields of a composite type.

  • When using composite fields in joins, filters, ordering, and other clauses, types of these fields must be defined by reference types only. For more information, see Restrictions on using composite type attributes
  • If you know the required type of a field of a composite type in advance, cast it.

For example:

CAST(DocumentData.DocumentBasis AS Document.AcceptanceNote).

See also: Dereferencing reference fields of a composite type in the query language

7. We recommend that you replace a dynamic list query with a more optimal one on the fly where possible.

For example, if you can change settings to rewrite a dynamic list query so that it accesses other metadata, thus speeding up its execution.

See also: Overriding texts of dynamic list queries

8. If the above recommendations cannot be implemented or failed, consider the following optimization options:

8.1. Avoid using some functions of dynamic lists.

For example:

  • Avoid outputting unimportant information, whose receiving results in more complicated queries.
  • Avoid service functions used to group lists.

8.2. Output data in a table or a value tree, not in a dynamic list.

You will be able to use optimization features unavailable for dynamic lists (privileged mode and other).

This approach can be used if one of the following conditions is true:

  • There is a small amount of source data (tens to hundreds of records).
  • Required filters applied to a list ensure that a small amount of data is output at a time.
  • Data is output in portions using other tools (manually), for example, as in full-text search results.

9. When it is necessary to display auxiliary columns in a dynamic list, which do not require filtering (including through search mechanisms), sorting, or grouping, and it is difficult, inefficient, or impossible to get data using the main query, use the OnGetDataAtServer handler of the managed form table. For example, the Exchange rate for today and Unit conversion factor columns in the currency list, and other.

For the OnGetDataAtServer handler to operate effectively, select all auxiliary information with a single query for all displayed rows that are passed to this handler as soon as the main dynamic list query is executed.

A dynamic list requires explicit restriction of filter, sorting, and grouping by auxiliary columns using the following dynamic list methods: SetRestrictionsForUseInGroup, SetRestrictionsForUseInOrder, and SetRestrictionsForUseInFilter.