Should any problems occur in project operation, we recommend that the following algorithm be used in order to eliminate the problems:
Establish a specific goal. The optimization and improvement has no limits. For example: each page with a catalog of your goods must open within a set amount of time, say, 0.2 second. Only establishing these specific goals and achieving them can be efficient, unlike the vague and formalized request “to make it work better”.
Find and remove irrelevant queries using the tool Performance monitor (Settings > Performance > SQL Queries).
An irrelevant query is:
A query in a cycle (should be removed from the cycle to a variable).
Queries that collect additional data in a cycle. (It is better to collect data in a filter, then display data in a single query, and after that introduce an additional cycle – breaking the data down; in this case, queries have no linear dependence on the number of elements).
Remove unused data from queries ($arSelect). Specify the fields to be used. It drastically improves performance because such a specific indication of data to be used means a lesser volume of sorting for the database. The database performs such sorting not in the hard drive but in the RAM.
Assess the possibility of using PROPERTY_* in combination with infoblocks 2.0. Infoblocks 2.0 store their properties in a separate table. This table is attached at the selection stage when PROPERTY_* is mentioned. And no properties are attached until values of these properties are selected.
When can we not use it? For example, in case of a small selection from a big number of elements (10 pieces of news out of several thousands of entries). It is not a straightforward aspect, and it depends very little on the developer. The reason is that sampling from infoblocks and infoblocks 2.0 leads to a different result. In simple infoblock entries start reproducing when selected. And infoblocks 2.0 return the array of property values. If a template code does not provide for this situation, the change of regular infoblocks to infoblocks 2.0 will destruct the template.
Review the execution plan of the most heavy queries and add/delete indexes.