При разработке отчетов и обработок в 1С:Предприятие мы часто сталкиваемся с необходимостью передавать списки значений в запросы. Например, это может быть массив номенклатуры или список складов. Если количество элементов в таком массиве невелико, стандартный подход с использованием параметра запроса типа "Массив" и оператора В прекрасно работает. Однако, когда массив содержит сотни элементов (например, 700, как в нашей ситуации), могут возникнуть серьезные проблемы: снижение производительности, длительные ожидания и даже ошибки, связанные с превышением таймаутов блокировок.
Давайте подробно разберем, почему это происходит, и предложим эффективные решения.
Основная причина проблем кроется в том, как платформа 1С и используемая СУБД (например, Microsoft SQL Server, PostgreSQL) обрабатывают оператор В с большим количеством элементов:
OR: Когда вы передаете массив в параметр запроса 1С, платформа транслирует его в набор условий ИЛИ (OR) для СУБД. Например, Номенклатура В (&СписокНоменклатуры) для большого списка [Н1, Н2, ..., Н700] превращается в Номенклатура = Н1 ИЛИ Номенклатура = Н2 ИЛИ ... ИЛИ Номенклатура = Н700.OR, часто не может построить оптимальный план выполнения запроса. Она может игнорировать индексы и выполнять полное сканирование таблиц или их значительных частей. Это приводит к значительному замедлению работы запроса.Рассмотрим конкретный пример запроса, с которым столкнулся автор темы, и предложим оптимизированные решения.
Один из самых эффективных способов решить проблему с большими массивами — это передать данные через временные таблицы запроса 1С. Вместо того чтобы передавать массив напрямую в параметр, мы сначала создаем временную таблицу, заполняем ее элементами нашего массива, а затем используем эту временную таблицу для фильтрации данных в основном запросе. СУБД гораздо лучше оптимизирует запросы с соединениями по временным таблицам, чем с длинными списками OR.
Разберем по шагам, как это реализовать, на примере запроса из нашей темы.
Предположим, у нас есть массив значений МассивПродукции и один склад ПараметрСклад. В исходном запросе они использовались так:
|ИЗ
| РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(
| ,
| Номенклатура В (&Продукция)
| И Склад В (&Склад)) КАК ТоварыВРезервеНаСкладахОстатки
Для оптимизации мы перепишем запрос, используя временные таблицы. Сначала создадим временные таблицы для номенклатуры и складов, а затем будем ссылаться на них в условиях отбора. Проанализируем предложенный оптимизированный запрос:
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("НачалоПериода", НачалоГода(ТекущаяДата()));
Запрос.УстановитьПараметр("КонецПериода", ТекущаяДата());
Запрос.Текст =
"ВЫБРАТЬ ПЕРВЫЕ 700
| Номенклатура.Ссылка
|ПОМЕСТИТЬ ВТ_Номенклатура
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Ссылка В (&Продукция)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Склады.Ссылка
|ПОМЕСТИТЬ ВТ_Склады
|ИЗ
| Справочник.Склады КАК Склады
|ГДЕ
| Склады.Ссылка В (&Склад)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыВРезервеНаСкладахОстатки.Номенклатура,
| ТоварыВРезервеНаСкладахОстатки.КоличествоОстаток КАК Резерв
|ПОМЕСТИТЬ ВТ_Резерв
|ИЗ
| РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(
| ,
| Склад В
| (ВЫБРАТЬ
| ВТ_Склады.Ссылка
| ИЗ
| ВТ_Склады)
| И Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ТоварыВРезервеНаСкладахОстатки
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура,
| МИНИМУМ(ТоварыНаСкладахОстаткиИОбороты.Период) КАК Минимум
|ПОМЕСТИТЬ ВТ_Минимум
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
| НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ),
| КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ),
| Регистратор,
| ,
| Склад В
| (ВЫБРАТЬ
| ВТ_Склады.Ссылка
| ИЗ
| ВТ_Склады)
| И Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ТоварыНаСкладахОстаткиИОбороты
|
|СГРУППИРОВАТЬ ПО
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТоварыНаСкладахОстаткиИОбороты.Период) КАК Период,
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
| СУММА(ТоварыНаСкладахОстаткиИОбороты.КоличествоНачальныйОстаток) КАК НачальныйОстаток,
| СУММА(ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КонечныйОстаток,
| СУММА(ТоварыНаСкладахОстаткиИОбороты.КоличествоПриход) КАК КоличествоПриход,
| СУММА(ТоварыНаСкладахОстаткиИОбороты.КоличествоРасход) КАК КоличествоРасход
|ПОМЕСТИТЬ ВТ_ПоПериодам
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
| &НачалоПериода,
| &КонецПериода,
| Месяц,
| ,
| Склад В
| (ВЫБРАТЬ
| ВТ_Склады.Ссылка
| ИЗ
| ВТ_Склады)
| И Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ТоварыНаСкладахОстаткиИОбороты
|
|СГРУППИРОВАТЬ ПО
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.КоличествоОстаток КАК НачальныйОстаток,
| &НачалоПериода КАК Период
|ПОМЕСТИТЬ ВТ_Начало
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ),
| Склад В
| (ВЫБРАТЬ
| ВТ_Склады.Ссылка
| ИЗ
| ВТ_Склады)
| И Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ТоварыНаСкладахОстатки
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.КоличествоОстаток КАК КонечныйОстаток,
| КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ) КАК Период
|ПОМЕСТИТЬ ВТ_Конец
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| Склад В
| (ВЫБРАТЬ
| ВТ_Склады.Ссылка
| ИЗ
| ВТ_Склады)
| И Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ТоварыНаСкладахОстатки
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ЗначенияТочкиЗаказаСрезПоследних.Номенклатура,
| ЗначенияТочкиЗаказаСрезПоследних.ЗначениеТочкиЗаказа,
| ЗначенияТочкиЗаказаСрезПоследних.МинимальныйСтраховойЗапас
|ПОМЕСТИТЬ ВТ_точкиЗаказа
|ИЗ
| РегистрСведений.ЗначенияТочкиЗаказа.СрезПоследних(
| &КонецПериода,
| Номенклатура В
| (ВЫБРАТЬ
| ВТ_Номенклатура.Ссылка
| ИЗ
| ВТ_Номенклатура)) КАК ЗначенияТочкиЗаказаСрезПоследних
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ПоПериодам.Номенклатура КАК Номенклатура,
| ЕСТЬNULL(ВТ_Начало.НачальныйОстаток, 0) КАК НачальныйОстатокНаНачало,
| ВТ_ПоПериодам.КоличествоПриход,
| ВТ_ПоПериодам.КоличествоРасход,
| ЕСТЬNULL(ВТ_Конец.КонечныйОстаток, 0) КАК КонечныйОстатокНаКонец,
| ЕСТЬNULL(ВТ_ПоПериодам.КоличествоРасход, 0) / ВЫБОР
| КОГДА ВТ_ПоПериодам.Период < 2
| ТОГДА 1
| ИНАЧЕ ВТ_ПоПериодам.Период - 1
| КОНЕЦ КАК СреднийРасход,
| ВТ_ПоПериодам.Период КАК Период,
| ВТ_ПоПериодам.НачальныйОстаток,
| ВТ_ПоПериодам.КонечныйОстаток,
| ВТ_Минимум.Минимум КАК ДатаПоступления,
| ЕСТЬNULL(ВТ_Резерв.Резерв, 0) КАК Резерв,
| ВТ_точкиЗаказа.ЗначениеТочкиЗаказа,
| ВТ_точкиЗаказа.МинимальныйСтраховойЗапас
|ИЗ
| ВТ_ПоПериодам КАК ВТ_ПоПериодам
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Начало КАК ВТ_Начало
| ПО ВТ_ПоПериодам.Номенклатура = ВТ_Начало.Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Конец КАК ВТ_Конец
| ПО ВТ_ПоПериодам.Номенклатура = ВТ_Конец.Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Минимум КАК ВТ_Минимум
| ПО ВТ_ПоПериодам.Номенклатура = ВТ_Минимум.Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Резерв КАК ВТ_Резерв
| ПО ВТ_ПоПериодам.Номенклатура = ВТ_Резерв.Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_точкиЗаказа КАК ВТ_точкиЗаказа
| ПО ВТ_ПоПериодам.Номенклатура = ВТ_точкиЗаказа.Номенклатура";
Что мы сделали и почему это эффективно:
ВЫБРАТЬ ПЕРВЫЕ 700
| Номенклатура.Ссылка
|ПОМЕСТИТЬ ВТ_Номенклатура
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Ссылка В (&Продукция)
|;
|
|ВЫБРАТЬ
| Склады.Ссылка
|ПОМЕСТИТЬ ВТ_Склады
|ИЗ
| Справочник.Склады КАК Склады
|ГДЕ
| Склады.Ссылка В (&Склад)
|;
Мы создаем две временные таблицы: ВТ_Номенклатура и ВТ_Склады. На этапе заполнения этих временных таблиц происходит однократная передача параметров &Продукция и &Склад. Даже если здесь используется оператор В с большим списком, это действие происходит один раз и его производительность может быть выше, так как СУБД может эффективнее оптимизировать запись в новую таблицу.
Вместо непосредственной передачи большого массива в каждую виртуальную таблицу регистра, мы используем подзапросы, ссылающиеся на наши временные таблицы:
Склад В
(ВЫБРАТЬ
ВТ_Склады.Ссылка
ИЗ
ВТ_Склады)
И Номенклатура В
(ВЫБРАТЬ
ВТ_Номенклатура.Ссылка
ИЗ
ВТ_Номенклатура)
Таким образом, вместо длинного списка OR, СУБД получает условие типа "Номенклатура, которая есть в таблице ВТ_Номенклатура". Это позволяет СУБД использовать более эффективные планы выполнения, например, с помощью хешированных соединений или индексов по временным таблицам, если они будут автоматически созданы. Такая переработка запроса может значительно ускорить его выполнение, как показано в обсуждении (168 секунд против 18 секунд).
Для реализации этого подхода в коде 1С мы сначала должны сформировать массивы МассивПродукции и МассивСкладов, а затем установить их как параметры запроса:
// ... формирование МассивПродукции и МассивСкладов
Запрос = Новый Запрос;
Запрос.Текст = МойОптимизированныйТекстЗапроса; // Текст запроса, использующий ВТ
Запрос.УстановитьПараметр("Продукция", МассивПродукции);
Запрос.УстановитьПараметр("Склад", МассивСкладов);
РезультатЗапроса = Запрос.Выполнить();
Второй, не менее эффективный, способ — это передача в запрос не просто массива, а целой ТаблицыЗначений. Этот метод особенно полезен, когда вам нужно передать не просто список ссылок, а более сложную структуру данных с несколькими колонками. Платформа 1С:Предприятие примет ТаблицуЗначений и автоматически создаст для нее временную таблицу на стороне СУБД, к которой вы сможете обращаться непосредственно в запросе.
Разберем по шагам, как это сделать:
ТаблицыЗначений в коде 1С:
Сначала мы формируем ТаблицуЗначений в коде 1С, добавляем в нее необходимые колонки и заполняем данными.
// Исходный массив из 700 элементов
МассивПродукции = Новый Массив;
// ... заполнение МассиваПродукции
ТаблицаНоменклатурыДляЗапроса = Новый ТаблицаЗначений;
ТаблицаНоменклатурыДляЗапроса.Колонки.Добавить("Номенклатура"); // Создаем колонку с именем, по которому будем обращаться в запросе
Для Каждого ЭлементМассива Из МассивПродукции Цикл
НоваяСтрока = ТаблицаНоменклатурыДляЗапроса.Добавить();
НоваяСтрока.Номенклатура = ЭлементМассива;
КонецЦикла;
// Если есть и склады:
ТаблицаСкладовДляЗапроса = Новый ТаблицаЗначений;
ТаблицаСкладовДляЗапроса.Колонки.Добавить("Склад");
// ... заполнение ТаблицыСкладовДляЗапроса
ТаблицыЗначений как параметра запроса:
Передаем готовую ТаблицуЗначений в запрос с помощью метода УстановитьПараметр.
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТаблицаНоменклатуры", ТаблицаНоменклатурыДляЗапроса);
Запрос.УстановитьПараметр("ТаблицаСкладов", ТаблицаСкладовДляЗапроса);
ТаблицыЗначений в тексте запроса:
В тексте запроса мы можем обращаться к этой ТаблицеЗначений как к источнику данных, используя символ & перед именем параметра. Затем ее можно поместить во временную таблицу или использовать напрямую в условии В.
Запрос.Текст =
"ВЫБРАТЬ
| ТН.Номенклатура
|ПОМЕСТИТЬ ВТ_Номенклатура
|ИЗ
| &ТаблицаНоменклатуры КАК ТН
|;
|
|ВЫБРАТЬ
| ТС.Склад
|ПОМЕСТИТЬ ВТ_Склады
|ИЗ
| &ТаблицаСкладов КАК ТС
|;
|
|// Далее используем ВТ_Номенклатура и ВТ_Склады как в первом решении
|// ...
|ИЗ
| РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(
| ,
| Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_Номенклатура)
| И Склад В (ВЫБРАТЬ Склад ИЗ ВТ_Склады)) КАК ТоварыВРезервеНаСкладахОстатки
|// ...
";
Этот метод так же эффективно обходит проблемы с производительностью и блокировками, как и прямое создание временных таблиц для справочников. Он более гибок, если данные для фильтрации не являются простыми ссылками из уже существующих справочников, а формируются динамически.
В тексте темы также упоминалась ошибка "Деление на ноль". Эта проблема, как правило, не связана напрямую с размером массива или блокировками, а возникает при математических вычислениях в запросе, когда знаменатель выражения становится равным нулю. В запросе 1С для предотвращения таких ошибок мы используем конструкцию ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ.
Рассмотрим проблемный участок из оригинального запроса и его исправленный вариант:
Исходный фрагмент, потенциально приводящий к ошибке деления на ноль:
| ЕСТЬNULL(ВТ_ПоПериодам.КоличествоРасход, 0) / ВТ_ПоПериодам.Период КАК СреднийРасход,
Если поле ВТ_ПоПериодам.Период могло быть равно 0 (например, если нет данных за период), то возникла бы ошибка. Исправленный вариант, который мы видим в оптимизированном запросе, обрабатывает эту ситуацию:
| ЕСТЬNULL(ВТ_ПоПериодам.КоличествоРасход, 0) / ВЫБОР
| КОГДА ВТ_ПоПериодам.Период < 2
| ТОГДА 1
| ИНАЧЕ ВТ_ПоПериодам.Период - 1
| КОНЕЦ КАК СреднийРасход,
Здесь используется условное выражение: если Период меньше 2 (т.е., 0 или 1), то для деления используется значение 1. Это предотвращает деление на ноль и обеспечивает корректное вычисление даже при отсутствии данных за полный период или при наличии всего одного периода (что в контексте среднего расхода может быть логично рассматривать как расход за один период).
Помимо методов передачи больших массивов, существуют и другие общие подходы к оптимизации запросов, которые помогут избежать проблем с производительностью и блокировками:
Убедитесь, что все поля, используемые в условиях ГДЕ, СОЕДИНЕНИЕ ПО, а также в группировках СГРУППИРОВАТЬ ПО, имеют соответствующие индексы в вашей СУБД. Для регистров накопления и сведений это, как правило, измерения. Правильные индексы позволяют СУБД быстро находить нужные данные, что критически важно для производительности.
Всегда используйте четкие и минимально необходимые границы периода для виртуальных таблиц регистров накопления и сведений (например, .Остатки(&НачалоПериода, &КонецПериода, ... )). Чем меньше данных будет просканировано, тем быстрее выполнится запрос.
Выбирайте из таблиц только те поля, которые вам действительно необходимы. Избыточный выбор полей увеличивает объем передаваемых данных и нагрузку на СУБД.
Старайтесь использовать виртуальные таблицы регистров (Остатки, Обороты, СрезПоследних) для получения агрегированных данных, а не прямой обход таблиц движений. Они оптимизированы платформой для эффективного получения этих данных.
Если запрос продолжает работать медленно, используйте инструменты вашей СУБД (например, SQL Server Management Studio для SQL Server, EXPLAIN ANALYZE для PostgreSQL) для анализа плана выполнения запроса. Это поможет выявить узкие места и предложить дополнительные меры оптимизации, такие как создание специфических индексов.
Если по каким-то причинам предыдущие методы не подходят, можно разбить большой массив на несколько меньших частей (например, по 200-300 элементов) и выполнять запрос в цикле для каждой части. Однако этот метод обычно менее производителен из-за накладных расходов на многократное выполнение запроса и повторное открытие соединений с базой данных. Его следует рассматривать как крайнюю меру.
Передача больших массивов данных в запросы 1С — это распространенная задача, которая требует внимательного подхода к оптимизации. Используя временные таблицы или передавая ТаблицыЗначений в запросы, мы можем значительно улучшить производительность и стабильность работы системы, избегая проблем с блокировками и неоптимальными планами выполнения. Всегда помните о важности правильного индексирования и анализа планов запросов для достижения наилучших результатов.