Ситуация, когда запрос в 1С годами работал «за доли секунды», а в один прекрасный день начал намертво вешать сервер с загрузкой процессора под 100%, — это классический сценарий деградации плана запроса. В данной статье мы подробно разберем, почему «протухают» запросы, как SQL Server принимает неверные решения и какими методами можно вернуть системе былую производительность. Проанализируем конкретный пример «упоротого» левого соединения и найдем оптимальные пути решения.
Рассмотрим ситуацию: при выполнении простого, на первый взгляд, запроса к справочнику характеристик и табличной части документов сервер уходит в глубокое пике. Исходный код запроса выглядел следующим образом:
ВЫБРАТЬ
ХарактеристикиНоменклатуры.Ссылка КАК ХарактеристикаНоменклатуры,
ХарактеристикиНоменклатуры.ДатаПеч КАК ДатаПеч,
ЗаказПокупателяУслуги.НомерСтроки КАК НомерСтроки
ИЗ
Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПокупателя.Услуги КАК ЗаказПокупателяУслуги
ПО ХарактеристикиНоменклатуры.Ссылка = ЗаказПокупателяУслуги.ХарактеристикаНоменклатуры
ГДЕ
ХарактеристикиНоменклатуры.ДатаПеч >= &ДатаНачала
И НЕ ХарактеристикиНоменклатуры.ПометкаУдаления
На первый взгляд, здесь нет ничего криминального. Однако при объеме данных около 900 000 записей в справочнике и 1.7 млн строк в табличной части, этот запрос начал выполняться за 31 секунду вместо привычных мгновений. Выясним причину такого поведения. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, а затем накладываем фильтры в секции ГДЕ, оптимизатор SQL Server может выбрать крайне неэффективную стратегию Nested Loops (вложенные циклы). Это приводит к тому, что для каждой строки из левой таблицы (которая прошла фильтр) сервер выполняет полный поиск в правой таблице.
Проанализируем ситуацию, почему это происходит «вдруг». В MS SQL Server существует механизм кэширования планов запросов. Когда запрос выполняется впервые, сервер строит «дорожную карту» его выполнения. Если в первый раз вы передали параметр &ДатаНачала, который отсекал 99% данных, сервер построил план, идеально подходящий для малого объема данных. Но со временем данных стало больше, или статистика распределения значений в таблице обновилась, и старый план стал катастрофически неэффективен. Это явление называется Parameter Sniffing.
Кроме того, SQL Server обновляет статистику не в реальном времени, а при изменении определенного процента данных (обычно около 20%). Как только объем накопленных данных в табличной части ЗаказПокупателя.Услуги перешагнул критический порог, оптимизатор «передумал» и заменил быстрый Hash Join на медленный Nested Loops.
Самый надежный способ «помочь» базе данных — это жестко ограничить набор данных, с которыми она работает, до того, как начнется соединение. Разберем по шагам, как переписать запрос с использованием временных таблиц:
Посмотрим на пример кода:
ВЫБРАТЬ
Характеристики.Ссылка КАК Ссылка,
Характеристики.ДатаПеч КАК ДатаПеч
ПОМЕСТИТЬ ВТ_Характеристики
ИЗ
Справочник.ХарактеристикиНоменклатуры КАК Характеристики
ГДЕ
Характеристики.ДатаПеч >= &ДатаНачала
И НЕ Характеристики.ПометкаУдаления
ИНДЕКСИРОВАТЬ ПО
Ссылка;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Характеристики.Ссылка КАК ХарактеристикаНоменклатуры,
ВТ_Характеристики.ДатаПеч КАК ДатаПеч,
ЗаказПокупателяУслуги.НомерСтроки КАК НомерСтроки
ИЗ
ВТ_Характеристики КАК ВТ_Характеристики
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПокупателя.Услуги КАК ЗаказПокупателяУслуги
ПО ВТ_Характеристики.Ссылка = ЗаказПокупателяУслуги.ХарактеристикаНоменклатуры
Такой подход гарантирует, что SQL Server не будет сканировать все 900 тысяч записей справочника при каждом обращении к табличной части. Он будет работать только с теми 369 строками, которые попали в выборку по дате.
Иногда (хотя это и менее надежно, чем временные таблицы) помогает перенос условий из секции ГДЕ в секцию ПО. Рассмотрим, как это выглядит в коде:
ВЫБРАТЬ
Характеристики.Ссылка КАК ХарактеристикаНоменклатуры,
Характеристики.ДатаПеч КАК ДатаПеч,
ЗаказПокупателяУслуги.НомерСтроки КАК НомерСтроки
ИЗ
Справочник.ХарактеристикиНоменклатуры КАК Характеристики
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПокупателя.Услуги КАК ЗаказПокупателяУслуги
ПО Характеристики.Ссылка = ЗаказПокупателяУслуги.ХарактеристикаНоменклатуры
И Характеристики.ДатаПеч >= &ДатаНачала
И (НЕ Характеристики.ПометкаУдаления)
Важное замечание: При ЛЕВОМ СОЕДИНЕНИИ условия на левую таблицу в секции ПО работают не так, как в ГДЕ. Они определяют, нужно ли присоединять правую таблицу к конкретной строке левой, но не исключают саму строку левой таблицы из результата. Для полной фильтрации строк все же рекомендуется использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ, если это допустимо бизнес-логикой, или предварительную выборку во временную таблицу.
Выясним причину, почему соединение с табличной частью может быть медленным само по себе. В 1С реквизиты табличных частей (например, ХарактеристикаНоменклатуры в таблице Услуги) по умолчанию не индексируются.
Если для этого реквизита в конфигураторе не стоит свойство Индексировать, то при соединении SQL Server вынужден делать Full Table Scan — полное сканирование таблицы из 1.7 млн строк для каждой характеристики из левой части. Это создает колоссальную нагрузку на дисковую подсистему и процессор.
Рекомендация: Всегда проверяйте наличие индекса у полей, по которым осуществляется связь (соединение) таблиц.
Чтобы подобные «сюрпризы» не повторялись, проанализируем регламенты обслуживания базы данных. Для стабильной работы 1С необходимо:
DBCC FREEPROCCACHE (использовать осторожно на рабочих базах!).В заключение отметим, что использование прямых соединений с огромными таблицами в 1С — это всегда риск. Профессиональным подходом считается предварительная фильтрация данных во ВременныеТаблицы с обязательным индексированием полей связи. Это не только решает текущую проблему зависания, но и страхует систему от подобных сбоев в будущем.