При работе с унаследованным кодом программисты часто сталкиваются с запросами-монстрами. Одной из самых неприятных проблем является огромная вложенность условий ВЫБОР (аналог CASE в SQL), особенно когда речь идет о поиске данных вверх по иерархии справочника (например, поиск КПП или ОКТМО головного подразделения). Такие запросы не только трудно читать, но они и вызывают ошибки SQL-сервера, так как парсер базы данных имеет лимиты на уровень вложенности выражений.
В этой статье мы разберем, как превратить нечитаемого «монстра» в элегантный и производительный код. Мы проанализируем ситуацию, выясним причины падения системы и рассмотрим по шагам несколько эффективных методов решения проблемы.
Рассмотрим ситуацию, представленную в коде автора. Там используется конструкция ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ВЫБОР КОГДА .... С точки зрения SQL-сервера, каждое новое слово ВЫБОР (CASE) внутри секции ИНАЧЕ создает новый уровень вложенности. Если таких уровней больше 10–15, SQL-сервер может аварийно завершить обработку запроса.
Вторая проблема — обращения через точку вида Подразделение.Родитель.Родитель.Родитель.КПП. В платформе 1С каждое такое обращение в запросе неявно порождает ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN). В примере автора таких соединений десятки. Когда оптимизатор SQL видит запрос с 50–100 соединениями к одной и той же таблице, построение плана выполнения занимает слишком много ресурсов, что и приводит к зависаниям.
Самый быстрый способ «причесать» запрос и убрать ошибку вложенности — это изменить структуру самой конструкции ВЫБОР. Вместо того чтобы вкладывать один ВЫБОР в другой, мы можем перечислить все условия в рамках одного блока КОГДА.
Рассмотрим на примере. Вместо конструкции:
ВЫБОР
КОГДА Условие1 ТОГДА Результат1
ИНАЧЕ ВЫБОР
КОГДА Условие2 ТОГДА Результат2
ИНАЧЕ ВЫБОР
КОГДА Условие3 ТОГДА Результат3
КОНЕЦ
КОНЕЦ
КОНЕЦ
Мы будем использовать плоскую структуру:
ВЫБОР
КОГДА Условие1 ТОГДА Результат1
КОГДА Условие2 ТОГДА Результат2
КОГДА Условие3 ТОГДА Результат3
ИНАЧЕ РезультатПоУмолчанию
КОНЕЦ
Такой подход значительно снижает нагрузку на парсер SQL и делает код гораздо более читаемым. Однако он не решает проблему избыточных соединений (обращений через точку).
Чтобы избежать многократных обращений через точку в основном теле запроса, мы можем предварительно собрать все необходимые данные во временную таблицу. Это позволит нам вычислить КПП и ОКТМО для каждого подразделения один раз, а затем просто присоединить готовую таблицу к основному результату.
Разберем этот процесс по шагам:
ПодразделениеОрганизации, которые встречаются в ваших данных, во временную таблицу ВТ_СписокПодразделений.Подразделение.Пример структуры такого запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
НДФЛ.ПодразделениеОрганизации КАК Подразделение
ПОМЕСТИТЬ ВТ_Подразделения
ИЗ
РегистрНакопления.НДФЛСведенияОДоходах КАК НДФЛ;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Подразделение,
ВЫБОР
КОГДА ВТ.Подразделение.КодПоОКТМО <> &ПустойКод ТОГДА ВТ.Подразделение.КодПоОКТМО
КОГДА ВТ.Подразделение.Родитель.КодПоОКТМО <> &ПустойКод ТОГДА ВТ.Подразделение.Родитель.КодПоОКТМО
// ... и так далее по иерархии
КОНЕЦ КАК ВычисленныйОКТМО
ПОМЕСТИТЬ ВТ_СправочникЗначений
ИЗ
ВТ_Подразделения КАК ВТ;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
НДФЛ.*,
ЕСТЬNULL(Данные.ВычисленныйОКТМО, &ПустойКод) КАК ОКТМО
ИЗ
РегистрНакопления.НДФЛСведенияОДоходах КАК НДФЛ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СправочникЗначений КАК Данные
ПО НДФЛ.ПодразделениеОрганизации = Данные.Подразделение
Если иерархия подразделений очень глубокая (например, более 10 уровней), даже плоский ВЫБОР может стать громоздким. В сообществе 1С-разработчиков известен метод, позволяющий «прыгать» по уровням иерархии логарифмически. Вместо того чтобы проверять каждого родителя (1, 2, 3, 4...), мы подготавливаем таблицу связей, где для каждого элемента сразу известны его предки на расстоянии 1, 2, 4, 8 и 16 уровней вверх.
Проанализируем преимущества этого метода:
Для реализации этого метода обычно строится временная таблица соответствий Ссылка - Предок через серию левых соединений таблицы самой с собой.
Если запрос все равно работает медленно, стоит рассмотреть вариант предварительной подготовки таблицы соответствий на языке 1С. Мы можем программно обойти дерево подразделений, вычислить для каждого из них нужный КПП и ОКТМО, заполнить таблицу значений (ТЗ) и передать её в запрос как параметр.
Посмотрим на пример реализации:
// Программно формируем соответствие
ТаблицаСоответствий = Новый ТаблицаЗначений;
ТаблицаСоответствий.Колонки.Добавить("Подразделение", Новый ОписаниеТипов("СправочникСсылка.ПодразделенияОрганизаций"));
ТаблицаСоответствий.Колонки.Добавить("КПП", Новый ОписаниеТипов("Строка"));
// Заполняем таблицу (можно использовать рекурсию или метод выборки справочника)
// ... логика заполнения ...
// Передаем в запрос
Запрос.УстановитьПараметр("ТаблицаСоответствий", ТаблицаСоответствий);
Запрос.Текст = "ВЫБРАТЬ * ПОМЕСТИТЬ ВТ_Соответствия ИЗ &ТаблицаСоответствий КАК Т; ...";
Этот способ гарантирует, что SQL-сервер получит максимально простую задачу: простое соединение двух таблиц без вычисления сложных условий в момент выполнения.
Подводя итог, можно сказать, что «монструозные» запросы с вложенными ВЫБОР — это технический долг, который необходимо рефакторить. Важно запомнить:
ИНАЧЕ ВЫБОР, если можно использовать плоский список КОГДА.JOIN.Временные Таблицы для промежуточных вычислений.Эти методы помогут вам не только исправить текущую ошибку, но и значительно ускорить работу отчетов в будущем.