При разработке механизмов проведения документов или формировании отчетов разработчики 1С часто сталкиваются с задачей получения актуальных остатков регистра по списку позиций из табличной части документа. Неправильное построение такого запроса способно многократно снизить производительность системы, вызвать избыточные блокировки таблиц базы данных и привести к неверным результатам.
Рассмотрим две типичные реализации запроса, выясним причины их неоптимальности и разберем, как написать по-настоящему эффективный и надежный запрос к виртуальной таблице остатков.
Разберем два варианта запроса, предложенных к сравнению:
Вариант 1: Создается временная таблица ВТ_Товары по ссылке на документ, а затем во втором запросе пакета виртуальная таблица РегистрНакопления.ОстаткиНоменклатуры.Остатки соединяется с табличной частью документа, причем в параметрах среза используется фильтр Номенклатура В (ВЫБРАТЬ ВТ_Товары.Номенклатура ИЗ ВТ_Товары).
Вариант 2: Структура аналогична, однако в параметрах виртуальной таблицы остатков отбор по номенклатуре полностью отсутствует, а связь выполняется только через секцию ПО левого соединения.
Выясним, почему второй вариант категорически неприемлем, а первый содержит грубые архитектурные ошибки.
Если в параметрах виртуальной таблицы ОстаткиНоменклатуры.Остатки не указать условие отбора по измерению Номенклатура, платформа 1С выполнит расчет остатков по абсолютно всем существующим в базе позициям номенклатуры на указанный &МоментВремени. Лишь после полного расчета итогов СУБД отфильтрует строки по условию соединения.
В реальной рабочей базе с сотнями тысяч записей это приводит к колоссальной нагрузке на процессор, чтению гигабайтов данных с диска и длительным транзакционным блокировкам. На экзаменах «1С:Специалист» за отсутствие фильтров в параметрах виртуальных таблиц сразу снимаются баллы.
Хотя в первом варианте отбор в виртуальную таблицу передан, сам запрос содержит несколько критических недостатков:
Документ.Оприходование.Товары, но условие ГДЕ Ссылка = &Ссылка отсутствует. В результате система выполняет соединение всех строк всех документов конфигурации с полученными остатками.ВТ_Товары, нет никакого смысла снова обращаться к тяжелой физической табличной части документа. Работать необходимо непосредственно с временной таблицей.ИНДЕКСИРОВАТЬ ПО заставляет СУБД применять неоптимальные планы выполнения (например, сканирование таблицы вместо индексного поиска).Разберем по шагам, как построить производительный запрос в зависимости от контекста задачи.
Если нам требуется вывести строки по всем позициям документа, даже если остатка на складе нет или движений по регистру никогда не было, применяем левое соединение проиндексированной временной таблицы со свернутыми товарами и виртуальной таблицы остатков. При этом обязательно обрабатываем NULL через функцию ЕСТЬNULL:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ОприходованиеТовары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
Документ.Оприходование.Товары КАК ОприходованиеТовары
ГДЕ
ОприходованиеТовары.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Товары.Номенклатура КАК Номенклатура,
ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток
ИЗ
ВТ_Товары КАК ВТ_Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.Остатки(
&МоментВремени,
Номенклатура В
(ВЫБРАТЬ
ВТ_Товары.Номенклатура
ИЗ
ВТ_Товары)) КАК ОстаткиНоменклатурыОстатки
ПО ВТ_Товары.Номенклатура = ОстаткиНоменклатурыОстатки.Номенклатура
Проанализируем ключевые моменты этой структуры:
ВЫБРАТЬ РАЗЛИЧНЫЕ гарантирует уникальность ссылок номенклатуры во временной таблице.ИНДЕКСИРОВАТЬ ПО Номенклатура создает индекс в СУБД, что ускоряет выполнение операции SEMI JOIN при наложении отбора в виртуальной таблице.ВТ_Товары, а не из физической таблицы документа.ЕСТЬNULL заменяет отсутствующие значения нулем, предотвращая возможные ошибки вычислений в коде модуля.Если задачей является простой контроль или анализ ненулевых остатков, соединение таблиц вовсе не требуется. Достаточно прочитать данные только из виртуальной таблицы с фильтром по временной таблице:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ОприходованиеТовары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
Документ.Оприходование.Товары КАК ОприходованиеТовары
ГДЕ
ОприходованиеТовары.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.Остатки(
&МоментВремени,
Номенклатура В
(ВЫБРАТЬ
ВТ_Товары.Номенклатура
ИЗ
ВТ_Товары)) КАК Остатки
Этот вариант выполняется быстрее всего, так как не тратит ресурсы на операцию соединения на уровне СУБД. Однако помним: позиции, по которым остаток строго равен нулю или по которым не было записей в регистре, в итоговую выборку не попадут.
Рассмотрим подробнее ситуацию, когда номенклатура уже выгружена в массив в коде до вызова запроса (например, в обработке проведения небольшого документа до 20–50 строк). В этом случае создание временной таблицы в базе tempdb создает лишние накладные расходы.
Быстрее передать массив ссылок напрямую через параметр запроса:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.КоличествоОстаток КАК КоличествоОстаток
|ИЗ
| РегистрНакопления.ОстаткиНоменклатуры.Остатки(&МоментВремени, Номенклатура В (&МассивНоменклатуры)) КАК Остатки";
Запрос.УстановитьПараметр("МоментВремени", МоментВремени());
Запрос.УстановитьПараметр("МассивНоменклатуры", ТаблицаТоваров.ВыгрузитьКолонку("Номенклатура"));
Важное предостережение: передавать массив через параметр рекомендуется только при относительно небольшом количестве элементов. Если документ содержит сотни или тысячи строк, обязательно используйте пакетный запрос с временной таблицей и директивой ИНДЕКСИРОВАТЬ ПО, чтобы не перегружать парсер СУБД и оптимально использовать кэш планов запросов.