При разработке механизмов проведения документов по новой методике (когда сначала записываются движения документа в базу данных, а затем проверяются возникшие отрицательные остатки) разработчики часто сталкиваются с необходимостью отбора данных виртуальной таблицы остатков по сложным условиям. Одно из наиболее частых требований — отфильтровать остатки только по тем комбинациям измерений (например, Номенклатура и Характеристика), которые присутствуют в табличной части проводимого документа.
В этой статье мы подробно разберем, как правильно составить такой запрос, избежав при этом типичных ошибок, приводящих к получению пустых результатов, снижению производительности СУБД и логическим сбоям при контроле остатков.
Рассмотрим ситуацию, когда разработчик пытается получить остатки по комбинации полей из временной таблицы Товары. Исходный код выглядит следующим образом:
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.Текст =
"ВЫБРАТЬ
| ЗаказКлиентаТовары.Номенклатура КАК Номенклатура,
| ЗаказКлиентаТовары.Характеристика КАК Характеристика
|ПОМЕСТИТЬ Товары
|ИЗ
| Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
|ГДЕ
| ЗаказКлиентаТовары.Ссылка = &Ссылка";
Запрос.УстановитьПараметр("Ссылка", Ссылка);
Запрос.Выполнить();
Запрос.Текст =
"ВЫБРАТЬ
| МойРегистр.Номенклатура КАК Номенклатура,
| -МойРегистр.КоличествоОстаток КАК Дефицит
|ИЗ
| РегистрНакопления.МойРегистр.Остатки(
| &МоментВремени,
| (Номенклатура, Характеристика) В
| (ВЫБРАТЬ
| Товары.Номенклатура КАК Номенклатура,
| Товары.Характеристика КАК Характеристика
| ИЗ
| Товары КАК Товары)) КАК МойРегистр
|ГДЕ
| МойРегистр < 0"; // Ошибка здесь!
При выполнении этого кода разработчики часто замечают странное поведение: если накладывать отбор только по полю Номенклатура, запрос отрабатывает корректно и находит дефицит. Но стоит добавить в условие Характеристика — результат всегда оказывается пустым, несмотря на то, что отрицательные остатки гарантированно должны быть.
Выясним причины такого поведения по шагам.
Самая частая причина пустых результатов при отборе по нескольким измерениям — банальное незаполнение одного из реквизитов при записи движений в регистр.
Если в коде проведения документа движения записываются без учета характеристики (например, в движении поле Характеристика остается пустым или равным Справочники.ХарактеристикиНоменклатуры.ПустаяСсылка()), а в документе (и, следовательно, во временной таблице Товары) характеристика заполнена конкретным значением, то СУБД не сможет сопоставить эти строки. Условие по паре (Номенклатура, Характеристика) не выполнится, и запрос вернет пустой результат.
Решение: Перед выполнением запроса контроля остатков убедитесь в отладчике, что данные в регистре накопления физически записались с теми же значениями измерений, которые фигурируют в табличной части документа.
Обратим внимание на условие в конце исходного запроса:
ГДЕ
МойРегистр < 0
В данном случае МойРегистр — это псевдоним (алиас) самой виртуальной таблицы в секции ИЗ. Платформа 1С не позволяет сравнивать всю таблицу или строку целиком с числовым значением. Сравнивать необходимо конкретный числовой ресурс.
Решение: Условие должно проверять значение ресурса остатка. Поскольку псевдонимы полей из секции ВЫБРАТЬ (такие как Дефицит) недоступны в секции ГДЕ, необходимо использовать оригинальное имя поля таблицы-источника:
ГДЕ
МойРегистр.КоличествоОстаток < 0
---
В сообществе разработчиков 1С существует устойчивое мнение, что конструкция группового сравнения кортежей вида (Номенклатура, Характеристика) В (ВЫБРАТЬ...) "ломает индексы" СУБД и приводит к снижению производительности. Из-за этого некоторые специалисты советуют разбивать групповое условие на несколько одиночных операторов В:
// Внимание: Неэффективный и потенциально ошибочный вариант!
РегистрНакопления.МойРегистр.Остатки(
&МоментВремени,
Номенклатура В (ВЫБРАТЬ Товары.Номенклатура ИЗ Товары)
И Характеристика В (ВЫБРАТЬ Товары.Характеристика ИЗ Товары)
)
Проанализируем, почему этот совет вреден как с точки зрения производительности, так и с точки зрения бизнес-логики.
Если мы разделяем групповой отбор на два независимых условия, СУБД выберет все остатки, где номенклатура совпадает с любой номенклатурой из документа, а характеристика — с любой характеристикой из документа.
Представим, что в документе у нас две строки:
Если применить раздельные отборы В, запрос выберет из базы данных остатки по следующим несуществующим в документе парам:
В результате логика контроля остатков будет нарушена: избыточные данные могут исказить расчет дефицита, либо система заблокирует проведение документа из-за нехватки товара, которого в документе даже не было.
Современные версии платформы "1С:Предприятие" транслируют конструкцию группового отбора (Измерение1, Измерение2) В (ВЫБРАТЬ...) в SQL-запрос с использованием оператора EXISTS:
EXISTS (
SELECT 1 FROM #TempTable
WHERE #TempTable.Номенклатура = Регистр.Номенклатура
AND #TempTable.Характеристика = Регистр.Характеристика
)
Оптимизаторы современных СУБД (MS SQL Server, PostgreSQL) отлично умеют строить планы выполнения для таких запросов. Они выполняют соединение за один проход по индексу регистра, в отличие от двух раздельных проходов при использовании одиночных операторов В.
---
Чтобы СУБД могла эффективно сопоставлять данные виртуальной таблицы остатков с временной таблицей документов через оператор EXISTS, временная таблица обязательно должна быть проиндексирована по полям сопоставления.
Если не наложить индекс на временную таблицу Товары, СУБД будет вынуждена выполнять полное сканирование таблицы (Table Scan) для проверки каждого измерения регистра. При проведении документов с большим количеством строк это мгновенно приведет к критическому падению производительности и блокировкам базы данных.
Рассмотрим, как правильно составить запрос формирования временной таблицы с индексами:
Запрос.Текст =
"ВЫБРАТЬ
| ЗаказКлиентаТовары.Номенклатура КАК Номенклатура,
| ЗаказКлиентаТовары.Характеристика КАК Характеристика
|ПОМЕСТИТЬ Товары
|ИЗ
| Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
|ГДЕ
| ЗаказКлиентаТовары.Ссылка = &Ссылка
|
|ИНДЕКСИРОВАТЬ ПО
| Номенклатура,
| Характеристика";
---
При использовании новой методики проведения крайне важно правильно указать момент времени, на который мы получаем остатки. Если передать в параметры виртуальной таблицы просто значение Движения.МойРегистр.Граница() или МоментВремени() проводимого документа, платформа по умолчанию рассчитает остатки на **начало** этой секунды. Это означает, что только что записанные движения текущего документа не будут учтены СУБД.
Чтобы гарантировать получение остатков строго **после** записи движений текущего документа (включая их в расчет), необходимо использовать объект Граница с видом ВидГраницы.Включая.
Рассмотрим пример правильной подготовки параметров в коде на встроенном языке 1С:
// Сначала записываем движения документа в транзакции проведения
Движения.МойРегистр.Записать();
// Создаем границу, включающую момент времени текущего документа
ГраницаКонтроля = Новый Граница(ЭтотОбъект.МоментВремени(), ВидГраницы.Включая);
Запрос.УстановитьПараметр("МоментВремени", ГраницаКонтроля);
---
Объединим все правила и лучшие практики разработки в один готовый шаблон. Этот код можно использовать в обработчике ОбработкаПроведения любого документа, требующего контроля остатков с учетом характеристик:
Процедура ОбработкаПроведения(Отказ, РежимПроведения)
// 1. Записываем движения по регистрам (новая методика проведения)
Движения.МойРегистр.Записывать = Истина;
Движения.МойРегистр.Записать();
// Если это не оперативное проведение, контроль остатков обычно не требуется
Если РежимПроведения <> РежимПроведенияДокумента.Оперативный Тогда
Возврат;
КонецЕсли;
// 2. Инициализируем запрос и менеджер временных таблиц
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Формируем ВТ "Товары" с обязательным индексированием по измерениям отбора
Запрос.Текст =
"ВЫБРАТЬ ВЫРАЗИТЬ(Док.Номенклатура КАК Справочник.Номенклатура) КАК Номенклатура,
| ВЫРАЗИТЬ(Док.Характеристика КАК Справочник.ХарактеристикиНоменклатуры) КАК Характеристика
|ПОМЕСТИТЬ ВТ_Товары
|ИЗ
| Документ.ЗаказКлиента.Товары КАК Док
|ГДЕ
| Док.Ссылка = &Ссылка
|
|ИНДЕКСИРОВАТЬ ПО
| Номенклатура,
| Характеристика";
Запрос.УстановитьПараметр("Ссылка", Ссылка);
Запрос.Выполнить();
// 3. Получаем отрицательные остатки (дефицит) после записи наших движений
Запрос.Текст =
"ВЫБРАТЬ
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.Характеристика КАК Характеристика,
| -Остатки.КоличествоОстаток КАК Дефицит
|ИЗ
| РегистрНакопления.МойРегистр.Остатки(
| &МоментВремени,
| (Номенклатура, Характеристика) В
| (ВЫБРАТЬ
| Товары.Номенклатура,
| Товары.Характеристика
| ИЗ
| ВТ_Товары КАК Товары)) КАК Остатки
|ГДЕ
| Остатки.КоличествоОстаток < 0";
// Применяем Границу с видом Включая
ГраницаКонтроля = Новый Граница(ЭтотОбъект.МоментВремени(), ВидГраницы.Включая);
Запрос.УстановитьПараметр("МоментВремени", ГраницаКонтроля);
РезультатЗапроса = Запрос.Выполнить();
Если Не РезультатЗапроса.Пустой() Тогда
Отказ = Истина;
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
Сообщение = Новый СообщениеПользователю;
Сообщение.Текст = ШаблонФСП(
"Недостаточно товара %1 с характеристикой %2. Требуется еще %3 ед.",
Выборка.Номенклатура,
Выборка.Характеристика,
Выборка.Дефицит
);
Сообщение.Сообщить();
КонецЦикла;
КонецЕсли;
КонецПроцедуры
Для надежной и быстрой работы механизмов контроля остатков всегда придерживайтесь следующих стандартов проектирования запросов в 1С:
ГДЕ. Это заставит СУБД работать только с нужным подмножеством записей.(Измерение1, Измерение2) В (...). Она корректно транслируется платформой через EXISTS и не нарушает логику связей, в отличие от раздельных условий В.Граница со значением ВидГраницы.Включая.