При написании запросов в системе 1С:Предприятие разработчики часто сталкиваются с необходимостью наложить необязательный фильтр. Типичный пример — отчет или обработка по остаткам товаров, где пользователь может выбрать конкретный склад, а может оставить поле пустым для получения данных по всем складам сразу.
Простейший и самый очевидный путь — склеивание строк через оператор + или использование условных операторов в самом запросе. Однако каждый из этих подходов имеет свои скрытые угрозы: от потери читаемости кода до катастрофического падения производительности всей СУБД на больших базах данных. В этой статье мы подробно разберем, почему классические решения могут быть опасны, и рассмотрим наиболее надежные и современные альтернативы.
В исходном сообщении темы автор предлагает использовать конкатенацию строк:
Если ЗначениеЗаполнено(Склад) Тогда
Запрос.Текст = Запрос.Текст + " ГДЕ ТоварыНаСкладахОстаткиИОбороты.Склад = &Склад";
КонецЕсли;
Рассмотрим основные минусы такого подхода:
Выясним, какими способами можно решить эту задачу элегантно и без ущерба для стабильности системы.
Для того чтобы не разрывать текст запроса, разработчики часто прибегают к условной логике внутри самого SQL-запроса. Например, переносят проверку в параметры виртуальной таблицы или в секцию ГДЕ:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииНачальныйОстаток КАК ВНаличииНачальныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , ,
&Склад = Значение(Справочник.Склады.ПустаяСсылка) ИЛИ Склад = &Склад) КАК ТоварыНаСкладахОстаткиИОбороты
На первый взгляд это выглядит красиво: запрос цельный, легко читается. Однако с точки зрения СУБД (будь то MS SQL Server или PostgreSQL) этот код таит в себе огромную проблему — Parameter Sniffing (залипание параметров) и неоптимальные планы выполнения.
Проанализируем ситуацию: когда СУБД компилирует данный запрос, она строит единый план выполнения. Если при первом запуске параметр &Склад был пустой, СУБД строит план для чтения всей таблицы (Index Scan или Table Scan). При последующих вызовах с заполненным складом СУБД может применить тот же самый сохраненный план вместо быстрого точечного поиска по индексу (Index Seek). В результате простой запрос начинает выполняться минутами, перегружая дисковую подсистему сервера.
Использование конструкции ВЫБОР КОГДА ... ТОГДА вместо ИЛИ несколько улучшает ситуацию в ряде СУБД, но глобально проблему не решает — индексы таблицы при сложных условных конструкциях в параметрах виртуальных таблиц часто перестают задействоваться корректно.
Чтобы избежать проблем с производительностью СУБД и при этом сохранить читаемость структуры, мы можем применить метод текстовых шаблонов. В тексте запроса оставляется специальный маркер-параметр, который затем заменяется в коде на реальное условие или на заглушку ИСТИНА.
Разберем этот пример на практике:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
| ТоварыНаСкладахОстаткиИОбороты.ВНаличииКонечныйОстаток КАК ВНаличииКонечныйОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , &УсловиеСклад) КАК ТоварыНаСкладахОстаткиИОбороты";
Если ЗначениеЗаполнено(Склад) Тогда
Запрос.Текст = СтрЗаменить(Запрос.Текст, "&УсловиеСклад", "Склад = &Склад");
Запрос.УстановитьПараметр("Склад", Склад);
Иначе
Запрос.Текст = СтрЗаменить(Запрос.Текст, "&УсловиеСклад", "ИСТИНА");
КонецЕсли;
Преимущества метода:
Недостатки:
&УсловиеСклада вместо &УсловиеСклад), автозамена не сработает, и запрос упадет с синтаксической ошибкой СУБД.Начиная с версии платформы 1С 8.3.5 разработчикам доступен мощный встроенный инструмент — СхемаЗапроса. Этот объект позволяет работать с текстом запроса не на уровне манипуляций со строками, а как с полноценным программным объектом, состоящим из таблиц, доступных полей, условий и операторов.
Посмотрим на пример реализации динамического отбора с помощью СхемаЗапроса:
ТекстЗапроса =
"ВЫБРАТЬ
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
| ТоварыНаСкладахОстаткиИОбороты.ВНаличииКонечныйОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ТоварыНаСкладахОстаткиИОбороты";
Если ЗначениеЗаполнено(Склад) Тогда
Схема = Новый СхемаЗапроса;
Схема.УстановитьТекстЗапроса(ТекстЗапроса);
// Получаем первый пакет и первый оператор запроса
Пакет = Схема.ПакетЗапросов[0];
Оператор = Пакет.Операторы[0];
// Получаем источник (нашу виртуальную таблицу ОстаткиИОбороты)
ИсточникТаблица = Оператор.Источники[0].Источник;
// Параметры виртуальной таблицы хранятся в коллекции.
// Для ОстаткиИОбороты индекс 4 соответствует параметру "Условие" (Отбор)
ИсточникТаблица.Параметры[4].Выражение = Новый ВыражениеСхемыЗапроса("Склад = &Склад");
// Генерируем обновленный текст запроса
ТекстЗапроса = Схема.ПолучитьТекстЗапроса();
КонецЕсли;
Запрос = Новый Запрос(ТекстЗапроса);
Если ЗначениеЗаполнено(Склад) Тогда
Запрос.УстановитьПараметр("Склад", Склад);
КонецЕсли;
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Почему этот подход считается лучшим в современной разработке:
ГДЕ, но и новые поля, таблицы для левого соединения, менять порядок сортировки и группировки.&УсловиеСклад. Он остается чистым и валидным для конструктора запросов.Если ваша задача — построение отчета или динамического списка на управляемой форме, лучшим решением будет полный отказ от ручного написания кода динамических отборов. Передайте эту задачу СКД.
Достаточно указать в фигурных скобках расширение языка запросов компоновки данных:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.ВНаличииКонечныйОстаток КАК ВНаличииКонечныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , {Склад.* КАК Склад}) КАК ТоварыНаСкладахОстаткиИОбороты
В этом случае платформа автоматически выполнит всю "грязную" работу за вас. Если пользователь установит отбор по складу в настройках отчета, СКД сама динамически подставит условие в оптимальное место виртуальной таблицы. Если отбор не выбран — условие будет полностью вырезано из результирующего SQL-запроса, что гарантирует максимальное быстродействие.
Выбор оптимального метода зависит от архитектуры вашего решения:
СхемаЗапроса как наиболее надежный инструмент модификации запросов.СтрЗаменить с заменяемыми маркерами условий, тщательно избегая громоздких логических выражений с оператором ИЛИ в тексте самого запроса.