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