При разработке отчетов и механизмов расчета заработной платы в 1С (например, в конфигурации «Управление нашей фирмой») часто возникает задача отфильтровать данные по дополнительным реквизитам объектов. Например, нам необходимо получить объем продаж по номенклатурной группе «Стекло», но только по тем договорам, за которыми закреплен конкретный менеджер (агент), указанный в дополнительных реквизитах договора контрагента.
В этой статье мы подробно разберем, как связать виртуальную таблицу оборотов регистра накопления с табличной частью дополнительных реквизитов справочника, проанализируем типичные ошибки производительности и спроектируем оптимальный, безопасный запрос по стандартам разработки 1С.
Сформулируем исходные условия. У нас есть регистр накопления Продажи.Обороты. Нам нужно связать его со справочником ДоговорыКонтрагентов, у которого включен механизм дополнительных реквизитов (реализованный на базе Библиотеки Стандартных Подсистем — БСП). Дополнительный реквизит «Агент» (ссылающийся на справочник Сотрудники или ФизическиеЛица) хранится в подчиненной табличной части договора.
Давайте разберем, где именно хранятся эти данные. В системе 1С дополнительные реквизиты объектов не добавляются как физические колонки в основную таблицу СУБД. Они записываются в подчиненную табличную часть справочника — ДополнительныеРеквизиты. Эта табличная часть содержит два ключевых реквизита:
ДополнительныеРеквизитыИСведения (идентификатор свойства, например, «Агент»);Таким образом, для получения нужного нам фильтра мы должны связать обороты продаж с этой табличной частью.
Рассмотрим первый рабочий вариант запроса, к которому пришли участники обсуждения на форуме. Этот вариант работоспособен на небольших базах данных и наглядно показывает логику связи таблиц.
ВЫБРАТЬ
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот
ИЗ
Справочник.ДоговорыКонтрагентов.ДополнительныеРеквизиты КАК ДоговорыКонтрагентовДополнительныеРеквизиты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(
&НачалоПериода,
&КонецПериода,
,
Номенклатура.КатегорияНоменклатуры.Наименование = "Стекло"
) КАК ПродажиОбороты
ПО ДоговорыКонтрагентовДополнительныеРеквизиты.Ссылка = ПродажиОбороты.Документ.Договор
И (ДоговорыКонтрагентовДополнительныеРеквизиты.Свойство.Наименование = "Агент")
И (ДоговорыКонтрагентовДополнительныеРеквизиты.Значение = &Сотрудник)
Проанализируем этот запрос по шагам:
Справочник.ДоговорыКонтрагентов.ДополнительныеРеквизиты.Продажи.Обороты. Это автоматически отсекает все продажи по договорам, у которых не заполнен или отсутствует нужный реквизит.ПродажиОбороты.Документ.Договор.Свойство.Наименование = "Агент") и на равенство значения параметру запроса (Значение = &Сотрудник).Несмотря на то, что данный запрос решает задачу и возвращает верный результат, он обладает серьезными архитектурными недостатками, которые могут привести к зависанию базы данных при увеличении объема транзакций.
Выясним причины, почему первый вариант запроса считается неоптимальным в профессиональной разработке 1С:
1. Обращение через точку к составному типу («через две точки»)
Поле Документ (Регистратор) в регистре продаж имеет составной тип данных. В него могут входить десятки документов: Расходная накладная, Отчет о розничных продажах, Акт выполненных работ и т.д. Когда мы пишем конструкцию ПродажиОбороты.Документ.Договор, СУБД вынуждена выполнять неявное левое соединение (LEFT OUTER JOIN) со всеми таблицами этих документов. Это порождает гигантский SQL-запрос и колоссально снижает производительность СУБД.
2. Поиск по строковому представлению свойства
Фильтрация по условию Свойство.Наименование = "Агент" опасна. Во-первых, если пользователь в режиме предприятия переименует этот реквизит (например, исправит опечатку или напишет «Агент (продажи)»), ваш запрос мгновенно сломается. Во-вторых, сравнение текстовых строк в базе данных происходит медленнее, чем сравнение ссылочных уникальных идентификаторов (GUID).
3. Отсутствие использования временных таблиц
Прямое соединение большой физической таблицы подчиненного справочника с тяжелой виртуальной таблицей оборотов затрудняет построение эффективного плана запроса СУБД.
Теперь спроектируем этот же запрос по всем стандартам разработки фирмы «1С». Мы применим методику работы с временными таблицами, типизацию составных полей и передачу свойства через параметр ссылки.
Рассмотрим подробнее оптимальный алгоритм работы:
ВЫРАЗИТЬ или обратимся к измерению напрямую, если оно есть в структуре регистра.Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ДопРеквизиты.Ссылка КАК Договор
ПОМЕСТИТЬ ВТ_ДоговорыАгента
ИЗ
Справочник.ДоговорыКонтрагентов.ДополнительныеРеквизиты КАК ДопРеквизиты
ГДЕ
ДопРеквизиты.Свойство = &Свойство
И ДопРеквизиты.Значение = &Сотрудник
ИНДЕКСИРОВАТЬ ПО
Договор
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот
ИЗ
РегистрНакопления.Продажи.Обороты(
&НачалоПериода,
&КонецПериода,
,
Номенклатура.КатегорияНоменклатуры.Наименование = "Стекло"
) КАК ПродажиОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДоговорыАгента КАК ВТ_Договоры
ПО ВТ_Договоры.Договор = ВЫРАЗИТЬ(ПродажиОбороты.Документ КАК Документ.РасходнаяНакладная).Договор
Разберем ключевые улучшения, которые мы внесли в этот запрос:
ВТ_ДоговорыАгента позволило локализовать выборку договоров. СУБД сначала делает быструю выборку по индексу табличной части, получает небольшой массив ссылок и только затем соединяет его с оборотами.ИНДЕКСИРОВАТЬ ПО Договор значительно повышает скорость соединения на уровне SQL-сервера.&Свойство точную ссылку на элемент плана видов характеристик.ВЫРАЗИТЬ(ПродажиОбороты.Документ КАК Документ.РасходнаяНакладная).Договор указывает платформе, что соединение нужно строить только по конкретной таблице документов (в нашем примере — Расходная накладная). Это избавляет систему от неявных соединений со всеми возможными регистраторами регистра «Продажи». Примечание: если у вас продажи оформляются несколькими документами (например, Расходными накладными и Актами выполненных работ), вы можете объединить их во временной таблице или использовать прямые измерения регистра, если учетная политика конфигурации заполняет измерение «ЗаказПокупателя» или «Договор» напрямую в регистре.Если вы решаете данную задачу внутри программного модуля (например, при расчете показателей в документе начисления зарплаты), вам не обязательно писать запросы вручную. Поскольку конфигурация «Управление нашей фирмой» базируется на Библиотеке Стандартных Подсистем (БСП), вы можете использовать встроенный программный интерфейс подсистемы «Свойства».
Для быстрого получения значения дополнительного реквизита по конкретному договору можно использовать метод общего модуля:
ЗначениеАгента = УправлениеСвойствами.ЗначениеСвойства(ДоговорСсылка, "Агент");
Если же вам необходимо получить свойства для целого списка (массива) договоров, во избежание запросов в цикле (что является критической ошибкой разработки) используйте следующий вызов:
ТаблицаСвойств = УправлениеСвойствами.ЗначенияСвойств(МассивДоговоров, Истина);
Данный метод вернет структурированную таблицу значений со всеми допреквизитами для переданного массива объектов, которую в дальнейшем можно быстро обработать программно.
Используя предложенные методики оптимизации запросов и программные инструменты БСП, вы сможете создавать быстрые, отказоустойчивые и масштабируемые решения в любой конфигурации на платформе «1С:Предприятие 8.3».