Оптимизация запросов является критически важной задачей для обеспечения высокой производительности информационной системы 1С, особенно при работе с большими объемами данных и типовыми механизмами, такими как регистры дополнительных свойств. В данной статье мы подробно разберем несколько распространенных проблем и предложим эффективные решения для улучшения скорости выполнения ваших запросов.
Начнем с анализа типичных сценариев использования регистра дополнительных свойств и выявим "узкие места", которые могут замедлять работу.
Часто для получения нескольких дополнительных свойств одного объекта разработчики используют несколько левых соединений с регистром сведений ДопсвойстваОбъектов. Это рабочий подход, но он может быть крайне неэффективным, так как каждое соединение требует отдельного прохода по индексу или таблице.
Рассмотрим пример такого запроса, представленного в исходной теме:
запрос=новый запрос;
запрос.Текст="ВЫБРАТЬ
| РезкаНоменклатурыНаСклад.Номенклатура,
| РезкаНоменклатурыНаСклад.Номенклатура.ВысотаММ КАК Высота,
| РезкаНоменклатурыНаСклад.Номенклатура.ШиринаММ КАК Ширина,
| РезкаНоменклатурыНаСклад.Номенклатура.ДлинаММ КАК длина,
| ЕСТЬNULL(ЖесткостьМарки.Значение, 0) КАК Жесткость,
| ЕСТЬNULL(ПлотностьМарки.Значение, 0) КАК Плотность,
| РезкаНоменклатурыНаСклад.Исполнение
|ИЗ
| РегистрСведений.РезкаНоменклатурыНаСклад КАК РезкаНоменклатурыНаСклад
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК ПлотностьМарки
| ПО РезкаНоменклатурыНаСклад.Марка = ПлотностьМарки.Объект
| И (ПлотностьМарки.ВидДопсвойства = &СвПлотностьМарки)
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК ЖесткостьМарки
| ПО РезкаНоменклатурыНаСклад.Марка = ЖесткостьМарки.Объект
| И (ЖесткостьМарки.ВидДопсвойства = &СвЖесткостьМарки)
|ГДЕ
| РезкаНоменклатурыНаСклад.Использовать";
запрос.УстановитьПараметр("СвПлотностьМарки",ПланыВидовХарактеристик.ВидыДопсвойств.НайтиПоКоду("00038"));
запрос.УстановитьПараметр("СвЖесткостьМарки",ПланыВидовХарактеристик.ВидыДопсвойств.НайтиПоКоду("00010"));
ТЗВрем=запрос.Выполнить().Выгрузить();
Здесь мы видим два отдельных левых соединения с регистром ДопсвойстваОбъектов для получения двух разных свойств (Плотность и Жесткость). Для каждого свойства создается свой "псевдоним" (ПлотностьМарки, ЖесткостьМарки).
Оптимизировать такой запрос можно, используя одно левое соединение с регистром ДопсвойстваОбъектов, а затем "разворачивая" строки в столбцы с помощью функции ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ NULL КОНЕЦ и агрегатных функций (например, МАКСИМУМ или СУММА).
Объединение соединений: Вместо двух или более соединений, делаем одно соединение с регистром дополнительных свойств.
Условный отбор свойств: В секции ПО указываем условие, которое отбирает только те виды дополнительных свойств, которые нам интересны. Это уменьшает количество строк, передаваемых на следующий этап.
Условная агрегация: Используем оператор ВЫБОР внутри агрегатной функции МАКСИМУМ (или другой подходящей), чтобы выбрать нужное значение для каждого свойства. Если свойство отсутствует для объекта, ЕСТЬNULL поможет установить значение по умолчанию.
Перенос поиска по коду в запрос: Вместо использования ПланыВидовХарактеристик.ВидыДопсвойств.НайтиПоКоду() вне запроса, что приводит к дополнительным обращениям к базе данных, следует указывать коды видов свойств непосредственно в тексте запроса. Это сокращает накладные расходы.
Посмотрим на пример оптимизированного запроса:
запрос=новый запрос;
запрос.Текст="ВЫБРАТЬ
| РезкаНоменклатурыНаСклад.Номенклатура,
| РезкаНоменклатурыНаСклад.Номенклатура.ВысотаММ КАК Высота,
| РезкаНоменклатурыНаСклад.Номенклатура.ШиринаММ КАК Ширина,
| РезкаНоменклатурыНаСклад.Номенклатура.ДлинаММ КАК Длина,
| ЕСТЬNULL(МАКСИМУМ(ВЫБОР
| КОГДА Допсвойства.ВидДопсвойства.Код = ""00010""
| ТОГДА Допсвойства.Значение
| ИНАЧЕ NULL
| КОНЕЦ), 0) КАК Жесткость,
| ЕСТЬNULL(МАКСИМУМ(ВЫБОР
| КОГДА Допсвойства.ВидДопсвойства.Код = ""00038""
| ТОГДА Допсвойства.Значение
| ИНАЧЕ NULL
| КОНЕЦ), 0) КАК Плотность,
| РезкаНоменклатурыНаСклад.Исполнение
|ИЗ
| РегистрСведений.РезкаНоменклатурыНаСклад КАК РезкаНоменклатурыНаСклад
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК Допсвойства
| ПО РезкаНоменклатурыНаСклад.Марка = Допсвойства.Объект
| И (Допсвойства.ВидДопсвойства.Код В (""00010"", ""00038""))
|ГДЕ
| РезкаНоменклатурыНаСклад.Использовать
|СГРУППИРОВАТЬ ПО
| РезкаНоменклатурыНаСклад.Номенклатура,
| РезкаНоменклатурыНаСклад.Исполнение,
| РезкаНоменклатурыНаСклад.Номенклатура.ВысотаММ,
| РезкаНоменклатурыНаСклад.Номенклатура.ШиринаММ,
| РезкаНоменклатурыНаСклад.Номенклатура.ДлинаММ"; // Добавляем поля номенклатуры в GROUP BY
// Параметры для кодов свойств больше не нужны, они прописаны прямо в запросе.
// Если "00010" и "00038" могут меняться, можно использовать параметры:
// запрос.УстановитьПараметр("КодЖесткость", "00010");
// запрос.УстановитьПараметр("КодПлотность", "00038");
ТЗВрем=запрос.Выполнить().Выгрузить();
Обратите внимание, что поля ВысотаММ, ШиринаММ, ДлинаММ теперь явно включены в секцию СГРУППИРОВАТЬ ПО, поскольку они не являются агрегатными функциями и являются частью полей выборки, когда агрегируется Значение.
Одна из наиболее частых и дорогостоящих ошибок в программировании на 1С – это выполнение запросов внутри цикла для каждого элемента коллекции. Каждый вызов запроса к СУБД сопряжен с накладными расходами на установление соединения, парсинг запроса, выполнение и передачу данных. Многократное повторение этих операций приводит к существенному замедлению работы.
Пример такого подхода из исходной темы:
Функция ЗапросПоДопСвойствам(ОбъектЗапроса, КодДопСвойства)
тРез = глВыполнитьЗапрос("
|ВЫБРАТЬ
| Значение
|ИЗ
| РегистрСведений.ДопсвойстваОбъектов
|ГДЕ
| Объект = &ТекОбъект и
| ВидДопсвойства.Код = &ТекКод",
Новый Структура("ТекОбъект,ТекКод",
ОбъектЗапроса, КодДопСвойства));
Если тРез.Количество()>0 тогда
Возврат тРез[0].Значение;
Иначе
Возврат Неопределено;
КонецЕсли;
КонецФункции
Если функция ЗапросПоДопСвойствам вызывается в цикле для каждой номенклатуры (ОбъектЗапроса), это приведет к выполнению отдельного запроса к базе данных для каждой номенклатуры. Для 1000 номенклатур будет выполнено 1000 запросов, что крайне неэффективно.
Вместо выполнения множества мелких запросов, всегда стремитесь получить все необходимые данные одним большим запросом, передавая в него список или массив объектов. Результат такого запроса можно поместить во временную таблицу или выгрузить в ТаблицуЗначений и затем обрабатывать уже в памяти 1С.
Сбор параметров: Соберите все уникальные объекты (например, Марки или Номенклатуры), для которых нужны дополнительные свойства, в один список значений или массив.
Единый запрос: Выполните один запрос к регистру ДопсвойстваОбъектов, используя условие Объект В (&СписокОбъектов), чтобы получить все нужные свойства для всех объектов сразу. Выбирайте также вид свойства, если нужно получить несколько разных свойств.
Обработка результатов: Выгрузите результат запроса в ТаблицуЗначений или поместите во временную таблицу. Затем используйте ее для дальнейшей обработки в 1С (например, для заполнения структуры или нового поля в существующей таблице, используя метод НайтиСтроки() или обходя результат запроса).
Пример оптимизированного подхода:
// Предположим, у нас есть список объектов "Марка", для которых нужно получить свойства
СписокМарок = Новый СписокЗначений();
СписокМарок.Добавить(Справочники.Марки.НайтиПоКоду("001"));
СписокМарок.Добавить(Справочники.Марки.НайтиПоКоду("002"));
// ... заполняем список марок из вашей основной выборки
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| ДопсвойстваОбъектов.Объект КАК Марка,
| ДопсвойстваОбъектов.ВидДопсвойства.Код КАК КодСвойства,
| ДопсвойстваОбъектов.Значение
|ИЗ
| РегистрСведений.ДопсвойстваОбъектов КАК ДопсвойстваОбъектов
|ГДЕ
| ДопсвойстваОбъектов.Объект В (&СписокМарок)
| И ДопсвойстваОбъектов.ВидДопсвойства.Код В (""00010"", ""00038"")"; // Только нужные коды свойств
Запрос.УстановитьПараметр("СписокМарок", СписокМарок);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
// Теперь можно обработать РезультатЗапроса
// Например, если у вас уже есть таблица с марками, можно добавить в нее колонки
// и заполнить их из РезультатЗапроса
Для Каждого СтрокаМарки Из ВашаОсновнаяТаблицаМарок Цикл
НайденныеСвойства = РезультатЗапроса.НайтиСтроки(Новый Структура("Марка", СтрокаМарки.Марка));
Для Каждого СтрокаСвойства Из НайденныеСвойства Цикл
Если СтрокаСвойства.КодСвойства = "00010" Тогда
СтрокаМарки.Жесткость = СтрокаСвойства.Значение;
ИначеЕсли СтрокаСвойства.КодСвойства = "00038" Тогда
СтрокаМарки.Плотность = СтрокаСвойства.Значение;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Такой подход позволяет значительно сократить количество обращений к СУБД, повышая общую производительность.
Вложенные запросы могут быть мощным инструментом, но их неправильное использование часто приводит к катастрофическому падению производительности. Особенно это актуально, когда во вложенном запросе выбирается большой объем данных, который затем сильно фильтруется во внешнем запросе.
Рассмотрим пример из исходной темы, демонстрирующий такой анти-паттерн:
тзПлотность=глВыполнитьЗапрос("ВЫБРАТЬ
| ВложенныйЗапрос.Ссылка,
| ДопсвойстваОбъектов.Значение КАК Плотность
|ИЗ
| (ВЫБРАТЬ
| ДопсвойстваОбъектов.Значение КАК Значение,
| Номенклатура.Ссылка КАК Ссылка
| ИЗ
| Справочник.Номенклатура КАК Номенклатура
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК ДопсвойстваОбъектов
| ПО Номенклатура.Ссылка = ДопсвойстваОбъектов.Объект
| ГДЕ
| ДопсвойстваОбъектов.ВидДопсвойства = &ВидДопсвойства_МаркаПены) КАК ВложенныйЗапрос
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК ДопсвойстваОбъектов
| ПО ВложенныйЗапрос.Значение = ДопсвойстваОбъектов.Объект
|ГДЕ
| ДопсвойстваОбъектов.ВидДопсвойства = &ВидДопсвойства_Плотность
| И ВложенныйЗапрос.Ссылка = &Ссылка",
Новый Структура("ВидДопсвойства_МаркаПены,ВидДопсвойства_Плотность,Ссылка",
ПланыВидовХарактеристик.ВидыДопсвойств.НайтиПоКоду("00009"),ПланыВидовХарактеристик.ВидыДопсвойств.НайтиПоКоду("00038"),Номенклатура));
Этот запрос является крайне неэффективным. Вложенный запрос сначала соединяет весь справочник Номенклатура с регистром ДопсвойстваОбъектов по одному виду свойства, а затем внешний запрос повторно соединяется с регистром ДопсвойстваОбъектов по другому виду свойства и фильтрует результат по одной конкретной ссылке номенклатуры. Это приводит к выбору огромного избыточного объема данных, которые затем отбрасываются.
Для таких случаев необходимо переписывать запрос, используя прямые соединения и максимально сужая выборку на ранних этапах выполнения запроса.
Исключение избыточных вложенных запросов: Там, где это возможно, переписываем логику так, чтобы избежать вложенных запросов, которые выбирают много данных, но потом сильно фильтруются.
Передача параметров: Всегда передавайте параметры отбора как можно раньше и "глубже" в запрос (например, в параметрах виртуальных таблиц или в секции ГДЕ для основных таблиц).
Оптимизированный запрос может выглядеть так (предполагаем, что МаркаПены хранится как допсвойство номенклатуры, а Плотность как допсвойство самой МаркиПены):
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
ПлотностьМарки.Значение КАК Плотность
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК МаркаПеныНоменклатуры
ПО Номенклатура.Ссылка = МаркаПеныНоменклатуры.Объект
И МаркаПеныНоменклатуры.ВидДопсвойства.Код = ""00009"" // Допсвойство, хранящее МаркуПены для Номенклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК ПлотностьМарки
ПО МаркаПеныНоменклатуры.Значение = ПлотностьМарки.Объект // МаркаПены является объектом для плотности
И ПлотностьМарки.ВидДопсвойства.Код = ""00038"" // Допсвойство "Плотность" для МаркиПены
ГДЕ
Номенклатура.Ссылка = &Ссылка
В этом запросе мы явно соединяемся с таблицей Номенклатура, затем находим ее дополнительное свойство "МаркаПены", а уже от этой "МаркиПены" ищем её дополнительное свойство "Плотность". Все фильтруется по конкретной номенклатуре с самого начала, что значительно эффективнее.
В 1С, когда поле имеет составной тип (например, может быть ссылкой на Справочник.Номенклатура, Справочник.Материалы и т.д.), и вы обращаетесь к его реквизитам через точку (например, МоеПолеСоставногоТипа.Наименование), платформа автоматически выполняет неявное левое соединение со всеми таблицами, типы которых входят в состав этого поля. Это может быть очень накладно, если типов много и таблицы большие.
Например, в запросе: РезкаНоменклатурыНаСклад.Марка = ПлотностьМарки.Объект, если РезкаНоменклатурыНаСклад.Марка является полем составного типа, то СУБД должна будет сначала определить, к какому типу относится значение в поле Марка, а затем выполнить соответствующее соединение. Без явного указания типа, это может привести к лишним проверкам.
Оператор ВЫРАЗИТЬ позволяет явно указать платформе, к какому типу данных вы хотите привести поле составного типа. Это исключает неявные соединения со всеми возможными типами и позволяет СУБД построить более эффективный план выполнения запроса.
Использование ВЫРАЗИТЬ имеет смысл в двух случаях:
Когда вы обращаетесь к реквизитам поля составного типа через точку. Например, если РезкаНоменклатурыНаСклад.Марка имеет составной тип, а вы пишете РезкаНоменклатурыНаСклад.Марка.Наименование. В этом случае, ВЫРАЗИТЬ(РезкаНоменклатурыНаСклад.Марка КАК Справочник.МаркиНоменклатуры).Наименование будет намного эффективнее.
Когда поле составного типа используется в условии соединения (ПО) или отбора (ГДЕ), и вы уверены, что оно всегда будет ссылкой на определенный тип. Например, если РезкаНоменклатурыНаСклад.Марка всегда будет ссылкой на Справочник.МаркиНоменклатуры, то можно написать: ВЫРАЗИТЬ(РезкаНоменклатурыНаСклад.Марка КАК Справочник.МаркиНоменклатуры) = ПлотностьМарки.Объект. Это подскажет СУБД, какую именно таблицу использовать для соединения.
Важно: Если вы не обращаетесь к реквизитам поля составного типа через точку и не приводите его к конкретному типу в условии соединения, а просто используете его как целое значение (например, РезкаНоменклатурыНаСклад.Марка), то использование ВЫРАЗИТЬ не даст прироста производительности, так как лишних соединений и так не создавалось.
Пример применения оператора ВЫРАЗИТЬ:
// Предположим, что поле РезкаНоменклатурыНаСклад.Марка может быть разных типов,
// но в данном случае мы знаем, что это всегда Справочник.МаркиНоменклатуры.
// Изначально было: ПО РезкаНоменклатурыНаСклад.Марка = ПлотностьМарки.Объект
// Можно переписать на:
ПО ВЫРАЗИТЬ(РезкаНоменклатурыНаСклад.Марка КАК Справочник.МаркиНоменклатуры) = ПлотностьМарки.Объект
Виртуальные таблицы (например, Остатки, Обороты регистров накопления и сведений) в 1С предоставляют агрегированные или временные данные. Они имеют свои особенности использования, и крайне важно правильно передавать в них параметры отбора.
В исходной теме был запрос, где условие ДатаЗапуска < НАЧАЛОПЕРИОДА(&ДатаОст, ДЕНЬ) было закомментировано, а Номенклатура В ИЕРАРХИИ (&Номенклатура) передавалось в параметры виртуальной таблицы. Это правильный подход.
// До
РегистрНакопления.ПроизводственныеЗадания.Остатки(
&ДатаОст,
// | ДатаЗапуска < НАЧАЛОПЕРИОДА(&ДатаОст, ДЕНЬ) и
// | Номенклатура В ИЕРАРХИИ (&Номенклатура)
) КАК ПроизводственныеЗаданияОстатки
// После (в сообщении 9)
РегистрНакопления.ПроизводственныеЗадания.Остатки(&ДатаОст, Номенклатура В ИЕРАРХИИ (&Номенклатура)) КАК ПроизводственныеЗаданияОстатки
Все условия отбора, которые относятся к измерениям или ресурсам виртуальной таблицы, должны быть переданы как параметры этой виртуальной таблицы. Это позволяет СУБД сформировать оптимальный набор данных еще до его агрегации, значительно сокращая объем обрабатываемой информации.
Например, если вы хотите получить остатки по конкретной номенклатуре и на определенную дату, параметры должны быть переданы напрямую в функцию виртуальной таблицы, а не в секцию ГДЕ внешнего запроса.
// Правильно
РегистрНакопления.МойРегистр.Остатки(
&МоментВремени,
Номенклатура = &МояНоменклатура И Склад = &МойСклад
) КАК МойРегистрОстатки
// Неправильно (условие на Склад будет применяться к уже построенной таблице остатков по всем складам)
РегистрНакопления.МойРегистр.Остатки(
&МоментВремени,
Номенклатура = &МояНоменклатура
) КАК МойРегистрОстатки
ГДЕ
МойРегистрОстатки.Склад = &МойСклад
Временные таблицы (ПОМЕСТИТЬ) являются мощным инструментом для разбиения сложных запросов на более простые шаги. Они особенно полезны, когда:
Исходная таблица очень большая, но для дальнейшей обработки нужна только ее небольшая отфильтрованная часть.
Результат одного запроса используется несколько раз в других частях большого запроса.
Необходимо агрегировать данные на промежуточном этапе, прежде чем соединять их с другими таблицами.
В сообщении 5 темы было предложено использовать временную таблицу для предварительного отбора данных из регистра РезкаНоменклатурыНаСклад:
ВЫБРАТЬ
РезкаНоменклатурыНаСклад.Номенклатура,
РезкаНоменклатурыНаСклад.Марка,
РезкаНоменклатурыНаСклад.Исполнение
ПОМЕСТИТЬ втРезкаНоменклатуры
ИЗ
РегистрСведений.РезкаНоменклатурыНаСклад КАК РезкаНоменклатурыНаСклад
ГДЕ
РезкаНоменклатурыНаСклад.Использовать
ИНДЕКСИРОВАТЬ ПО
Марка;
// Далее основной запрос будет использовать втРезкаНоменклатуры
ВЫБРАТЬ
втРезкаНоменклатуры.Номенклатура,
// ... другие поля
ИЗ
втРезкаНоменклатуры КАК втРезкаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДопсвойстваОбъектов КАК Допсвойства
ПО втРезкаНоменклатуры.Марка = Допсвойства.Объект
// ...
Важный момент: всегда используйте ИНДЕКСИРОВАТЬ ПО для временных таблиц по тем полям, которые будут использоваться в условиях соединений или отбора. Это обеспечит высокую скорость доступа к данным во временной таблице.
Если определенные дополнительные свойства (например, "Плотность" или "Жесткость" для марки) являются критически важными для производительности и постоянно запрашиваются, рассмотрите возможность переноса этих свойств из регистра ДопсвойстваОбъектов непосредственно в реквизиты соответствующего справочника (например, в справочник "Марки").
Это может привести к небольшому дублированию данных или необходимости поддерживать синхронизацию, но кардинально ускорит получение этих свойств, так как они будут доступны прямым обращением к реквизиту объекта, без необходимости соединений с регистром сведений. Это особенно актуально, если регистр доп. свойств очень большой.
Оптимизация запросов в 1С – это многогранный процесс, требующий глубокого понимания принципов работы СУБД и особенностей платформы 1С:Предприятие. Мы рассмотрели наиболее распространенные проблемы и эффективные методы их решения: от условной агрегации и избегания "запросов в цикле" до правильного использования оператора ВЫРАЗИТЬ и архитектурных изменений. Применяя эти рекомендации, вы сможете значительно повысить производительность и отказоустойчивость ваших информационных систем.