При разработке отчетов в 1С программисты часто сталкиваются с задачей: вывести остатки и обороты по регистру накопления, но при этом отобразить дополнительные реквизиты из документов-регистраторов или из самой физической таблицы движений. Самый очевидный и простой на первый взгляд путь — написать запрос с левым соединением виртуальной таблицы ОстаткиИОбороты и физической таблицы движений этого же регистра.
Однако на практике такое решение часто преподносит неприятные сюрпризы. В отчете начинают появляться «фантомные» строки — склады или товары, по которым остатков и движений в выбранном периоде давно нет, а их показатели равны нулю. Если убрать соединение с движениями — отчет работает корректно. Стоит его вернуть — и выборка «раздувается». В этой статье мы подробно разберем, почему возникает эта проблема, как устроены механизмы хранения итогов и как правильно спроектировать эффективный и быстродействующий запрос.
Выясним причины такого поведения системы и рассмотрим пошаговые сценарии решения проблемы.
Первая и самая частая причина появления строк с нулевыми остатками при соединении таблиц — это так называемые несхлопнутые итоги в базе данных SQL.
Платформа 1С хранит остатки в специальных таблицах итогов. При проведении документов и записи движений СУБД не всегда мгновенно производит свертку («схлопывание») записей, которые в сумме дают ноль. Если в базе скопилось много таких записей, то физически в таблице итогов присутствуют строки, где ресурс (например, Количество) равен нулю.
Когда мы обращаемся только к виртуальной таблице остатков, платформа оптимизирует запрос и не возвращает эти «нулевые» записи. Но как только мы выполняем ЛЕВОЕ СОЕДИНЕНИЕ с таблицей движений по нескольким измерениям и регистратору, СУБД вынуждена сопоставлять каждую физическую запись итогов (даже нулевую) с движениями. В результате в выборку попадают записи, которых там быть не должно.
Решение проблемы:
ГДЕ или ИМЕЮЩИЕ для отсечения нулевых итогов. Проанализируем пример условия:
ГДЕ
(ТоварыНаСкладахОстаткиИОбороты.КоличествоНачальныйОстаток <> 0
ИЛИ ТоварыНаСкладахОстаткиИОбороты.КоличествоПриход <> 0
ИЛИ ТоварыНаСкладахОстаткиИОбороты.КоличествоРасход <> 0
ИЛИ ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток <> 0)
Посмотрим на исходный запрос, в котором виртуальная таблица ОстаткиИОбороты соединяется с физической таблицей регистра. С точки зрения СУБД, виртуальная таблица — это сложный вложенный подзапрос с агрегатными функциями и группировками. Прямое соединение такого подзапроса с другой таблицей заставляет оптимизатор SQL-сервера строить неоптимальные планы выполнения (например, использовать алгоритм Nested Loops вместо быстрого Hash Match).
Это не только замедляет работу системы в десятки раз, но и может приводить к непредсказуемым результатам выборки при наложении условий связи.
Правильное решение — использование временных таблиц:
Разберем по шагам правильную методику разработки таких запросов. Вместо одного тяжелого запроса мы разделим логику на два этапа с использованием временной таблицы.
Шаг 1. Выбираем данные из виртуальной таблицы ОстаткиИОбороты во временную таблицу и обязательно индексируем ее по полям, по которым в дальнейшем будем производить соединение.
Шаг 2. Соединяем подготовленную и проиндексированную временную таблицу с физической таблицей движений (или таблицей документов) для получения необходимых реквизитов.
Рассмотрим пример правильного построения запроса:
ВЫБРАТЬ
ОстаткиОбороты.Склад КАК Склад,
ОстаткиОбороты.Номенклатура КАК Номенклатура,
ОстаткиОбороты.Регистратор КАК Регистратор,
ОстаткиОбороты.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
ОстаткиОбороты.КоличествоПриход КАК КоличествоПриход,
ОстаткиОбороты.КоличествоРасход КАК КоличествоРасход,
ОстаткиОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток
ПОМЕСТИТЬ ВТ_ОстаткиИОбороты
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&ДатаНачала, &ДатаКонца, Регистратор, , ) КАК ОстаткиОбороты
ГДЕ
(ОстаткиОбороты.КоличествоНачальныйОстаток <> 0
ИЛИ ОстаткиОбороты.КоличествоПриход <> 0
ИЛИ ОстаткиОбороты.КоличествоРасход <> 0
ИЛИ ОстаткиОбороты.КоличествоКонечныйОстаток <> 0)
ИНДЕКСИРОВАТЬ ПО
Склад,
Номенклатура,
Регистратор
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ОстаткиИОбороты.Склад КАК Склад,
ВТ_ОстаткиИОбороты.Номенклатура КАК Номенклатура,
ВТ_ОстаткиИОбороты.Регистратор КАК Регистратор,
ВТ_ОстаткиИОбороты.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
ВТ_ОстаткиИОбороты.КоличествоПриход КАК КоличествоПриход,
ВТ_ОстаткиИОбороты.КоличествоРасход КАК КоличествоРасход,
ВТ_ОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
ТаблицаДвижений.РеквизитДвижения КАК РеквизитДвижения
ИЗ
ВТ_ОстаткиИОбороты КАК ВТ_ОстаткиИОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах КАК ТаблицаДвижений
ПО ВТ_ОстаткиИОбороты.Склад = ТаблицаДвижений.Склад
И ВТ_ОстаткиИОбороты.Номенклатура = ТаблицаДвижений.Номенклатура
И ВТ_ОстаткиИОбороты.Регистратор = ТаблицаДвижений.Регистратор
Проанализируем условия связи в исходном запросе автора темы:
И ТоварыНаСкладахОстаткиИОбороты.Качество = Движения.Качество
И ТоварыНаСкладахОстаткиИОбороты.ХарактеристикаНоменклатуры = Движения.ХарактеристикаНоменклатуры
И ТоварыНаСкладахОстаткиИОбороты.СерияНоменклатуры = Движения.СерияНоменклатуры
При этом в списке группировки и выбора самой таблицы ОстаткиИОбороты данные поля (Качество, Характеристика, Серия) отсутствуют! Это грубая ошибка. Если эти измерения не выбраны во вложенном запросе (или не указаны в группировке виртуальной таблицы), соединение по ним вернет непредсказуемый результат или приведет к синтаксической ошибке СУБД.
Кроме того, условие по периоду Движения.Период МЕЖДУ &ДатаНачала И &ДатаКонца внутри связи ПО часто является избыточным, если мы уже соединяемся по полю Регистратор. Поле Регистратор само по себе однозначно определяет транзакцию и момент времени. Однако в некоторых случаях указание периода помогает оптимизатору SQL быстрее использовать индексы физической таблицы. Если вы используете период в условиях связи, делайте это осознанно и только после анализа плана запроса.
Важно помнить архитектурную особенность платформы 1С при работе с итогами. Согласно официальным методическим рекомендациям, расчет остатков по регистратору корректно работает только внутри группировки по периоду.
Поскольку регистратор является лишь уточнением периода, попытка получить остатки в разрезе регистраторов без соблюдения временной иерархии может вернуть некорректные остатки. Если вам необходимо вывести отчет с иерархией по времени, структура группировок должна выглядеть так:
Если же требуется вывести просто плоский список документов с остатками «на момент проведения», используйте периодичность Регистратор и получайте остатки непосредственно из виртуальной таблицы, не прибегая к соединениям с физической таблицей без крайней необходимости.
Если ваш отчет разрабатывается на СКД, то оптимальным решением для связи остатков с реквизитами движений будет использование двух наборов данных и механизма связи между ними.
Рассмотрим, как это настроить:
ОстаткиИОбороты (получаем поля: Склад, Номенклатура, Регистратор, Остатки и Обороты).Склад, Номенклатура и Регистратор.При таком подходе СКД самостоятельно сформирует оптимальные параметризованные запросы к СУБД, передавая список регистраторов из первого набора во второй. Это полностью исключит появление пустых строк и значительно повысит скорость формирования отчета.