Одна из наиболее распространенных ошибок начинающих разработчиков в среде 1С — это выполнение запросов внутри цикла. Подобный подход крайне негативно сказывается на производительности системы. При работе с большими базами данных (например, в конфигурациях «Далион», «Управление торговлей» или «Розница») запрос в цикле гарантированно приведет к зависанию интерфейса, перегрузке сервера СУБД и блокировкам таблиц.
В этой статье мы подробно разберем, как избавиться от запросов в цикле и получить остатки товаров, актуальные цены (как из регистра сведений, так и напрямую из документов поступления) и штрихкоды с помощью всего одного эффективного пакетного запроса.
Рассмотрим ситуацию, представленную автором темы. Изначально разработчик пытался решить задачу следующим образом: сначала он получал остатки товаров на складах, выгружал их в таблицу значений, а затем в цикле обходил каждую строку и делал отдельный запрос к документам поступления для определения последней цены закупки.
Если на остатках находится 10 000 позиций номенклатуры, система сделает 10 001 запрос к базе данных (1 запрос на получение остатков + 10 000 запросов в цикле). Каждое обращение к СУБД тратит время на сетевой пинг, компиляцию плана запроса и чтение данных с диска. В результате простейший отчет может формироваться часами.
Мы исправим эту архитектурную ошибку и объединим все операции в один пакетный запрос.
---
Самый очевидный способ объединить эти данные — использовать левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ) таблицы остатков с виртуальной таблицей цен и регистром штрихкодов. Рассмотрим пример такого запроса:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК Остаток,
ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
ЕСТЬNULL(Штрихкоды.Штрихкод, "") КАК Штрихкод
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
ПО ТоварыНаСкладахОстатки.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследних.ХарактеристикаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
ПО ТоварыНаСкладахОстатки.Номенклатура = Штрихкоды.Владелец
И ТоварыНаСкладахОстатки.ХарактеристикаНоменклатуры = Штрихкоды.ХарактеристикаНоменклатуры
ГДЕ
ТоварыНаСкладахОстатки.КоличествоОстаток > 0
Преимущества этого подхода: он прост в понимании, легко пишется в конструкторе запросов и быстро решает задачу «в лоб».
Недостатки и скрытые проблемы:
---
Чтобы избежать дублирования строк и гарантировать максимальную скорость выполнения запроса на больших базах данных, мы применим технологию временных таблиц (ВТ). Мы разобьем задачу на логические шаги, накладывая фильтры на каждом этапе.
Проанализируем по шагам структуру правильного пакетного запроса:
Шаг 1. Получаем остатки товаров во временную таблицу. Мы выбираем только те товары, которые реально есть на складе, и сохраняем их во временную таблицу ВТ_Остатки. Обязательно индексируем поля связи для ускорения последующих соединений.
Шаг 2. Получаем штрихкоды. Мы выбираем штрихкоды только для тех товаров, которые попали в нашу таблицу ВТ_Остатки. Чтобы избежать размножения строк, мы группируем штрихкоды и выбираем один максимальный (или самый первый) штрихкод с помощью агрегатной функции МАКСИМУМ(Штрихкод).
Шаг 3. Получаем цены. Аналогично выбираем срез последних цен только по тем товарам, которые есть в остатках.
Шаг 4. Объединяем данные в финальной выборке.
Посмотрим на готовый текст оптимального пакетного запроса:
// ШАГ 1: Получаем остатки товаров и индексируем их
ВЫБРАТЬ
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ТоварыОстатки.КоличествоОстаток КАК Остаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, КоличествоОстаток > 0) КАК ТоварыОстатки
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ХарактеристикаНоменклатуры
;
// ШАГ 2: Получаем штрихкоды только для позиций из ВТ_Остатки (исключая дубли)
ВЫБРАТЬ
ШтрихкодыРег.Владелец КАК Номенклатура,
ШтрихкодыReg.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
МАКСИМУМ(ШтрихкодыРег.Штрихкод) КАК Штрихкод
ПОМЕСТИТЬ ВТ_Штрихкоды
ИЗ
РегистрСведений.Штрихкоды КАК ШтрихкодыРег
ГДЕ
ШтрихкодыРег.Владелец В (ВЫБРАТЬ ВТ_Остатки.Номенклатура ИЗ ВТ_Остатки)
ГРУППИРОВАТЬ ПО
ШтрихкодыРег.Владелец,
ШтрихкодыРег.ХарактеристикаНоменклатуры
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ХарактеристикаНоменклатуры
;
// ШАГ 3: Получаем цены «СрезПоследних» только для позиций из ВТ_Остатки
ВЫБРАТЬ
ЦеныСрез.Номенклатура КАК Номенклатура,
ЦеныСрез.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ЦеныСрез.Цена КАК Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период,
ТипЦен = &ТипЦен
И Номенклатура В (ВЫБРАТЬ ВТ_Остатки.Номенклатура ИЗ ВТ_Остатки)
) КАК ЦеныСрез
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ХарактеристикаНоменклатуры
;
// ШАГ 4: Соединяем временные таблицы для получения итогового результата
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Остатки.Остаток КАК КоличествоОстаток,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена,
ЕСТЬNULL(ШК.Штрихкод, "") КАК Штрихкод
ИЗ
ВТ_Остатки КАК Остатки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК Цены
ПО Остатки.Номенклатура = Цены.Номенклатура
И Остатки.ХарактеристикаНоменклатуры = Цены.ХарактеристикаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Штрихкоды КАК ШК
ПО Остатки.Номенклатура = ШК.Номенклатура
И Остатки.ХарактеристикаНоменклатуры = ШК.ХарактеристикаНоменклатуры
Использование конструкции В Номенклатура В (ВЫБРАТЬ... ИЗ ВТ_Остатки) позволяет СУБД мгновенно отфильтровать ненужные записи. Базе данных больше не требуется сканировать миллионы цен и штрихкодов — обрабатываются исключительно те записи, по которым есть остатки товаров на складах.
---
Иногда перед разработчиком стоит задача вывести не розничную цену из прайс-листа (регистра сведений), а именно цену последнего фактического поступления товара из проведенных документов ПриходнаяНакладная. В исходном коде автор пытался делать это в цикле, выбирая ПЕРВЫЕ 1 с сортировкой по убыванию даты.
Давайте выясним, как решить эту задачу в рамках единого пакетного запроса без использования циклов. Нам потребуется определить максимальную дату прихода для каждого товара, а затем соединиться с табличной частью документа для извлечения цены.
Рассмотрим пример реализации:
// ШАГ 1: Помещаем остатки во временную таблицу (уже проиндексированную)
ВЫБРАТЬ
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.КоличествоОстаток КАК Остаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, КоличествоОстаток > 0) КАК ТоварыОстатки
ИНДЕКСИРОВАТЬ ПО Номенклатура
;
// ШАГ 2: Находим дату последнего поступления для каждого товара на остатках
ВЫБРАТЬ
ПоступлениеТовары.Номенклатура КАК Номенклатура,
МАКСИМУМ(ПоступлениеТовары.Ссылка.Дата) КАК МаксДата
ПОМЕСТИТЬ ВТ_ПоследниеДаты
ИЗ
Документ.ПриходнаяНакладная.Товары КАК ПоступлениеТовары
ГДЕ
ПоступлениеТовары.Ссылка.Проведен = ИСТИНА
И ПоступлениеТовары.Номенклатура В (ВЫБРАТЬ ВТ_Остатки.Номенклатура ИЗ ВТ_Остатки)
ГРУППИРОВАТЬ ПО
ПоступлениеТовары.Номенклатура
ИНДЕКСИРОВАТЬ ПО Номенклатура, МаксДата
;
// ШАГ 3: Вытаскиваем конкретную цену из последней накладной по найденной дате
ВЫБРАТЬ
ПоступлениеТовары.Номенклатура КАК Номенклатура,
МАКСИМУМ(ПоступлениеТовары.Цена) КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныПоследнегоПрихода
ИЗ
Документ.ПриходнаяНакладная.Товары КАК ПоступлениеТовары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПоследниеДаты КАК ПоследниеДаты
ПО ПоступлениеТовары.Номенклатура = ПоследниеДаты.Номенклатура
И ПоступлениеТовары.Ссылка.Дата = ПоследниеДаты.МаксДата
ГДЕ
ПоступлениеТовары.Ссылка.Проведен = ИСТИНА
ГРУППИРОВАТЬ ПО
ПоступлениеТовары.Номенклатура
ИНДЕКСИРОВАТЬ ПО Номенклатура
;
// ШАГ 4: Итоговое соединение остатков и цен прихода
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.Остаток КАК КоличествоОстаток,
ЕСТЬNULL(ЦеныПрихода.Цена, 0) КАК ЦенаПрихода
ИЗ
ВТ_Остатки КАК Остатки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныПоследнегоПрихода КАК ЦеныПрихода
ПО Остатки.Номенклатура = ЦеныПрихода.Номенклатура
Важное примечание к Шагу 3: Мы используем функцию МАКСИМУМ(ПоступлениеТовары.Цена) на случай, если в одну и ту же секунду (или в рамках одного документа) было введено несколько строк с разной ценой на одну позицию. Это защитит запрос от непредвиденного раздвоения строк.
---
В ходе обсуждения на форуме (Сообщение 16) был предложен альтернативный вариант с использованием ОБЪЕДИНИТЬ ВСЕ (UNION ALL) и последующей группировкой по виртуальным полям. Хотя этот вариант выглядит изящно и позиционируется как быстрый, он таит в себе огромную угрозу производительности.
В предложенном коде запросы ко второму и третьему блоку (объединение с регистрами ЦеныНоменклатуры.СрезПоследних и Штрихкоды) выполняются абсолютно по всей базе данных без каких-либо отборов по номенклатуре. СУБД будет вынуждена прочитать и обработать все цены и штрихкоды, которые когда-либо заводились в системе, включая архивные и неиспользуемые товары. Это приведет к колоссальной нагрузке на дисковую подсистему и оперативную память сервера.
Правило разработки в 1С: При использовании объединений (ОБЪЕДИНИТЬ ВСЕ) во всех вложенных запросах обязательно должны присутствовать жесткие фильтры (например, связь с таблицей остатков), аналогичные тем, что мы применили во втором варианте решения.
---
Если перед нами стоит задача вывести абсолютно все штрихкоды товара, а не только один максимальный, мы не можем сделать это стандартными средствами языка SQL в запросе 1С, так как функции агрегации строк (например, STRING_AGG) платформой напрямую не поддерживаются.
Для решения этой проблемы мы можем использовать возможности Системы компоновки данных (СКД) или программную обработку результата запроса.
Решение средствами СКД:
Штрихкод в набор данных как обычное детальное поле (допуская дублирование строк при выборке).Штрихкод укажем следующее выражение:
СоединитьСтроки(ВычислитьВыражениеТаблицыЗначений("Штрихкод")).
---
При написании запроса обязательно учитывайте конфигурацию, в которой ведется разработка, так как названия регистров и измерений могут различаться:
РегистрСведений.Штрихкоды.Владелец (тип значения — справочник Номенклатура).КатегорияЦен вместо привычного ТипЦен.РегистрСведений.ШтрихкодыНоменклатуры.Номенклатура (вместо Владелец).Характеристика (вместо ХарактеристикаНоменклатуры).Изучив эти нюансы и используя технологию временных таблиц с обязательным индексированием полей связи, мы сможем создавать быстрые, надежные и легко масштабируемые отчеты в любых учетных решениях на платформе 1С:Предприятие.