Как оптимизировать запрос для получения субконто конкретного типа в 1С

← На главную

При работе с регистрами бухгалтерии в платформе «1С:Предприятие 8» разработчики часто сталкиваются с необходимостью выборки данных из полей субконто. Поскольку субконто имеют составной тип данных, неоптимальные запросы к ним могут привести к серьезному падению производительности всей системы. В этой статье мы подробно разберем, как исправить логические ошибки и оптимизировать запрос для получения уникальных значений субконто определенного типа.

Анализ исходной проблемы и логической ошибки

Рассмотрим исходный запрос, в котором разработчик пытается получить уникальные элементы справочника Инструменты из пяти полей субконто временной таблицы:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВЫБОР
        КОГДА ВтТаблицаОстатков.Субконто1 ССЫЛКА Справочник.Инструменты
            ТОГДА ВтТаблицаОстатков.Субконто1
        КОГДА ВтТаблицаОстатков.Субконто2 ССЫЛКА Справочник.Инструменты
            ТОГДА ВтТаблицаОстатков.Субконто2
        КОГДА ВтТаблицаОстатков.Субконто3 ССЫЛКА Справочник.Инструменты
            ТОГДА ВтТаблицаОстатков.Субконто3
        КОГДА ВтТаблицаОстатков.Субконто4 ССЫЛКА Справочник.Инструменты
            ТОГДА ВтТаблицаОстатков.Субконто4
        КОГДА ВтТаблицаОстатков.Субконто5 ССЫЛКА Справочник.Инструменты
            ТОГДА ВтТаблицаОстатков.Субконто5
        ИНАЧЕ ЗНАЧЕНИЕ(Справочник.Инструменты.ПустаяСсылка)
    КОНЕЦ КАК Инструмент
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков

На первый взгляд, этот запрос кажется рабочим. Однако в нем кроется критическая логическая ошибка. Конструкция ВЫБОР КОГДА вычисляется последовательно до первого выполняющегося условия. Если в одной строке таблицы в Субконто1 будет указан инструмент, а в Субконто2 — другой инструмент, то запрос вернет только значение из первого субконто. Второй инструмент будет безвозвратно утерян для итоговой выборки.

Если перед нами стоит задача собрать абсолютно все уникальные инструменты, которые встречаются в любых колонках субконто, такой подход использовать нельзя.

Решение логической ошибки с помощью ОБЪЕДИНИТЬ ВСЕ

Для того чтобы не потерять данные, необходимо «развернуть» строки исходной таблицы. Мы объединим все пять колонок в одну единую таблицу (транспонируем их), а затем применим фильтр по типу. Это гарантирует получение полной и достоверной картины.

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


ВЫБРАТЬ
    ВтТаблицаОстатков.Субконто1 КАК Субконто
ПОМЕСТИТЬ ВтВсеСубконто
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков
ГДЕ
    ВтТаблицаОстатков.Субконто1 ССЫЛКА Справочник.Инструменты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВтТаблицаОстатков.Субконто2
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков
ГДЕ
    ВтТаблицаОстатков.Субконто2 ССЫЛКА Справочник.Инструменты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВтТаблицаОстатков.Субконто3
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков
ГДЕ
    ВтТаблицаОстатков.Субконто3 ССЫЛКА Справочник.Инструменты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВтТаблицаОстатков.Субконто4
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков
ГДЕ
    ВтТаблицаОстатков.Субконто4 ССЫЛКА Справочник.Инструменты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВтТаблицаОстатков.Субконто5
ИЗ
    ВтТаблицаОстатков КАК ВтТаблицаОстатков
ГДЕ
    ВтТаблицаОстатков.Субконто5 ССЫЛКА Справочник.Инструменты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВЫРАЗИТЬ(ВтВсеСубконто.Субконто КАК Справочник.Инструменты) КАК Инструмент
ИЗ
    ВтВсеСубконто КАК ВтВсеСубконто
ГДЕ
    ВтВсеСубконто.Субконто <> ЗНАЧЕНИЕ(Справочник.Инструменты.ПустаяСсылка)

В этой конструкции мы сначала собираем все подходящие значения во временную таблицу ВтВсеСубконто с помощью быстрого оператора ОБЪЕДИНИТЬ ВСЕ, а затем во втором пакете запроса выбираем уникальные записи через ключевое слово РАЗЛИЧНЫЕ.

Оптимизация на уровне СУБД с помощью функции ВЫРАЗИТЬ

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

Для того чтобы исключить эти избыточные соединения на уровне SQL-запроса, всегда используйте конструкцию ВЫРАЗИТЬ. В нашем примере кода выше мы применили конструкцию:

ВЫРАЗИТЬ(ВтВсеСубконто.Субконто КАК Справочник.Инструменты)

Это сообщает платформе точный тип данных, за счет чего SQL Server строит максимально простой и эффективный план выполнения запроса, обращаясь напрямую только к одной физической таблице справочника.

Лучшая практика: Оптимизация на этапе получения виртуальной таблицы

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

У виртуальных таблиц остатков и оборотов есть третий параметр — Субконто. Передав туда массив нужных нам видов субконто (в нашем случае это предопределенный вид характеристики Инструменты), мы получаем колоссальные преимущества:

Платформа автоматически отфильтрует остатки и вернет данные только по тем счетам и субконто, которые содержат тип Справочник.Инструменты.
Независимо от того, каким по порядку (первым, вторым или третьим) вид субконто «Инструменты» задан в настройках конкретного счета, в выборку он всегда вернется в поле Субконто1.
Поле Субконто1 в итоговой выборке будет иметь строго типизированный вид, что полностью исключает необходимость громоздких проверок ВЫБОР КОГДА ... ССЫЛКА.

Пример оптимального получения остатков непосредственно из базы данных:


// Формируем массив с нужным видом субконто
МассивВидовСубконто = Новый Массив;
МассивВидовСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконто.Инструменты);

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("МассивВидовСубконто", МассивВидовСубконто);
Запрос.Текст = 
    "ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ХозрасчетныйОстатки.Субконто1 КАК Инструмент
    |ИЗ
    |    РегистрБухгалтерии.Хозрасчетный.Остатки(&Период, , &МассивВидовСубконто, ) КАК ХозрасчетныйОстатки";

Этот вариант работает быстрее всего, поскольку фильтрация и типизация происходят на стороне сервера базы данных в момент формирования выборки остатков, снижая нагрузку на сеть и дисковую подсистему СУБД.

Индексирование временных таблиц

Если вы всё же вынуждены передавать данные в запрос из внешнего источника через параметр &ТаблицаОстатков, и объем этой таблицы превышает несколько сотен строк, обязательно индексируйте временную таблицу по проверяемым полям субконто.


ВЫБРАТЬ
    ТаблицаОстатков.Субконто1,
    ТаблицаОстатков.Субконто2,
    ТаблицаОстатков.Субконто3
ПОМЕСТИТЬ ВтТаблицаОстатков
ИЗ
    &ТаблицаОстатков КАК ТаблицаОстатков

ИНДЕКСИРОВАТЬ ПО
    Субконто1,
    Субконто2,
    Субконто3

Наличие индекса значительно ускорит операции сравнения и группировки при последующем транспонировании или поиске уникальных значений через ВЫБРАТЬ РАЗЛИЧНЫЕ.