Разработчики на платформе «1С:Предприятие 8» регулярно сталкиваются с ситуацией, когда простой и лаконичный запрос в конструкторе 1С при выполнении начинает сильно «тормозить». При анализе такого запроса через технологический журнал или SQL Server Profiler выясняется поразительная вещь: СУБД получает гигантский SQL-код, состоящий из тысяч строк и содержащий сотни операций LEFT OUTER JOIN. В этой статье мы подробно разберем, почему так происходит, как одна маленькая точка в запросе может обрушить производительность всей системы, и как правильно оптимизировать такие запросы.
Для того чтобы понять причины столь странного поведения транслятора запросов 1С, нам необходимо заглянуть на уровень базы данных СУБД. В платформе «1С:Предприятие» существует возможность назначать реквизитам составные типы данных (например, когда реквизит может содержать ссылку на любой документ или справочник). В СУБД такое поле не может храниться в одной колонке, поскольку разные типы данных требуют разной идентификации.
Поэтому для каждого реквизита составного типа в физической таблице СУБД создается несколько колонок:
Когда мы пишем запрос к такому полю, платформа должна корректно сопоставить эти колонки с физическими таблицами СУБД.
Рассмотрим исходный запрос, который вызвал проблему у автора темы:
ВЫБРАТЬ
ДокументВозвратТоваровОтПокупателя.Ссылка КАК Ссылка,
Регистр_ТКД.ЕстьКопия КАК ЕстьТКД
ИЗ
Документ.ВозвратТоваровОтПокупателя КАК ДокументВозвратТоваровОтПокупателя
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Регистр_ТКД КАК Регистр_ТКД
ПО ДокументВозвратТоваровОтПокупателя.Ссылка = Регистр_ТКД.ДокТКД.Ссылка
И Регистр_ТКД.ДокТКД ССЫЛКА Документ.ВозвратТоваровОтПокупателя
Проанализируем условие связи: ДокументВозвратТоваровОтПокупателя.Ссылка = Регистр_ТКД.ДокТКД.Ссылка. Реквизит ДокТКД в регистре сведений имеет составной тип «Любая ссылка» (или содержит очень длинный список возможных документов).
Обращаясь к полю составного типа через точку (даже если это безобидное обращение к свойству .Ссылка), мы запускаем механизм неявного разыменования (dereferencing). Платформа 1С трактует это так: «Разработчик хочет получить значение свойства конкретного объекта из составного типа». Но поскольку тип составной, компилятор запросов 1С не знает заранее, в какой именно таблице лежит этот объект.
Чтобы гарантировать корректность запроса на уровне базы данных, транслятор 1С генерирует левые соединения (LEFT OUTER JOIN) со всеми физическими таблицами объектов, которые теоретически могут храниться в этом реквизите. Если в вашей конфигурации объявлено 200 видов документов, платформа честно добавит 200 левых соединений в итоговый SQL-запрос. СУБД тратит колоссальные ресурсы на компиляцию такого плана выполнения и его кэширование, что и приводит к зависанию системы.
Выяснив причину проблемы, мы можем легко её устранить. Поскольку свойство Ссылка для любого объекта — это и есть сам уникальный идентификатор (который уже хранится в поле _RRRef составного типа), нам совершенно не требуется обращаться к нему через точку. Достаточно сравнивать поля напрямую.
Перепишем наше условие связи, убрав .Ссылка у составного поля:
ВЫБРАТЬ
ДокументВозвратТоваровОтПокупателя.Ссылка КАК Ссылка,
Регистр_ТКД.ЕстьКопия КАК ЕстьТКД
ИЗ
Документ.ВозвратТоваровОтПокупателя КАК ДокументВозвратТоваровОтПокупателя
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Регистр_ТКД КАК Регистр_ТКД
ПО ДокументВозвратТоваровОтПокупателя.Ссылка = Регистр_ТКД.ДокТКД
Обратите внимание: нам даже не требуется дополнительное условие ССЫЛКА Документ.ВозвратТоваровОтПокупателя. Почему? Потому что левая таблица связи Документ.ВозвратТоваровОтПокупателя уже является строго типизированной. Платформа 1С на этапе трансляции понимает это и строит максимально эффективный SQL-код:
LEFT OUTER JOIN dbo._InfoRg31523 T2
ON T2._Fld31524_RRRef = T1._IDRRef
AND T2._Fld31524_RTRef = 0x00000139
AND T2._Fld31524_TYPE = 0x08
Вместо 4000 строк кода SQL-сервер получает лаконичное и быстрое соединение, которое мгновенно отрабатывает по индексам. Время выполнения падает с минут до миллисекунд.
В ходе обсуждения на форумах часто предлагают альтернативный вариант: использовать конструкцию ВЫРАЗИТЬ непосредственно в условии связи, чтобы явно указать платформе тип соединяемого поля:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Регистр_ТКД КАК Регистр_ТКД
ПО ДокументВозвратТоваровОтПокупателя.Ссылка = ВЫРАЗИТЬ(Регистр_ТКД.ДокТКД КАК Документ.ВозвратТоваровОтПокупателя)
Рассмотрим подробнее, почему этот подход является опасной ловушкой производительности, которая на больших базах данных работает медленнее прямого сравнения в сотни раз.
Проанализируем физический смысл происходящего в СУБД. На уровне SQL-запроса конструкция ВЫРАЗИТЬ(Регистр_ТКД.ДокТКД КАК Документ.ВозвратТоваровОтПокупателя) компилируется СУБД в логическую ветку вычисления:
ON ((CASE WHEN T2._Fld31524_RTRef = 0x00000139 THEN T2._Fld31524_RRRef END = T1._IDRRef))
С точки зрения оптимизатора MS SQL Server (и любой другой современной СУБД), такое условие является Non-Sargable (не подлежащим оптимизации с помощью поиска по индексу). СУБД не может выполнить операцию Index Seek (быстрый точечный поиск по индексу регистра), поскольку колонка _Fld31524_RRRef «обернута» в функцию CASE WHEN.
В результате СУБД вынуждена прибегнуть к полному сканированию индекса или всей таблицы (Clustered Index Scan или Table Scan). Если в регистре содержатся миллионы строк, ваш запрос полностью парализует работу сервера. Тесты на реальных базах данных подтверждают эту теорию: выполнение запроса с прямым сравнением занимает менее 1 мс, в то время как аналогичный запрос с ВЫРАЗИТЬ в условии JOIN выполняется более 2.3 секунд (замедление более чем в 200 раз!).
Что же делать, если нам действительно необходимо предварительно типизировать составное поле перед соединением, чтобы исключить лишние записи из выборки и повысить скорость работы СУБД? Для этого существует золотой стандарт разработки на платформе 1С — использование временных таблиц.
Разберем этот паттерн по шагам:
ГДЕ.ГДЕ условие ССЫЛКА отлично оптимизируется СУБД, так как преобразуется в простое равенство полей типа и таблицы (_RTRef = 0x00000139), что позволяет задействовать индекс.ВЫРАЗИТЬ значение.Посмотрим на пример реализации этого паттерна:
// Шаг 1: Выбираем данные и типизируем их во временной таблице
ВЫБРАТЬ
ВЫРАЗИТЬ(Регистр_ТКД.ДокТКД КАК Документ.ВозвратТоваровОтПокупателя) КАК ДокТКД,
Регистр_ТКД.ЕстьКопия КАК ЕстьТКД
ПОМЕСТИТЬ ВТ_ДанныеРегистра
ИЗ
РегистрСведений.Регистр_ТКД КАК Регистр_ТКД
ГДЕ
Регистр_ТКД.ДокТКД ССЫЛКА Документ.ВозвратТоваровОтПокупателя
;
// Шаг 2: Выполняем быстрое и простое соединение
ВЫБРАТЬ
ДокументВозвратТоваровОтПокупателя.Ссылка КАК Ссылка,
ВТ_ДанныеРегистра.ЕстьТКД КАК ЕстьТКД
ИЗ
Документ.ВозвратТоваровОтПокупателя КАК ДокументВозвратТоваровОтПокупателя
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеРегистра КАК ВТ_ДанныеРегистра
ПО ДокументВозвратТоваровОтПокупателя.Ссылка = ВТ_ДанныеРегистра.ДокТКД
Благодаря такому подходу мы получаем два простых, предсказуемых и легких для СУБД плана выполнения запросов вместо одного перегруженного «монстра». Это гарантирует стабильную скорость работы системы даже при росте объема базы данных в геометрической прогрессии.
Проблема разыменования составных полей через точку становится критической, если в вашей информационной базе включен механизм RLS (Row-Level Security). Когда пользователь с ограниченными правами запускает запрос с неявным разыменованием (например, содержащий ДокТКД.Ссылка), платформа 1С вынуждена не просто добавить 200 левых соединений, но и к каждому из них применить подзапросы ограничений RLS, соответствующие конкретному виду документа.
В этом случае SQL-запрос мгновенно разрастается до сотен тысяч строк. Сервер СУБД тратит минуты только на то, чтобы распарсить этот текст и построить план запроса, расходуя гигабайты оперативной памяти. Избежать этого позволяет только строгое следование правилам разработки и отказ от использования разыменования составных типов.
Для обеспечения максимального быстродействия систем на платформе «1С:Предприятие 8» придерживайтесь следующих правил:
.Ссылка у полей составного типа. Обращайтесь к полю напрямую (Регистр.Документ вместо Регистр.Документ.Ссылка).ВЫРАЗИТЬ непосредственно внутри условий связи ПО (ON) в соединениях таблиц.ГДЕ.