Почему зависает запрос к виртуальной таблице ДвиженияССубконто на SQL и как его ускорить?

← На главную

Разработчики систем на платформе 1С частенько сталкиваются с ситуацией, когда один и тот же запрос на файловой базе «летает» за доли секунды, а в клиент-серверном варианте (особенно на таких СУБД, как MS SQL Server, PostgreSQL или IBM DB2) зависает намертво или выполняется непозволительно долго — по 15–20 минут. Особенно ярко эта проблема проявляется при работе со сложными виртуальными таблицами регистров бухгалтерии, такими как ДвиженияССубконто.

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

Давайте разберем эту проблему по косточкам.

Причина 1. Фильтрация в секции ГДЕ вместо параметров виртуальной таблицы

Проанализируем исходный запрос, который вызывал у автора темы наибольшие трудности:


ВЫБРАТЬ
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма,
    ХозрасчетныйДвиженияССубконто.КоличествоКт КАК Количество,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК Товар,
    ХозрасчетныйДвиженияССубконто.Регистратор КАК Регистратор,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2.ЕдиницаИзмерения КАК ЕдиницаИзмерения
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто КАК ХозрасчетныйДвиженияССубконто
ГДЕ
    ХозрасчетныйДвиженияССубконто.Регистратор В(&МассивОбъектов)
    И ХозрасчетныйДвиженияССубконто.Содержание = "Списание запасов на затраты производства"
СГРУППИРОВАТЬ ПО
    ХозрасчетныйДвиженияССубконто.СубконтоКт2,
    ХозрасчетныйДвиженияССубконто.КоличествоКт,
    ХозрасчетныйДвиженияССубконто.Регистратор,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2.ЕдиницаИзмерения,
    ХозрасчетныйДвиженияССубконто.НомерСтроки
УПОРЯДОЧИТЬ ПО
    ХозрасчетныйДвиженияССубконто.НомерСтроки

Главная архитектурная ошибка здесь заключается в том, что отбор по регистратору и содержанию операции наложен в секции ГДЕ, а не в параметрах виртуальной таблицы. Давайте разберемся, почему для СУБД это критично.

Виртуальная таблица ДвиженияССубконто не существует физически в базе данных. На уровне SQL-сервера это сложнейший вложенный запрос (Subquery), который динамически собирается платформой 1С. Платформа берет основную таблицу проводок (физическую таблицу движений) и соединяет ее с таблицами значений субконто.

Если мы не указываем параметры отбора непосредственно в скобках виртуальной таблицы (то есть пишем просто ДвиженияССубконто КАК...), транслятор 1С генерирует SQL-запрос, который заставляет СУБД:

Собрать абсолютно все движения регистра со всеми субконто за всю историю ведения учета в базе данных во временный набор данных.
И только после того, как эта гигантская выборка (состоящая из миллионов строк) будет полностью построена в памяти SQL-сервера, наложить на нее фильтр из секции ГДЕ (отобрать нужные регистраторы и содержание).

Разумеется, на файловой базе при небольшом объеме данных или за счет кэширования операционной системы этот процесс может пройти незаметно. Но на большой рабочей базе в SQL-варианте СУБД мгновенно упирается в дисковый ввод-вывод или нехватку оперативной памяти, что приводит к зависанию.

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

Давайте посмотрим, как преобразится наш запрос:


ВЫБРАТЬ
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма,
    ХозрасчетныйДвиженияССубконто.КоличествоКт КАК Количество,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК Товар,
    ХозрасчетныйДвиженияССубконто.Регистратор КАК Регистратор,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2.ЕдиницаИзмерения КАК ЕдиницаИзмерения
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        ,
        ,
        Регистратор В (&МассивОбъектов) И Содержание = "Списание запасов на затраты производства",
        ,
    ) КАК ХозрасчетныйДвиженияССубконто
СГРУППИРОВАТЬ ПО
    ХозрасчетныйДвиженияССубконто.СубконтоКт2,
    ХозрасчетныйДвиженияССубконто.КоличествоКт,
    ХозрасчетныйДвиженияССубконто.Регистратор,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2.ЕдиницаИзмерения,
    ХозрасчетныйДвиженияССубконто.НомерСтроки
УПОРЯДОЧИТЬ ПО
    ХозрасчетныйДвиженияССубконто.НомерСтроки

Перенос условий внутрь скобок виртуальной таблицы — это базовое правило оптимизации в 1С, которое в разы сокращает объем обрабатываемых сервером данных.

Причина 2. Разыменование полей составного типа без типизации

Обратите внимание на конструкцию ХозрасчетныйДвиженияССубконто.СубконтоКт2.ЕдиницаИзмерения в тексте запроса. Поле СубконтоКт2 имеет составной тип данных. На уровне конфигурации в качестве этого субконто могут выступать десятки различных объектов: справочники «Номенклатура», «Контрагенты», «Основные средства», «Договоры» и другие.

Когда мы пишем обращение «через точку» к реквизиту составного поля (например, к свойству ЕдиницаИзмерения), платформа 1С на уровне SQL вынуждена выполнить левое соединение (LEFT JOIN) со всеми таблицами справочников, которые теоретически могут быть записаны в это поле субконто. В результате итоговый SQL-запрос разрастается до колоссальных размеров, включая десятки тяжелых соединений, многие из которых возвращают пустые значения.

Оптимизатор СУБД (особенно чувствительный к этому IBM DB2) при виде такого обилия соединений просто «сходит с ума», строя неоптимальный план выполнения запроса с полным сканированием таблиц (Table Scan).

Решение проблемы: Мы должны явно указать платформе, из какого конкретно справочника нужно получить реквизит. Для этого используется конструкция ВЫРАЗИТЬ.

Рассмотрим правильный вариант написания этой секции:


ВЫБРАТЬ
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма,
    ХозрасчетныйДвиженияССубконто.КоличествоКт КАК Количество,
    ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК Справочник.Номенклатура) КАК Товар,
    ХозрасчетныйДвиженияССубконто.Регистратор КАК Регистратор,
    ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК Справочник.Номенклатура).ЕдиницаИзмерения КАК ЕдиницаИзмерения

При использовании ВЫРАЗИТЬ платформа 1С сгенерирует SQL-код, содержащий левое соединение только с одной конкретной таблицей — справочником «Номенклатура». Все остальные лишние соединения будут полностью исключены из плана запроса, что кардинально повысит скорость его работы.

Причина 3. Обращение через точку к реквизитам Регистратора

В сообщении 16 приводится еще один медленный запрос, в котором содержится следующая конструкция в секции ГДЕ:

ХозрасчетныйДвиженияССубконто.Регистратор.Контрагент = &Контрагент

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

Решение проблемы: Использовать временные таблицы. Сначала мы должны выбрать ссылки на интересующие нас документы во временную таблицу с отбором по контрагенту, проиндексировать ее, а затем использовать эту временную таблицу в параметрах виртуальной таблицы регистра.

Давайте разберем этот паттерн на практике:


// Шаг 1. Отбираем нужные документы во временную таблицу
ВЫБРАТЬ
    Реализация.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Документы
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Контрагент = &Контрагент

ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

// Шаг 2. Основной запрос к виртуальной таблице с фильтрацией по ВТ
ВЫБРАТЬ
    ХозрасчетныйДвиженияССубконто.Регистратор КАК Регистратор,
    ХозрасчетныйДвиженияССубконто.СчетДт КАК СчетДт,
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК СубконтоКт1,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК СубконтоКт2,
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма,
    ХозрасчетныйДвиженияССубконто.КоличествоКт КАК КоличествоКт
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        ,
        ,
        Регистратор В (ВЫБРАТЬ Ссылка ИЗ ВТ_Документы)
            И СубконтоКт1 = &СубконтоКт1
            И Содержание = &Содержание,
        ,
    ) КАК ХозрасчетныйДвиженияССубконто
СГРУППИРОВАТЬ ПО
    ХозрасчетныйДвиженияССубконто.КоличествоКт,
    ХозрасчетныйДвиженияССубконто.Регистратор,
    ХозрасчетныйДвиженияССубконто.СубконтоКт2,
    ХозрасчетныйДвиженияССубконто.СубконтоКт1,
    ХозрасчетныйДвиженияССубконто.СчетДт

Благодаря такому подходу СУБД мгновенно найдет нужные проводки по индексу регистра, используя готовый плоский список документов-регистраторов.

Причина 4. Специфика СУБД IBM DB2 и устаревание статистики

Автор темы упомянул важную деталь: «Раньше где-то 2 недели назад формировался быстро». Почему же производительность резко упала при неизменном коде запроса?

Все современные СУБД (и в особенности IBM DB2) используют стоимостный оптимизатор запросов. При получении запроса оптимизатор оценивает, как быстрее получить данные: выполнить поиск по индексу (Index Seek) или полностью просканировать всю таблицу на диске (Table Scan). Свой выбор оптимизатор делает на основе статистики распределения данных в таблицах.

Если за две недели в базу было введено много новых документов, проведено закрытие месяца или групповое перепроведение, статистика распределения данных в таблицах регистров безнадежно устарела. Оптимизатор DB2 начинает ошибаться в оценке кардинальности соединений и выбирает наихудший план выполнения (например, вложенные циклы Nested Loops вместо быстрого Hash Join).

Решение проблемы: На СУБД DB2 необходимо настроить регулярное регламентное задание по обновлению статистики (команда RUNSTATS). Для MS SQL Server аналогичной процедурой является UPDATE STATISTICS. Также крайне полезно регулярно выполнять пересчет итогов регистров из конфигуратора 1С или с помощью встроенных механизмов платформы.

Резюме: Чек-лист по ускорению запросов к регистрам бухгалтерии

Чтобы ваши запросы к регистрам бухгалтерии работали молниеносно на любой СУБД, всегда придерживайтесь следующих правил:

Ограничивайте период: По возможности всегда задавайте параметры НачалоПериода и КонецПериода в виртуальных таблицах. Чтение данных за всю историю — главный враг производительности.
Фильтруйте данные «на входе»: Никогда не пишите условия по полям виртуальной таблицы в секции ГДЕ. Все отборы (по счетам, субконто, регистраторам) пишите строго внутри параметров виртуальной таблицы.
Типизируйте составные типы: При обращении к реквизитам субконто всегда используйте конструкцию ВЫРАЗИТЬ, чтобы избежать лавинообразного роста числа левых соединений (LEFT JOIN) в SQL-запросе.
Избегайте разыменования регистратора: Не пишите Регистратор.Реквизит. Если нужен отбор по реквизитам документов, сначала соберите нужные ссылки во временную таблицу, проиндексируйте ее и передайте в параметры виртуальной таблицы регистра.
Следите за здоровьем СУБД: Настройте регулярные регламентные операции на уровне базы данных: обновление статистики, дефрагментацию и реорганизацию индексов, а также актуализацию итогов в 1С.