Пояснение к правилу 9 (не использовать соединения с подзапросами, а использовать временные таблицы)

В качестве иллюстрации приводится фрагмент статьи К. Рупасова «Типичные причины неоптимальной работы запросов и методы оптимизации». Фрагмент приведен без изменений.

Рекомендации

При написании запросов не следует использовать соединения с подзапросами.

Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с подзапросами, то его следует переписать с использованием временных таблиц.

Если запрос содержит соединения с подзапросами, то это может привести к следующим негативным последствиям:

• крайне медленное выполнение запроса при слабой загрузке серверного оборудования. Замедление запроса может быть очень значительным (до нескольких порядков);
• нестабильная работа запроса. При некоторых условиях запрос может работать достаточно быстро, при других – очень медленно;
• значительная разница по времени выполнения запроса на разных СУБД;
• повышенная чувствительность запроса к актуальности и полноте статистик. Сразу после полного обновления статистик запрос может работать быстро, но через некоторое время опять замедлиться.

Пример потенциально опасного запроса, использующего соединение с подзапросом:

В данном примере в правой части соединения используется подзапрос, а не объект метаданных. Обратите внимание на то, что не важно, в какой части соединения (правой или левой) используется подзапрос. Точно так же не важно, какого типа соединение указано (ЛЕВОЕ, ПРАВОЕ и т. д.). Во всех случаях такая конструкция является потенциально опасной и должна быть исправлена при помощи временных таблиц.

Обратите внимание на то, что возможность использования временных таблиц появилась в «1С:Предприятии» начиная с версии 8.1. Если вы используете версию 8.0, то для решения проблемы производительности такого запроса следует перейти на 8.1.

Для оптимизации запроса следует разбить его на несколько отдельных запросов (по числу подзапросов, используемых в соединениях). Эти запросы рекомендуется поместить в один пакетный запрос.

Внимание!

Не забудьте проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.

Для вышеприведенного примера получится следующий пакетный запрос:

Пояснения

Во встроенном языке запросов «1С:Предприятия» версии 8.0 отсутствовала возможность использовать временные таблицы и писать пакетные запросы. При этом часто было необходимо выполнять сложные вычисления в рамках одного запроса (то есть одного цикла взаимодействия клиент – сервер «1С:Предприятия» – сервер СУБД). Для решения таких задач использовались подзапросы – обращения не к объектам метаданных, а к выборкам из этих объектов. Как правило, подзапросы выполнялись с группировкой и часто использовались в соединениях.

Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае проблемой для оптимизатора является выбор правильного способа соединения. Существует несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обеих выборок на основании имеющейся статистики. Если же одна из соединяемых выборок представляет собой подзапрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.

Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях, будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.

Следует понимать, что, переписав запрос таким образом, мы, возможно, внесли в него некоторое замедление за счет дополнительных накладных расходов – создания временных таблиц. Если СУБД не ошибется с выбором плана, то она, возможно, выполнит старый запрос быстрее, чем новый. Однако это замедление всегда будет крайне незначительным. Размер замедления зависит от используемой СУБД и производительности оборудования. В типичном случае на создание одной временной таблицы может уйти несколько миллисекунд. То есть эти замедления не могут оказать заметного влияния на производительность системы, и, как правило, ими можно пренебречь.

Отправить