При разработке отчетов в среде 1С:Предприятие часто возникает задача перейти от частного к общему. Например, у нас есть готовый запрос, который анализирует остатки и обороты только по тем позициям номенклатуры, которые фигурировали в конкретном документе (например, в «Требовании-накладной»). Однако бизнес-требования меняются, и теперь нам необходимо увидеть аналогичный отчет, но уже по всей существующей в справочнике номенклатуре, независимо от наличия документов или движений за период.
В рамках этой статьи мы подробно разберем, как решить эту задачу, проанализируем решения, предложенные разработчиками на форуме, исправим критические ошибки производительности и синтаксиса, а также составим итоговый оптимизированный запрос, соответствующий стандартам разработки фирмы «1С».
Рассмотрим исходный запрос, с которым столкнулся автор темы. В нем используется временная таблица врТН, куда помещаются позиции номенклатуры из табличной части «Материалы» проведенных документов «Требование-накладная» за определенный период:
ВЫБРАТЬ
ТребованиеНакладнаяМатериалы.Номенклатура КАК Номенклатура,
ТребованиеНакладнаяМатериалы.Количество КАК Количество
ПОМЕСТИТЬ врТН
ИЗ
Документ.ТребованиеНакладная.Materials КАК ТребованиеНакладнаяМатериалы
ГДЕ
ТребованиеНакладнаяМатериалы.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
И ТребованиеНакладнаяМатериалы.Ссылка.Проведен
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
врТН.Номенклатура КАК Номенклатура,
врТН.Количество КАК КоличествоВТН,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоПриход КАК КоличествоПриход,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоРасход КАК КоличествоРасход,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстатокНДС
ИЗ
врТН КАК врТН
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НДСПоПриобретеннымЦенностям.ОстаткиИОбороты КАК НДСПоПриобретеннымЦенностямОстаткиИОбороты
ПО врТН.Номенклатура = НДСПоПриобретеннымЦенностямОстаткиИОбороты.Номенклатура
УПОРЯДОЧИТЬ ПО
КоличествоПриход
АВТОУПОРЯДОЧИВАНИЕ
Этот запрос отлично работает, когда нас интересует узкий круг товаров из документов списания. Но если мы уберем первую временную таблицу, перед нами встанет вопрос: как правильно связать весь справочник Номенклатура с виртуальной таблицей регистра накопления? Давайте разберем два основных сценария решения этой задачи.
Если перед нами не стоит задача вывести «пустые» позиции номенклатуры (по которым не было ни остатков, ни приходов, ни расходов за период), то обращаться к справочнику вообще нет необходимости. Данные можно получить напрямую из регистра накопления.
Каждая запись в виртуальной таблице ОстаткиИОбороты уже содержит измерение Номенклатура. Мы можем просто прочитать данные из этой таблицы. Этот подход является самым быстродействующим для СУБД, так как исключает операцию соединения таблиц:
ВЫБРАТЬ
НДСПоПриобретеннымЦенностямОстаткиИОбороты.Номенклатура КАК Номенклатура,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоПриход КАК КоличествоПриход,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоРасход КАК КоличествоРасход,
НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток
ИЗ
РегистрНакопления.НДСПоПриобретеннымЦенностям.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания) КАК НДСПоПриобретеннымЦенностямОстаткиИОбороты
УПОРЯДОЧИТЬ ПО
КоличествоПриход
Преимущества: Максимальная скорость выполнения запроса, отсутствие лишних связей.
Недостатки: В результат не попадут товары, по которым в выбранном периоде отсутствуют остатки и не было никаких оборотов.
Если бизнес-логика требует отобразить в отчете полный перечень номенклатуры предприятия (например, для проведения инвентаризации или выявления позиций с нулевыми остатками), нам придется использовать ЛЕВОЕ СОЕДИНЕНИЕ физической таблицы справочника Номенклатура с виртуальной таблицей регистра.
При левом соединении таблица справочника выступает в роли «главной» (левой) таблицы. Это гарантирует, что каждая позиция номенклатуры попадет в выборку, даже если по ней никогда не регистрировались движения в регистре НДСПоПриобретеннымЦенностям.
Давайте подробно разберем критические ошибки, которые часто допускают разработчики при написании таких запросов, и то, как их исправить.
При написании запросов с левым соединением справочников и регистров необходимо учитывать требования стандартов разработки 1С для предотвращения падения производительности системы.
В некоторых сообщениях на форуме можно встретить обращение к таблице регистра без указания параметров в скобках: РегистрНакопления.НДСПоПриобретеннымЦенностям.ОстаткиИОбороты. Это грубейшая ошибка. Если не передать параметры периода (НачалоПериода, КонецПериода) непосредственно внутрь виртуальной таблицы, СУБД будет вынуждена рассчитывать итоги за всю историю ведения учета в базе данных. На реальных базах данных с миллионами записей это гарантированно приведет к зависанию сервера или ошибке Time-out. Правильно передавать параметры: ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания).
Если мы просто соединим справочник Номенклатура, в отчет попадут группы (папки), элементы с пометкой удаления, а также услуги, по которым в принципе не может быть остатков на складах. Чтобы отчет не превратился в «мусорку», необходимо наложить фильтр в секции ГДЕ: исключить группы с помощью условия НЕ СпрНоменклатура.ЭтоГруппа и удаленные элементы НЕ СпрНоменклатура.ПометкаУдаления.
В сообщении 7 на форуме была допущена синтаксическая ошибка: ЕСТЬNULL(КоличествоПриход, ). Пропущен второй обязательный параметр функции. Поскольку при левом соединении для отсутствующих движений СУБД вернет значение NULL, его обязательно нужно преобразовать в числовой ноль с помощью функции ЕСТЬNULL(..., 0). Это позволит корректно выполнять математические операции в отчете и избежать пустых ячеек.
Сортировка по полям, которые могут содержать NULL, работает непредсказуемо в зависимости от используемой СУБД (MS SQL, PostgreSQL и др.). Позиции без движений могут оказаться хаотично разбросанными вверху или внизу списка. Рекомендуется выполнять сортировку либо по текстовым полям (например, Номенклатура.Наименование), либо по вычисляемым полям, где значение NULL уже заменено на 0.
В некоторых решениях предлагалось помещать весь справочник номенклатуры во временную таблицу. Помещение огромных объемов данных (десятки и сотни тысяч строк справочника) во временные таблицы без предварительной фильтрации избыточно нагружает базу данных tempdb на сервере СУБД. В данной задаче гораздо эффективнее использовать прямое левое соединение физической таблицы справочника с виртуальной таблицей регистра.
С учетом всех правил оптимизации и исправления синтаксических ошибок, правильный и производительный запрос для вывода всей номенклатуры с остатками и оборотами НДС будет выглядеть следующим образом:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоПриход, 0) КАК КоличествоПриход,
ЕСТЬNULL(НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоРасход, 0) КАК КоличествоРасход,
ЕСТЬNULL(НДСПоПриобретеннымЦенностямОстаткиИОбороты.КоличествоКонечныйОстаток, 0) КАК КоличествоКонечныйОстаток
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НДСПоПриобретеннымЦенностям.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания, , , ) КАК НДСПоПриобретеннымЦенностямОстаткиИОбороты
ПО СпрНоменклатура.Ссылка = НДСПоПриобретеннымЦенностямОстаткиИОбороты.Номенклатура
ГДЕ
НЕ СпрНоменклатура.ЭтоГруппа
И НЕ СпрНоменклатура.ПометкаУдаления
УПОРЯДОЧИТЬ ПО
СпрНоменклатура.Наименование
АВТОУПОРЯДОЧИВАНИЕ
Разберем по шагам, почему этот вариант оптимален:
ЛЕВОЕ СОЕДИНЕНИЕ, благодаря чему в итоговую выборку попадут все элементы справочника, независимо от наличия движений.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания) жестко ограничили период выборки данных регистра на уровне СУБД.ГДЕ мы отсекли папки и помеченные на удаление элементы справочника Номенклатура, исключив появление неинформативного «мусора» в отчете.ЕСТЬNULL гарантирует замену пустых значений на 0, что делает итоговую таблицу аккуратной и удобной для дальнейшего математического анализа.УПОРЯДОЧИТЬ ПО) настроено по наименованию номенклатуры, что обеспечивает привычную для пользователей алфавитную сортировку отчета.Применяя этот подход, вы сможете строить быстродействующие отчеты, которые не будут перегружать сервер 1С даже на крупных базах данных с большим количеством складских операций.