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