При работе с платформой 1С:Предприятие разработчики часто сталкиваются с задачами получения данных из табличных частей (ТЧ) справочников или документов. Типичный сценарий: у нас есть справочник с дополнительными реквизитами, организованными по модели EAV (Entity-Attribute-Value), где в строках ТЧ хранятся пары «ИмяРеквизита – Значение». Нам необходимо найти элемент справочника по одному реквизиту (например, «Объект»), а затем получить значение другого реквизита (например, «GUID») для этого же элемента.
В этой статье мы подробно разберем, почему один из вариантов запроса для решения этой задачи работает существенно медленнее другого, выясним скрытые механизмы работы платформы 1С и СУБД, а также рассмотрим несколько эффективных способов оптимизации таких запросов.
Проанализируем два запроса, которые решают одну и ту же задачу, но показывают разную производительность.
Вариант 1 (с использованием временной таблицы):
ВЫБРАТЬ РАЗЛИЧНЫЕ
КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка
ПОМЕСТИТЬ втСсылка
ИЗ
Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты
ГДЕ
КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.ИмяРеквизита = "Объект"
И КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Значение = &Объект
И НЕ КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка.ПометкаУдаления
И КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка.ИмяСправочника = "МеркурийПлощадка"
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втСсылка.Ссылка,
КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Значение
ИЗ
втСсылка КАК втСсылка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты
ПО втСсылка.Ссылка = КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка
ГДЕ
КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.ИмяРеквизита = "GUID"
Вариант 2 (прямое левое соединение без временных таблиц):
ВЫБРАТЬ
ДопрСправочникСсылка.Ссылка,
ДП2.Значение
ИЗ
Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК ДопрСправочникСсылка
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК ДП2
ПО ДопрСправочникСсылка.Ссылка = ДП2.Ссылка
ГДЕ
ДопрСправочникСсылка.ИмяРеквизита = "Объект"
И ДопрСправочникСсылка.Значение = &Объект
И НЕ ДопрСправочникСсылка.Ссылка.ПометкаУдаления
И ДопрСправочникСсылка.Ссылка.ИмяСправочника = "МеркурийПлощадка"
И ДП2.ИмяРеквизита = "GUID"
На практике оказывается, что Вариант 1 (с временной таблицей) отрабатывает значительно быстрее Варианта 2. Выясним, почему это происходит.
Внимательно посмотрим на условия в секции ГДЕ обоих запросов:
И НЕ КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка.ПометкаУдаления
И КонтурEDI_ДополнительныеСправочникиДополнительныеРеквизиты.Ссылка.ИмяСправочника = "МеркурийПлощадка"
Поле Ссылка в табличной части представляет собой ссылку на шапку справочника. Когда мы обращаемся к реквизитам шапки через точку от ТЧ (Ссылка.ПометкаУдаления, Ссылка.ИмяСправочника), платформа 1С выполняет так называемое разыменование полей.
Под капотом транслятор запросов 1С превращает каждое такое обращение в неявное левое соединение (LEFT OUTER JOIN) таблицы табличной части с таблицей шапки справочника. Поскольку обращение идет ко всей табличной части, СУБД вынуждена соединять эти таблицы по всем записям еще до того, как начнется основная фильтрация данных.
Разберем разницу в логике построения планов выполнения запросов для обоих вариантов:
&Объект (очень жесткий и селективный фильтр), выполняет соединение с шапкой для проверки пометки удаления и записывает результат (буквально несколько строк) в крошечную временную таблицу втСсылка. На втором шаге СУБД соединяет эту микроскопическую таблицу с физической таблицей ТЧ. Оптимизатору СУБД очень легко построить идеальный план выполнения, когда одна из соединяемых таблиц содержит всего несколько записей.&Объект. Это приводит к лишнему чтению страниц данных с диска (или из кэша СУБД) и росту времени выполнения.Кроме того, во втором варианте условие ДП2.ИмяРеквизита = "GUID" находится в секции ГДЕ. Это фактически превращает левое соединение (LEFT JOIN) во внутреннее (INNER JOIN), так как строки с NULL значениями отсекаются этим фильтром. Но оптимизатору СУБД сложнее перестроить план «на лету», чем когда это явно задано разработчиком.
Чтобы избежать неявных соединений и помочь оптимизатору построить быстрый план, мы можем применить явное соединение таблицы ТЧ с таблицей шапки справочника. Проанализируем следующий вариант запроса:
ВЫБРАТЬ
ДП2.Ссылка,
ДП2.Значение
ИЗ
Справочник.КонтурEDI_ДополнительныеСправочники КАК КонтурEDI_ДС
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ConturEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК КонтурEDI_ДР
ПО КонтурEDI_ДР.Ссылка = КонтурEDI_ДС.Ссылка
И КонтурEDI_ДР.ИмяРеквизита = "Объект"
И КонтурEDI_ДР.Значение = &Объект
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК ДП2
ПО ДП2.Ссылка = КонтурEDI_ДР.Ссылка
И ДП2.ИмяРеквизита = "GUID"
ГДЕ
КонтурEDI_ДС.ПометкаУдаления = Ложь
И КонтурEDI_ДС.ИмяСправочника = "МеркурийПлощадка"
Почему этот запрос работает быстрее всего?
Мы поменяли логику построения соединения. Теперь СУБД начинает выборку с таблицы шапки справочника (КонтурEDI_ДС), где по индексам ПометкаУдаления и ИмяСправочника быстро отсекает ненужные элементы. Затем полученный узкий набор данных соединяется с табличной частью по индексу Ссылка. Мы полностью убрали разыменование полей через точку, исключив скрытые левые соединения СУБД.
Для EAV-моделей существует еще один крайне эффективный метод. Он позволяет получить значения разных реквизитов из ТЧ за один проход по таблице без многократных соединений ТЧ самой с собой. Мы можем использовать конструкцию МАКСИМУМ(ВЫБОР...) с группировкой.
Рассмотрим этот метод на примере:
ВЫБРАТЬ
ДопРеквизиты.Ссылка КАК Ссылка,
МАКСИМУМ(ВЫБОР КОГДА ДопРеквизиты.ИмяРеквизита = "Объект" ТОГДА ДопРеквизиты.Значение КОНЕЦ) КАК Объект,
МАКСИМУМ(ВЫБОР КОГДА ДопРеквизиты.ИмяРеквизита = "GUID" ТОГДА ДопРеквизиты.Значение КОНЕЦ) КАК GUID
ПОМЕСТИТЬ втДанные
ИЗ
Справочник.КонтурEDI_ДополнительныеСправочники.ДополнительныеРеквизиты КАК ДопРеквизиты
ГДЕ
ДопРеквизиты.ИмяРеквизита В ("Объект", "GUID")
СГРУППИРОВАТЬ ПО
ДопРеквизиты.Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втДанные.Ссылка,
втДанные.GUID
ИЗ
втДанные КАК втДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.КонтурEDI_ДополнительныеСправочники КАК Шапка
ПО втДанные.Ссылка = Шапка.Ссылка
ГДЕ
втДанные.Объект = &Объект
И НЕ Шапка.ПометкаУдаления
И Шапка.ИмяСправочника = "МеркурийПлощадка"
Разберем, как это работает: во временную таблицу втДанные мы считываем только те строки ТЧ, у которых ИмяРеквизита принимает значения «Объект» или «GUID». С помощью группировки по полю Ссылка и условного агрегата МАКСИМУМ мы «разворачиваем» строки в колонки. СУБД делает всего один легкий проход по табличной части. После этого мы соединяем полученный результат с таблицей шапки и накладываем необходимые отборы.
1. Развенчание мифа: «ЛЕВОЕ СОЕДИНЕНИЕ + ГДЕ НЕ NULL» против «ВНУТРЕННЕГО СОЕДИНЕНИЯ»
В процессе обсуждения часто всплывает старый миф о том, что левое соединение с последующим отбором по НЕ NULL в секции ГДЕ работает быстрее, чем обычное внутреннее соединение. Это утверждение было актуально во времена старых версий MS SQL Server (2000/2005) и старых платформ 1С 8.1/8.2, когда оптимизатор СУБД мог построить неоптимальный план для INNER JOIN.
В современных реалиях (MS SQL Server 2016+, PostgreSQL) этот баг давно исправлен. Использование INNER JOIN является стандартом. Оно позволяет оптимизатору СУБД точнее оценить кардинальность (количество строк) на этапе планирования запроса, что предотвращает выбор тяжелых операций типа Hash Match вместо быстрых Nested Loops.
2. Индексация временных таблиц: когда она действительно нужна?
При использовании временных таблиц всегда встает вопрос о необходимости их индексации:
Index Seek) вместо полного сканирования таблицы (Table Scan).tempdb превысят всю возможную выгоду от его использования.Для достижения максимальной производительности при написании запросов в 1С придерживайтесь следующих правил: