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