В практике разработчика 1С периодически возникают ситуации, когда, казалось бы, простой запрос к небольшой таблице начинает выполняться десятки секунд. При анализе плана выполнения SQL Server мы можем увидеть пугающие цифры: система сообщает о сканировании десятков миллионов строк, хотя в самой таблице их всего пара сотен тысяч. В этой статье мы разберем конкретный кейс на базе конфигурации "Альфа-Авто", выясним математику появления "лишних" строк в плане и научимся оптимизировать такие запросы.
Рассмотрим ситуацию: в управляемой форме используется динамический список. Основной запрос выполняет ЛЕВОЕ СОЕДИНЕНИЕ между таблицей Документ.ЗаказНаряд и Документ.УРВ_ПакетРабот. В таблице УРВ_ПакетРабот всего 143 000 записей. Однако в плане выполнения SQL мы видим, что фактическое количество обработанных строк составляет более 61 миллиона.
Первое, что приходит в голову — устаревшая статистика или отсутствие индексов. Но даже принудительное обновление статистики и обслуживание индексов не меняют картину. Разберемся, почему это происходит.
Проанализируем план запроса глубже. Главный секрет кроется в операторе Nested Loops (вложенные циклы). Этот механизм работает по следующему принципу: для каждой строки, полученной из "внешней" (ведущей) таблицы, SQL Server выполняет поиск во "внутренней" таблице.
Разберем математику из нашего примера:
_Document5983 (это и есть наш УРВ_ПакетРабот), в которой 143 000 строк.Таким образом, цифра в 61 миллион — это не количество данных в базе, а суммарное количество строк, которые пришлось "просмотреть" серверу в процессе выполнения циклического соединения. Это происходит, когда оптимизатор ошибочно ожидает, что из внешней таблицы придет всего 1 строка, и выбирает стратегию Nested Loops вместо более эффективного в данном случае Hash Match или Merge Join.
Одной из причин, почему оптимизатор SQL Server не может эффективно использовать индексы, является использование условий, которые делают запрос не-SARGable (Search ARGumentable). В нашем примере в секции ГДЕ или в условии соединения используется конструкция:
ВЫБОР
КОГДА УРВ_ПакетРабот.Проведен ТЫГДА ЛОЖЬ
ИНАЧЕ ИСТИНА
КОНЕЦ
Когда мы оборачиваем поле в ВЫБОР КОГДА (CASE на уровне SQL), мы лишаем оптимизатор возможности выполнить быстрый поиск по индексу (Index Seek). Вместо этого сервер выполняет полное сканирование (Index Scan), что в сочетании с Nested Loops и приводит к катастрофическому падению производительности.
Решение: заменим сложный выбор на прямое логическое условие. Рассмотрим пример оптимизации запроса:
ВЫБРАТЬ ПЕРВЫЕ 1000
Т.Ссылка
ИЗ
Документ.ЗаказНаряд КАК Т
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Документ.УРВ_ПакетРабот КАК Т2
ПО
Т.Ссылка = Т2.ДокументОснование
ГДЕ
Т2.Проведен = ЛОЖЬ
Прямое сравнение Т2.Проведен = ЛОЖЬ позволяет SQL Server прозрачно увидеть селективность данных и использовать индекс по полю Проведен.
В теме обсуждения прозвучал важный момент: использование ключевого слова ПЕРВЫЕ в тексте запроса динамического списка. Нужно понимать, что платформа 1С:Предприятие 8.3 управляет выборкой данных в динамических списках самостоятельно.
Проанализируем особенности поведения платформы:
ПЕРВЫЕ в тексте динамического списка может вызвать ошибку при редактировании.ПЕРВЫЕ, так как она сама добавляет параметры TOP или OFFSET...FETCH для порционного считывания данных (по 25, 45 или 100 строк), необходимых для заполнения видимой области формы.Ситуация осложняется, когда пользователь начинает использовать поиск в динамическом списке. Рассмотрим случай, когда в запрос передается параметр типа "%215%".
Использование символа процента в начале строки поиска гарантированно приводит к Full Scan (полному сканированию таблицы), так как индексы по строковым полям в SQL Server строятся слева направо. Индекс не может помочь найти подстроку в середине текста.
Также не стоит забывать про эффект Parameter Sniffing. SQL Server кэширует план запроса, исходя из первых переданных параметров. Если первый поиск был очень специфичным (вернул 1 строку), сервер построит план с Nested Loops. Если следующий поиск будет массовым (например, по пустой строке или часто встречающейся подстроке), сервер попытается использовать старый "быстрый" план, который на больших данных превратится в "медленный".
Подведем итоги и проанализируем шаги, которые помогут исправить ситуацию с медленным запросом:
ВЫБОР КОГДА в простые логические операции в секции ГДЕ или в условиях связи ПО.ДокументОснование), проиндексированы. В 1С это достигается установкой свойства "Индексировать" у реквизита.ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Это дает оптимизатору больше свободы в выборе ведущей таблицы.Основную таблицу в свойствах. Это позволит платформе корректно генерировать быстрые запросы для порционного вывода данных.Соблюдение этих правил позволит избежать появления "фантомных" миллионов строк в планах запросов и обеспечит стабильную работу интерфейса для пользователей.