Выполнение запросов внутри циклов — это одна из самых распространенных ошибок производительности в разработке на платформе 1С:Предприятие. Когда нам требуется получить остатки по регистру накопления или срез последних по регистру сведений на несколько различных дат (например, на конец каждого месяца года), первая интуитивная мысль начинающего разработчика — просто обойти массив дат циклом и на каждой итерации вызвать запрос с параметром. Однако такой подход генерирует сотни и тысячи лишних обращений к СУБД, вызывает избыточную нагрузку на дисковую подсистему и сеть, а также приводит к неэффективному использованию кэша планов запросов SQL.
В этой статье мы подробно разберем, как избавиться от запроса в цикле при получении остатков на список произвольных дат. Мы проанализируем как классические методы чистого языка запросов 1С, так и современные, более производительные платформенные механизмы, включая Систему компоновки данных (СКД) и гибридные алгоритмы обработки данных.
Прежде чем перейти к решениям, давайте кратко разберем, почему запрос в цикле считается критическим антипаттерном. При вызове метода Запрос.Выполнить() внутри цикла происходит следующее:
Наша главная задача — передать СУБД весь массив дат за один раз, получить консолидированный результат в одном пакете и обработать его оптимальным образом.
Этот способ основан на соединении таблицы нужных нам дат с физической таблицей движений или итогов регистра. Давайте разберем этот алгоритм на примере получения цен номенклатуры (срез последних) на список дат. Тот же принцип применим и для регистров накопления.
Идея заключается в том, чтобы поместить наш массив дат во временную таблицу, соединить её по неэквивалентному условию (например, дата из массива больше или равна дате движения регистра), сгруппировать по максимальному периоду и получить актуальные данные на каждую контрольную точку.
Рассмотрим по шагам структуру такого запроса:
// Шаг 1: Передаем массив дат в параметры запроса и помещаем его во временную таблицу
ВЫБРАТЬ
МассивДат.Дата КАК ПериодКонтроля
ПОМЕСТИТЬ втДаты
ИЗ
&МассивДат КАК МассивДат
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем срез последних цен на каждую дату из нашей временной таблицы
ВЫБРАТЬ
втДаты.ПериодКонтроля КАК ПериодКонтроля,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК АктуальныйПериод
ПОМЕСТИТЬ втМаксимальныеПериоды
ИЗ
втДаты КАК втДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ЦеныНоменклатуры.Период <= втДаты.ПериодКонтроля
СГРУППИРОВАТЬ ПО
втДаты.ПериодКонтроля,
ЦеныНоменклатуры.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем полученные максимальные периоды с реальной таблицей цен для получения ресурсов
ВЫБРАТЬ
втМаксимальныеПериоды.ПериодКонтроля КАК ПериодКонтроля,
втМаксимальныеПериоды.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрез.Цена КАК Цена
ИЗ
втМаксимальныеПериоды КАК втМаксимальныеПериоды
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатурыСрез
ПО втМаксимальныеПериоды.Номенклатура = ЦеныНоменклатурыСрез.Номенклатура
И втМаксимальныеПериоды.АктуальныйПериод = ЦеныНоменклатурыСрез.Period
Преимущества метода: Запрос выполняется за один шаг на сервере СУБД, полностью исключая циклы в коде 1С.
Ограничения метода: На больших объемах данных неэквивалентное соединение ПО ЦеныНоменклатуры.Период <= втДаты.ПериодКонтроля может выполняться медленно, так как СУБД вынуждена сопоставлять каждую дату с множеством записей регистра. При миллионах строк в регистре производительность может резко снизиться.
Если мы работаем с регистром накопления, то наиболее эффективным способом в чистом запросе является использование виртуальной таблицы ОстаткиИОбороты с нужной периодичностью (например, День, Месяц) и последующим расчетом нарастающего итога.
Проблема виртуальной таблицы ОстаткиИОбороты состоит в том, что она по умолчанию возвращает строки только для тех периодов, в которых были реальные движения по регистру. Если по товару не было движений в течение нескольких месяцев, то в выборке эти месяцы просто будут отсутствовать, хотя остаток на складе физически сохраняется.
Для решения этой проблемы мы используем левое соединение вспомогательной таблицы всех интересующих нас дат (например, дней или месяцев) с виртуальной таблицей остатков и оборотов. Посмотрим на практический пример решения:
// Помещаем в параметры запроса массив интересующих нас месяцев (начало месяцев)
ВЫБРАТЬ
Календарь.Период КАК Период
ПОМЕСТИТЬ втДатыПериода
ИЗ
&ТаблицаМесяцев КАК Календарь
;
////////////////////////////////////////////////////////////////////////////////
// Получаем остатки и обороты за весь интервал с детализацией по периоду
ВЫБРАТЬ
ОстаткиТоваров.Номенклатура КАК Номенклатура,
ОстаткиТоваров.Период КАК Период,
ОстаткиТоваров.КоличествоНачальныйОстаток КАК НачальныйОстаток,
ОстаткиТоваров.КоличествоОборот КАК Оборот,
ОстаткиТоваров.КоличествоКонечныйОстаток КАК КонечныйОстаток
ПОМЕСТИТЬ втДвижения
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Месяц, , ) КАК ОстаткиТоваров
;
////////////////////////////////////////////////////////////////////////////////
// Расчет остатка на каждую дату из таблицы втДатыПериода
ВЫБРАТЬ
втДатыПериода.Период КАК ПериодКонтроля,
втДвижения.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР
КОГДА втДвижения.Период = &НачалоПериода
ТОГДА втДвижения.НачальныйОстаток
ИНАЧЕ ВЫБОР
КОГДА втДвижения.Период <= втДатыПериода.Период
ТОГДА втДвижения.Оборот
ИНАЧЕ 0
КОНЕЦ
КОНЕЦ) КАК КоличествоОстаток
ИЗ
втДатыПериода КАК втДатыПериода
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втДвижения КАК втДвижения
ПО втДвижения.Период <= втДатыПериода.Период
СГРУППИРОВАТЬ ПО
втДатыПериода.Период,
втДвижения.Номенклатура
В данном примере мы берем начальный остаток на самую раннюю границу и прибавляем к нему все обороты за периоды, предшествующие или равные нашей целевой дате. Такой математический подход позволяет гарантированно рассчитать актуальный остаток на любую из выбранных дат, даже если в конкретном месяце движений по номенклатуре не было.
Система компоновки данных (СКД) встроена в платформу 1С:Предприятие и обладает уникальными возможностями оптимизации запросов, о которых многие разработчики забывают. Механизм связей наборов данных позволяет полностью переложить задачу циклического получения срезов данных на плечи самой платформы.
При использовании этого метода платформа 1С самостоятельно формирует и отправляет в СУБД пакетные запросы оптимального объема, сводя к минимуму накладные расходы. Разберем алгоритм настройки СКД по шагам:
НаборДанных_Даты. Этот набор будет содержать список всех интересующих нас дат (колонка ПериодКонтроля). Мы можем передать туда готовую таблицу значений с датами из кода.НаборДанных_Остатки. Напишем простейший запрос к виртуальной таблице остатков регистра, используя параметр:
ВЫБРАТЬ
Остатки.Организация КАК Организация,
Остатки.ФизическоеЛицо КАК ФизическоеЛицо,
Остатки.СуммаОстаток КАК СуммаОстаток
ИЗ
РегистрНакопления.ЗадолженностьПоУдержаниямФизическихЛиц.Остатки(&ПериодГраницы, Организация = &Организация) КАК Остатки
НаборДанных_ДатыНаборДанных_ОстаткиПериодКонтроляФизическоеЛицо (если связываем по аналитике)ПериодГраницы. Это сопоставит дату контроля из первого набора с параметром виртуальной таблицы остатков второго набора.При выполнении компоновки платформа 1С автоматически сгенерирует временные таблицы, передаст туда ваши даты, соединит их с итогами регистра на уровне СУБД и вернет готовый результат. Для разработчика этот процесс выглядит декларативно и просто, а скорость выполнения оказывается максимальной.
Иногда (особенно в высоконагруженных системах) расчет нарастающих итогов средствами СУБД (как в Методе 1 и 2) может работать медленно из-за квадратичной сложности неэквивалентных соединений. На каждый день периода СУБД вынуждена суммировать все предыдущие дни, что при больших периодах превращается в вычислительную проблему.
В таких сценариях самым производительным решением оказывается гибридный подход:
ТаблицуЗначений на сервере 1С.Номенклатура) и по Периоду.Рассмотрим пример реализации такого алгоритма в коде:
// Шаг 1: Запрос получает только реальные движения
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Товары.Номенклатура КАК Номенклатура,
| Товары.Период КАК Период,
| Товары.КоличествоНачальныйОстаток КАК НачОстаток,
| Товары.КоличествоОборот КАК Оборот
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоМесяца, &КонецМесяца, День, , ) КАК Товары
|УПОРЯДОЧИТЬ ПО
| Номенклатура,
| Период";
Запрос.УстановитьПараметр("НачалоМесяца", НачалоМесяца);
Запрос.УстановитьПараметр("КонецМесяца", КонецМесяца);
ТаблицаДвижений = Запрос.Выполнить().Выгрузить();
// Шаг 2: Создаем результирующую таблицу значений для хранения итогов по нужным нам датам
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("ДатаКонтроля", Новый ОписаниеТипов("Дата"));
ТаблицаРезультат.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТаблицаРезультат.Колонки.Добавить("Остаток", Новый ОписаниеТипов("Число"));
// Шаг 3: Алгоритм последовательного расчета остатков
Для Каждого СтрокаДаты Из МассивКонтрольныхДат Цикл
ТекущаяДата = СтрокаДаты.Значение;
// Перебираем сгруппированные данные в памяти
Для Каждого НоменклатураСсылка Из СписокНоменклатуры Цикл
ТекущийОстаток = 0;
ПерваяСтрока = Истина;
Для Каждого СтрокаДвижения Из ТаблицаДвижений Цикл
Если СтрокаДвижения.Номенклатура <> НоменклатураСсылка Тогда
Продолжить;
КонецЕсли;
Если ПерваяСтрока Тогда
ТекущийОстаток = СтрокаДвижения.НачОстаток;
ПерваяСтрока = Ложь;
КонецЕсли;
Если СтрокаДвижения.Период <= ТекущаяДата Тогда
ТекущийОстаток = ТекущийОстаток + СтрокаДвижения.Оборот;
Иначе
Прервать; // Периоды упорядочены, дальше идти нет смысла
КонецЕсли;
КонецЦикла;
// Добавляем рассчитанный остаток в результирующую таблицу
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.ДатаКонтроля = ТекущаяДата;
НоваяСтрока.Номенклатура = НоменклатураСсылка;
НоваяСтрока.Остаток = ТекущийОстаток;
КонецЦикла;
КонецЦикла;
Почему это выгодно? Несмотря на то что в коде присутствуют вложенные циклы, они выполняются над уже подготовленными данными в памяти сервера приложений 1С. Нет ни единого обращения к СУБД, отсутствуют тяжелые неэквивалентные соединения, нагрузка на SQL-сервер минимальна. Данный гибридный подход является промышленным стандартом при оптимизации сложных высоконагруженных расчетов (например, в партионном учете или расчете себестоимости крупных ERP-систем).
Для наглядности сравним все рассмотренные методы, чтобы вы могли выбрать наиболее подходящий под вашу задачу:
| Критерий сравнения | Чистый запрос (Метод 1 и 2) | Связи наборов СКД (Метод 3) | Гибридный метод (Метод 4) |
|---|---|---|---|
| Сложность разработки | Средняя (требуется понимание нарастающих итогов) | Низкая (настраивается визуально в конструкторе) | Высокая (требуется написание эффективных алгоритмов на встроенном языке) |
| Скорость на малых данных | Высокая | Высокая | Средняя (из-за накладных расходов на обход ТЗ) |
| Скорость на больших данных | Низкая (из-за неэквивалентных JOIN в СУБД) | Высокая (СУБД оптимизирует пакеты) | Максимальная (линейная сложность на сервере приложений) |
| Где лучше использовать | В простых отчетах и небольших обработках | В любых сложных отчетах, выводимых пользователю | В фоновых заданиях, закрытии месяца, проведении тяжелых документов |
Подведем итоги. Мы рассмотрели несколько способов решения классической задачи получения среза данных на список дат. Главный вывод — никогда не пишите запросы к виртуальным таблицам внутри циклов. Если ваша цель — построить отчет, используйте возможности связей наборов данных в СКД. Если вы разрабатываете сложный расчетный механизм в проведении документов или регламентных заданиях, отдайте предпочтение гибридному подходу, который сбережет ресурсы вашего сервера СУБД.