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