Разработчики в среде «1С:Предприятие» регулярно сталкиваются с ситуациями, когда производительность отчетов или обработок резко падает при увеличении объемов данных. Одной из наиболее коварных и распространенных причин такого поведения является использование вычисляемых логических конструкций в секции фильтрации запроса. В частности, речь идет об операторе ВЫБОР внутри секции ГДЕ.
В этой статье мы подробно разберем, почему конструкция ВЫБОР в условиях запроса превращает даже самую простую выборку в кошмар для СУБД, проанализируем реальный пример из практики разработчиков, выявим скрытые сопутствующие ошибки и рассмотрим эффективные методы оптимизации с использованием временных таблиц и конструкции ОБЪЕДИНИТЬ ВСЕ.
Для того чтобы понять, почему СУБД «зависает» на подобных запросах, нам необходимо обратиться к теории баз данных и рассмотреть понятие SARGability (Search Argumentable — возможность использования поискового аргумента). Запрос или условие называют SARGable, если оптимизатор СУБД (будь то MS SQL Server, PostgreSQL, Oracle или IBM DB2) может эффективно использовать имеющиеся индексы для быстрого поиска нужных строк.
Когда мы накладываем простое условие фильтрации, например, ГДЕ РСУслуги.ДатаИВремя МЕЖДУ &ДатаНач И &ДатаКон, СУБД моментально обращается к индексу по полю ДатаИВремя. Она выполняет операцию Index Seek (поиск по индексу), переходя сразу к нужному диапазону записей в дереве индекса. Это происходит за доли миллисекунд даже на таблицах в десятки миллионов строк.
Что происходит, когда мы оборачиваем поле в оператор ВЫБОР внутри условия ГДЕ? Рассмотрим типичное выражение:
ГДЕ
(ВЫБОР
КОГДА РСУслуги.ДатаИВремя = ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
ТОГДА ТЧДокумента.Ссылка.Дата
ИНАЧЕ РСУслуги.ДатаИВремя
КОНЕЦ) МЕЖДУ &ДатаНач И &ДатаКон
С точки зрения СУБД, левая часть сравнения перестает быть простым полем. Теперь это динамически вычисляемое выражение (функция CASE WHEN... на уровне SQL-запроса). СУБД принципиально не может сопоставить результат выполнения этого динамического выражения со значениями в статичном B-Tree индексе. У нее просто нет готового индекса по результату этой функции.
В результате СУБД вынуждена отказаться от операции Index Seek и переходит к стратегии Index Scan или Table Scan (полное сканирование индекса или таблицы). Процесс выполнения выглядит следующим образом:
ВЫБОР на уровне процессора сервера СУБД.&ДатаНач и &ДатаКон.Если в вашей базе данных хранятся сотни тысяч или миллионы строк, такой запрос гарантированно приведет к следующим негативным последствиям:
---
Давайте внимательно проанализируем реальный запрос, который вызывал проблемы с производительностью:
ВЫБРАТЬ
ТЧДокумента.Ссылка КАК Ссылка,
ТЧДокумента.НомерСтроки КАК НомерСтроки,
ТЧДокумента.Номенклатура КАК Номенклатура,
ТЧДокумента.УникальныйИдентификатор,
РСУслуги.Коэффициент КАК Коэффициент,
ВЫБОР
КОГДА РСУслуги.ДатаИВремя = ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
ТОГДА ТЧДокумента.Ссылка.Дата
ИНАЧЕ РСУслуги.ДатаИВремя
КОНЕЦ КАК ДатаИВремя
ИЗ
Документ.Заказ.Услуги КАК ТЧДокумента
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Услуги КАК РСУслуги
ПО ТЧДокумента.УникальныйИдентификатор = РСУслуги.УникальныйИдентификатор
ГДЕ
ТЧДокумента.НомерСтроки.Ссылка.Проведен = ИСТИНА
И (ВЫБОР
КОГДА РСУслуги.ДатаИВремя = ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
ТОГДА ТЧДокумента.Ссылка.Дата
ИНАЧЕ РСУслуги.ДатаИВремя
КОНЕЦ) МЕЖДУ &ДатаНач И &ДатаКон
Помимо очевидной проблемы с ВЫБОР в секции ГДЕ, здесь присутствуют еще две грубейшие архитектурные ошибки, которые «убивают» производительность этого кода:
1. Ошибка разыменования ссылки через числовое поле. Обратите внимание на условие: ТЧДокумента.НомерСтроки.Ссылка.Проведен = ИСТИНА. Реквизит НомерСтроки табличной части документа имеет тип Число. Попытка обратиться к свойству .Ссылка от числа — это синтаксическая и логическая ошибка. Платформа 1С при выполнении такого запроса либо выдаст ошибку, либо неявно преобразует запрос в неоптимальную структуру. Корректное обращение должно быть напрямую от табличной части к документу-владельцу: ТЧДокумента.Ссылка.Проведен = ИСТИНА.
2. Неявные левые соединения (разыменование через точку). Обращение к реквизитам через точку от ссылочного поля (например, ТЧДокумента.Ссылка.Дата или ТЧДокумента.Ссылка.Проведен) заставляет платформу «1С:Предприятие» генерировать неявные левые соединения (LEFT OUTER JOIN) с таблицей шапки документа Документ.Заказ на уровне СУБД. Если бы поле имело составной тип данных, СУБД создала бы соединения со всеми таблицами типов, входящих в составной тип. Это приводит к значительному усложнению плана выполнения запроса.
3. Соединение по неиндексированному строковому полю. Связь таблиц происходит по полю УникальныйИдентификатор. Если в регистре сведений Услуги данный реквизит не является измерением (измерения регистров индексируются автоматически) и для него не установлено свойство Индексировать в конфигураторе, то соединение огромной табличной части с регистром будет выполняться крайне медленно через сканирование всей таблицы регистра.
---
Выясним, как кардинально переписать логику запроса без использования медленного ВЫБОР в условиях. Самым эффективным решением здесь является разделение запроса на две независимые ветки с последующим их объединением через ОБЪЕДИНИТЬ ВСЕ.
Мы разобьем нашу логику на два простых сценария:
01.01.0001). В этом случае мы накладываем условие отбора по дате напрямую на индексированное поле регистра сведений ДатаИВремя.01.01.0001). В этом случае мы подставляем дату документа и накладываем условие отбора напрямую на индексированное поле даты документа.Рассмотрим по шагам, как будет выглядеть такой оптимизированный запрос:
ВЫБРАТЬ
ТЧДокумента.Ссылка КАК Ссылка,
ТЧДокумента.НомерСтроки КАК НомерСтроки,
ТЧДокумента.Номенклатура КАК Номенклатура,
ТЧДокумента.УникальныйИдентификатор КАК УникальныйИдентификатор,
РСУслуги.Коэффициент КАК Коэффициент,
РСУслуги.ДатаИВремя КАК ДатаИВремя
ИЗ
Документ.Заказ.Услуги КАК ТЧДокумента
// Явно соединяем с шапкой документа, чтобы избежать скрытых соединений через точку
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заказ КАК ШапкаДокумента
ПО ТЧДокумента.Ссылка = ШапкаДокумента.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Услуги КАК РСУслуги
ПО ТЧДокумента.УникальныйИдентификатор = РСУслуги.УникальныйИдентификатор
ГДЕ
ШапкаДокумента.Проведен = ИСТИНА
И РСУслуги.ДатаИВремя <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И РСУслуги.ДатаИВремя МЕЖДУ &ДатаНач И &ДатаКон
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТЧДокумента.Ссылка КАК Ссылка,
ТЧДокумента.НомерСтроки КАК НомерСтроки,
ТЧДокумента.Номенклатура КАК Номенклатура,
ТЧДокумента.УникальныйИдентификатор КАК УникальныйИдентификатор,
РСУслуги.Коэффициент КАК Коэффициент,
ШапкаДокумента.Дата КАК ДатаИВремя
ИЗ
Документ.Заказ.Услуги КАК ТЧДокумента
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заказ КАК ШапкаДокумента
ПО ТЧДокумента.Ссылка = ШапкаДокумента.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Услуги КАК РСУслуги
ПО ТЧДокумента.УникальныйИдентификатор = РСУслуги.УникальныйИдентификатор
ГДЕ
ШапкаДокумента.Проведен = ИСТИНА
И РСУслуги.ДатаИВремя = ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И ШапкаДокумента.Дата МЕЖДУ &ДатаНач И &ДатаКон
Почему этот вариант работает в разы быстрее?
СУБД теперь видит два абсолютно прозрачных запроса. В первой части СУБД использует индекс по полю ДатаИВремя таблицы регистра сведений Услуги и мгновенно отсекает строки, не входящие в период. Во второй части СУБД использует стандартный индекс по полю Дата таблицы документов Заказ. В обоих случаях выполняется быстрая операция Index Seek. Полученные результаты просто склеиваются в один поток без дополнительных затрат на вычисления.
Важное примечание: Мы использовали именно ОБЪЕДИНИТЬ ВСЕ вместо ОБЪЕДИНИТЬ. Оператор ОБЪЕДИНИТЬ выполняет неявную операцию DISTINCT (поиск и удаление дублирующихся строк), что заставляет СУБД выполнять сортировку всей выборки в оперативной памяти (или сброс во временную базу TempDB), сильно замедляя запрос. ОБЪЕДИНИТЬ ВСЕ просто склеивает результаты, работая максимально быстро.
---
Если документов в базе данных очень много, а период отбора достаточно велик, даже разделение через ОБЪЕДИНИТЬ ВСЕ может производить избыточную работу, повторно соединяя таблицы документов и регистров в разных ветках. В таких сценариях идеальным решением является предварительное кэширование данных во временные таблицы (ВТ) с их последующей индексацией.
Проанализируем этот двухшаговый подход подробнее:
Шаг 1. Отбор документов и выгрузка во временную таблицу. Мы предварительно выбираем только проведенные документы за интересующий нас период (с небольшим «запасом» по дате, если даты в регистре могут отличаться от дат документов), чтобы СУБД не анализировала весь исторический объем базы данных.
Шаг 2. Индексация временной таблицы. Перед соединением временной таблицы с регистром сведений мы обязаны проиндексировать ее по полям связи. Это критически важно для СУБД, чтобы соединение происходило по индексу, а не путем полного сканирования.
Посмотрим на практический пример реализации такой схемы запроса:
// ШАГ 1: Отбираем только подходящие по дате проведенные документы во временную таблицу
ВЫБРАТЬ
Заказ.Ссылка КАК Ссылка,
Заказ.Дата КАК Дата
ПОМЕСТИТЬ ВТ_Документы
ИЗ
Документ.Заказ КАК Заказ
ГДЕ
Заказ.Проведен = ИСТИНА
И Заказ.Дата МЕЖДУ &ДатаНач И &ДатаКон
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Соединяем подготовленный и ограниченный список документов с табличной частью и регистром
ВЫБРАТЬ
ТЧДокумента.Ссылка КАК Ссылка,
ТЧДокумента.НомерСтроки КАК НомерСтроки,
ТЧДокумента.Номенклатура КАК Номенклатура,
ТЧДокумента.УникальныйИдентификатор КАК УникальныйИдентификатор,
РСУслуги.Коэффициент КАК Коэффициент,
РСУслуги.ДатаИВремя КАК ДатаИВремя
ИЗ
ВТ_Документы КАК ВТ_Документы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заказ.Услуги КАК ТЧДокумента
ПО ВТ_Документы.Ссылка = ТЧДокумента.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Услуги КАК РСУслуги
ПО ТЧДокумента.УникальныйИдентификатор = РСУслуги.УникальныйИдентификатор
ГДЕ
РСУслуги.ДатаИВремя <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И РСУслуги.ДатаИВремя МЕЖДУ &ДатаНач И &ДатаКон
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТЧДокумента.Ссылка КАК Ссылка,
ТЧДокумента.НомерСтроки КАК НомерСтроки,
ТЧДокумента.Номенклатура КАК Номенклатура,
ТЧДокумента.УникальныйИдентификатор КАК УникальныйИдентификатор,
РСУслуги.Коэффициент КАК Коэффициент,
ВТ_Документы.Дата КАК ДатаИВремя
ИЗ
ВТ_Документы КАК ВТ_Документы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заказ.Услуги КАК ТЧДокумента
ПО ВТ_Документы.Ссылка = ТЧДокумента.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Услуги КАК РСУслуги
ПО ТЧДокумента.УникальныйИдентификатор = РСУслуги.УникальныйИдентификатор
ГДЕ
РСУслуги.ДатаИВремя = ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И ВТ_Документы.Дата МЕЖДУ &ДатаНач И &ДатаКон
Используя этот подход, мы гарантируем СУБД минимальный объем обрабатываемых данных. Сначала мы жестко отсекаем документы по индексу в шапке, затем индексируем временную таблицу по ссылке, и только потом подключаем тяжелые таблицы табличных частей и регистров сведений. Данный шаблон проектирования запросов считается золотым стандартом разработки на платформе «1С:Предприятие 8».
---
Для наглядности сравним все три подхода к написанию данного запроса:
| Критерий сравнения | Исходный запрос (ВЫБОР в ГДЕ) | Оптимизация через ОБЪЕДИНИТЬ ВСЕ | Оптимизация через Временные Таблицы + ОБЪЕДИНИТЬ ВСЕ |
|---|---|---|---|
| Использование индексов СУБД | Нет (Сканирование таблиц / Scan) | Да (Поиск по индексу / Index Seek) | Да (Максимально эффективный поиск) |
| Скорость на малых базах | Удовлетворительно | Быстро | Быстро |
| Скорость на больших базах (миллионы строк) | Критически медленно (зависания, таймауты) | Быстро | Максимально быстро и стабильно |
| Нагрузка на TempDB/ОЗУ | Очень высокая (из-за сканирования) | Низкая | Минимальная (строго контролируемый объем данных) |
---
Чтобы ваши запросы всегда выполнялись быстро, а пользователи не жаловались на медленную работу системы, рекомендуем придерживаться следующих правил:
ЕСТЬNULL, ВЫБОР, арифметические операции (сложение, вычитание дат) или функции работы со строками делают запрос не-SARGable. СУБД перестанет использовать индексы.ТЧ.Ссылка.Проведен) — это скрытый LEFT JOIN. Пишите соединения явно с помощью ключевых слов ВНУТРЕННЕЕ СОЕДИНЕНИЕ или ЛЕВОЕ СОЕДИНЕНИЕ. Это сделает ваш код прозрачным для оптимизатора СУБД.ИНДЕКСИРОВАТЬ ПО .... Без индексов СУБД будет вынуждена сканировать временную таблицу при каждом соединении.СправочникСсылка с аналогичным типом). Соединения по уникальным идентификаторам (UUID), строкам неограниченной длины или составным типам данных работают значительно медленнее.Применяя данные подходы на практике, вы сможете ускорить выполнение ваших отчетов в десятки и сотни раз, снизить нагрузку на сервер и обеспечить комфортную работу пользователей в системе «1С:Предприятие 8».