Часто разработчики 1С сталкиваются с парадоксальной ситуацией: есть запрос, который фильтрует данные по предварительно подготовленной временной таблице. В процессе выполнения алгоритма эта временная таблица оказывается пустой, однако запрос не возвращает мгновенный пустой результат, а "вешается" на десятки минут или даже часов. Более того, подобное поведение может привести к катастрофическому разрастанию системной базы данных СУБД (например, tempdb в MS SQL Server) до сотен гигабайт или даже терабайта.
Разберем подобную ситуацию на реальном примере из типовой конфигурации 1С:ERP Управление предприятием (и 1С:ЗУП), где при открытии или заполнении документа "Резервы по оплате труда" система зависает. Проанализируем исходный код, выясним скрытые механизмы работы оптимизатора СУБД и по шагам применим эффективные методы оптимизации.
Рассмотрим структуру запроса, который стал причиной зависания:
ВЫБРАТЬ
ДатыПолученияДанныхПоИсточникамДанных.ОбщийГрафик КАК ОбщийГрафик,
ПодневныеДанныеУчетаВремени.Дата КАК Дата,
// ... выбор множества других полей ...
ИЗ
ВТФильтрДля КАК ДатыПолученияДанныхПоИсточникамДанных
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ГрафикиРаботыСотрудников КАК ГрафикиРаботыСотрудников
ПО (ГрафикиРаботыСотрудников.Ссылка = ДатыПолученияДанныхПоИсточникамДанных.ОбщийГрафик)
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПланВидовРасчета.Начисления.ВидыВремени КАК ТаблицаНаборовВидовВремени
ПО (ТаблицаНаборовВидовВремени.Ссылка = ДатыПолученияДанныхПоИсточникамДанных.КлючНабораВидовВремени)
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ПодневныеДанныеУчетаВремени
ПО (ПодневныеДанныеУчетаВремени.ГрафикРаботы = ДатыПолученияДанныхПоИсточникамДанных.ИсточникДанныхФактическогоВремени)
И (ПодневныеДанныеУчетаВремени.ВидУчетаВремени = ТаблицаНаборовВидовВремени.ВидВремени)
И (ПодневныеДанныеУчетаВремени.Дата МЕЖДУ ДатыПолученияДанныхПоИсточникамДанных.ДатаНачала И ДатыПолученияДанныхПоИсточникамДанных.ДатаОкончания)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДанныеПроизводственногоКалендаря КАК ДанныеПроизводственногоКалендаря
ПО (ГрафикиРаботыСотрудников.ПроизводственныйКалендарь = ДанныеПроизводственногоКалендаря.ПроизводственныйКалендарь)
И (ВЫБОР
КОГДА ПодневныеДанныеУчетаВремени.ПереходящаяЧастьТекущейСмены
ТОГДА ДОБАВИТЬКДАТЕ(ПодневныеДанныеУчетаВремени.Дата, ДЕНЬ, 1)
ИНАЧЕ ПодневныеДанныеУчетаВремени.Дата
КОНЕЦ = ДанныеПроизводственногоКалендаря.Дата)
И (ДанныеПроизводственногоКалендаря.ВидДня = ЗНАЧЕНИЕ(Перечисление.ВидыДнейПроизводственногоКалендаря.Праздник))
И (ТаблицаНаборовВидовВремени.ОпределяющийЗаПраздничныеДни)
ГДЕ
(НЕ ТаблицаНаборовВидовВремени.ОпределяющийЗаПраздничныеДни
ИЛИ НЕ ДанныеПроизводственногоКалендаря.Дата ЕСТЬ NULL)
СГРУППИРОВАТЬ ПО
// ... поля группировки ...
Даже если таблица ВТФильтрДля пустая, запрос выполняется бесконечно долго. Выясним причину такого поведения.
Когда платформа 1С отправляет текст запроса на сервер баз данных (например, MS SQL), оптимизатор СУБД должен сначала построить план выполнения. Он делает это на основе статистики и структуры связей таблиц до того, как начнет "прогонять" строки.
ЛЕВОЕ СОЕДИНЕНИЕ мы видим конструкцию ВЫБОР КОГДА ... ТОГДА ДОБАВИТЬКДАТЕ(...). Использование функций прямо в условии соединения лишает СУБД возможности использовать индексы. Системе приходится вычислять это выражение для каждой возможной комбинации строк перед проверкой условия.ГрафикиРаботыПоВидамВремени (например, 2 миллиона записей) с ДанныеПроизводственногоКалендаря в памяти, и лишь затем попытаться отфильтровать этот гигантский массив пустой временной таблицей.tempdb: Из-за соединения "каждого с каждым" для вычисления функции ДОБАВИТЬКДАТЕ, СУБД не хватает оперативной памяти. Она начинает активно сбрасывать промежуточные результаты на диск в системную базу tempdb. Это объясняет, почему база tempdb за ночь может разрастись до 1 ТБ, а запрос так и не завершится.ВТФильтрДля была не пустой, и СУБД скомпилировала тяжелый план выполнения. В дальнейшем, даже когда таблица пуста, СУБД берет старый план из кэша, что приводит к катастрофическому падению производительности.Проанализируем причину появления таких запросов с точки зрения прикладной логики ERP (или ЗУП). Документ "Резервы по оплате труда" автоматически формируется регламентными операциями при закрытии месяца. Если в системе есть кадровые ошибки или не до конца настроенные графики, при автоматическом создании документа некоторые служебные реквизиты (например, Идентификатор строки) могут остаться пустыми.
При пустом идентификаторе типовые модули расчета передают в алгоритмы выборки пустые массивы. Запрос лишается жестких отборов и начинает пытаться рассчитать или отфильтровать графики времени "по всему предприятию за все время". Это запускает цепную реакцию, приводящую к декартову произведению.
Рассмотрим детально, как нужно переписать такой запрос, чтобы он работал молниеносно при любых объемах данных.
Когда вы выполняете левое соединение со сложным условием, зависящим от нескольких внутренних соединений, оптимизатор часто сходит с ума. Необходимо разбить запрос на два этапа.
Сначала поместим результат всех внутренних соединений в новую временную таблицу:
ВЫБРАТЬ
ДатыПолученияДанныхПоИсточникамДанных.ОбщийГрафик,
ПодневныеДанныеУчетаВремени.Дата,
ПодневныеДанныеУчетаВремени.ПереходящаяЧастьТекущейСмены
// ... остальные поля ...
ПОМЕСТИТЬ ВТ_ПредварительныеДанные
ИЗ
ВТФильтрДля КАК ДатыПолученияДанныхПоИсточникамДанных
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ГрафикиРаботыСотрудников КАК ГрафикиРаботыСотрудников
ПО ...
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПланВидовРасчета.Начисления.ВидыВремени КАК ТаблицаНаборовВидовВремени
ПО ...
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ПодневныеДанныеУчетаВремени
ПО ...
;
Конструкцию ВЫБОР КОГДА ... ДОБАВИТЬКДАТЕ(...) нельзя использовать в секции ПО. Подготовим "Дату связи" заранее, прямо на этапе формирования временной таблицы:
ВЫБРАТЬ
// ... базовые поля ...
ВЫБОР
КОГДА ПодневныеДанныеУчетаВремени.ПереходящаяЧастьТекущейСмены
ТОГДА ДОБАВИТЬКДАТЕ(ПодневныеДанныеУчетаВремени.Дата, ДЕНЬ, 1)
ИНАЧЕ ПодневныеДанныеУчетаВремени.Дата
КОНЕЦ КАК ДатаДляСвязиСКалендарем
ПОМЕСТИТЬ ВТ_ПредварительныеДанные
ИЗ
ВТФильтрДля ...
ИНДЕКСИРОВАТЬ ПО
ДатаДляСвязиСКалендарем
;
Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО. Индексы во временной таблице критически важны. Они подскажут оптимизатору СУБД правильный метод соединения (например, Nested Loops вместо Hash Match).
В исходном запросе присутствует конструкция: ГДЕ (НЕ ... ИЛИ НЕ ... ЕСТЬ NULL). Условие ИЛИ (OR) заставляет систему сканировать данные повторно. Заменим эту логику. Поскольку мы уже вынесли данные во временную таблицу, итоговый запрос будет выглядеть просто и прозрачно:
ВЫБРАТЬ
ПредварительныеДанные.ОбщийГрафик,
ПредварительныеДанные.Дата,
// ...
ИЗ
ВТ_ПредварительныеДанные КАК ПредварительныеДанные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДанныеПроизводственногоКалендаря КАК ДанныеПроизводственногоКалендаря
ПО ПредварительныеДанные.ДатаДляСвязиСКалендарем = ДанныеПроизводственногоКалендаря.Дата
И ДанныеПроизводственногоКалендаря.ВидДня = ЗНАЧЕНИЕ(Перечисление.ВидыДнейПроизводственногоКалендаря.Праздник)
ГДЕ
// Фильтруем только те записи, которые нам действительно нужны
(НЕ ПредварительныеДанные.ОпределяющийЗаПраздничныеДни
ИЛИ ДанныеПроизводственногоКалендаря.Дата ЕСТЬ НЕ NULL)
В идеале, если производительность все еще недостаточна из-за ИЛИ в ГДЕ, такой запрос разбивают на два блока через ОБЪЕДИНИТЬ ВСЕ:
НЕ ПредварительныеДанные.ОпределяющийЗаПраздничныеДни = ИСТИНА.ДанныеПроизводственногоКалендаря.Дата ЕСТЬ НЕ NULL.Проанализируем, чего мы добились применяя данные методики оптимизации:
ВЫБОР и ДОБАВИТЬКДАТЕ в секции соединения, сделав условия поиска SARGable (то есть позволяющими использовать индексы таблиц СУБД).tempdb, так как теперь сервер не хранит гигантские промежуточные таблицы при вычислении связей.Идентификатор строки в документе "Резервы по оплате труда".Всегда помните золотое правило написания запросов в 1С: соединения таблиц должны происходить строго по проиндексированным полям (или полям простых типов) без использования математических функций, преобразований типов и конструкций "ВЫБОР" непосредственно в условии соединения ПО.