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