В практике разработки на платформе 1С часто встречается парадоксальная ситуация: простой и оптимизированный (на первый взгляд) запрос, который обычно выполняется за сотые доли секунды, внезапно начинает «тормозить», занимая 20 и более секунд. При этом любые незначительные изменения в тексте запроса (добавление пробела или перестановка условий) временно возвращают былую скорость. В этой статье мы детально разберем природу этого явления, проанализируем причины и внедрим надежное решение.
Ситуация, описанная в проблеме, указывает на классическую проблему Parameter Sniffing (захват параметров) в MS SQL Server. Давайте разберем этот механизм по шагам. Когда запрос выполняется впервые, SQL-сервер строит план выполнения — маршрут, по которому он будет искать данные. Он смотрит на текущую статистику распределения данных и на значения параметров, которые были переданы в этот первый раз.
Если при первом запуске был передан параметр (например, Карта), по которому в базе всего 2-3 записи, SQL-сервер решает, что оптимально использовать поиск по индексу (Index Seek) с последующим добором данных (Key Lookup). Этот план кешируется. Если через некоторое время статистика обновляется или передается другой параметр, для которого этот план неэффективен (например, данных много), SQL-сервер продолжает использовать старый «кривой» план из кеша. В результате вместо быстрого поиска мы получаем тяжелое сканирование всей таблицы.
Почему помогает изменение текста запроса? Любое изменение текста (даже перестановка полей в ВЫБРАТЬ) заставляет SQL-сервер считать этот запрос новым и строить для него свежий план выполнения. Однако это лишь временная мера, так как новый план тоже может со временем стать неактуальным.
Рассмотрим исходный запрос, который вызывает проблемы:
ВЫБРАТЬ
Протокол.Ссылка,
Протокол.НеИсполнен
ИЗ
Документ.Протокол КАК Протокол
ГДЕ
Протокол.Карта = &Карта
И Протокол.НеИсполнен = ЛОЖЬ
И Протокол.Вид В(&ВидыПротоколов)
И Протокол.Проведен = ИСТИНА
И Протокол.Дата <= &НаДату
УПОРЯДОЧИТЬ ПО
Протокол.Дата
Здесь есть несколько «подводных камней»:
Карта имеет составной тип, SQL-серверу сложнее оценивать селективность индекса.Карта, то для получения полей НеИсполнен, Проведен и Дата серверу приходится постоянно обращаться к основной таблице документа (Key Lookup), что крайне накладно при большом количестве записей.Наиболее эффективный способ «заставить» оптимизатор SQL работать правильно — это разделить запрос на части. Сначала мы выберем идентификаторы (ссылки) документов по самому селективному полю, а затем получим все остальные данные. Это гарантирует использование нужного индекса.
Рассмотрим правильную реализацию с использованием временной таблицы:
// Шаг 1: Отбираем только ссылки по основному индексу (поле Карта)
ВЫБРАТЬ
Протокол.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Протоколы
ИЗ
Документ.Протокол КАК Протокол
ГДЕ
Протокол.Карта = &Карта;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем финальные данные, накладывая остальные фильтры
ВЫБРАТЬ
ВТ.Ссылка,
ВТ.Ссылка.НеИсполнен
ИЗ
ВТ_Протоколы КАК ВТ
ГДЕ
ВТ.Ссылка.НеИсполнен = ЛОЖЬ
И ВТ.Ссылка.Вид В(&ВидыПротоколов)
И ВТ.Ссылка.Проведен = ИСТИНА
И ВТ.Ссылка.Дата <= &НаДату
УПОРЯДОЧИТЬ ПО
ВТ.Ссылка.Дата
Важный нюанс: Обратите внимание на использование ВТ.Ссылка.Поле во втором запросе. Хотя в 1С обращение через точку считается «плохим тоном» из-за неявных соединений, в данном конкретном случае (когда мы уже отобрали 100-200 записей во временную таблицу) это позволяет SQL-серверу выполнить точечные обращения к кластерному индексу, что работает молниеносно. Однако еще лучше будет выбрать все необходимые поля для фильтрации уже в первом запросе, если они входят в индекс.
Если база данных серверная, критически важно следить за актуальностью статистики. В системе 1С данные меняются очень интенсивно. Если статистика обновляется раз в неделю, SQL-сервер «слепнет» уже на второй день работы.
Разберем рекомендации по настройке сервера:
WITH FULLSCAN, чтобы сервер видел полную картину распределения ссылок.DBCC FREEPROCCACHE, но помните, что это сбросит планы для всех запросов и может вызвать кратковременный скачок нагрузки на CPU.Проанализируем ситуацию с индексами. В исходном сообщении упоминается, что индексы по булевым полям (НеИсполнен) и малочисленным справочникам (Вид) были убраны. Это верное решение, так как низкая селективность таких полей только мешает оптимизатору.
Однако, если вы используете современные версии платформы (начиная с 8.3.27), вы можете воспользоваться механизмом включающих (INCLUDE) индексов. Это позволяет добавить в индекс по полю Карта дополнительные поля (Проведен, Дата, НеИсполнен) в качестве «покрывающих». В этом случае SQL-сервер найдет все необходимые данные прямо в теле индекса и ему вообще не придется обращаться к основной таблице документа. Это снимает проблему Key Lookup раз и навсегда.
Чтобы ваш запрос работал стабильно и быстро, придерживайтесь следующих правил:
Карте).В (&Массив) в основном теле запроса к огромным таблицам, если состав массива постоянно меняется. Лучше предварительно выгрузить массив во временную таблицу и соединиться с ней.Применение подхода с временной таблицей, как показано в Решении №1, является «золотым стандартом» оптимизации в 1С, позволяющим надежно победить нестабильность планов запросов без внесения изменений в настройки самого SQL-сервера.