При работе с крупными базами данных под управлением 1С:ERP Управление предприятием 2 (особенно когда справочник Номенклатура разрастается до 1 000 000 и более элементов) пользователи часто сталкиваются с критической проблемой производительности. При попытке подобрать товар в документы продажи с установленным флагом «Только в наличии» форма подбора зависает на длительное время (от 10 до 15 минут).
В этой статье мы подробно разберем, почему типовой функционал начинает давать сбой на больших объемах данных, проанализируем поведение СУБД на уровне планов запросов и рассмотрим четыре эффективных способа решения этой проблемы.
Для того чтобы понять, почему происходит зависание, нам необходимо проанализировать логику работы динамических списков платформы 1С и СУБД (например, MS SQL Server) в двух разных состояниях формы подбора.
Состояние 1: Флаг «Только в наличии» снят.
В этом режиме динамический список работает практически мгновенно. Платформа формирует SQL-запрос с конструкцией SELECT TOP 45 (или другим небольшим количеством строк, необходимым для заполнения текущего экрана пользователя). Оптимизатор СУБД видит, что данные упорядочены по наименованию номенклатуры. Он обращается к индексу по наименованию справочника Номенклатура, быстро считывает первые 45 записей и выполняет точечные и очень быстрые соединения (операторы Nested Loops / Index Seek) с таблицами цен и остатков только для этих 45 позиций. Нагрузка на систему минимальна.
Состояние 2: Флаг «Только в наличии» установлен.
Как только пользователь взводит флаг, в секцию ГДЕ запроса динамического списка добавляется условие фильтрации по остаткам (например, СвободныеОстатки.ВНаличииОстаток > 0). Это в корне меняет стратегию оптимизатора СУБД:
LEFT JOIN во внутреннее соединение (INNER JOIN). СУБД больше не может просто вернуть первые строки справочника.tempdb (оператор Lazy Spool / Index Spool). При объеме справочника в 1,6 млн записей создание этого spool-объекта на диске полностью парализует работу СУБД, вызывая долгое зависание.TOP 45.Проанализируем структуру типового запроса, который формируется формой подбора 1С:ERP. Обратите внимание на обилие вложенных условий ВЫБОР КОГДА и ресурсоемких математических операций пересчета упаковок:
ВЫБРАТЬ ПЕРВЫЕ 45
СправочникНоменклатура.Ссылка КАК Ссылка,
СправочникНоменклатура.Артикул КАК Артикул,
СправочникНоменклатура.Код КАК Код,
СправочникНоменклатура.Наименование КАК Наименование,
СправочникНоменклатура.ЭтоГруппа КАК ЭтоГруппа,
ВЫБОР
КОГДА СправочникНоменклатура.ТипНоменклатуры В (ЗНАЧЕНИЕ(Перечисление.ТипыНоменклатуры.Товар), ЗНАЧЕНИЕ(Перечисление.ТипыНоменклатуры.МногооборотнаяТара))
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ЭтоТовар,
ЕСТЬNULL(ЦеныНоменклатуры.Упаковка, ЗНАЧЕНИЕ(Справочник.УпаковкиЕдиницыИзмерения.ПустаяСсылка)) КАК Упаковка,
ВЫРАЗИТЬ(ЕСТЬNULL(ЦеныНоменклатуры.Цена, 0) КАК ЧИСЛО(31, 2)) КАК Цена,
ВЫРАЗИТЬ(ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) / ЕСТЬNULL(ВЫБОР
КОГДА ЦеныНоменклатуры.Упаковка.ТипИзмеряемойВеличины = ЗНАЧЕНИЕ(Перечисление.ТипыИзмеряемыхВеличин.Вес)
И ЦеныНоменклатуры.Номенклатура.ВесИспользовать
И ЕСТЬNULL(ЦеныНоменклатуры.Упаковка.Знаменатель, 0) <> 0
ТОГДА ВЫРАЗИТЬ(ЦеныНоменклатуры.Упаковка.Числитель / ЦеныНоменклатуры.Упаковка.Знаменатель КАК ЧИСЛО(15, 7))
ИНАЧЕ 1
КОНЕЦ, 1) КАК ЧИСЛО(15, 3)) КАК ВНаличииОстаток
ИЗ
Справочник.Номенклатура КАК СправочникНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ВидЦены = &ВидЦены) КАК ЦеныНоменклатуры
ПО (ЦеныНоменклатуры.Номенклатура = СправочникНоменклатура.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(, Склад = &Склад) КАК СвободныеОстатки
ПО (СвободныеОстатки.Номенклатура = СправочникНоменклатура.Ссылка)
ГДЕ
НЕ СправочникНоменклатура.ЭтоГруппа
И ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) > 0
УПОРЯДОЧИТЬ ПО
Наименование
Когда мы раскомментируем условие ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) > 0, СУБД теряет возможность быстрого отсечения строк. Выяснив причину деградации производительности, перейдем к рассмотрению способов решения проблемы.
Самый эффективный архитектурный способ победить медленную работу формы — изменить приоритеты таблиц в запросе динамического списка при помощи расширения конфигурации.
Если флаг «Только в наличии» активен, мы должны сделать основной таблицей запроса виртуальную таблицу остатков, а сам справочник Номенклатура присоединять к ней через ЛЕВОЕ СОЕДИНЕНИЕ. Разберем этот подход подробнее:
Реализовать это можно в расширении формы подбора, перехватив событие изменения флага «Только в наличии» и динамически подменив текст запроса динамического списка СписокНоменклатуры.
Этот метод является современным стандартом разработки на платформе 1С:Предприятие 8.3. Мы можем полностью исключить ресурсоемкие соединения с таблицами цен и остатков из основного запроса динамического списка.
Разберем по шагам, как реализовать этот алгоритм:
Номенклатура (ссылка, код, артикул, наименование). Никаких виртуальных таблиц остатков и срезов цен в основном запросе быть не должно.ПриПолученииДанныхНаСервере мы получаем массив только тех строк, которые реально отображаются на экране пользователя (обычно это не более 30–45 строк).Этот метод полностью исключает влияние оптимизатора СУБД на скорость работы списка, гарантируя мгновенный отклик интерфейса.
Если конфигурацию необходимо обновить с минимальным вмешательством в логику построения динамического списка, мы можем применить метод предварительного кэширования ключей.
При установке флага «Только в наличии» мы выполняем на сервере быстрый и легкий запрос, выбирающий исключительно ссылки номенклатуры, имеющие остаток на выбранном складе:
ВЫБРАТЬ
СвободныеОстатки.Номенклатура КАК Номенклатура
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(, Склад = &Склад) КАК СвободныеОстатки
Результат этого запроса (массив ссылок) мы передаем в параметры отбора динамического списка:
ЭлементОтбора = СписокНоменклатуры.Отбор.Элементы.Добавить(Тип("ЭлементОтбораКомпоновкиДанных"));
ЭлементОтбора.ЛевоеЗначение = Новый ПолеКомпоновкиДанных("Ссылка");
ЭлементОтбора.ВидСравнения = ВидСравненияКомпоновкиДанных.ВСписке;
ЭлементОтбора.ПравоеЗначение = МассивНоменклатурыСОстатками;
ЭлементОтбора.Использование = ИСТИНА;
Поскольку массив содержит только те позиции, которые физически есть на складе, СУБД мгновенно выполняет поиск по индексу ID (первичному ключу), что исключает необходимость сканирования всего справочника.
Если изменение конфигурации или написание расширений по каким-то причинам нежелательно, проблему можно локализовать силами системного администратора на уровне сервера баз данных.
Шаг 1: Использование Query Store (Хранилище запросов)
В MS SQL Server необходимо включить функцию Query Store, отследить тяжелый запрос динамического списка подбора (его легко узнать по оператору Lazy Spool и высокой стоимости выполнения) и принудительно зафиксировать для него заведомо быстрый план выполнения (Force Plan), который использовался до наложения тяжелого отбора.
Шаг 2: Создание Plan Guides (Руководств планов)
Для данного SQL-запроса можно создать руководство плана и жестко прописать хинты оптимизатору. Например, использование хинта OPTION (RECOMPILE) заставит SQL Server перестраивать план при каждом вызове с учетом конкретных параметров склада, а хинт OPTION (QUERYTRACEON 8690) принудительно запретит оптимизатору использовать spool-операторы (что уберет парализующую нагрузку на TempDB).
Шаг 3: Обновление статистики с FULLSCAN
Часто причиной выбора неэффективного плана (включая злополучный Lazy Spool) является устаревшая или неточная статистика СУБД. Обычного регламентного обновления статистики может не хватать. Рекомендуется настроить регламентное задание на ежесуточное обновление статистики с параметром FULLSCAN для таблиц справочника Номенклатуры (_Reference...) и регистров цен и остатков.
Мы выяснили причину зависания подбора в 1С:ERP при наложении фильтра по наличию и разобрали четыре способа борьбы с этой проблемой. Для систем с высокой интенсивностью продаж и миллионными справочниками наиболее надежным и перспективным решением является перенос расчетов в событие ПриПолученииДанныхНаСервере (Решение №2) либо динамический реверс запроса (Решение №1). Эти подходы гарантируют стабильную и быструю работу системы вне зависимости от роста объема базы данных.