Как исправить ошибку СУБД «был достигнут предел служб выражений» в 1С при выполнении запроса в MS SQL Server?

← На главную

При доработке стандартных отчетов или сложных запросов в платформах 1С Предприятие разработчики иногда сталкиваются со специфической ошибкой СУБД: «Внутренняя ошибка, был достигнут предел служб выражений». Особая коварность этой проблемы заключается в том, что в файловой базе данных или при использовании СУБД PostgreSQL запрос может выполняться совершенно нормально и быстро, однако на базе MS SQL Server возникает сбой.

В этом материале мы подробнее проанализируем ситуацию, выясним техническую причину возникновения данной ошибки и разберем по шагам практические способы ее решения.

Выясним причину возникновения ошибки

Технически эта ошибка соответствует внутреннему коду MS SQL Server 8632 (QUERY_EXPRESSION_TOO_COMPLEX). Она свидетельствует о том, что оптимизатор или синтаксический анализатор MS SQL Server превысил внутренний предел по количеству допустимых выражений, констант и условий в рамках одного запроса (жесткий лимит составляет 65 535 элементов).

В контексте платформы 1С эта проблема чаще всего возникает при совпадении следующих факторов:

Составной тип данных субконто. Поля видов субконто могут содержать десятки различных типов (справочники, документы и т.д.). Платформа 1С при трансляции запроса на язык T-SQL разворачивает каждое субконто в громоздкий набор соединений и условий проверки типов.
Использование логических конструкций с выбором типа. Когда в запросе применяется конструкция ВЫБОР КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто1) = &ТипИзмерения1 ... КОНЕЦ, платформа 1С строит многочисленные вложенные условия для проверки всех возможных типов составного поля.
Расчет итогов и иерархии в том же запросе. Попытка одновременно производить динамическое сопоставление типов субконто и рассчитывать итоги (ИТОГИ ПО Счет ИЕРАРХИЯ) в одном пакете вызова заставляет СУБД генерировать слишком сложный деревенный план выполнения.

Проанализируем проблемный запрос

Рассмотрим пример кода запроса, при формировании которого возникает ошибка СУБД. В отчете оборотно-сальдовой ведомости (ОСВ) по счету разработчик попытался динамически сопоставить типы субконто в зависимости от настроек отчета:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ОстаткиИОбороты.Счет КАК Счет,
    СуммаНачальныйОстатокДт КАК СуммаНачальныйОстатокДт,
    СуммаНачальныйОстатокКт КАК СуммаНачальныйОстатокКт,
    СуммаОборотДт КАК СуммаОборотДт,
    СуммаОборотКт КАК СуммаОборотКт,
    СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    СуммаКонечныйОстатокКт КАК СуммаКонечныйОстатокКт, 
    ВЫБОР 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто1) = &ТипИзмерения1 ТОГДА ОстаткиИОбороты.Субконто1 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто2) = &ТипИзмерения1 ТОГДА ОстаткиИОбороты.Субконто2 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто3) = &ТипИзмерения1 ТОГДА ОстаткиИОбороты.Субконто3 
    КОНЕЦ КАК Субконто1, 
    ВЫБОР 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто1) = &ТипИзмерения2 ТОГДА ОстаткиИОбороты.Субконто1 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто2) = &ТипИзмерения2 ТОГДА ОстаткиИОбороты.Субконто2 
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто3) = &ТипИзмерения2 ТОГДА ОстаткиИОбороты.Субконто3 
    КОНЕЦ КАК Субконто2 
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , Счет В ИЕРАРХИИ (&Счет), , Организация = &Организация) КАК ОстаткиИОбороты
ИТОГИ
    СУММА(СуммаНачальныйОстатокДт),
    СУММА(СуммаНачальныйОстатокКт),
    СУММА(СуммаОборотДт),
    СУММА(СуммаОборотКт),
    СУММА(СуммаКонечныйОстатокДт),
    СУММА(СуммаКонечныйОстатокКт)
ПО ОБЩИЕ,
    Счет ИЕРАРХИЯ КАК Счет, 
    Субконто1, 
    Субконто2
АВТОУПОРЯДОЧИВАНИЕ

Решение проблемы: разделим выполнение на два этапа через временную таблицу

Самый эффективный и простой способ обойти это ограничение MS SQL Server — вынести вычисление сложных выражений с ТИПЗНАЧЕНИЯ во временную таблицу. На первом шаге мы помещаем выборку с уже обработанными субконто во временную таблицу ВТ, а на втором шаге считываем из нее данные и рассчитываем итоги.

Разберем готовое рабочее решение по шагам:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ОстаткиИОбороты.Счет КАК Счет,
    ОстаткиИОбороты.СуммаНачальныйОстатокДт КАК СуммаНачальныйОстатокДт,
    ОстаткиИОбороты.СуммаНачальныйОстатокКт КАК СуммаНачальныйОстатокКт,
    ОстаткиИОбороты.СуммаОборотДт КАК СуммаОборотДт,
    ОстаткиИОбороты.СуммаОборотКт КАК СуммаОборотКт,
    ОстаткиИОбороты.СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    ОстаткиИОбороты.СуммаКонечныйОстатокКт КАК СуммаКонечныйОстатокКт,
    ВЫБОР
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто1) = &ТипИзмерения1
            ТОГДА ОстаткиИОбороты.Субконто1
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто2) = &ТипИзмерения1
            ТОГДА ОстаткиИОбороты.Субконто2
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто3) = &ТипИзмерения1
            ТОГДА ОстаткиИОбороты.Субконто3
    КОНЕЦ КАК Субконто1,
    ВЫБОР
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто1) = &ТипИзмерения2
            ТОГДА ОстаткиИОбороты.Субконто1
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто2) = &ТипИзмерения2
            ТОГДА ОстаткиИОбороты.Субконто2
        КОГДА ТИПЗНАЧЕНИЯ(ОстаткиИОбороты.Субконто3) = &ТипИзмерения2
            ТОГДА ОстаткиИОбороты.Субконто3
    КОНЕЦ КАК Субконто2
ПОМЕСТИТЬ ВТ
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , Счет В ИЕРАРХИИ (&Счет), , Организация = &Организация) КАК ОстаткиИОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Счет КАК Счет,
    ВТ.СуммаНачальныйОстатокДт КАК СуммаНачальныйОстатокДт,
    ВТ.СуммаНачальныйОстатокКт КАК СуммаНачальныйОстатокКт,
    ВТ.СуммаОборотДт КАК СуммаОборотДт,
    ВТ.СуммаОборотКт КАК СуммаОборотКт,
    ВТ.СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    ВТ.СуммаКонечныйОстатокКт КАК СуммаКонечныйОстатокКт,
    ЕСТЬNULL(ВТ.Субконто1, НЕОПРЕДЕЛЕНО) КАК Субконто1,
    ЕСТЬNULL(ВТ.Субконто2, НЕОПРЕДЕЛЕНО) КАК Субконто2,
    ПРЕДСТАВЛЕНИЕ(ЕСТЬNULL(ВТ.Субконто1, НЕОПРЕДЕЛЕНО)) КАК Субконто1Представление,
    ПРЕДСТАВЛЕНИЕ(ЕСТЬNULL(ВТ.Субконто2, НЕОПРЕДЕЛЕНО)) КАК Субконто2Представление,
    ПРЕДСТАВЛЕНИЕ(ВТ.Счет)
ИЗ
    ВТ КАК ВТ
ИТОГИ
    СУММА(СуммаНачальныйОстатокДт),
    СУММА(СуммаНачальныйОстатокКт),
    СУММА(СуммаОборотДт),
    СУММА(СуммаОборотКт),
    СУММА(СуммаКонечныйОстатокДт),
    СУММА(СуммаКонечныйОстатокКт)
ПО
    ОБЩИЕ,
    Счет ИЕРАРХИЯ,
    Субконто1,
    Субконто2
АВТОУПОРЯДОЧИВАНИЕ

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

Альтернативные способы оптимизации выражений

Рассмотрим дополнительные методы решения подобных проблем, которые помогут предотвратить появление ошибок пределов СУБД:

Явное приведение типов с помощью ВЫРАЗИТЬ. Вместо использования функции ТИПЗНАЧЕНИЯ рекомендуется использовать явно указанные типы: ВЫРАЗИТЬ(ОстаткиИОбороты.Субконто1 КАК Справочник.Контрагенты). Это избавляет платформа 1С от генерации условий для десятков других возможных видов субконто.
Потиповое разбиение через ОБЪЕДИНИТЬ ВСЕ. Если типы субконто известны заранее, можно сформировать несколько небольших запросов во временные таблицы под каждый конкретный тип, после чего объединить их.
Обработка на стороне встроенного языка 1С. Если запрос пишется для специализированной обработки или отчета, можно выгрузить сырые данные во внутреннюю ТаблицуЗначений и заполнить нужные колонки с помощью стандартных средств встроенного языка (функция ТипЗнч()).
Очистка кэша планов MS SQL Server. В редких случаях ошибка проявляется из-за сбоя в кэшированном плане выполнения. Для утилизации устаревших планов можно выполнить команду очистки кэша на SQL-сервере: DBCC FREEPROCCACHE.