Scope: managed applications, mobile applications, and ordinary applications.
Before using more advanced methods of query optimization, make sure your query is suitable for the task.
1.1. Minimize the size of selected data to choose only the data required to solve the task.
For example, if you need to get values of particular fields, do not select all fields just in case using the SELECT* FROM … clause
Instead of selecting a large size of data for its further processing (rollup, sorting, calculations, and other) on 1C:Enterprise server, first of all, try to find out if this task can be processed by a data base to get a ready-to-use result.
See also: Merging similar queries.
- Prepare several simpler query texts depending on the query preconditions and parameter values instead of sending a single universal query to DBMS.
- Ensure more efficient post-processing of data selected from DBMS by the query on 1C:Enterprise server using 1C:Enterprise language tools.
2.2. When you develop queries, make sure they use efficient query plans. DBMS will most likely select an incorrect query plan for complex queries. It is particularly relevent to DB2, PostgreSQL, and Oracle DBMS.
Avoid unreasonably making queries complex:
- Do not add nested queries only to improve readability.
- Avoid using complicated conditions with subqueries and the CASE statement when joining queries and in the WHERE statement.
- Use the minimum required number of tables in a query. Depending on the table structure, even 5-7 tables in a query can be excessive since the time spent to analyze the query by DBMS optimizer increases on a non-linear basis and finally an inefficient query plan is generated.
To find out which query execution plan is selected by DBMS optimizer, use a query console, a technological log, or DBMS tools. As a rule, a query is complicated and is inefficiently executed if a timeout warning exists in a compiled query plan. It means that DBMS optimizer did not have enough time for a better query plan.
See also: Queries in dynamic lists