Каждый разработчик 1С рано или поздно сталкивается с ситуацией, когда отчет или выгрузка начинает показывать «фантастические» цифры остатков. Представьте сценарий: на складе лежит всего 4 единицы определенной модели одежды (например, по одной штуке размеров S, M, L, XL), а внешняя обработка выгрузки на сайт упрямо заявляет, что их в наличии целых 100 штук! Откуда берется это умножение и как локализовать ошибку?
В этой статье мы подробно разберем, почему возникает этот баг, проанализируем архитектурные ошибки исходного запроса и напишем надежное, быстрое и красивое решение с использованием временных таблиц.
Основной источник беды в подобных запросах — это декартово произведение строк (или перемножение данных) при некорректном использовании операции ЛЕВОЕ СОЕДИНЕНИЕ. Давайте внимательно посмотрим на фрагмент кода, который вызвал справедливую критику опытных программистов на форуме:
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Поле1,
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура
ИЗ
РегистрНакопления.ТоварыВЯчейках.Остатки КАК ТоварыВЯчейкахОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
ПО ТоварыВЯчейкахОстатки.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
ГДЕ
ЦеныНоменклатурыСрезПоследних.ВидЦены = &Зак) КАК ВложенныйЗапрос
ПО ТоварыВЯчейкахОстатки.Номенклатура = ВложенныйЗапрос.Номенклатура
Проанализируем этот вложенный запрос по шагам и выясним причину сбоя:
РегистрНакопления.ТоварыВЯчейках.Остатки. Если один и тот же товар хранится в нескольких ячейках склада или имеет несколько разных характеристик (размеров), то эта таблица вернет несколько строк для одной и той же номенклатуры. Поскольку вложенный запрос не содержит конструкции СГРУППИРОВАТЬ ПО, все эти дублирующиеся строки попадают в результат выборки вложенного запроса.ПО ТоварыВЯчейкахОстатки.Номенклатура = ВложенныйЗапрос.Номенклатура.ВЫБРАТЬ срабатывает агрегатная функция СУММА(ТоварыВЯчейкахОстатки.ВНаличииОстаток), СУБД складывает остатки по всем этим 25 перемноженным строкам. В итоге реальный остаток в 4 штуки превращается в 100.Вторая важнейшая проблема исходного кода — полное отсутствие учета характеристик. Одежда почти всегда ведется в разрезе дополнительных свойств: размер, цвет, рост. В системе 1С за это отвечает измерение Характеристика (в некоторых конфигурациях — ХарактеристикаНоменклатуры).
Если мы разрабатываем выгрузку для интернет-магазина, у нас есть два пути решения задачи:
Характеристика обязательно должно присутствовать в выборке запроса, по нему должно происходить соединение с ценами и группировка остатков.Характеристика не выгружается, однако промежуточные таблицы остатков обязательно должны быть сгруппированы до уровня чистой Номенклатуры до того, как к ним будут присоединены таблицы цен.Помимо декартова произведения, исходный запрос содержит еще несколько грубых архитектурных ошибок, которые сильно замедляют работу базы данных и могут привести к зависанию системы при росте справочника номенклатуры:
Ошибка №1: Фильтрация цен в секции ГДЕ вместо параметров виртуальной таблицы.
Во вложенном запросе срез последних получается стандартным обращением: РегистрСведений.ЦеныНоменклатуры.SrezPoslednih, а отбор по нужному виду цен наложен ниже в условии ГДЕ ЦеныНоменклатурыСрезПоследних.ВидЦены = &Зак. Это заставляет СУБД сначала прочитать абсолютно все цены по всем товарам и всем видам цен из базы данных, построить гигантскую виртуальную таблицу в памяти и только потом отсечь лишнее. Правильный подход — передавать отборы в параметры виртуальной таблицы: РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ВидЦены = &Зак).
Ошибка №2: Получение цен через левое соединение с остатками.
Разработчик зачем-то соединяет регистр цен с регистром остатков прямо внутри вложенного запроса, чтобы получить закупочную цену. Чтобы узнать цену номенклатуры, остатки вообще не нужны! Достаточно напрямую обратиться к срезу последних регистра цен.
Ошибка №3: Отсутствие параметров даты во вложенных таблицах.
Основной запрос получает остатки на определенную дату &НаДату, а вложенные запросы обращаются к таблицам без указания параметров времени, из-за чего данные по ценам и остаткам могут стать неактуальными или несогласованными.
Для того чтобы исправить все эти архитектурные проблемы, мы полностью откажемся от вложенных запросов в секции ИЗ и перепишем логику с использованием Временных Таблиц (ВТ) и Менеджера временных таблиц. Использование ВТ — это стандарт разработки фирмы «1С». Оно делает код легко читаемым, исключает появление дублей строк и оптимизирует нагрузку на СУБД.
Рассмотрим структуру нашего нового, оптимизированного запроса:
Номенклатура, Характеристика и Склад.ОБЪЕДИНИТЬ ВСЕ и производим финальную группировку, чтобы получить чистые уникальные строки по каждому товару и размеру.Номенклатуре, так и по Характеристике.Посмотрим на пример реализации этого алгоритма в коде 1С:
&НаСервере
Процедура ЗаполнитьНаСервере()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыВЯчейкахОстатки.Номенклатура КАК Номенклатура,
| ТоварыВЯчейкахОстатки.Характеристика КАК Характеристика,
| ТоварыВЯчейкахОстатки.Ячейка.Владелец КАК Склад,
| СУММА(ТоварыВЯчейкахОстатки.ВНаличииОстаток) КАК Остаток
|ПОМЕСТИТЬ ВТ_ОстаткиЯчейки
|ИЗ
| РегистрНакопления.ТоварыВЯчейках.Остатки(&НаДату, Ячейка.Владелец = &ОснСклад И Ячейка <> &Ячейка И Ячейка <> &Ячейка2 И Ячейка <> &Ячейка3) КАК ТоварыВЯчейкахОстатки
|
|СГРУППИРОВАТЬ ПО
| ТоварыВЯчейкахОстатки.Номенклатура,
| ТоварыВЯчейкахОстатки.Характеристика,
| ТоварыВЯчейкахОстатки.Ячейка.Владелец
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
| ТоварыНаСкладахОстатки.Характеристика КАК Характеристика,
| ТоварыНаСкладахОстатки.Склад КАК Склад,
| СУММА(ТоварыНаСкладахОстатки.ВНаличииОстаток) КАК Остаток
|ПОМЕСТИТЬ ВТ_ОстаткиСклады
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(&НаДату, Склад <> &ОснСклад) КАК ТоварыНаСкладахОстатки
|
|СГРУППИРОВАТЬ ПО
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Характеристика,
| ТоварыНаСкладахОстатки.Склад
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВсеОстатки.Номенклатура КАК Номенклатура,
| ВсеОстатки.Характеристика КАК Характеристика,
| ВсеОстатки.Склад КАК Склад,
| СУММА(ВсеОстатки.Остаток) КАК Остаток
|ПОМЕСТИТЬ ВТ_ВсеОстатки
|ИЗ
| (ВЫБРАТЬ Номенклатура, Характеристика, Склад, Остаток ИЗ ВТ_ОстаткиЯчейки
| ОБЪЕДИНИТЬ ВСЕ
| ВЫБРАТЬ Номенклатура, Характеристика, Склад, Остаток ИЗ ВТ_ОстаткиСклады) КАК ВсеОстатки
|
|СГРУППИРОВАТЬ ПО
| ВсеОстатки.Номенклатура,
| ВсеОстатки.Характеристика,
| ВсеОстатки.Склад
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Остатки.Номенклатура КАК Номенклатура,
| Остатки.Характеристика КАК Характеристика,
| Остатки.Номенклатура.Артикул КАК Артикул,
| Остатки.Номенклатура.Описание КАК Описание,
| Остатки.Остаток КАК Остаток,
| Остатки.Склад КАК Склад,
| ЕСТЬNULL(ЦеныРозничные.Цена, 0) КАК Цена,
| ЕСТЬNULL(ЦеныЗакупочные.Цена, 0) КАК ЦенаЗак
|ИЗ
| ВТ_ВсеОстатки КАК Остатки
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НаДату, ВидЦены = &ВидЦены) КАК ЦеныРозничные
| ПО Остатки.Номенклатура = ЦеныРозничные.Номенклатура
| И Остатки.Характеристика = ЦеныРозничные.Характеристика
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НаДату, ВидЦены = &Зак) КАК ЦеныЗакупочные
| ПО Остатки.Номенклатура = ЦеныЗакупочные.Номенклатура
| И Остатки.Характеристика = ЦеныЗакупочные.Характеристика";
Запрос.УстановитьПараметр("ВидЦены", ВидЦены);
Запрос.УстановитьПараметр("НаДату", ТекущаяДата());
Запрос.УстановитьПараметр("Ячейка", Справочники.СкладскиеЯчейки.НайтиПоНаименованию("БРАК"));
Запрос.УстановитьПараметр("Ячейка2", Справочники.СкладскиеЯчейки.НайтиПоНаименованию("Недовоз"));
Запрос.УстановитьПараметр("Ячейка3", Справочники.СкладскиеЯчейки.НайтиПоНаименованию("ПЕРЕМЕЩЕНИЕ"));
Запрос.УстановитьПараметр("ОснСклад", Справочники.Склады.НайтиПоНаименованию("Основной"));
Запрос.УстановитьПараметр("Зак", Справочники.ВидыЦен.НайтиПоНаименованию("Закупочная"));
РезультатЗапроса = Запрос.Выполнить();
ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();
ТоварыКВыгрузке.Очистить();
Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
НовСтр = ТоварыКВыгрузке.Добавить();
НовСтр.Номенклатура = ВыборкаДетальныеЗаписи.Номенклатура;
// Если в вашей ТЗ "ТоварыКВыгрузке" заведена колонка для характеристик, раскомментируйте строку ниже:
// НовСтр.Характеристика = ВыборкаДетальныеЗаписи.Характеристика;
НовСтр.Остаток = ВыборкаДетальныеЗаписи.Остаток;
НовСтр.Цена = ВыборкаДетальныеЗаписи.Цена;
НовСтр.Склад = ВыборкаДетальныеЗаписи.Склад;
НовСтр.ЦенаЗак = ВыборкаДетальныеЗаписи.ЦенаЗак;
КонецЦикла;
КонецПроцедуры
Давайте подведем итог и проанализируем преимущества новой архитектуры запроса:
ВТ_ОстаткиЯчейки и ВТ_ОстаткиСклады, а также их итоговому объединению в ВТ_ВсеОстатки, СУБД оперирует только уникальными записями. Левое соединение с ценами больше физически не способно раздуть количество остатка.ПО Остатки.Характеристика = ЦеныРозничные.Характеристика. Это гарантирует точность сопоставления остатков и цен для каждой отдельной товарной позиции.ВидЦены = &ВидЦены и ВидЦены = &Зак) переданы непосредственно в параметры виртуальных таблиц среза последних. СУБД получит из базы данных только целевые строки, что существенно снизит нагрузку на сервер 1С и SQL-сервер.Полезный совет: Если на вашем сайте учет ведется без деления по характеристикам (нужен просто суммарный остаток карточки товара), вы можете использовать этот же шаблон запроса, предварительно удалив из него все упоминания поля Характеристика. Временные таблицы автоматически сгруппируют данные до уровня чистой номенклатуры, исключив дублирование строк.