В процессе сопровождения и администрирования баз данных 1С часто возникает задача «очистки» справочника номенклатуры. Карточки товаров, которые были созданы ошибочно, дублируют существующие позиции или просто больше не используются в торговом обороте, захламляют базу данных, затрудняют поиск и замедляют работу пользователей при подборе в документах. Чтобы пометить такие позиции на удаление или перенести их в специальную папку «Архив», нам необходимо предварительно выявить их с помощью запроса. Для этой задачи можно использовать обработку для быстрого поиска и удаления неиспользованных элементов.
Формулировка задачи звучит просто: получить список номенклатуры, по которой на конечную дату периода остаток равен нулю, и при этом в течение всего выбранного периода не было абсолютно никаких движений (ни прихода, ни расхода). Однако при реализации этой задачи в конструкторе запросов 1С начинающие разработчики часто совершают грубые ошибки, ведущие к неверным результатам или критическому снижению производительности системы. В этой статье мы подробно разберем все логические ловушки, технические нюансы работы СУБД и оптимальные варианты решения этой задачи в соответствии со стандартами разработки фирмы «1С».
Прежде чем перейти к написанию кода, проанализируем основные ошибки, которые часто встречаются в подобных запросах на форумах:
std733 и std657), прямое соединение реальной таблицы (в данном случае Справочник.Номенклатура) с виртуальными таблицами регистров накопления крайне не рекомендуется. СУБД не может эффективно построить план такого запроса, что приводит к полному сканированию таблиц (Index Scan / Table Scan) и перегрузке сервера. Правильным решением является использование временных таблиц с обязательным индексированием связующих полей.НЕ (Номенклатура, Характеристика) В (ВЫБРАТЬ ...) таит в себе скрытую угрозу. Если во вложенном запросе окажется строка, где характеристика имеет значение NULL (что абсолютно естественно для товаров, не использующих характеристики), то из-за особенностей трехзначной логики SQL весь запрос вернет пустой результат. Сравнение кортежей с участием NULL всегда следует заменять на классическое ЛЕВОЕ СОЕДИНЕНИЕ по двум полям с последующей проверкой правого поля на ЕСТЬ NULL в секции ГДЕ.ОстаткиИОбороты и по товару не будет движений в указанном периоде, СУБД может не вернуть строку по этой позиции, если на складе при этом числится остаток с прошлых периодов. Поэтому важно проверять все показатели: и начальный остаток, и приход, и расход, и конечный остаток. Если все они равны нулю (или отсутствуют вовсе, то есть возвращают NULL при левом соединении), значит, позиция действительно пуста и не двигалась.Рассмотрим самый простой способ решения задачи «в один запрос». Этот подход отлично подходит для небольших баз данных или нечастого интерактивного использования администратором. Мы соединим справочник номенклатуры с виртуальной таблицей остатков и оборотов и применим фильтрацию сгруппированных данных в секции ИМЕЮЩИЕ.
Посмотрим на пример такого запроса:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ХарактеристикиНоменклатуры.Ссылка КАК Характеристика
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
ПО СпрНоменклатура.Ссылка = ХарактеристикиНоменклатуры.Владелец
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания, , , ) КАК ТоварыНаСкладахОстаткиИОбороты
ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстаткиИОбороты.Номенклатура
И (ХарактеристикиНоменклатуры.Ссылка = ТоварыНаСкладахОстаткиИОбороты.Характеристика ИЛИ ХарактеристикиНоменклатуры.Ссылка ЕСТЬ NULL)
ГДЕ
НЕ СпрНоменклатура.ЭтоГруппа
И НЕ СпрНоменклатура.ПометкаУдаления
И СпрНоменклатура.Ссылка В ИЕРАРХИИ(&ГруппаНоменклатуры)
СГРУППИРОВАТЬ ПО
СпрНоменклатура.Ссылка,
ХарактеристикиНоменклатуры.Ссылка
ИМЕЮЩИЕ
ЕСТЬNULL(СУММА(ТоварыНаСкладахОстаткиИОбороты.ВНаличииНачальныйОстаток), 0) = 0 И
ЕСТЬNULL(СУММА(ТоварыНаСкладахОстаткиИОбороты.ВНаличииПриход), 0) = 0 И
ЕСТЬNULL(СУММА(ТоварыНаСкладахОстаткиИОбороты.ВНаличииРасход), 0) = 0 И
ЕСТЬNULL(СУММА(ТоварыНаСкладахОстаткиИОбороты.ВНаличииКонечныйОстаток), 0) = 0
Разберем подробнее, как работает этот запрос:
Номенклатура и левым соединением подключаем таблицу ХарактеристикиНоменклатуры, чтобы проверять остатки в разрезе характеристик (если они используются).ОстаткиИОбороты регистра накопления (например, ТоварыНаСкладах).ГДЕ мы сразу отсекаем группы номенклатуры, помеченные на удаление элементы, и ограничиваем поиск определенной группой (папкой) с помощью параметра &ГруппаНоменклатуры.ГРУППИРОВАТЬ ПО объединяет данные по конкретным связкам «Товар — Характеристика».ИМЕЮЩИЕ. Мы суммируем начальный остаток, приход, расход и конечный остаток. Конструкция ЕСТЬNULL гарантирует, что если по товару вообще не было никаких записей в регистре за всю историю (то есть левое соединение вернуло NULL), система превратит этот NULL в 0, и условие успешно выполнится. Такие товары тоже попадут в результирующую выборку, что абсолютно правильно.Если в вашей базе данных сотни тысяч позиций номенклатуры и миллионы записей в регистрах, предыдущий запрос может привести к длительной блокировке таблиц или завершиться ошибкой превышения времени ожидания СУБД. Проанализируем профессиональный подход к решению этой задачи с использованием временных таблиц и индексации.
Разберем этот процесс по шагам в пакетном запросе:
Сначала мы выберем всю интересующую нас номенклатуру во временную таблицу и проиндексируем ее по полям связи.
ВЫБРАТЬ РАЗРЕШЕННЫЕ
спрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ХарактеристикиНоменклатуры.Ссылка, ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)) КАК Характеристика
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
Справочник.Номенклатура КАК спрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
ПО спрНоменклатура.Ссылка = ХарактеристикиНоменклатуры.Владелец
ГДЕ
НЕ спрНоменклатура.ЭтоГруппа
И НЕ спрНоменклатура.ПометкаУдаления
И спрНоменклатура.Ссылка В ИЕРАРХИИ(&ГруппаНоменклатуры)
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Характеристика
;
Теперь мы выбираем остатки и обороты из регистра. Главное преимущество этого шага — мы передаем подготовленный список номенклатуры в параметры виртуальной таблицы в качестве фильтра. Это заставит СУБД обрабатывать только малую часть данных регистра, а не весь регистр целиком.
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.Характеристика КАК Характеристика,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииНачальныйОстаток КАК НачальныйОстаток,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииПриход КАК Приход,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииРасход КАК Расход,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииКонечныйОстаток КАК КонечныйОстаток
ПОМЕСТИТЬ ВТ_ДвиженияРегистра
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&ДатаНачала,
&ДатаОкончания,
,
,
Номенклатура В (ВЫБРАТЬ ВТ_Номенклатура.Номенклатура ИЗ ВТ_Номенклатура)
) КАК ТоварыНаСкладахОстаткиИОбороты
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Характеристика
;
На последнем шаге мы соединяем две временные таблицы. Так как обе они проиндексированы, операция соединения в СУБД произойдет мгновенно (используя алгоритм Merge Join или Hash Match). Мы отбираем только те позиции, у которых в регистре либо вообще нет записей (Движения.Номенклатура ЕСТЬ NULL), либо все показатели движения и остатков строго равны нулю.
ВЫБРАТЬ
ВТ_Номенклатура.Номенклатура КАК Номенклатура,
ВТ_Номенклатура.Характеристика КАК Характеристика
ИЗ
ВТ_Номенклатура КАК ВТ_Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДвиженияРегистра КАК ВТ_ДвиженияРегистра
ПО ВТ_Номенклатура.Номенклатура = ВТ_ДвиженияРегистра.Номенклатура
И ВТ_Номенклатура.Характеристика = ВТ_ДвиженияРегистра.Характеристика
ГДЕ
(ВТ_ДвиженияРегистра.Номенклатура ЕСТЬ NULL)
ИЛИ (
ЕСТЬNULL(ВТ_ДвиженияРегистра.НачальныйОстаток, 0) = 0 И
ЕСТЬNULL(ВТ_ДвиженияРегистра.Приход, 0) = 0 И
ЕСТЬNULL(ВТ_ДвиженияРегистра.Расход, 0) = 0 И
ЕСТЬNULL(ВТ_ДвиженияРегистра.КонечныйОстаток, 0) = 0
)
Этот двухэтапный метод полностью соответствует лучшим практикам разработки на платформе 1С:Предприятие 8. Он гарантирует предсказуемое время выполнения запроса вне зависимости от размера информационной базы и исключает появление избыточных блокировок в СУБД (MS SQL, PostgreSQL и др.). Для корректного освобождения ресурсов при работе с временными таблицами подойдёт инструмент для оптимизации временных таблиц в пакетных запросах.
Для качественного анализа номенклатуры и очистки базы данных помните следующие правила:
НЕ (Номенклатура, Характеристика) В ... на левое соединение с фильтром ЕСТЬ NULL. Это убережет ваш запрос от неверной логики при обработке пустых характеристик.ПОМЕСТИТЬ и их обязательным индексированием по полям связи (номенклатура, характеристика, склад).СвободныеОстатки, ТоварыОрганизаций), обязательно адаптируйте имя регистра накопления и полей остатка в тексте запроса.