Как запросить виртуальную таблицу оборотов 1С с отбором по дополнительному реквизиту-дате документа?

При работе с платформой 1С:Предприятие 8.3, особенно в конфигурациях, таких как 1С:Комплексная автоматизация 2.4, часто возникает задача получения данных из виртуальных таблиц регистров накопления (например, оборотов) с отбором по реквизитам документа-регистратора. Особенную сложность представляют ситуации, когда отбор должен происходить по дополнительным реквизитам документа, которые не являются измерениями регистра.

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

Понимание проблемы: Отбор по дополнительному реквизиту

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

Наш сценарий: нам необходимо получить обороты по расчетам с клиентами (РегистрНакопления.РасчетыСКлиентамиПоДокументам) за определенный период, но только для тех реализаций, у которых дополнительный реквизит «ДатаДоставки» попадает в заданный интервал.

Типовое хранение дополнительных реквизитов в конфигурациях 1С (например, КА 2.4) осуществляется в табличной части документа, которая обычно называется ДополнительныеРеквизиты (или ДополнительныеСведения, если это отдельные сведения). В нашем случае, судя по структуре запроса Документ.РеализацияТоваровУслуг.ДополнительныеРеквизиты, речь идет именно о дополнительных реквизитах, являющихся частью документа.

Неоптимальный, но показательный первоначальный подход

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

1. Мы сначала выбираем все необходимые ссылки на документы-регистраторы, у которых нужный дополнительный реквизит попадает в наш интервал.
2. Затем мы соединяем эту выборку с виртуальной таблицей оборотов, отбирая только те обороты, регистратор которых присутствует в нашей предварительной выборке.

Пример такого запроса (сначала предложенный вариант, который затем был оптимизирован):

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

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Соединяем с виртуальной таблицей оборотов
ВЫБРАТЬ
    РасчетыСКлиентамиПоДокументамОбороты.*
ИЗ
    РегистрНакопления.РасчетыСКлиентамиПоДокументам.Обороты(&ДатаНачала, &ДатаОкончания, Авто) КАК РасчетыСКлиентамиПоДокументамОбороты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Отбор
        ПО Отбор.Ссылка = РасчетыСКлиентамиПоДокументамОбороты.Регистратор
        И Отбор.ДатаДоставки МЕЖДУ &ДатаНачала И &ДатаОкончания

Разберем, почему этот запрос может быть медленным и как его улучшить:

1. МАКСИМУМ(Значение) и СГРУППИРОВАТЬ ПО Ссылка: Подсистема дополнительных реквизитов и сведений в 1С гарантирует уникальность свойства для каждого объекта. Это означает, что у одного документа не может быть двух дополнительных реквизитов с одинаковым именем. Следовательно, использование агрегатной функции МАКСИМУМ() и группировка здесь излишни, они только замедляют выполнение запроса, поскольку создают дополнительную нагрузку на сервер БД.
2. Условие Ссылка.Проведен: Это условие становится избыточным, когда мы выполняем ВНУТРЕННЕЕ СОЕДИНЕНИЕ с регистром накопления. Регистр накопления содержит записи только по проведенным документам (регистраторам). Если документ не проведен, его не будет в регистре, и, соответственно, в результате соединения он не появится.
3. Отбор Отбор.ДатаДоставки МЕЖДУ &ДатаНачала И &ДатаОкончания в условии соединения: Этот отбор лучше выполнить на более раннем этапе — при формировании временной таблицы Отбор. Чем раньше мы отсеем ненужные данные, тем меньше записей будет участвовать в дальнейших операциях.

Оптимизированное решение с использованием временных таблиц и индексации

Для достижения максимальной производительности, особенно при больших объемах данных, мы рекомендуем следующий подход. Он основан на эффективном использовании временных таблиц и применении индексов.

Шаг 1: Отбираем ссылки на документы с фильтром по дополнительному реквизиту и индексируем временную таблицу

На этом шаге мы выбираем все ссылки на документы РеализацияТоваровУслуг, у которых дополнительный реквизит с именем "ДатаДоставки" попадает в заданный нами интервал дат (&ДатаНачала и &ДатаОкончания). Мы сразу же помещаем эти ссылки во временную таблицу и создаем индекс по полю Ссылка.

Почему это важно:

• Ранний отбор: Условие Значение МЕЖДУ &ДатаНачала И &ДатаОкончания применяется сразу при формировании временной таблицы. Это значительно сокращает количество записей, которые будут переданы на следующий этап.
• Отсутствие группировки: Как было сказано ранее, группировка и агрегатные функции для получения уникального дополнительного реквизита не нужны.
• ИНДЕКСИРОВАТЬ ПО Ссылка: Использование ключевого слова ИНДЕКСИРОВАТЬ ПО для временных таблиц критически важно. При создании индекса по полю Ссылка (по которому затем будет происходить соединение с виртуальной таблицей оборотов) скорость операции соединения увеличивается многократно. Сервер СУБД сможет быстрее находить соответствующие записи.

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

Здесь:

• Документ.РеализацияТоваровУслуг.ДополнительныеРеквизиты: это таблица, которая хранит все дополнительные реквизиты для документов типа "Реализация товаров и услуг". Каждая запись в этой таблице содержит ссылку на документ (Ссылка), ссылку на свойство (Свойство) и его значение (Значение).
• Свойство.Имя = "ДатаДоставки": мы отбираем только записи, относящиеся к дополнительному реквизиту с системным именем "ДатаДоставки". Это более надежный способ, чем отбор по наименованию, так как имя свойства уникально и неизменно, в отличие от наименования, которое может быть изменено пользователем.
• Значение МЕЖДУ &ДатаНачала И &ДатаОкончания: это условие фильтрует документы по значению их дополнительного реквизита "ДатаДоставки". Важно помнить, что в 1С при использовании МЕЖДУ даты включаются в интервал.
• ИНДЕКСИРОВАТЬ ПО Ссылка: создаем индекс по полю Ссылка во временной таблице ОтборДокументовПоДатеДоставки. Это обеспечит быстрый поиск при последующем соединении.

Шаг 2: Соединяем временную таблицу с виртуальной таблицей оборотов

После того как мы получили отфильтрованный список ссылок на документы, мы можем выполнить эффективное соединение с виртуальной таблицей оборотов регистра накопления РасчетыСКлиентамиПоДокументам.

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    РасчетыСКлиентамиПоДокументамОбороты.*
ИЗ
    РегистрНакопления.РасчетыСКлиентамиПоДокументам.Обороты(
        &ДатаНачала,
        &ДатаОкончания,
        Авто,
        Регистратор В (ВЫБРАТЬ Ссылка ИЗ ОтборДокументовПоДатеДоставки)
    ) КАК РасчетыСКлиентамиПоДокументамОбороты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОтборДокументовПоДатеДоставки КАК Отбор
        ПО РасчетыСКлиентамиПоДокументамОбороты.Регистратор = Отбор.Ссылка

Разберем детали этого запроса:

• РегистрНакопления.РасчетыСКлиентамиПоДокументам.Обороты(&ДатаНачала, &ДатаОкончания, Авто, Регистратор В (ВЫБРАТЬ Ссылка ИЗ ОтборДокументовПоДатеДоставки)): Здесь мы используем виртуальную таблицу оборотов регистра накопления.
  • &ДатаНачала и &ДатаОкончания: параметры для периода, за который нам нужны обороты.
  • Авто: означает, что периодичность оборотов будет определена автоматически (по регистратору).
  • Регистратор В (ВЫБРАТЬ Ссылка ИЗ ОтборДокументовПоДатеДоставки): Это ключевое улучшение. Мы передаем список отфильтрованных регистраторов непосредственно в параметр виртуальной таблицы. Это позволяет системе управления базами данных (СУБД) максимально эффективно отфильтровать данные на своем уровне, еще до формирования самой виртуальной таблицы. В результате виртуальная таблица Обороты будет построена уже только для нужных нам регистраторов, что значительно сократит объем обрабатываемых данных.
• ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОтборДокументовПоДатеДоставки КАК Отбор ПО РасчетыСКлиентамиПоДокументамОбороты.Регистратор = Отбор.Ссылка: Хотя мы уже отфильтровали регистраторы в параметре виртуальной таблицы, явное ВНУТРЕННЕЕ СОЕДИНЕНИЕ с нашей временной таблицей ОтборДокументовПоДатеДоставки все равно рекомендуется. Это подтверждает связь и гарантирует, что мы получим только те обороты, регистраторы которых были в нашем отфильтрованном списке. Благодаря индексу, созданному на шаге 1, это соединение будет очень быстрым.
• Почему не нужно условие на Проведен: Поскольку регистры накопления формируются только проведенными документами, все регистраторы, присутствующие в таблице оборотов, уже являются проведенными. Дополнительная проверка избыточна.

Дополнительные рекомендации и нюансы

Для более глубокого понимания и дальнейшей оптимизации работы с запросами к виртуальным таблицам и дополнительным реквизитам, обратите внимание на следующие моменты:

1. Различие между "Дополнительными реквизитами" и "Дополнительными сведениями":

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

2. Применение конструкции ИНДЕКСИРОВАТЬ ПО для временных таблиц:

   Использование ключевого слова ИНДЕКСИРОВАТЬ ПО позволяет создать индексы для временных таблиц. Это особенно полезно, когда временная таблица будет использоваться для соединения с другими таблицами или для отбора данных. Индекс значительно ускоряет процесс соединения и поиска нужных значений. Однако следует помнить, что построение индекса само по себе требует времени, поэтому индексирование имеет смысл, если во временной таблице ожидается значительное количество записей, и она будет часто использоваться в условиях соединения или отбора. В противном случае, накладные расходы на создание индекса могут нивелировать выигрыш в производительности. Рекомендуется индексировать поля временных таблиц, по которым происходит соединение с другими таблицами базы данных.

3. Оптимизация запросов с виртуальными таблицами и дополнительными реквизитами:

   • Временные таблицы вместо вложенных запросов: В общем случае, использование временных таблиц для промежуточных результатов предпочтительнее вложенных запросов. Временные таблицы улучшают читаемость кода, позволяют повторно использовать полученные данные и часто обеспечивают более высокую производительность, поскольку оптимизатор запросов лучше "понимает" структуру данных. В нашем случае, передача списка регистраторов в параметр виртуальной таблицы Регистратор В (...), а затем соединение — это гибридный и очень эффективный подход.
   • Избегание "двух точек" в параметрах виртуальных таблиц: При передаче в параметры виртуальной таблицы сложных выражений с обращением через точку (например, Документ.Контрагент.Партнер) платформа может выполнять неоптимальные соединения. Если нужно отбирать обороты по реквизиту регистратора, который не является измерением регистра, то правильный подход — сначала отобрать ссылки на нужные документы во временную таблицу (с условием по дополнительному реквизиту), а затем использовать этот список в параметрах виртуальной таблицы или соединить с ней.
   • Ограничения отбора в виртуальных таблицах: Виртуальные таблицы регистров накопления (включая обороты) позволяют отбирать данные только по своим измерениям, а не по произвольным реквизитам или реквизитам регистратора, если они не являются измерениями регистра. Поэтому для отбора по дополнительному реквизиту документа-регистратора всегда необходим предварительный этап получения ссылок на документы.

4. Особенности работы с датами в виртуальных таблицах оборотов:

   Виртуальные таблицы оборотов включают в себя как начальную, так и конечную даты указанного периода. Это означает, что при использовании, например, МЕЖДУ &ДатаНачала И &ДатаОкончания будут учтены движения, произошедшие включительно в эти даты.

5. Влияние индексов на операции записи:

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

Заключение

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