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.