При работе с типовыми и особенно нетиповыми конфигурациями программисты часто сталкиваются с «наследием» в виде огромных запросов на несколько сотен строк. Основная проблема таких запросов — глубокая вложенность и использование одинаковых псевдонимов для разных уровней вложенности, что делает отладку практически невозможной. Рассмотрим на конкретном примере, как разобрать подобную структуру, устранить путаницу в именах и подготовить почву для оптимизации через временные таблицы.
В исходном запросе автор столкнулся с тем, что вложенные таблицы на разных уровнях называются одинаково — Лимиты. Это стандартная ошибка при написании запросов «на скорую руку» или при многократном копировании блоков кода. Рассмотрим, почему это плохо:
Проанализируем структуру, которую автор предоставил для разбора. В ней используется объединение данных из СрезПоследних и основной таблицы регистра сведений MCC_ДополнительныеНачисления. Это делается для того, чтобы получить состояние лимитов на начало периода и все их изменения внутри выбранного интервала.
Прежде чем что-то менять, необходимо привести запрос к читаемому виду. Разделим вложенные уровни отступами и выделим логические блоки. Рассмотрим пример правильного форматирования, где каждый уровень вложенности отделен скобками и имеет четкую структуру:
ВЫБРАТЬ
СотрСДатами.ПодразделениеОрганизации,
СотрСДатами.ДатаНачала,
СотрСДатами.ДатаОкончания,
Лимиты.Период КАК Период,
МИНИМУМ(Лимиты.Период1) КАК Период1,
Лимиты.Прекратить
ПОМЕСТИТЬ ЛимитыБезСумм
ИЗ
(
ВЫБРАТЬ
Лимиты.ПодразделениеОрганизации КАК ПодразделениеОрганизации,
Лимиты.Лимит КАК Лимит,
Лимиты.Период КАК Период,
ВЫБОР
КОГДА ЕСТЬNULL(Лимит1.Период, &ДатаКон) = &ДатаКон
ТОГДА &ДатаКон
ИНАЧЕ КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(Лимит1.Период, ДЕНЬ, -1), ДЕНЬ)
КОНЕЦ КАК Период1,
Лимиты.Прекратить КАК Прекратить
ИЗ
(ВЫБРАТЬ... ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ...) КАК Лимиты
ЛЕВОЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ... ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ...) КАК Лимит1
ПО Лимиты.ПодразделениеОрганизации = Лимит1.ПодразделениеОрганизации
И Лимиты.Период < Лимит1.Период
) КАК Лимиты
Важный момент: Обратите внимание на условие Лимиты.Период < Лимит1.Период. Это классический прием «развертки» периодов в 1С. Мы соединяем таблицу саму с собой, чтобы для каждой записи найти «следующую» по времени дату и использовать ее как дату окончания текущего интервала.
Чтобы не запутаться, присвоим каждому вложенному запросу уникальный номер. Выясним причину, по которой это необходимо: при анализе 900-строчного запроса только уникальные имена (например, Лимиты0, Лимиты2, Лимиты3) позволят вам быстро найти место возникновения ошибки.
Посмотрим на результат переименования:
ВЫБРАТЬ
Лимиты3.ПодразделениеОрганизации,
Лимиты3.Период КАК Период,
МИНИМУМ(Лимиты3.Период1) КАК Период1
ИЗ
(ВЫБРАТЬ
Лимиты2.ПодразделениеОрганизации КАК ПодразделениеОрганизации,
Лимиты2.Период КАК Период,
ВЫБОР
КОГДА ЕСТЬNULL(Лимит1.Период, &ДатаКон) = &ДатаКон ТОГДА &ДатаКон
ИНАЧЕ КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(Лимит1.Период, ДЕНЬ, -1), ДЕНЬ)
КОНЕЦ КАК Период1
ИЗ
(ВЫБРАТЬ ... ) КАК Лимиты2
ЛЕВОЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ ... ) КАК Лимиты0
ПО Лимиты2.ПодразделениеОрганизации = Лимит1.ПодразделениеОрганизации
И Лимиты2.Период < Лимит1.Период) КАК Лимиты3
Теперь структура стала прозрачной. Мы видим, что Лимиты2 — это источник данных, а Лимиты0 (внутри Лимит1) используется для поиска даты окончания периода.
Проанализируем содержимое блоков Лимиты0 и Лимиты2. При внимательном сравнении текстов часто выясняется, что «файлы идентичны». Это означает, что система дважды выполняет один и тот же тяжелый запрос к базе данных (дважды считывает срез последних и дважды сканирует физическую таблицу регистра).
Рекомендация: Если вы видите два абсолютно одинаковых вложенных запроса, это первый кандидат на вынос во Временную Таблицу. Вы один раз получаете данные, индексируете их и затем используете в соединениях.
Разберем по шагам, как превратить этот запутанный код в элегантную последовательность временных таблиц. Это не только сократит количество строк (те самые 900 строк могут превратиться в 200), но и значительно ускорит выполнение.
1. Создаем ВТ_ВсеСобытия:
Сюда мы помещаем результат объединения СрезПоследних и данных регистра. Это «сырые» точки изменения лимитов.
ВЫБРАТЬ
ПодразделениеОрганизации, Лимит, &ДатаНач КАК Период
ИЗ РегистрСведений.MCC_ДополнительныеНачисления.СрезПоследних(&ДатаНач, Сотрудник = &ПустойСотр)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПодразделениеОрганизации, Лимит, Период
ИЗ РегистрСведений.MCC_ДополнительныеНачисления
ГДЕ Период МЕЖДУ &ДатаНач И &ДатаКон И Сотрудник = &ПустойСотр
ПОМЕСТИТЬ ВТ_ВсеСобытия;
ИНДЕКСИРОВАТЬ ПО ПодразделениеОрганизации, Период
2. Рассчитываем периоды действия:
Теперь мы соединяем ВТ_ВсеСобытия саму с собой. Благодаря индексу, SQL-сервер сделает это мгновенно.
ВЫБРАТЬ
Т1.ПодразделениеОрганизации,
Т1.Период КАК ДатаНачалаЗаписи,
МИНИМУМ(ЕСТЬNULL(Т2.Период, &ДатаКон)) КАК ДатаОкончанияЗаписи
ПОМЕСТИТЬ ВТ_ИнтервалыЛимитов
ИЗ ВТ_ВсеСобытия КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВсеСобытия КАК Т2
ПО Т1.ПодразделениеОрганизации = Т2.ПодразделениеОрганизации
И Т1.Период < Т2.Период
СГРУППИРОВАТЬ ПО Т1.ПодразделениеОрганизации, Т1.Период
Проанализировав ситуацию, можно выделить несколько ключевых выгод от использования временных таблиц в подобных задачах:
ПодразделениеОрганизации и Период.Сотрудниками, Начислениями или Остатками в разных частях общего запроса без дублирования кода.Разбор запроса «с бородой» — это всегда работа исследователя. Начните с форматирования, выделите уровни вложенности уникальными именами, найдите дублирующиеся блоки и безжалостно выносите их во временные таблицы. Это единственный способ превратить трудноподдерживаемый код в эффективный инструмент, который не будет «падать» по таймауту при росте объема данных.