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