При разработке и оптимизации сложных многопакетных запросов в системе «1С:Предприятие» перед разработчиком часто встает дилемма: как эффективнее отсечь лишние записи в основной таблице по данным другой таблицы? Традиционно рассматриваются два основных подхода: использование конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ (Inner Join) и использование секции ГДЕ ... В (ВЫБРАТЬ ...). На первый взгляд может показаться, что один из этих методов заведомо быстрее, однако на практике поведение СУБД преподносит сюрпризы. В этой статье мы подробно разберем, как работают эти механизмы «под капотом» СУБД, проанализируем их влияние на производительность, рассмотрим влияние составных типов и научимся инструментам профилирования для поиска узких мест.
Прежде чем приступать к написанию кода, давайте разберем, как наши запросы транслируются платформой 1С на уровень СУБД (будь то MS SQL Server или PostgreSQL) и как их интерпретирует оптимизатор базы данных.
Рассмотрим два варианта написания одного и того же по смыслу запроса.
Вариант 1. Использование секции ГДЕ с подзапросом по вхождению:
ВЫБРАТЬ
Т1.Основание
ИЗ
Документ.Жалоба КАК Т1
ГДЕ
Т1.Основание В (ВЫБРАТЬ Т2.Закупка ИЗ Т2)
Когда СУБД видит конструкцию ГДЕ ... В (ВЫБРАТЬ ...), оптимизатор чаще всего преобразует ее в специальный физический оператор — Left Semi Join (полусоединение). Основная особенность полусоединения заключается в том, что СУБД прекращает поиск в подчиненной таблице Т2 сразу же, как только находит первое совпадение для строки из таблицы Т1. Это дает два важнейших преимущества:
Т2 поле Закупка содержит дублирующиеся значения, строка из Т1 попадет в итоговую выборку ровно один раз. Строки не «размножатся».Вариант 2. Использование ВНУТРЕННЕГО СОЕДИНЕНИЯ:
ВЫБРАТЬ
Т1.Основание
ИЗ
Документ.Жалоба КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Т2 КАК Т2 ПО Т1.Основание = Т2.Закупка
При использовании ВНУТРЕННЕГО СОЕДИНЕНИЯ СУБД обязана сопоставить каждую строку из Т1 со всеми подходящими строками из Т2. Если в таблице отбора Т2 значения в колонке Закупка не уникальны, то строки исходной таблицы Т1 продублируются в результате запроса zoveel раз, сколько дублей встретится в Т2. Чтобы избежать этого эффекта, разработчикам приходится принудительно использовать ключевые слова РАЗЛИЧНЫЕ или выполнять группировку СГРУППИРОВАТЬ ПО. Это заставляет СУБД выполнять дополнительные тяжелые операции сортировки (Sort) или хэширования (Hash Match), что значительно снижает общую производительность запроса.
Казалось бы, из теоретического описания логики работы следует однозначный вывод: Semi-Join (секция ГДЕ В) должен работать быстрее или как минимум не медленнее обычного соединения. Однако на практике все зависит от качества работы оптимизатора СУБД. В сложных многопакетных запросах оптимизатор нередко ошибается, и вот почему:
Nested Loops и более ресурсоемким хэш-соединением Hash Match), оптимизатору необходимо знать примерное количество строк, которое вернет подзапрос ВЫБРАТЬ Т2.Закупка ИЗ Т2. Если таблица Т2 является виртуальной таблицей 1С или сложным вложенным запросом, СУБД не может точно рассчитать объем данных до момента непосредственного выполнения.Table Scan или Index Scan) вместо быстрого точечного поиска по индексу (Index Seek). В реальной практике 1С-разработчиков замена конструкции ГДЕ ... В (...) на прямое внутреннее соединение в таких «запутанных» СУБД ситуациях приводила к колоссальному ускорению выполнения запроса (иногда в десятки и сотни раз).Для того чтобы нивелировать непредсказуемое поведение оптимизатора СУБД и гарантировать стабильно высокую скорость работы запроса независимо от объема данных, в сообществе 1С выработан проверенный стандарт оптимизации. Разберем его по шагам.
Шаг 1. Выгрузка данных отбора во временную таблицу. Мы не пишем подзапрос в секции ГДЕ или в соединении напрямую. Сначала мы выбираем данные для отбора в промежуточный контейнер:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Т2.Закупка КАК Закупка
ПОМЕСТИТЬ ВТ_Отбор
ИЗ
Т2 КАК Т2
ГДЕ
Т2.Закупка <> ЗНАЧЕНИЕ(Документ.Закупка.ПустаяСсылка)
;
Шаг 2. Обязательное индексирование временной таблицы. Мы явно указываем платформе проиндексировать поле связи. Это создает физический индекс в базе данных СУБД для временной таблицы:
ИНДЕКСИРОВАТЬ ПО
Закупка
;
Шаг 3. Внутреннее соединение с проиндексированной временной таблицей. В основном запросе пакета мы связываем исходную таблицу с подготовленной временной таблицей:
ВЫБРАТЬ
Т1.Основание
ИЗ
Документ.Жалоба КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Отбор КАК ВТ_Отбор ПО Т1.Основание = ВТ_Отбор.Закупка
Почему эта схема работает быстрее всего? Когда СУБД анализирует такой запрос, она имеет дело не со скрытым динамическим подзапросом, а с физически существующей временной таблицей (на уровне СУБД это таблицы типа #temp). СУБД точно знает ее размер, структуру и имеет по ней готовый индекс. Оптимизатор СУБД гарантированно построит максимально эффективный план выполнения запроса, используя Index Seek.
Проанализируем еще одну частую проблему — работу с составными типами данных. В системе 1С поле Основание часто является составным (может ссылаться на несколько разных видов документов). На уровне СУБД такое поле хранится в виде нескольких физических колонок (например, колонка для уникального идентификатора ссылки _IDRRef и колонка для определения типа документа _TYPE).
Посмотрим, как ведут себя наши конструкции при работе с составными типами:
ГДЕ Т1.Основание В (...) по составному полю СУБД вынуждена строить тяжелые многоколоночные сравнения. Это практически полностью исключает возможность использования индексов таблиц СУБД, приводя к полному сканированию таблиц (Table Scan).ВНУТРЕННЕГО СОЕДИНЕНИЯ по составному полю платформа 1С транслирует запрос в сложнейший SQL-код с множеством логических ветвлений CASE WHEN или неявных левых соединений со всеми таблицами, входящими в этот составной тип. Это перегружает оптимизатор СУБД.Решение: Перед фильтрацией или соединением составное поле необходимо строго типизировать. Сделать это можно с помощью конструкции ВЫРАЗИТЬ. Проанализируем пример правильного приведения типа перед выполнением соединения:
ВЫБРАТЬ
ВЫРАЗИТЬ(Т1.Основание КАК Документ.Закупка) КАК ОснованиеТипизированное
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
Документ.Жалоба КАК Т1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т.ОснованиеТипизированное
ИЗ
ВТ_ИсходныеДанные КАК Т
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Отбор КАК ВТ_Отбор ПО Т.ОснованиеТипизированное = ВТ_Отбор.Закупка
Типизация сворачивает проверку на уровне СУБД до сравнения колонок одной конкретной физической таблицы, что позволяет СУБД полноценно задействовать имеющиеся индексы. Для автоматического выявления проблемных мест можно использовать обработку для проверки запросов на неявное разыменование составных типов и лишние поля.
Давайте выясним, как изменится ситуация, если перед нами встанет противоположная задача: не отобрать записи, а наоборот — исключить их (то есть использовать отрицание). Разница между подходами в данном случае становится критической для корректности работы системы.
Представим, что мы решили написать условие исключения через НЕ В:
ГДЕ
Т1.Основание НЕ В (ВЫБРАТЬ Т2.Закупка ИЗ Т2)
Внимание! Если в таблице Т2 в поле Закупка окажется хотя бы одна строка со значением NULL (например, из-за незаполненного реквизита), то в соответствии с правилами трехзначной логики SQL (True, False, Unknown) весь запрос вернет абсолютно пустой результат. Это классическая и очень опасная ошибка разработки.
Для безопасного и быстрого исключения записей всегда следует использовать конструкцию ЛЕВОЕ СОЕДИНЕНИЕ с последующим отбором по значению NULL в секции ГДЕ:
ВЫБРАТЬ
Т1.Основание
ИЗ
Документ.Жалоба КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Отбор КАК ВТ_Отбор ПО Т1.Основание = ВТ_Отбор.Закупка
ГДЕ
ВТ_Отбор.Закупка ЕСТЬ NULL
Такой запрос отработает стабильно, предсказуемо и быстро при любых исходных данных.
Ни одна теоретическая рекомендация не заменит реального замера производительности на конкретной базе данных. Если вы столкнулись с медленной работой "лютого" многопакетного запроса, воспользуйтесь следующими инструментами:
Reads) и загрузку процессора (CPU).DBMSSQL или DBPOSTGRS. Это даст вам точную информацию о том, какие именно пакеты запроса выполняются дольше всего и какие физические SQL-запросы генерирует платформа.Scan или предупреждения о неявном приведении типов).Оптимизация огромных запросов — это комплексный процесс. Как показывает практика ведущих экспертов по технологическим вопросам, сокращение количества строк кода и числа обращений к одной и той же таблице дает гораздо больший эффект, чем просто замена синтаксических конструкций. Для наглядного анализа взаимосвязей в таких конструкциях подойдёт консоль запросов с визуализацией связей временных таблиц в виде графа.
Постарайтесь придерживаться правила: получать максимум необходимых данных из большой таблицы за один проход по ней. Если ваш запрос обращается к одной и той же физической таблице (например, к регистру бухгалтерии или регистру накопления) 10 или 20 раз в разных пакетах, то никакая замена ГДЕ на СОЕДИНЕНИЕ не спасет производительность. Пересмотрите архитектуру запроса: соберите все необходимые исходные данные во временную таблицу за один проход, проиндексируйте ее, а затем используйте для расчетов в последующих пакетах. Это позволит сократить время выполнения запроса в разы!