Как оптимизировать запрос к большим таблицам при поиске отсутствующих записей в 1С

← На главную

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

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

Анализ исходного запроса и причин низкой производительности

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


ВЫБРАТЬ
    vscСреднемесячныйРасходЛС.ЛицевойСчет КАК ЛицевойСчет
ПОМЕСТИТЬ ТаблицаРасчетПоСреднему
ИЗ
    Документ.vscСреднемесячныйРасходЛС КАК vscСреднемесячныйРасходЛС
ГДЕ
    vscСреднемесячныйРасходЛС.Период МЕЖДУ &ПарамДатаОт И &ПарамДатаДо
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    vscЛицевыеСчета.Ссылка КАК РезультатСсылка
ИЗ
    Справочник.vscЛицевыеСчета КАК vscЛицевыеСчета
        ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаРасчетПоСреднему КАК ТаблицаРасчетПоСреднему
        ПО vscЛицевыеСчета.Ссылка = ТаблицаРасчетПоСреднему.ЛицевойСчет
ГДЕ
    ТаблицаРасчетПоСреднему.ЛицевойСчет ЕСТЬ NULL 
    И vscЛицевыеСчета.ПодразделениеОрганизации = &ПарамПодразделение

Выясним причину, по которой оптимизатор MS SQL Server строит неэффективный план выполнения:

Отсутствие покрывающего индекса в документах: В платформе 1С свойство Индексировать у реквизита документа создает только одиночный некластеризованный индекс. Если поле Период проиндексировано, в его индекс не входят данные реквизита ЛицевойСчет. Чтобы получить лицевой счет, СУБД вынуждена либо выполнять тяжелые обращения к кластерному индексу (Key Lookup), либо полностью сканировать индекс по лицевому счету (Index Scan).
Избыточный объем временной таблицы: Первый пакет помещает во временную таблицу все строки за период, включая дубликаты. Миллионы строк без уникальности перегружают базу tempdb.
Отсутствие индекса по временной таблице: При левом соединении с неиндексированной временной таблицей СУБД строит оператор хэш-соединения (Hash Match), что создает колоссальную нагрузку на оперативную память и дисковую подсистему.
Неправильный порядок фильтрации: Вместо выборки документов только по интересующему подразделению запрос пытается поднять данные по всей организации.

Способ 1. Оптимизация на уровне текста запроса и временных таблиц

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

Отобрать во временную таблицу только лицевые счета нужного подразделения (это существенно уменьшит выборку).
Свернуть таблицу документов с помощью ВЫБРАТЬ РАЗЛИЧНЫЕ или группировки, отсекая лишние записи.
Обязательно проиндексировать временные таблицы по полям связи.

Посмотрим на исправленный вариант текста запроса:


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

ИНДЕКСИРОВАТЬ ПО
    ЛицевойСчет
;
////////////////////////////////////////////////////////////////////////////////
// 2. Выбираем уникальные лицевые счета из документов за период, 
// ограничив выборку только счетами нашего подразделения
ВЫБРАТЬ РАЗЛИЧНЫЕ
    vscСреднемесячныйРасходЛС.ЛицевойСчет КАК ЛицевойСчет
ПОМЕСТИТЬ ВТ_РасходЗаПериод
ИЗ
    Документ.vscСреднемесячныйРасходЛС КАК vscСреднемесячныйРасходЛС
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЛицевыеСчетаПодразделения КАК ВТ_ЛицевыеСчетаПодразделения
        ПО vscСреднемесячныйРасходЛС.ЛицевойСчет = ВТ_ЛицевыеСчетаПодразделения.ЛицевойСчет
ГДЕ
    vscСреднемесячныйРасходЛС.Период МЕЖДУ &ПарамДатаОт И &ПарамДатаДо

ИНДЕКСИРОВАТЬ ПО
    ЛицевойСчет
;
////////////////////////////////////////////////////////////////////////////////
// 3. Получаем итоговый результат через левое соединение двух небольших проиндексированных таблиц
ВЫБРАТЬ
    ВТ_ЛицевыеСчетаПодразделения.ЛицевойСчет КАК РезультатСсылка
ИЗ
    ВТ_ЛицевыеСчетаПодразделения КАК ВТ_ЛицевыеСчетаПодразделения
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РасходЗаПериод КАК ВТ_РасходЗаПериод
        ПО ВТ_ЛицевыеСчетаПодразделения.ЛицевойСчет = ВТ_РасходЗаПериод.ЛицевойСчет
ГДЕ
    ВТ_РасходЗаПериод.ЛицевойСчет ЕСТЬ NULL

Такая схема позволяет оптимизатору СУБД перейти от полного сканирования многомиллионной таблицы документов к быстрому поиску по индексам (Index Seek) и точечным соединениям.

Способ 2. Архитектурное решение через перенос данных в регистр сведений

Рассмотрим фундаментальную причину сложности: документы 1С не предназначены для хранения периодических агрегатов и аналитических срезов больших объемов, так как у них нельзя штатными средствами платформы создать составной индекс.

Разберем по шагам архитектурные изменения:

Создание регистра сведений: Создадим непериодический или периодический регистр сведений (например, vscСреднемесячныйРасходЛС).
Порядок измерений: Первыми измерениями назначим ЛицевойСчет и Период (или Период и ЛицевойСчет в зависимости от преобладающих условий отбора). В регистрах сведений платформа 1С автоматически создает составной кластерный индекс по всем измерениям в порядке их следования.
Индексация справочника: Для реквизита ПодразделениеОрганизации в справочнике vscЛицевыеСчета включим свойство Индексировать.

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

Способ 3. Обслуживание СУБД и устранение эффекта Parameter Sniffing

Проанализируем сопутствующие факторы, влияющие на поведение оптимизатора MS SQL Server:

Устаревшая статистика (Statistics): В таблицах с частой записью документов статистика распределения значений быстро теряет актуальность. Оптимизатор СУБД ошибается в оценке количества строк (Cardinality Estimation) и выбирает соединение хэшированием вместо вложенных циклов (Nested Loops). Решением является настройка регулярного регламентного обновления статистики базы данных (команда UPDATE STATISTICS WITH FULLSCAN).
Проблема Parameter Sniffing: При первой компиляции параметризованного запроса СУБД запоминает план под переданные даты. Если первый запуск был выполнен за большой период (например, за год), план с полным сканированием может зафиксироваться в кэше и применяться даже для запросов за один день. Своевременная очистка процедурного кэша и корректные интервалы дат исключают данную проблему.