Многие разработчики 1С рано или поздно сталкиваются с загадочной ситуацией: при проведении документа (например, отчета о розничных продажах) система «зависает» на несколько минут. Однако, если извлечь проблемный запрос с помощью замера производительности и выполнить его в консоли запросов с теми же параметрами, он отрабатывает за 1–2 секунды. Рассмотрим подробнее, почему возникает такая колоссальная разница в скорости (иногда в десятки раз) и какие шаги необходимо предпринять для устранения этой проблемы.
Проблема носит комплексный характер и обычно связана как с особенностями работы оптимизатора MS SQL Server, так и с использованием антипаттернов при написании текстов запросов в платформе 1С. Проанализируем ситуацию и разберем основные причины и способы их решения.
Первое, на что стоит обратить внимание в клиент-серверном варианте базы данных на базе MS SQL Server — это механизм кэширования планов выполнения. Когда платформа 1С впервые отправляет запрос к СУБД, оптимизатор SQL строит план выполнения этого запроса, опираясь на параметры, переданные именно в этот момент.
Если в первый раз был передан, например, пустой массив номенклатуры, SQL Server построит один план и закэширует его для экономии времени при последующих вызовах. Когда при следующем проведении документа в этот же запрос передается массив из 1000 элементов, SQL Server берет старый кэшированный план. Для нового объема данных этот план оказывается катастрофически неэффективным, что и приводит к длительному выполнению.
В консоли запросов (или при изменении текста запроса хотя бы на один пробел) генерируется новый хэш, и SQL Server строит новый, оптимальный план под текущие параметры. Отсюда и возникает магия быстрого выполнения в консоли.
Как решить проблему:
DBCC FREEPROCCACHE
Важно: Выполнение этой команды сбрасывает кэш планов для всей СУБД, что вызовет кратковременную нагрузку на сервер из-за перестроения планов для всех активных запросов. Делайте это с осторожностью.Разберем типичную ошибку (антипаттерн) при написании запросов. Очень часто разработчики пытаются сделать универсальный запрос, встраивая конструкции ВЫБОР КОГДА или логическое ИЛИ прямо в параметры виртуальной таблицы (например, в условие виртуальной таблицы остатков).
Посмотрим на пример такого неоптимального кода:
РегистрНакопления.ТоварыВРознице.Остатки(
&Дата,
Склад В (&Склад)
И ВЫБОР
КОГДА &РазворачиватьДоКачества ТОГДА ИСТИНА
ИНАЧЕ Качество = ЗНАЧЕНИЕ(Справочник.Качество.Новый)
КОНЕЦ
) КАК ТаблицаОстатков
При трансляции этого кода в язык SQL платформа генерирует сложные условия с оператором CASE WHEN. Оптимизатор СУБД не может применить эффективный поиск по индексу (Index Seek) при наличии таких условий. В результате SQL Server вынужден выполнять полное сканирование таблицы (Index Scan). В большой базе это означает перебор миллионов записей вместо быстрого поиска.
Как решить проблему:
Избавьтесь от логических ветвлений в параметрах ВТ. Лучший подход — динамическое формирование текста запроса перед его выполнением. Посмотрим, как это реализовать:
// Базовый текст запроса с маркером
ТекстЗапроса = "ВЫБРАТЬ ... ИЗ РегистрНакопления.ТоварыВРознице.Остатки(&Дата, Склад В (&Склад) И &УсловиеПоКачеству) ...";
// Формируем условие в коде
Если РазворачиватьДоКачества Тогда
ТекстУсловия = "ИСТИНА";
Иначе
ТекстУсловия = "Качество = ЗНАЧЕНИЕ(Справочник.Качество.Новый)";
КонецЕсли;
// Заменяем маркер в тексте запроса
Запрос.Текст = СтрЗаменить(ТекстЗапроса, "&УсловиеПоКачеству", ТекстУсловия);
Такой подход заставляет 1С передать в СУБД простой и понятный SQL-запрос, который идеально ложится на индексы.
Выясним еще одну причину резкого падения производительности. В 1С часто используется условие вида ГДЕ Номенклатура В (&МассивНоменклатуры). Если при некоторых настройках этот массив оказывается пустым, платформа транслирует его в SQL в специфические фиктивные проверки. Для СУБД это ломает логику построения плана, индексы игнорируются, и база снова сканирует всю таблицу.
Как решить проблему:
Если массив может быть пустым (и это подразумевает, что отбор делать не нужно), текст запроса нужно динамически изменять, либо использовать дополнительный булево параметр.
Вариант с динамическим текстом:
Если МассивНоменклатуры.Количество() > 0 Тогда
Запрос.Текст = СтрЗаменить(Запрос.Текст, "&УсловиеНоменклатура", "Номенклатура В (&МассивНоменклатуры)");
Запрос.УстановитьПараметр("МассивНоменклатуры", МассивНоменклатуры);
Иначе
// Если массив пустой, убираем отбор
Запрос.Текст = СтрЗаменить(Запрос.Текст, "&УсловиеНоменклатура", "ИСТИНА");
КонецЕсли;
Разберем ситуацию с блокировками. Запрос в консоли выполняется в режиме обычного чтения. Запрос в модуле объекта при проведении документа выполняется внутри транзакции.
Особое внимание нужно уделить конструкции ДЛЯ ИЗМЕНЕНИЯ. В SQL она транслируется в хинт UPDLOCK. Если ваш запрос содержит неоптимальные условия (например, из пунктов 2 и 3) и SQL Server решает просканировать всю таблицу (Index Scan), то блокировка UPDLOCK будет наложена на все просканированные строки. Это приводит к колоссальным затратам ресурсов сервера на управление блокировками (и может вызвать эскалацию блокировок до уровня всей таблицы). Другие пользователи в этот момент будут "висеть" в ожидании снятия блокировки.
Кроме того, если в запросе используется менеджер временных таблиц (ПОМЕСТИТЬ ВТ), то создание и запись во временную таблицу (tempdb) внутри транзакции также накладывает дополнительные накладные расходы.
Подведем итоги. Если вы столкнулись с десятикратной разницей в скорости выполнения запроса, выполните следующие шаги:
ВЫБОР КОГДА и ИЛИ из параметров виртуальных таблиц. Замените их динамической сборкой текста через СтрЗаменить.В (&Массив) не передаются пустые коллекции. Если коллекция пуста — динамически вырезайте это условие из текста запроса.EXEC sp_updatestats).DBCC FREEPROCCACHE на SQL сервере.Применение этих правил гарантированно избавит вас от эффекта "в консоли летает, в базе тормозит" и сделает работу пользователей комфортной.