Как выбрать из регистра накопления документы, у которых более одной строки в движениях?

← На главную

При работе с крупными базами данных в системе «1С:Предприятие» разработчики часто сталкиваются с задачами оптимизации запросов. Одна из классических задач — выборка документов (регистраторов), которые сформировали более одной строки движений в определенном регистре накопления (или регистре сведений). На первый взгляд задача кажется простой, но стандартные подходы могут привести к серьезным просадкам производительности СУБД на больших объемах данных.

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

Решение 1. Классический метод с группировкой и условием ИМЕЮЩИЕ (Простой, но медленный)

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

Давайте посмотрим на пример такого запроса:


ВЫБРАТЬ
    Продажи.Регистратор КАК Регистратор
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Регистратор
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(Продажи.Регистратор) > 1

Как это работает на уровне СУБД:

СУБД вынуждена выполнить сканирование таблицы регистра (или выбранного индекса по периоду).
Для выполнения группировки GROUP BY база данных строит в памяти (или на диске в tempdb, если записей слишком много) хэш-таблицу либо производит сортировку данных по регистраторам.
Для каждого регистратора подсчитывается агрегатная функция COUNT(*).
На финальном этапе применяется фильтр, отсекающий группы, где количество строк равно единице.

Минусы подхода: На таблицах с миллионами записей этот запрос будет выполняться крайне медленно. Операция группировки по всему регистру создает колоссальную нагрузку на дисковую подсистему и оперативную память сервера СУБД.

Решение 2. Использование временных таблиц (Громоздкий и неэффективный способ)

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


ВЫБРАТЬ
    Продажи.Регистратор КАК Регистратор,
    СУММА(1) КАК СтрокаРегистратора
ПОМЕСТИТЬ ВТ_СтрокиРегистратор
ИЗ
    РегистрНакопления.Продажи КАК Продажи
ГДЕ
    Продажи.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
СГРУППИРОВАТЬ ПО
    Продажи.Регистратор
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_СтрокиРегистратор.Регистратор КАК Регистратор
ИЗ
    ВТ_СтрокиРегистратор КАК ВТ_СтрокиРегистратор
ГДЕ
    ВТ_СтрокиРегистратор.СтрокаРегистратора > 1

Почему этот способ не рекомендуется:

Этот вариант объединяет в себе все недостатки первого метода (тяжелая группировка по регистратору) и добавляет новые накладные расходы. СУБД тратит драгоценное время на создание временной таблицы в базе данных tempdb, запись в нее сотен тысяч строк и последующее чтение. На пустом месте создается колоссальный объем дискового I/O, что негативно сказывается на общей производительности системы.

Решение 3. Использование поля НомерСтроки (Оптимальный и быстрый способ)

А теперь рассмотрим наиболее элегантный, профессиональный и быстрый метод решения этой задачи. Он основан на знании внутренней архитектуры хранения движений в 1С. В любом регистре (накопления, бухгалтерии, сведений с регистратором) движения всегда записываются последовательно, начиная с номера строки 1.

Если у документа в движениях присутствует более одной строки, это гарантирует, что в регистре для этого документа всегда существует запись с номером строки, равным 2.

Запрос в этом случае приобретает максимально простой вид:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Продажи.Регистратор КАК Регистратор
ИЗ
    РегистрНакопления.Продажи КАК Продажи
ГДЕ
    Продажи.НомерСтроки = 2

Почему это работает молниеносно на уровне СУБД:

Отсутствие группировок: В запросе нет тяжелой операции GROUP BY. СУБД выполняет простую фильтрацию строк по условию WHERE _LineNo = 2.
Индексация: Системное поле НомерСтроки (в СУБД оно называется _LineNo) входит в состав основного кластерного индекса таблицы движений регистра (обычно индекс выглядит как Период + Регистратор + НомерСтроки). СУБД моментально находит нужные записи по индексу.

Тонкий нюанс: почему «НомерСтроки = 2», а не «НомерСтроки > 1»?

Иногда разработчики, понимая логику работы с номерами строк, допускают ошибку в оптимизации и пишут условие НомерСтроки > 1. Давайте проанализируем, почему этот вариант уступает по производительности фильтру НомерСтроки = 2.

Представим ситуацию: в нашей базе данных есть крупный документ, который делает 10 000 строк движений в регистре.

Если мы напишем условие ГДЕ НомерСтроки > 1, СУБД выберет из базы данных 9 999 записей для этого одного документа. После этого ключевое слово РАЗЛИЧНЫЕ (в SQL-запросе это транслируется в DISTINCT) будет вынуждено обрабатывать и дедуплицировать все эти 9 999 строк в оперативной памяти сервера, чтобы на выходе выдать нам одну ссылку на Регистратор.
Если же мы напишем условие ГДЕ НомерСтроки = 2, СУБД выберет ровно 1 запись для этого документа. Конструкция РАЗЛИЧНЫЕ отработает моментально, не тратя процессорное время на дедупликацию огромного массива данных.

Возможные ограничения метода

Хотя метод с фильтрацией по НомерСтроки = 2 является стандартом де-факто, рассмотрим редкие краевые случаи, когда его нужно использовать с осторожностью:

Прямая ручная запись в регистр (минуя проведение): Если в системе выполнялась запись движений напрямую через набор записей программным путем, и разработчик вручную сформировал некорректные номера строк (например, записал две строки с номерами 1 и 3, пропустив двойку), то запрос пропустит этот документ. Однако при стандартной работе платформы и типовых конфигураций возникновение такой ситуации исключено.
Поиск в табличных частях документов: Данный метод отлично применим и для поиска документов, у которых в табличной части (например, РеализацияТоваровУслуг.Товары) содержится более одной строки. Принцип абсолютно аналогичен, так как у всех табличных частей документов также присутствует системное индексируемое поле НомерСтроки.

Подведем итог: Для поиска документов с количеством строк движений более одной всегда используйте условие ГДЕ НомерСтроки = 2 в сочетании с ключевым словом РАЗЛИЧНЫЕ. Это сократит время выполнения запроса в десятки раз по сравнению с классической группировкой.