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