Почему тормозит поиск в динамическом списке 1С и как победить конструкцию ESCAPE в MS SQL Server?

Каждый разработчик 1С рано или поздно сталкивается с ситуацией, когда пользователи начинают жаловаться на медленную работу динамических списков. Особенно это заметно в крупных базах при попытке выполнить обычный контекстный поиск по началу строки или по вхождению (режим "содержит"). Кажется, что задача тривиальна, но под капотом платформы и СУБД начинают происходить странные вещи.

В этой статье мы подробно проанализируем, почему стандартный поиск в динамических списках 1С на СУБД MS SQL Server может выполняться по несколько секунд, какую роль в этом играет загадочная конструкция ESCAPE, и какими способами можно вернуть производительность системы на прежний высокий уровень.

В чем суть проблемы: пример из практики

Давайте рассмотрим конкретный живой пример. Пользователь открывает динамический список документов и выполняет контекстный поиск по реквизиту Клиент. На уровне СУБД MS SQL Server платформа 1С генерирует параметризованный запрос следующего вида:

exec sp_executesql N'
SELECT TOP 45
T1._IDRRef,
T1._Version,
T1._Fld7731RRef
FROM dbo._Document309 T1 
LEFT OUTER JOIN dbo._Reference162 T2 
ON T1._Fld7731RRef = T2._IDRRef
WHERE (T2._Description LIKE P1 ESCAPE ''/'')
ORDER BY (T1._Date_Time)',N'P1 nvarchar(4000)',N'%кукуку%'

Такой же запрос СУБД получает, если пользователь вручную настраивает отбор в списке с видом сравнения "Содержит". В обычных условиях этот запрос может выполняться мучительно долго — например, около 3 секунд. Однако, если мы проведем эксперимент и выполним тот же самый запрос, но вручную удалим из него конструкцию ESCAPE '/', время выполнения сократится до сотых долей секунды (0.15–0.2 секунды)!

Выясним причину такого поведения системного оптимизатора MS SQL Server и разберем по шагам, как решить эту проблему.

Почему MS SQL Server пасует перед ESCAPE?

Для начала разберемся, зачем вообще платформа 1С добавляет конструкцию ESCAPE '/' в запрос. Это штатное и жестко зашитое поведение платформы. Оно необходимо для обеспечения безопасности и корректности поиска. Если пользователь решит ввести в строку поиска спецсимволы СУБД (например, знак процента %, нижнее подчеркивание _ или квадратные скобки [), платформа должна экранировать их с помощью указанного символа косой черты (слэша). Это предотвращает искажение логики запроса и защищает систему от потенциальных SQL-инъекций.

Однако для оптимизатора запросов MS SQL Server (Query Optimizer) наличие ESCAPE становится серьезным препятствием. Проанализируем, что происходит внутри СУБД:

1. Проблема оценки кардинальности (Cardinality Estimation): Когда оптимизатор строит план выполнения для обычного оператора LIKE без дополнительных инструкций, он может эффективно использовать гистограммы статистики по индексированному полю. Он довольно точно оценивает, сколько строк вернет данный предикат. Но как только в запросе появляется ключевое слово ESCAPE, предикат становится "непрозрачным" для стандартных механизмов оценки. Оптимизатор часто переходит на фиксированные («заглушечные») оценки вероятности.
2. Неверный выбор плана выполнения: Из-за неверной оценки количества строк оптимизатор строит неоптимальный план. Например, вместо быстрого поиска по индексу (Index Seek) он выбирает полное сканирование таблицы (Index Scan или Table Scan).
3. Влияние сортировки (ORDER BY): Ситуация катастрофически усугубляется, если в запросе присутствует сортировка ORDER BY (а в динамических списках она есть практически всегда — например, по дате документа). Из-за завышенной или заниженной оценки кардинальности SQL Server может выбрать неверный тип соединения таблиц (например, ресурсоемкий Hash Join вместо легкого Nested Loops) или попытаться отсортировать огромный массив данных в памяти TempDB, что приводит к задержкам. Если убрать ORDER BY, то даже с неоптимальным планом запрос может отработать быстро, но вместе они дают синергетический эффект замедления.

Поскольку штатными средствами 1С заставить динамический список принудительно убрать ESCAPE невозможно (так как логика формирования SQL-текста находится на уровне ядра платформы), нам приходится искать альтернативные пути решения проблемы.

Решение №1: Использование сценарных планов (Plan Guides)

Если текст запроса, генерируемый динамическим списком, стабилен и неизменен (меняются только значения параметров), мы можем применить механизм СУБД MS SQL Server под названием Plan Guides (Руководства планов).

Этот механизм позволяет "перехватить" запрос по его точному текстовому шаблону на стороне СУБД и принудительно навязать ему правильный план выполнения или добавить оптимизирующие подсказки (хинты) без изменения кода самой конфигурации 1С. Рассмотрим, как это сделать:

1. Сначала необходимо получить точный текст запроса, который вызывает проблему, с помощью трассировки SQL Server Profiler или технологического журнала 1С.
2. Создаем руководство плана с помощью системной хранимой процедуры sp_create_plan_guide. Мы можем принудительно указать оптимизатору использовать конкретный индекс или задать определенный порядок соединения таблиц. Посмотрим на пример создания такого руководства:

EXEC sp_create_plan_guide 
    @name = N'Guide_Optimize_DocSearch', 
    @stmt = N'SELECT TOP 45 T1._IDRRef, T1._Version, T1._Fld7731RRef FROM dbo._Document309 T1 LEFT OUTER JOIN dbo._Reference162 T2 ON T1._Fld7731RRef = T2._IDRRef WHERE (T2._Description LIKE @P1 ESCAPE ''/'') ORDER BY (T1._Date_Time)', 
    @type = N'SQL', 
    @module_or_batch = NULL, 
    @params = N'@P1 nvarchar(4000)', 
    @hints = N'OPTION (FORCE ORDER, LOOP JOIN)';

Важное примечание: Текст запроса в параметре @stmt должен совпадать с тем, что отправляет 1С, символ в символ, включая все пробелы и переносы строк. Использование Plan Guides позволяет стабилизировать план выполнения и убрать задержки, вызванные неверной оценкой кардинальности.

Решение №2: Использование Query Store (Хранилище запросов)

Если вы работаете на современных версиях MS SQL Server (начиная с 2016 и выше), то вместо сложных манипуляций со сценарными планами можно задействовать штатный инструмент Query Store (Хранилище запросов). Это гораздо более удобная и визуальная альтернатива.

Рассмотрим порядок действий для оптимизации через Query Store:

1. Убедитесь, что для вашей базы данных включена функция Query Store (это можно сделать в свойствах базы в SQL Server Management Studio (SSMS)).
2. Найдите проблемный запрос с конструкцией ESCAPE в отчетах Query Store (раздел "Запросы с наибольшим потреблением ресурсов" или "Запросы с ухудшившейся производительностью").
3. Проанализируйте историю планов выполнения этого запроса. Найдите тот план, который выполнялся быстро (например, до того, как статистика обновилась неудачным образом).
4. Нажмите кнопку Force Plan (Принудительно использовать план) для быстрого и эффективного плана. Теперь СУБД всегда будет использовать именно его, игнорируя попытки оптимизатора построить новый плохой план из-за влияния ESCAPE.

Решение №3: Тонкая настройка СУБД (Флаги трассировки и статистика)

Иногда исправить поведение оптимизатора на запросах с ESCAPE можно системными настройками СУБД. Проанализируем две важные настройки:

1. Флаг трассировки 4199. Этот флаг активирует все исправления оптимизатора запросов, выпущенные Microsoft в рамках накопительных обновлений. Часто именно этот флаг возвращает оптимизатору способность адекватно оценивать селективность условий с ESCAPE. Его можно включить глобально для сервера или активировать на уровне конкретной базы данных.

2. Версия Compatibility Level (Уровень совместимости базы данных). При переходе на новые версии SQL Server (например, с 2008 на 2016 или 2019) кардинально меняется модель оценки кардинальности (Cardinality Estimator). Если после обновления СУБД динамические списки стали работать медленнее, попробуйте временно вернуть уровень совместимости базы данных на предыдущее значение (например, со 130 на 110) или включить использование устаревшей модели оценки кардинальности с помощью опции базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

3. Регулярное обновление статистики с полной очисткой. Так как при наличии конструкции ESCAPE точность гистограмм критически важна, настройте регламентное задание на обновление статистики по проблемным таблицам с параметром FULLSCAN. Это минимизирует вероятность ошибок оптимизатора.

Решение №4: Переход на Полнотекстовый поиск (ППС) 1С

Если вы хотите решить проблему кардинально на уровне кода 1С и полностью отказаться от тяжелых запросов СУБД с оператором LIKE %...%, рассмотрите возможность внедрения механизмов полнотекстового поиска платформы.

Полнотекстовый поиск 1С работает по совершенно иным алгоритмам. Он задействует специализированные полнотекстовые индексы. При этом разработчик может переопределить стандартное поведение поиска в динамическом списке:

1. Отключить стандартный контекстный поиск для списка.
2. Вывести на форму собственное поле ввода для поиска.
3. При изменении текста в этом поле программно получать массив ссылок, удовлетворяющих условию, через встроенный механизм ПолнотекстовыйПоиск.
4. Устанавливать простой и быстрый отбор по массиву найденных ссылок в динамическом списке:

Список.Отбор.Элементы.Очистить();
ГруппаОтбора = Список.Отбор.Элементы.Добавить(Тип("ГруппаЭлементовОтбораКомпоновкиДанных"));
// Установка отбора по массиву найденных идентификаторов
ЭлементОтбора = ГруппаОтбора.Элементы.Добавить(Тип("ЭлементОтбораКомпоновкиДанных"));
ЭлементОтбора.ЛевоеЗначение = Новый ПолеКомпоновкиДанных("Ссылка");
ЭлементОтбора.ВидСравнения = ВидСравненияКомпоновкиДанных.ВСписке;
ЭлементОтбора.ПравоеЗначение = МассивНайденныхСсылок;
ЭлементОтбора.Использование = Истина;

Такой подход полностью исключает появление ресурсоемких SQL-запросов с конструкцией ESCAPE и позволяет осуществлять мгновенный поиск даже по базам данных объемом в терабайты.

Резюме

Мы подробно проанализировали особенности работы оптимизатора MS SQL Server при получении запросов от динамических списков 1С. Подведем итог:

• Конструкция ESCAPE '/' добавляется платформой 1С принудительно для безопасного экранирования спецсимволов.
• Основной ущерб производительности наносит сбой в оценке количества строк оптимизатором SQL Server, из-за чего строится неэффективный план запроса в сочетании с ORDER BY.
• Наиболее быстрыми административными решениями без изменения метаданных являются настройка Plan Guides или фиксация планов через Query Store.
• Для системного решения проблемы производительности на больших объемах данных рекомендуется переводить поиск на встроенные механизмы Полнотекстового поиска 1С.