При разработке отчетов в системе компоновки данных (СКД) разработчики нередко сталкиваются с ситуацией, когда простой по структуре запрос выполняется десятки минут или полностью подвешивает базу данных. Типичный сценарий такой проблемы — необходимость вывести данные из регистра накопления и дополнить их реквизитами из табличной части документа-регистратора.
Разберем детально, почему запрос может выполняться медленно, какие грубые ошибки допускаются при связывании таблиц и как правильно оптимизировать запрос в платформе 1С:Предприятие.
Посмотрим на пример запроса, вызвавшего критическое падение производительности:
ВЫБРАТЬ
ДилЗаказТабличнаяЧасть1.Срок КАК Срок,
Потолки.Код,
Потолки.Заказчик,
Потолки.Материал,
Потолки.Площадь
ИЗ
РегистрНакопления.Потолки КАК Потолки
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ДилЗаказ.ТабличнаяЧасть1 КАК ДилЗаказТабличнаяЧасть1
ПО Потолки.Регистратор.Ссылка = ДилЗаказТабличнаяЧасть1.Срок
ГДЕ
ДилЗаказТабличнаяЧасть1.Ссылка.Дата МЕЖДУ &НачалоПериода И &ОкончаниеПериода
СГРУППИРОВАТЬ ПО
ДилЗаказТабличнаяЧасть1.Срок,
Потолки.Код,
Потолки.Заказчик,
Потолки.Материал,
Потолки.Площадь
Проанализируем этот код и выделим ключевые узкие места:
ПО Потолки.Регистратор.Ссылка = ДилЗаказТабличнаяЧасть1.Срок выполняется попытка соединить ссылку на документ с реквизитом даты или числа. На уровне СУБД такое сравнение либо возвращает пустой результат, либо заставляет платформу выполнять тяжелое неявное приведение типов на каждой строке.Регистратор: Поле Регистратор в регистрах накопления имеет составной ссылочный тип (все документы, являющиеся регистраторами данного регистра). Конструкция Потолки.Регистратор.Ссылка приводит к тому, что платформа генерирует неявные левые соединения со всеми физическими таблицами документов, входящих в состав регистраторов. Это многократно усложняет план запроса СУБД. Кроме того, само разыменование .Ссылка от ссылочного поля бессмысленно, так как Регистратор уже содержит ссылку.ГДЕ через дату документа (ДилЗаказТабличнаяЧасть1.Ссылка.Дата). Физическая таблица регистра накопления проиндексирована по полям Период и Регистратор. Отсутствие фильтра по полю Потолки.Период заставляет СУБД сканировать всю таблицу регистра целиком (Table Scan) вместо эффективного поиска по диапазону индекса.СГРУППИРОВАТЬ ПО используется для устранения дублей, возникших из-за декартова произведения. Выгрузка огромного промежуточного объема некорректных записей во временную базу TempDB для последующей сортировки и дедупликации окончательно блокирует работу базы.Рассмотрим, как правильно составить прямой запрос, если реквизит Срок требуется получить из табличной части документа.
Разберем по шагам необходимые исправления:
Период.ВЫРАЗИТЬ, чтобы платформа 1С обращалась только к таблице конкретного документа, а не ко всем возможным регистраторам.Посмотрим на пример оптимизированного запроса:
ВЫБРАТЬ
ДилЗаказТабличнаяЧасть.Срок КАК Срок,
Потолки.Код КАК Код,
Потолки.Заказчик КАК Заказчик,
Потолки.Материал КАК Материал,
Потолки.Площадь КАК Площадь
ИЗ
РегистрНакопления.Потолки КАК Потолки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ДилЗаказ.ТабличнаяЧасть1 КАК ДилЗаказТабличнаяЧасть
ПО (ВЫРАЗИТЬ(Потолки.Регистратор КАК Документ.ДилЗаказ) = ДилЗаказТабличнаяЧасть.Ссылка)
И (Потолки.НомерСтроки = ДилЗаказТабличнаяЧасть.НомерСтроки)
ГДЕ
Потолки.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
Выясним причину, по которой изначально потребовалось соединение с табличной частью документа. Если поле Срок описывает характеристику операции и регулярно требуется для построения отчетов по остаткам или оборотам, правильным решением будет вынесение этого поля непосредственно в структуру регистра.
Срок (тип Дата) в метаданные регистра накопления Потолки.ДилЗаказ при формировании движений по регистру заполним значение реквизита: Движение.Срок = СтрокаТабличнойЧасти.Срок;.После изменения структуры метаданных необходимость в каких-либо соединениях полностью отпадает. Запрос становится предельно простым и быстрым:
ВЫБРАТЬ
Потолки.Срок КАК Срок,
Потолки.Код КАК Код,
Потолки.Заказчик КАК Заказчик,
Потолки.Материал КАК Материал,
Потолки.Площадь КАК Площадь
ИЗ
РегистрНакопления.Потолки КАК Потолки
ГДЕ
Потолки.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
Такой запрос задействует штатный кластерный индекс регистра и выполняется мгновенно даже на базах объемом в миллионы записей.
Если отчет строится с помощью схемы компоновки данных, и архитектуру регистра изменить нельзя, разберем альтернативный механизм без тяжелых соединений в общем запросе. В СКД можно разделить источники данных на два независимых набора.
ВЫБРАТЬ
Потолки.Регистратор КАК Регистратор,
Потолки.НомерСтроки КАК НомерСтроки,
Потолки.Код КАК Код,
Потолки.Заказчик КАК Заказчик,
Потолки.Материал КАК Материал,
Потолки.Площадь КАК Площадь
ИЗ
РегистрНакопления.Потолки КАК Потолки
ГДЕ
Потолки.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
ВЫБРАТЬ
ДилЗаказ.Ссылка КАК Ссылка,
ДилЗаказ.НомерСтроки КАК НомерСтроки,
ДилЗаказ.Срок КАК Срок
ИЗ
Документ.ДилЗаказ.ТабличнаяЧасть1 КАК ДилЗаказ
ГДЕ
ДилЗаказ.Ссылка.Дата МЕЖДУ &НачалоПериода И &ОкончаниеПериода
НаборДанных1;НаборДанных2;Регистратор = Ссылка И НомерСтроки = НомерСтроки.При таком подходе платформа СКД выполняет выборку порциями и связывает данные в оперативной памяти генератора отчета, исключая сбои СУБД и предотвращая блокировки таблиц.