Оптимизация запросов
Такую статью я рекомендую всем программистам 1С прочитать внимательно, так как язык запросов – этот основной инструмент платформы 1С. В статье приводятся типичные причины неоптимальной работы запросов, диагностируемые на уровне кода конфигурации, и рассматриваются методики оптимизации запросов.
Основные причины неоптимальной работы запросов
1. Cоединения с подзапросами
Не следует использовать соединения с подзапросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с подзапросами, то его следует переписать с использованием временных таблиц.
Пример неоптимального опасного запроса, использующего соединение с подзапросом в правой части соединения используется подзапрос:
ВЫБРАТЬ ... ИЗ Документ.РеализацияТоваровУслуг ЛЕВОЕ СОЕДИНЕНИЕ ( ВЫБРАТЬ ИЗ РегистрСведений.Лимиты ГДЕ ... СГРУППИРОВАТЬ ПО ... ) ПО ...
Для оптимизации запроса следует разбить его на несколько отдельных запросов (по числу подзапросов, используемых в соединениях). Эти запросы рекомендуется поместить в один пакетный запрос.
// Создать менеджер временных таблиц МенеджерВТ = Новый МенеджерВременныхТаблиц; Запрос = Новый Запрос; Запрос.МенеджерВременныхТаблиц = МенеджерВТ; // Текст пакетного запроса Запрос.Текст = " // Заполняем временную таблицу. Запрос к регистру лимитов. | ВЫБРАТЬ ... | ПОМЕСТИТЬ Лимиты | ИЗ РегистрСведений.Лимиты | ГДЕ ... | СГРУППИРОВАТЬ ПО ... | ИНДЕКСИРОВАТЬ ПО ...; // Выполняем основной запрос с использованием временной таблицы ВЫБРАТЬ ... ИЗ Документ.РеализацияТоваровУслуг ЛЕВОЕ СОЕДИНЕНИЕ Лимиты ПО ...;" Внимание! очень важно в данном примере проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.
2. Cоединения с виртуальными таблицами
Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, “РегистрНакопления.Товары.Остатки()“) и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице. То есть, следует использовать ту же рекомендацию, что и в случае соединения с подзапросом (см Пункт 1).
Дело в том, что виртуальные таблицы, используемые в языке запросов 1С:Предприятия, могут разворачиваться в подзапросы при трансляции в язык SQL. Это связано с тем, что виртуальная таблица часто (но не всегда) получает данные из нескольких физических таблиц СУБД. Если вы используете соединение с виртуальной таблицей, то на уровне SQL оно может быть в некоторых случаях реализовано, как соединение с подзапросом. В этом случае оптимизатор СУБД может точно так же выбрать неоптимальный план, как при работе с подзапросом, использованным в языке 1С:Предприятия в явном виде.
3. Несоответствие индексов и условий запроса
Условия используются в следующих секциях запроса:
- ВЫБРАТЬ … ИЗ … ГДЕ <условие>
- СОЕДИНЕНИЕ … ПО <условие>
- ВЫБРАТЬ … ИЗ <ВиртуальнаяТаблица>(, <условие>)
- ИМЕЮЩИЕ <условие>
Для этих всех условий, использованных в запросе, должны быть подходящие подходящие индексы для оптимизации отбора данных по условию. Причем, подходящим является индекс, удовлетворяющий следующим требованиям:
- Требование 1 . Индекс содержит все поля перечисленные в условии;
- Требование 2. Эти поля находятся в самом начале индекса;
- Требование 3. Эти поля идут подряд, то есть между ними не «вклиниваются» поля, не участвующие в условии запроса;
Основные идексы, создаваемые 1С:Предприятием:
- индекс по уникальному идентификатору (ссылке) для всех объектных сущностей (справочники, документы и т.д.);
- индекс по регистратору (ссылке на документ) для таблиц движений регистров, подчиненных регистратору;
- индекс периоду и значениям всех измерений для итоговых таблиц регистров накопления;
- индекс периоду, счету и значениям всех измерений для итоговых таблиц регистров бухгалтерии.
В тех случаях, когда автоматически созданных индексов недостаточно, можно дополнительно проиндексировать реквизиты объекта метаданных в конфигураторе. Однако, следует иметь в виду, что создание индекса ускоряет процесс поиска информации, но может несколько замедлить процесс ее изменения пользователем (добавления, редактирования и удаления) в режиме запуска 1С предприятия. Поэтому индексы следует создавать осознанно и только в том случае, если точно известен запрос, для которого такой индекс необходим. Не следует создавать индексы “на всякий случай” или заведомо избыточные индексы. Например никогда не следует дополнительно индексировать первое измерение регистра, поскольку для поиска по значению первого измерения подходит основной индекс таблицы итогов, который автоматически создаст платформа.
В конфигурации описан регистр накопления ТоварыНаСкладах:
Платформа 1С:Предприятие автоматически создаст для таблицы остатков данного регистра индекс по периоду и всем измерениям в том порядке, в котором они перечислены в конфигураторе.
Рассмотрим несколько примеров запросов и проанализируем, смогут ли они оптимально выполняться при такой структуре данных.
Запрос 1
Запрос.Текст = "ВЫБРАТЬ | ТоварыНаСкладахОстатки.Склад, | ТоварыНаСкладахОстатки.Номенклатура, | ТоварыНаСкладахОстатки.Качество |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки";
В данном случае нарушено требование 2. В условии отсутствует отбор по первому полю индекса (Склад). Такой запрос не сможет выполниться оптимально. Для его выполнения серверу СУБД придется перебирать (сканировать) все записи таблицы. Время выполнения этой операции напрямую зависит от количества записей в таблице остатков регистра и может быть очень большим (и будет увеличиваться с ростом количества данных).
Варианты оптимизации:
- Проиндексировать измерение «Номенклатура»
- Поставить измерение «Номенклатура» первым в списке измерений. Будьте внимательны при использовании этого метода. В конфигурации могут присутствовать другие запросы, которые могут замедлиться в результате этой перестановки.
Запрос 2
Запрос.Текст = "ВЫБРАТЬ | ТоварыНаСкладахОстатки.Склад, | ТоварыНаСкладахОстатки.Номенклатура, | ТоварыНаСкладахОстатки.Качество |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки( | , | Качество = &Качество | И Склад = &Склад) КАК ТоварыНаСкладахОстатки";
В данном случае нарушено требование 3. Между измерениями «Склад» и «Качество» в структуре регистра находится измерение «Номенклатура», которое не задано в условии запроса. Этот запрос так же не сможет выполняться оптимально. При его выполнении СУБД выполнит поиск по первому полю индекса, но затем вынужденно просканирует некоторую его часть. Сканирование приведет к увеличению времени выполнения запроса и к блокировке избыточных записей в таблице, то есть к снижению общей пропускной способности системы.
Варианты оптимизации:
- Добавить в запрос условие по измерению «Номенклатура»
- Убрать из запроса условие по измерению «Качество»
- Перенести «Номенклатуру» из измерений в реквизиты
- Поменять местами измерения «Номенклатура» и «Качество
Запрос 3
Запрос.Текст = "ВЫБРАТЬ | ТоварыНаСкладахОстатки.Склад, | ТоварыНаСкладахОстатки.Номенклатура, | ТоварыНаСкладахОстатки.Качество, | ТоварыНаСкладахОстатки.КоличествоОстаток |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки( | , | Номенклатура = &Номенклатура | И Склад = &Склад) КАК ТоварыНаСкладахОстатки";
В этом случае требования соответствия индекса и запроса не нарушены. Данный запрос будет выполнен СУБД оптимальным способом. Обратите внимание на то, что порядок следования условий в запросе не обязан совпадать с порядком следования полей в индексе. Это не является проблемой и будет нормально обработано СУБД.
4. Использование логического ИЛИ в условиях
4.1 Использование логического ИЛИ в секции ГДЕ запроса
Не следует использовать ИЛИ в секции ГДЕ запроса. Это может привести к тому, что СУБД не сможет использовать индексы таблиц и будет выполнять сканирование, что увеличит время работы запроса и вероянтность возникновения блокировок. Вместо этого следует разбить один запрос на несколько и объединить результаты.
Например, запрос
ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001" ИЛИ Артикул = "002"
следует заменить на запрос
ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001" |ОБЪЕДИНИТЬ ВСЕ |ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "002"
4.2 . Включение пользователей в несколько ролей, каждая из которых имеет RLS
1С RLS (Record Level Security) или ограничение прав на уровне записи
Если в конфигурации описано несколько ролей с условиями RLS, то не следует назначать одному пользователю более одной такой роли. Если один пользователь будет включен, например, в две роли с RLS – бухгалтер и кадровик, то при выполнении всех его запросов к их условиям будут добавляться условия обоих RLS с использованием логического ИЛИ. Таким образом, даже если в исходном запросе нет условия ИЛИ, оно появится там после добавления условий RLS. Такой запрос так же может выполняться неоптимально – медленно и с избыточными блокировками.
Вместо этого следует создать “смешанную” роль – “бухгалтер-кадровик” и прописать ее RLS таким образом, чтобы избежать использования ИЛИ в условии, а пользователя включить в эту одну роль.
4. 3 Использование ИЛИ в условиях соединения
Не рекомендуется использовать логическое ИЛИ в условиях соединения, то есть в секции ПО запроса. Это так же может привести к выбору неоптимального плана и медленной работе запроса. Простого универсального способа переписать такой запрос без использования ИЛИ не существует. Следует проанализировать решаемую задачу и попытаться найти другой алгоритм ее решения.
5.Использование подзапросов в условии соединения
Не следует использовать подзапросы в условии соединения. Это может привести к значительному замедлению запроса и (в отдельных случаях) к его полной неработоспособности на некоторых СУБД. Пример запроса с использованием подзапроса в условии соединения:
Запрос.Текст = "ВЫБРАТЬ | ОстаткиТоваров.Номенклатура КАК Номенклатура, | Цены.Цена КАК ЦенаПрошлогоМесяца |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки(...) КАК ОстаткиТоваров | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И | Цены.Период В ( | ВЫБРАТЬ МАКСИМУМ(ЦеныПрошлогоМесяца.Период) | ИЗ РегистрСведений.Цена КАК ЦеныПрошлогоМесяца | ГДЕ ЦеныПрошлогоМесяца.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ) | И ЦеныПрошлогоМесяца.Номенклатура = ОстаткиТоваров.Номенклатура | ) | ГДЕ ОстаткиТоваров.Склад = &Склад";
В данном случае подзапрос в условии соединения используется для получения как бы “среза последних” на конец предыдущего периода. Причем, для каждой номенклатуры период может быть разным. Подобный запрос рекомендуется переписать с использованием временных таблиц. Например, это можно сделать следующим образом:
Запрос.Текст = " // Максимальные даты установки цен в прошлом периоде для данных номенклатур |ВЫБРАТЬ | ОстаткиТоваров.Номенклатура КАК Номенклатура, | МАКСИМУМ(Цены.Период) КАК Период |ПОМЕСТИТЬ ДатыПоНоменклатурам |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки(...) КАК ОстаткиТоваров | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И | Цены.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ) | СГРУППИРОВАТЬ ПО ОстаткиТоваров.Номенклатура | ГДЕ ОстаткиТоваров.Склад = &Склад; // Выбрать данные по цене за найденный период |ВЫБРАТЬ | ДатыПоНоменклатурам.Номенклатура КАК Номенклатура, | Цены.Цена КАК ЦенаПрошлогоМесяца |ИЗ ДатыПоНоменклатурам | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И | Цены.Период = ДатыПоНоменклатурам.Период ";
6.Получение данных через точку от полей составного типа
Если в запросе используется получение значения через точку от поля составного ссылочного типа, то при выполнении этого запроса будет выполняться соединение со всеми таблицами объектов, входящими в этот составной тип. В результате SQL текст запроса чрезвычайно усложняется, и при его выполнении оптимизатор СУБД может выбрать неоптимальный план. Это может привести к серьезным проблемам производительности и даже к неработоспособности запроса в отдельных случаях.
В частности, не рекомендуется обращаться к реквизитам регистратора регистра (например, “ТоварыНаСкладах.Регистратор.Дата”) и т.п. При этом не важно в какой части запроса вы используете реквизит, полученный через точку от поля составного типа – в списке возвращаемых полей, в условии и т.п. Во всех случаях такое обращение может привести к проблемам производительности.
Общая рекомендация заключается в том, чтобы по возможности ограничить количество соединений в таких запросах. Для этого можно использовать следующие приемы:
- Избегайте избыточности при создании полей составных ссылочных типов. Указывайте ровно столько возможных типов для данного поля, сколько необходимо. Не следует без необходимости использовать типы “любая ссылка” или “ссылка на любой документ” и т.п. Вместо этого следует более тщательно проанализировать прикладную логику и назначить для поля ровно те возможные типы ссылок, которые необходимы для решения задачи.
- При необходимости жертвуйте компактностью хранения данных ради производительности. Если в запросе вам понадобилось значение, полученное через ссылку, то, возможно, это значение можно хранить непосредственно в данном объекте. Например, если при работе с регистром вам требуется информация о дате регистратора, вы можете завести в регистре соответствующий реквизит и назначать ему значение при проведении документов. Это приведет к дублированию информации и некоторому (незначительному) увеличению ее объема, но может существенно повысить производительность и стабильность работы запроса.
- При необходимости жертвуйте компактностью и универсальностью кода ради производительности. Как правило, для выполнения конкретного запроса в данных условиях не нужны все возможные типы данной ссылки. В этом случае, следует ограничить количество возможных типов при помощи функции ВЫРАЗИТЬ. Если данный запрос является универсальным и используется в нескольких разных ситуациях (где типы ссылки могут быть разными), то можно формировать запрос динамически, подставляя в функцию ВЫРАЗИТЬ тот тип, который необходим при данных условиях. Это увеличит объем исходного кода и, возможно, сделает его менее универсальным, но может существенно повысить производительность и стабильность работы запроса.
Пример
В данном запросе используется обращение к реквизитам регистратора. Регистратор является полем составного типа, которое может принимать значения ссылки на один из 56 видов документов.
Запрос.Текст = "ВЫБРАТЬ | Продажи.Регистратор.Номер, | Продажи.Регистратор.Дата, | Продажи.Контрагент, | Продажи.Количество, | Продажи.Стоимость |ИЗ | РегистрНакопления.Продажи КАК Продажи |ГДЕ ...
SQL-текст этого запроса будет включать 56 левых соединений с таблицами документов. Это может привести к серьезным проблемам производительности при выполнении запроса. Однако, для решения данной конкретной задачи нет необходимости соединяться со всеми 56 видами документов. Условия запроса таковы, что при его выполнении будут выбраны только движения документов “РеализацияТоваровУслуг” и “ЗаказыПокупателя”. В этом случае мы можем значительно ускорить работу запроса, ограничив количество соединений при помощи функции ВЫРАЗИТЬ().
Запрос.Текст = "ВЫБРАТЬ | ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Номер | КОГДА Продажи.Регистратор ССЫЛКА Документ.ЗаказПокупателя | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.ЗаказПокупателя).Номер | КОНЕЦ ВЫБОРА КАК Номер, | ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Дата | КОГДА Продажи.Регистратор ССЫЛКА Документ.ЗаказПокупателя | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.ЗаказПокупателя).Дата | КОНЕЦ ВЫБОРА КАК Дата, | Продажи.Контрагент, | Продажи.Количество, | Продажи.Стоимость |ИЗ | РегистрНакопления.Продажи КАК Продажи |ГДЕ | Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг | ИЛИ Продажи.Регистратор ССЫЛКА Документ.ЗаказыПокупателя";
Этот запрос является более громоздким и, возможно, менее универсальным (он не будет правильно работать для других ситуаций – когда возможны другие значения типов регистратора). Однако, при его выполнении будет сформирован SQL запрос, который будет содержать всего два соединения с таблицами документов. Такой запрос будет работать значительно быстрее и стабильнее, чем запрос в его первоначальном виде.
7. Фильтрация виртуальных таблиц без использования параметров
При использовании виртуальных таблиц в запросах, следует передавать в параметры таблиц все условия, относящиеся к данной виртуальной таблице. Не рекомендуется фильтровать виртуальные таблицы при помощи условий в секции ГДЕ и т.п. Такой запрос будет возвращать правильный (с точки зрения функциональности) результат, но СУБД будет намного сложнее выбрать оптимальный план для его выполнения. В некоторых случаях это может привести к ошибкам оптимизатора СУБД и значительному замедлению работы запроса.
Например, следующий запрос использует секцию ГДЕ запроса для выборки из виртуальной таблицы.
Запрос.Текст = "ВЫБРАТЬ | Номенклатура |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки() |ГДЕ | Склад = &Склад";
Возможно, что в результате выполнения этого запроса сначала будут выбраны все записи виртуальной таблицы, а затем из них будет отобрана часть, соответствующая заданному условию. Было бы оптимальным вариантом ограничивать количество выбираемых записей на самом раннем этапе обработки запроса. Для этого следует передать условия в параметры виртуальной таблицы.
Запрос.Текст = "ВЫБРАТЬ | Номенклатура |ИЗ | РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад)";