При разработке отчетов и обработок в среде 1С перед программистом часто возникает задача: найти товары, по которым не было движений за определенный период времени. На первый взгляд, задача кажется тривиальной, однако на практике разработчики часто сталкиваются с тем, что разные варианты написания запроса выдают совершенно разное количество номенклатурных позиций. Более того, неоптимально написанный запрос может полностью заблокировать работу СУБД на больших базах данных.
В этой статье мы подробно разберем, почему два внешне похожих запроса работают по-разному, какие подводные камни скрывает оптимизатор СУБД при работе с виртуальными таблицами, и как написать максимально быстрый и корректный запрос для решения этой задачи.
Рассмотрим два варианта запроса, которые использовал автор темы для получения списка товаров без оборотов.
Вариант 1 (с использованием подзапроса и НЕ В):
ВЫБРАТЬ
Товар.Код,
Товар.Наименование,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Товар.Ссылка) КАК Колво
ИЗ
Справочник.Номенклатура КАК Товар
ГДЕ
НЕ Ссылка В (
ВЫБРАТЬ
Номенклатура
ИЗ
РегистрНакопления.ТоварыОрганизаций.Обороты(&НачалоПериода, &КонецПериода)
ГДЕ
КоличествоОборот <> 0
)
И НЕ Товар.ЭтоГруппа
И Товар.Родитель В ИЕРАРХИИ(&ГруппаТовара)
СГРУППИРОВАТЬ ПО
Товар.Код,
Товар.Наименование
УПОРЯДОЧИТЬ ПО
Товар.Наименование
Вариант 2 (с использованием ЛЕВОГО СОЕДИНЕНИЯ):
ВЫБРАТЬ
Товар.Код,
Товар.Наименование КАК Название,
1 КАК Поле1,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Товар.Ссылка) КАК Колво
ИЗ
Справочник.Номенклатура КАК Товар
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ТоварыОрганизацийОбороты
ПО Товар.Ссылка = ТоварыОрганизацийОбороты.Номенклатура
ГДЕ
НЕ Товар.ЭтоГруппа
И ТоварыОрганизацийОбороты.Номенклатура ЕСТЬ NULL
И Товар.Родитель В ИЕРАРХИИ(&ГруппаТовара)
СГРУППИРОВАТЬ ПО
Товар.Код,
Товар.Наименование
УПОРЯДОЧИТЬ ПО
Товар.Наименование
Проанализируем логику работы обоих вариантов и выясним причину расхождения результатов в количестве выбранных товаров.
В первом варианте в подзапросе выбираются только те товары, у которых оборот КоличествоОборот <> 0. Соответственно, условие НЕ Ссылка В (...) оставит в итоговой выборке те товары, у которых:
КоличествоОборот в виртуальной таблице оборотов будет равен 0. Так как условие подзапроса отсекает только ненулевые обороты, товары с нулевым результатом вычислений попадут в основную выборку.Во втором варианте выполняется левое соединение со всей виртуальной таблицей оборотов. Платформа 1С при обращении к виртуальной таблице без указания ресурсов в полях выборки оптимизирует запрос СУБД. Если за период по номенклатуре были хоть какие-то движения (пусть даже взаимно компенсирующие друг друга, дающие нулевой итог), запись в виртуальной таблице все равно сформируется. При левом соединении для такого товара поле ТоварыОрганизацийОбороты.Номенклатура НЕ будет равно NULL. Следовательно, условие ГДЕ ТоварыОрганизацийОбороты.Номенклатура ЕСТЬ NULL отсечет эту позицию.
Таким образом:
Давайте обратим внимание на важнейший аспект производительности СУБД. Во втором варианте запроса используется конструкция:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций.Обороты(&НачалоПериода, &КонецПериода, , )
С точки зрения стандартов разработки фирмы «1С» — это грубейшая ошибка. Если параметры отбора по номенклатуре не переданы непосредственно в параметры виртуальной таблицы (четвертый параметр Условие), СУБД будет вынуждена рассчитать обороты абсолютно для всей номенклатуры, хранящейся в базе данных за указанный период. И только после выполнения этого тяжелого расчета СУБД выполнит левое соединение и применит фильтр по иерархии номенклатуры.
На крупных базах данных с миллионами записей такой запрос приведет к полному сканированию таблиц (Table Scan), колоссальной нагрузке на процессор и дисковую подсистему, а также к таймауту выполнения транзакций.
В первом запросе используется условие ГДЕ НЕ Ссылка В (ВЫБРАТЬ...). Это решение также крайне не рекомендуется использовать на практике по двум причинам:
NOT IN (в который транслируется НЕ В) такова, что если подзапрос вернет хотя бы одну строку со значением NULL, весь основной запрос вернет пустой результат. Платформа 1С пытается обходить эту особенность, но для оптимизатора СУБД (MS SQL Server или PostgreSQL) такие конструкции остаются крайне сложными для анализа.NOT IN с вложенным подзапросом. Вместо быстрого поиска по индексу (Index Seek) СУБД применит ресурсоемкий алгоритм вложенных циклов (Nested Loops) или сканирование всей таблицы. Стандарты разработки предписывают заменять такие конструкции на левое соединение с проверкой на ЕСТЬ NULL.Для того чтобы решить задачу корректно и с максимальной скоростью работы, мы должны применить методику временных таблиц. Разделим решение задачи на три логических шага:
Ссылка для быстрого соединения.Рассмотрим пример правильного запроса:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Товар
ПОМЕСТИТЬ ВТ_НоменклатураФильтр
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.ЭтоГруппа
И Номенклатура.Родитель В ИЕРАРХИИ(&ГруппаТовара)
ИНДЕКСИРОВАТЬ ПО
Товар
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
НоменклатураФильтр.Товар.Код КАК Код,
НоменклатураФильтр.Товар.Наименование КАК Название,
1 КАК Поле1,
1 КАК Колво
ИЗ
ВТ_НоменклатураФильтр КАК НоменклатураФильтр
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций.Обороты(
&НачалоПериода,
&КонецПериода,
Авто,
Номенклатура В (ВЫБРАТЬ Т.Товар ИЗ ВТ_НоменклатураФильтр КАК Т)
) КАК ТоварыОбороты
ПО НоменклатураФильтр.Товар = ТоварыОбороты.Номенклатура
ГДЕ
ТоварыОбороты.Номенклатура ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Название
Почему этот запрос будет работать быстро:
ВТ_НоменклатураФильтр.ИНДЕКСИРОВАТЬ ПО, что ускорит операцию слияния.Если перед нами стоит задача определить исключительно факт физического отсутствия записей в регистре (без анализа итоговых показателей оборотов), можно рассмотреть вариант обращения к физической таблице движений регистра накопления напрямую (как предложено в сообщении 13).
ВЫБРАТЬ
Товар.Код КАК Код,
Товар.Наименование КАК Название,
1 КАК Поле1,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Товар.Ссылка) КАК Колво
ИЗ
Справочник.Номенклатура КАК Товар
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций КАК ТоварыОрганизацийДвижения
ПО Товар.Ссылка = ТоварыОрганизацийДвижения.Номенклатура
И (ТоварыОрганизацийДвижения.Период МЕЖДУ &НачалоПериода И &КонецПериода)
ГДЕ
НЕ Товар.ЭтоГруппа
И ТоварыОрганизацийДвижения.Номенклатура ЕСТЬ NULL
И Товар.Родитель В ИЕРАРХИИ(&ГруппаТовара)
СГРУППИРОВАТЬ ПО
Товар.Код,
Товар.Наименование
УПОРЯДОЧИТЬ ПО
Товар.Наименование
Преимущества прямого обращения к таблице движений:
Период + Номенклатура.Недостатки прямого обращения к таблице движений:
_AccumRgT), считывая уже готовые сохраненные суммы за закрытые месяцы, что работает в десятки раз быстрее прямого чтения движений за большие периоды.В сообщениях форума 7–11 поднимается важнейший методологический вопрос: действительно ли отсутствие оборотов означает отсутствие товара на складе?
В реальном учете ситуация, когда оборот за период равен нулю, совершенно не означает, что остаток товара равен нулю. На складе может лежать неликвидный товар, закупленный год назад. Оборотов по нему за последний месяц не было, но он фактически присутствует на складе, замораживая оборотные средства предприятия.
Если перед вами стоит классическая бизнес-задача по поиску неликвидов (товаров, которые лежат на складе мертвым грузом и не продаются), правильный алгоритм выглядит следующим образом:
Остатки.Обороты за период.КоличествоРасход > 0).Такой подход позволит предоставить бизнесу действительно ценную информацию — список залежавшихся товаров, по которым есть остатки, но отсутствуют продажи.