Оптимизация запросов

By Published Система стандартов и методик разработки

Печать (Ctrl+P)

По материалам из диска ИТС

Несоответствие индексов и условий запроса

1.1 Необходимо убедиться в том, что для всех условий, использованных в запросе, имеются подходящие индексы.   

Условия используются в следующих секциях запроса:

  • ВЫБРАТЬ … ИЗ … ГДЕ <условие>
  • СОЕДИНЕНИЕ … ПО <условие>
  • ВЫБРАТЬ … ИЗ <ВиртуальнаяТаблица>(, <условие>)
  • ИМЕЮЩИЕ <условие>

Для каждого условия должен существовать подходящий индекс. Подходящим является индекс, удовлетворяющий следующим требованиям: 

  1. Индекс содержит все поля перечисленные в условии;
  2. Эти поля находятся в самом начале индекса;
  3. Эти поля идут подряд, то есть между ними не «вклиниваются» поля, не участвующие в условии запроса

1.2 Если в структуре базы данных отсутствует индекс, удовлетворяющий всем перечисленным условиям, то для получения результата СУБД будет вынуждена сканировать таблицу или один из ее индексов. Это приведет к увеличению времени выполнения запроса, а также к возможному снижению параллельности системы, поскольку возрастет количество установленных блокировок.  

Требования к индексу связаны с физической структурой индекса в СУБД. Эта структура представляет собой дерево значений проиндексированных полей. На первом уровне дерева находятся значения первого поля индекса, на втором – второго и так далее. Такая структура позволяет достичь высокой эффективности при поиске по индексу. Кроме того, она гарантирует отсутствие деградации производительности индекса с ростом количества данных.  

Однако, индекс такой структуры, очевидно, может быть использован только строго определенным образом. Сначала необходимо провести поиск по значению первого поля индекса, затем – второго и так далее. Если, например, условие по первому полю индекса не указано, то индекс уже не сможет обеспечить быстрый поиск. Если указано условие по нескольким первым полям индекса, а затем одно или несколько полей индекса не задано, то индекс может быть использован только частично.

2. При создании объекта метаданных 1С:Предприятие автоматически создает индексы, которые должны подходить для работы большинства запросов.

Основные индексы, создаваемые 1С:Предприятием:

  • индекс по уникальному идентификатору (ссылке) для всех объектных сущностей (справочники, документы и т.д.);
  • индекс по регистратору (ссылке на документ) для таблиц движений регистров, подчиненных регистратору;
  • индекс по периоду и значениям всех измерений для итоговых таблиц регистров накопления;
  • индекс по периоду, счету и значениям всех измерений для итоговых таблиц регистров бухгалтерии.

3. В тех случаях, когда автоматически созданных индексов недостаточно, можно дополнительно проиндексировать реквизиты объекта метаданных.

При этом реквизиты справочников и документов рекомендуется индексировать с дополнительным упорядочиванием. Такой индекс будет учитывать упорядочивание по основному представлению объекта, тем самым он будет эффективно использоваться, например, когда в списке установлен отбор по данному реквизиту, а сам список упорядочен по полям основного представления объекта.

Следует иметь в виду, что создание индекса ускоряет процесс поиска информации, но может несколько замедлить процесс ее изменения (добавления, редактирования и удаления). Поэтому индексы следует создавать осознанно и только в том случае, если точно известен запрос, для которого такой индекс необходим. Не следует создавать индексы “на всякий случай” или заведомо избыточные индексы. В частности:

  • не следует дополнительно индексировать первое измерение регистра, поскольку для поиска по значению первого измерения подходит основной индекс таблицы итогов, который автоматически создаст платформа;
  • не следует создавать индексы по низкоселектевным полям. Например, индексировать реквизит типа Булево имеет смысл, только если незначительная часть записей всегда будет иметь одно значение, и в запросах всегда выбираются записи по этому значению.

Примеры

В конфигурации описан регистр накопления ТоварыНаСкладах: 

Платформа 1С:Предприятие автоматически создаст для таблицы остатков данного регистра индекс по периоду и всем измерениям в том порядке, в котором они перечислены в конфигураторе. 

Рассмотрим несколько примеров запросов и проанализируем, смогут ли они оптимально выполняться при такой структуре данных.

Запрос 1

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Качество
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки";  

В данном случае нарушено требование 2 раздела 1.1. В условии отсутствует отбор по первому полю индекса (Склад). Такой запрос не сможет выполниться оптимально. Для его выполнения серверу СУБД придется перебирать все записи таблицы. Время выполнения этой операции напрямую зависит от количества записей в таблице остатков регистра и может быть очень большим (и будет увеличиваться с ростом количества данных). 

Варианты оптимизации:

  • Проиндексировать измерение Номенклатура
  • Поставить измерение Номенклатура первым в списке измерений. Следует осторожно использовать этот метод. В конфигурации могут присутствовать другие запросы, которые могут замедлиться в результате этой перестановки.

Запрос 2

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Качество
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| Качество = &Качество
| И Склад = &Склад) КАК ТоварыНаСкладахОстатки";  

В данном случае нарушено требование 3 раздела 1.1. Между измерениями Склад и Качество в структуре регистра находится измерение Номенклатура, которое не задано в условии запроса. Этот запрос так же не сможет выполняться оптимально. При его выполнении СУБД выполнит поиск по первому полю индекса, но затем вынужденно просканирует некоторую его часть. Сканирование может привести к существенному увеличению времени выполнения запроса.  

Варианты оптимизации:

  • Добавить в запрос условие по измерению Номенклатура
  • Убрать из запроса условие по измерению Качество
  • Поменять местами измерения Номенклатура и Качество. Данную рекомендацию следует использовать, если временя выполнения запроса критическим образом сказывается на работы системы (например, запрос выполняется достаточно часто, или он выполняется не часто, но время его выполнения неприемлимо большое), т.к. это может замедлить выполнение других запросов, присутствующих в конфигурации.

Запрос 3

Запрос.Текст = "ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Склад,
| ТоварыНаСкладахОстатки.Номенклатура,
| ТоварыНаСкладахОстатки.Качество,
| ТоварыНаСкладахОстатки.КоличествоОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(
| ,
| Номенклатура = &Номенклатура
| И Склад = &Склад) КАК ТоварыНаСкладахОстатки";

В этом случае требования соответствия индекса и запроса не нарушены. Данный запрос будет выполнен СУБД оптимальным способом. Следует обратить внимание на то, что порядок следования условий в запросе не обязан совпадать с порядком следования полей в индексе. Это не является проблемой и будет нормально обработано СУБД

Разыменование ссылочных полей составного типа в языке запросов

1.1 В языке запросов возможно обращаться не только к полям исходных таблиц запроса, перечисленных в предложении ИЗ, но и к полям таблицы, на которую ссылается поле исходной таблицы запроса, если это поле имеет ссылочный тип. Имена полей при этом пишутся “через точку”. Применение такой конструкции приводит к неявному соединению с дополнительными таблицами для получения значений полей “через точку”.

Например, в запросе

ВЫБРАТЬ 
ТоварныеЗапасы.Товар КАК Товар, 
ТоварныеЗапасы.Количество КАК Количество, 
ТоварныеЗапасы.Товар.Артикул КАК Артикул 
ИЗ 
РегистрНакопления.ТоварныеЗапасы КАК ТоварныеЗапасы 
...

кроме явно указанной в предложении ИЗ таблицы РегистрНакопления.ТоварныеЗапасы неявно участвует таблица Справочник.Товары для получения значения поля Артикул. А в случае использования ограничений доступа на уровне записей (RLS), к запросу добавляются ещё и таблицы, участвующие в RLS к таблице Справочник.Товары.

1.2 Большое число исходных таблиц запроса приводит к его усложнению и может значительно увеличивать время его выполнения. Особенно это важно помнить в тех случаях, когда поле таблицы ссылочного типа имеет составной тип и может содержать ссылки на несколько таблиц. В таком случае, получение полей других таблиц “через точку” от такого поля составного типа приведет к соединению со всеми таблицами, ссылки на которые могут оказаться в данном поле и в RLS к этим таблицам.

Например, в приведенном ниже запросе получение даты регистратора приведет к неявному соединению с таблицами всех документов – регистраторов регистра ТоварныеЗапасы

ВЫБРАТЬ 
... 
ТоварныеЗапасы.Регистратор.Дата, 
... 
ИЗ 
РегистрНакопления.ТоварныеЗапасы КАК ТоварныеЗапасы 
...

Подобное получение данных “через точку” от ссылочных полей составного типа крайне нежелательно. Каждое исключение из этого правила должно тщательно анализироваться.

2.1 Следует избегать избыточности при создании полей составных ссылочных типов. Необходимо указывать ровно столько возможных типов для данного поля, сколько необходимо. Не следует без необходимости использовать типы “любая ссылка” или “ссылка на любой документ” и т.п.

Вместо этого следует более тщательно проанализировать прикладную логику и назначить для поля ровно те возможные типы ссылок, которые необходимы для решения задачи.

2.2 Для того чтобы избежать запросов с использованием большого числа исходных таблиц следует жертвовать компактностью хранения данных ради производительности и помещать соответствующие данные в исходную таблицу запроса.

Например, в регистре ТоварныеЗапасы можно завести реквизит ДатаРегистратора, заполнять его при проведении документов и использовать затем в запросах:

ВЫБРАТЬ 
... 
ТоварныеЗапасы.ДатаРегистратора, 
... 
ИЗ 
РегистрНакопления.ТоварныеЗапасы КАК ТоварныеЗапасы 
...

Это приведет к дублированию информации и некоторому (незначительному) увеличению ее объема, но может существенно повысить производительность и стабильность работы запроса.

2.3 При необходимости следует жертвовать компактностью и универсальностью кода ради производительности:

  • Как правило, для выполнения конкретного запроса в данных условиях не нужны все возможные типы данной ссылки. В этом случае, следует ограничить количество возможных типов при помощи функции ВЫРАЗИТЬ.
  • Если данный запрос является универсальным и используется в нескольких разных ситуациях (где типы ссылки могут быть разными), то можно формировать запрос динамически, подставляя в функцию ВЫРАЗИТЬ тот тип, который необходим при данных условиях. 

Это увеличит объем исходного кода и, возможно, сделает его менее универсальным, но может существенно повысить производительность и стабильность работы запроса.

Например, неправильно:

Запрос.Текст = "ВЫБРАТЬ
| Продажи.Регистратор.Номер,
| Продажи.Регистратор.Дата,
| Продажи.Контрагент,
| Продажи.Количество,
| Продажи.Стоимость
|ИЗ
| РегистрНакопления.Продажи КАК Продажи
|ГДЕ ... 

В данном запросе используется обращение к реквизитам регистратора. Регистратор является полем составного типа, которое может принимать значения ссылки на один из 56 видов документов.   
SQL-текст этого запроса будет включать 56 левых соединений с таблицами документов. Это может привести к серьезным проблемам производительности при выполнении запроса. 

Правильно:

Для решения данной конкретной задачи нет необходимости соединяться со всеми 56 видами документов. Условия запроса таковы, что при его выполнении будут выбраны только движения документов РеализацияТоваровУслуг и ЗаказыПокупателя. В этом случае можно значительно ускорить работу запроса, ограничив количество соединений при помощи функции ВЫРАЗИТЬ().  

Запрос.Текст = "ВЫБРАТЬ
| ВЫБОР
| КОГДА Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
| ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Номер
| КОГДА Продажи.Регистратор ССЫЛКА Документ.ЗаказПокупателя
| ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.ЗаказПокупателя).Номер
| КОНЕЦ ВЫБОРА КАК Номер,
| ВЫБОР
| КОГДА Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
| ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Дата
| КОГДА Продажи.Регистратор ССЫЛКА Документ.ЗаказПокупателя
| ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.ЗаказПокупателя).Дата
| КОНЕЦ ВЫБОРА КАК Дата,
| Продажи.Контрагент,
| Продажи.Количество,
| Продажи.Стоимость
|ИЗ
| РегистрНакопления.Продажи КАК Продажи
|ГДЕ
| Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
| ИЛИ Продажи.Регистратор ССЫЛКА Документ.ЗаказыПокупателя"; 


Этот запрос является более громоздким и, возможно, менее универсальным (он не будет правильно работать для других ситуаций – когда возможны другие значения типов регистратора). Однако, при его выполнении будет сформирован SQL запрос, который будет содержать всего два соединения с таблицами документов. Такой запрос будет работать значительно быстрее и стабильнее, чем запрос в его первоначальном виде.

Ограничения на соединения с вложенными запросами и виртуальными таблицами

1.1 При написании запросов не следует использовать соединения с вложенными запросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с вложенными запросами, то его следует переписать с использованием временных таблиц (не важно с какой стороны соединения находится вложенный запрос), кроме случая, когда вложенный запрос сканирует мало записей. 

Если запрос содержит соединения с вложенными запросами, то это может привести к следующим негативным последствиям: 

  • Крайне медленное выполнение запроса при слабой загрузке серверного оборудования. Замедление запроса может быть очень значительным (до нескольких порядков);
  • Нестабильная работа запроса. При некоторых условиях запрос может работать достаточно быстро, при других – очень медленно;
  • Значительная разница по времени выполнения запроса на разных СУБД;
  • Повышенная чувствительность запроса к актуальности и полноте статистик. Сразу после полного обновления статистик запрос может работать быстро, но через некоторое время опять замедлиться.

Пример потенциально опасного запроса, использующего соединение с вложенным запросом:

ВЫБРАТЬ ...
ИЗ Документ.РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ (
   ВЫБРАТЬ ИЗ РегистрСведений.Лимиты
   ГДЕ ...
   СГРУППИРОВАТЬ ПО ...
) ПО ...

Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединяемых выборок представляет собой вложенный запрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.

1.2 Для вышеприведенного примера получится следующий пакетный запрос:

// Создать менеджер временных таблиц
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
// Текст пакетного запроса
Запрос.Текст = "
  // Заполняем временную таблицу. Запрос к регистру лимитов.
   | ВЫБРАТЬ ...
   | ПОМЕСТИТЬ Лимиты
   | ИЗ РегистрСведений.Лимиты
   | ГДЕ ...
   | СГРУППИРОВАТЬ ПО ...
   | ИНДЕКСИРОВАТЬ ПО ...;
  
  // Выполняем основной запрос с использованием временной таблицы
   ВЫБРАТЬ ...
   ИЗ Документ.РеализацияТоваровУслуг
   ЛЕВОЕ СОЕДИНЕНИЕ Лимиты
   ПО ...;"

Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки. 

2. Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, РегистрНакопления.Товары.Остатки()) и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице (см. пункт 1.1).

3. Следует избегать неявных подзапросов, которые получаются при использовании вложенных соединений:

ВЫБРАТЬ ...
ИЗ Справочник.Номенклатура 
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
        ПО ...
    ПО ...

Проблема в том, что, по сути, этот запрос аналогичен следующему:

ВЫБРАТЬ ...
ИЗ Справочник.Номенклатура 
    ЛЕВОЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ ...
        ИЗ РегистрНакопления.ТоварыНаСкладах
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
            ПО ...)
    ПО ...

Вместо вложенных соединений, как показано выше, следует использовать последовательные соединения:

ВЫБРАТЬ ...
ИЗ Справочник.Номенклатура 
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
    ПО ...
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
    ПО ...

При этом следует понимать, что вложенные и последовательные соединения – это разные запросы, которые могут дать разный результат.

Если вложенное соединение использовано из предположения, что оно аналогично последовательному соединению, то следует просто переписать его на последовательное соединение.

Если вложенное соединение делается осмысленно, то от него следует отказаться, т.к. оно может существенно снизить производительность, как и соединение с подзапросом. Как и в случае с подзапросом, такое соединение можно заменить на соединение с временной таблицей, но лучше вначале подумать, как заменить его на последовательное соединение, т.к. оно будет работать эффективнее временной таблицы.

Ограничения на использование вложенных запросов в условии соединения

Не следует использовать вложенные запросы в условии соединения. Это может привести к значительному замедлению запроса и (в отдельных случаях) к его полной неработоспособности на некоторых СУБД. Пример запроса с использованием вложенного запроса в условии соединения:   

Запрос.Текст = "ВЫБРАТЬ
   | ОстаткиТоваров.Номенклатура КАК Номенклатура,
   | Цены.Цена КАК ЦенаПрошлогоМесяца
   |ИЗ
   | РегистрНакопления.ТоварыНаСкладах.Остатки(...) КАК ОстаткиТоваров
   | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
   | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
   | Цены.Период В (
   | ВЫБРАТЬ МАКСИМУМ(ЦеныПрошлогоМесяца.Период)
   | ИЗ РегистрСведений.Цена КАК ЦеныПрошлогоМесяца
   | ГДЕ ЦеныПрошлогоМесяца.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ)
   | И ЦеныПрошлогоМесяца.Номенклатура = ОстаткиТоваров.Номенклатура
   | )
   | ГДЕ ОстаткиТоваров.Склад = &Склад"; 

В данном случае вложенный запрос в условии соединения используется для получения как бы “среза последних” на конец предыдущего периода. Причем, для каждой номенклатуры период может быть разным.
Подобный запрос рекомендуется переписать с использованием временных таблиц. Например, это можно сделать следующим образом: 

Запрос.Текст = "
  // Максимальные даты установки цен в прошлом периоде для данных номенклатур
   |ВЫБРАТЬ
   | ОстаткиТоваров.Номенклатура КАК Номенклатура,
   | МАКСИМУМ(Цены.Период) КАК Период
   |ПОМЕСТИТЬ ДатыПоНоменклатурам
   |ИЗ
   | РегистрНакопления.ТоварыНаСкладах.Остатки(...) КАК ОстаткиТоваров
   | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
   | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
   | Цены.Период < НАЧАЛОПЕРИОДА(ОстаткиТоваров.Период, МЕСЯЦ)
   | СГРУППИРОВАТЬ ПО ОстаткиТоваров.Номенклатура
   | ГДЕ ОстаткиТоваров.Склад = &Склад;
  
  // Выбрать данные по цене за найденный период
   |ВЫБРАТЬ
   | ДатыПоНоменклатурам.Номенклатура КАК Номенклатура,
   | Цены.Цена КАК ЦенаПрошлогоМесяца
   |ИЗ ДатыПоНоменклатурам
   | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цена КАК Цены
   | ПО Цены.Номенклатура = ОстаткиТоваров.Номенклатура И
   | Цены.Период = ДатыПоНоменклатурам.Период";

Обращения к виртуальным таблицам

1. При использовании виртуальных таблиц в запросах, следует передавать в параметры таблиц все условия, относящиеся к данной виртуальной таблице. Не рекомендуется обращаться к виртуальным таблицам при помощи условий в секции ГДЕ и т.п.

Такой запрос будет возвращать правильный (с точки зрения функциональности) результат, но СУБД будет намного сложнее выбрать оптимальный план для его выполнения. В некоторых случаях это может привести к ошибкам оптимизатора СУБД и значительному замедлению работы запроса. 

Например, следующий запрос использует секцию ГДЕ запроса для выборки из виртуальной таблицы:

Запрос.Текст = "ВЫБРАТЬ
| Номенклатура
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки()
|ГДЕ
| Склад = &Склад";

Возможно, что в результате выполнения этого запроса сначала будут выбраны все записи виртуальной таблицы, а затем из них будет отобрана часть, соответствующая заданному условию.

Рекомендуется ограничивать количество выбираемых записей на самом раннем этапе обработки запроса. Для этого следует передать условия в параметры виртуальной таблицы. 

Запрос.Текст = "ВЫБРАТЬ
| Номенклатура
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад)";

2.1. При обращении к виртуальной таблице следует передавать в условия наиболее простые конструкции, например, “Измерение = Значение”. Не рекомендуется использовать подзапросы и соединения(*) в параметрах виртуальной таблицы, так как это приводит к медленной работе запроса.

* Примечание: как явные соединения в подзапросах, так и неявные – при обращении к полям «через точку» от ссылки и соединения, добавляемые из ограничений доступа к данным (RLS), предусмотренных в ролях конфигурации.

2.2. При необходимости использовать подзапросы рекомендуется соблюдать следующие условия:

  • в подзапросе только одна таблица, нет соединений с другими таблицами;
  • если в подзапросе таблица табличной части (например, Документ.Накладная.СписокТоваров), то не должно быть обращения к реквизитам таблицы-шапки (Накладная.Проведен);
  • если в подзапросе таблица, у которой могут быть табличные части (например, Документ.Накладная), то не должно быть обращений к табличным частям (например, ГДЕ Документ.Накладная.СписокТоваров.Номенклатура = “1”);
  • если в подзапросе временная таблица, то не должно быть условий (раздела ГДЕ);
  • если в подзапросе постоянная таблица, то условие (раздел ГДЕ) допустимо, только если условие выполняется для 80% (или более) случаев; отсутствие условия означает выполнение для 100% случаев.
  • если в подзапросе постоянная таблица, то в ограничениях доступа к данным (RLS) не должно содержатся подзапросов и соединений (допускаются только простые условия вида ГДЕ Реквизит = Значение, “ГДЕ Истина”). Например, при использовании стандартных шаблонов RLS, входящих в состав подсистемы «Управление доступом» Библиотеки стандартных подсистем к запросу неявно добавляется конструкция Exists с несколькими подзапросами и соединениями. В таких случаях следует переписать исходный запрос с использованием временной таблицы или привилегированного режима.

Например, неправильно:

... ИЗ
РегистрНакопления.ТоварыКОтгрузке.Остатки(
&ДатаОтгрузки,
&ОтображениеРаспоряжений
 И ДокументОтгрузки.Склад = &Склад       -- неявное соединение «через точку»
 ИЛИ ДокументОтгрузки В
  (ВЫБРАТЬ                               -- подзапрос с соединением
   Распоряжения.Распоряжение КАК ДокументОтгрузки
  ИЗ
  Документ.ЗаданиеНаПеревозку.Распоряжения КАК Распоряжения –- доступ к этому документу ограничен по сложному RLS, который неявно добавляет еще пару соединений
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЗаданиеНаПеревозку.СкладыПогрузки КАК  СкладыПогрузки 
  ПО
  Распоряжения.Ссылка = СкладыПогрузки.Ссылка
  И СкладыПогрузки.Склад = &Склад
  И Распоряжения.Ссылка.Проведен         –- здесь и ниже обращения к реквизитам шапки
  И Распоряжения.Ссылка.Статус В (...)))

правильно:

... ИЗ
РегистрНакопления.ТоварыКОтгрузке.Остатки(
 &ДатаОтгрузки,
 Склад = &Склад                         -- теперь это реквизит регистра
 ИЛИ ДокументОтгрузки В
 (ВЫБРАТЬ
  ЗаданияНаПеревозку.Распоряжение
  ИЗ
  ВременнаяТаблицаЗаданийНаПеревозку КАК ЗаданияНаПеревозку))  -- выборка из временной таблицы без условий

2.3. В случае, если нужно использовать несколько условий с подзапросами, следует выбрать одно, удовлетворяющее условиям выше и отфильтровывающее максимальное количество записей. Остальные условия следует накладывать на внешний запрос.

Кроме того, в ряде случаев можно обойтись и без перемещения условий на внешний запрос, если применять временные таблицы. 
Например, вместо условия (неправильно):

Номенклатура В (…) И Характеристика В (…) И Серия В(…)

правильно:

(Номенклатура, Характеристика, Серия) В (ВЫБРАТЬ Номенклатура, Характеристика, Серия ИЗ ВременнаяТаблицаТоваров)

Эффективные условия запросов

. Условия запросов должны быть написаны оптимально с точки зрения производительности, чтобы исключить существенное увеличение длительности выполнения запросов при увеличении объема данных в таблицах.

Поля основного условия в секциях ГДЕ, ПО и виртуальных таблицах должны быть проиндексированы. Основное условие может быть уточнено дополнительным условием, но объединять их следует только по И.

Важно понимать структуру индексов, которые получаются при индексировании полей и учитывать их при построении основного условия . Например, при индексировании разных полей одного объекта метаданных создаются разные индексы, а не один в который помещаются все проиндексированные поля и использоваться основным условием будет только один из них.

Основное условие – это то, что позволяет ограничить объем выборки больше других условий и его составляющие объединены по И.

Дополнительное условие – это то, что объединено с основным условием по И и его составляющие могут быть любой сложности (НЕ, <>, +, -, /, *, функции и т.п.).

Основное условие должно содержать только такие операции, которые позволяют выполнять поиск по индексу:

  • для первого и всех используемых полей индекса, кроме последнего, только = и И;
  • для последнего или единственного используемого поля индекса допустимо использовать =, >, <, >=, <=, ПОДОБНО, МЕЖДУ, В, ИЛИ (приводимое к В);
  • нельзя использовать арифметические операции, функции, отрицания и неравенства.

Для условий в ГДЕ или в виртуальной таблице следует индексировать поля в основной таблице, из которой выполняется выборка.

Для условий в ПО ЛЕВОГО соединения следует индексировать поля в правой таблице.

Для условий в ПО ВНУТРЕННЕГО соединения следует индексировать поля в таблице с большим количеством записей.

Основное условие желательно строить таким образом, чтобы оно использовало индексы, которые автоматически создает платформа.

1.1. Описанные выше требования допустимо не соблюдать, если в таблицах, из которых выполняется выборка, или с которыми выполняется соединение, всегда будет мало данных (менее 1000 записей) или запросы с такими условиями выполняются очень редко.

1.2. Если записей в таблице много и выполнить указанные выше требования невозможно, то можно попробовать:

  • преобразовать условия (см. п. 3, п. 4);
  • добавить в таблицу заранее вычисляемые индексированные поля, которые заполняются при записи в нее и используются вместо сложного условия;
  • если указанные выше рекомендации не помогли, то следует пересмотреть архитектуру решения так, чтобы можно было выполнить эти условия.

2. Оператор ИЛИ

2.1. В основном условии оператор ИЛИ можно использовать только для последнего из используемых или единственного поля индекса, когда оператор ИЛИ можно заменить на оператор В.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле = &Значение1
    ИЛИ Таблица.Поле = &Значение2

т.к. можно переписать при помощи оператора В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
    Таблица.Поле В (&Значения)

НЕПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1
    ИЛИ Таблица.Поле2 = &Значение2

нельзя переписать при помощи “В”, но можно переписать при помощи “ОБЪЕДИНИТЬ ВСЕ” (каждое поле Поле1 и Поле2 должны быть проиндексированы):

ГДЕ
    Таблица.Поле1 = &Значение1
ОБЪЕДИНИТЬ ВСЕ
ГДЕ
    Таблица.Поле2 = &Значение1

Примечание: заменить ИЛИ на ОБЪЕДИНИТЬ ВСЕ можно не всегда, убедитесь, что результат будет действительно тем же, что и при ИЛИ, перед тем, как применять.

2.2. В дополнительном условии оператор ИЛИ можно использовать без ограничений.

ПРАВИЛЬНО 1:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно использовать ИЛИ)
    (Таблица.Поле2 = &Значение2 ИЛИ Таблица.Поле3 = &Значение3)

ПРАВИЛЬНО 2:

ГДЕ
    (Таблица.Поле1 = &Значение1 ИЛИ Таблица.Поле1 = &Значение2)
    И
    (Таблица.Поле2 = &Значение3 ИЛИ Таблица.Поле2 = &Значение4)

т.к. можно переписать при помощи В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
    Таблица.Поле1 В (&Значения1)   // Основное условие
    И Таблица.Поле2 В (&Значения2) // Дополнительное условие (или наоборот)

3. Оператор ПОДОБНО

В основном условии для последнего из используемых или единственного поля индекса можно использовать оператор ПОДОБНО. Функции работы со строками, в некоторых случаях, можно привести к оператору ПОДОБНО и использовать его в основном условии.

НЕПРАВИЛЬНО 1:

ГДЕ
    ПОДСТРОКА(Таблица.Поле, 1, 6) = "строка"

ПРАВИЛЬНО 1:

ГДЕ
    Таблица.Поле ПОДОБНО "строка%"

НЕПРАВИЛЬНО 2:

ГДЕ
    ПОДСТРОКА(Таблица.Поле, 3, 6) = "строка"

НЕПРАВИЛЬНО 2:

ГДЕ
    Таблица.Поле ПОДОБНО "__строка%" // Литерал не должен начинаться с символов "_" или "%"

ПРАВИЛЬНО 2:

Добавить новое вычисляемое при записи в таблицу поле, которое будет содержать фрагмент ПОДСТРОКА(Таблица.Поле, 3, 6). Проиндексировать это поле и искать по следующему условию:

ГДЕ
    Таблица.ВычисляемоеПоле ПОДОБНО "строка%"

4. Оператор МЕЖДУ

В основном условии для последнего из используемых или единственного поля индекса можно использовать оператор МЕЖДУ. Функции работы с датой, в некоторых случаях, можно привести к оператору МЕЖДУ и использовать его в основном условии.

НЕПРАВИЛЬНО:

ГДЕ
    МЕСЯЦ(Таблица.Поле) = 1

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле МЕЖДУ &ДатаНачалаМесяца И &ДатаКонцаМесяца

Например, ДатаНачалаМесяца=01.01.2016, ДатаКонцаМесяца=31.01.2016 23:59:59

5. Выражение ВЫБОР

Выражение ВЫБОР можно использовать только в дополнительных условиях.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно использовать ВЫБОР)
    ВЫБОР
        КОГДА Таблица.Поле2 = &Значение2
            ТОГДА Таблица.Поле3 = &Значение3
        ИНАЧЕ Таблица.Поле4 = &Значение4
    КОНЕЦ

НЕПРАВИЛЬНО:

ГДЕ
    ВЫБОР // Основное условие (поиск по индексу использоваться не будет)
        КОГДА Таблица.Поле2 = &Значение2
            ТОГДА Таблица.Поле3 = &Значение3
        ИНАЧЕ Таблица.Поле4 = &Значение4
    КОНЕЦ

6. Арифметические операции

Арифметические операции над полями можно выполнять только в дополнительных условиях.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно выполнять арифметические операции)
    Таблица.Поле2 - 1 > 0

НЕПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 - 1 > 0 // Основное условие (поиск по индексу невозможен)

7. Если в конфигурации описано несколько ролей с разным ограничением доступа на уровне записей (RLS), то не следует назначать одному пользователю более одной такой роли. Если один пользователь будет включен, например, в две роли с RLS – бухгалтер и кадровик, то при выполнении всех его запросов к их условиям будут добавляться условия обоих RLS с использованием логического ИЛИ. Таким образом, даже если в исходном запросе нет условия ИЛИ, оно появится там после добавления условий RLS. Такой запрос так же может выполняться неоптимально – медленно и с избыточными блокировками.

Вместо этого следует:

  • Пересмотреть состав ролей таким образом, чтобы к одному объекту метаданных давала доступ только одна роль (на чтение, запись и т.п.);
  • При необходимости разработки нескольких ролей, предоставляющих доступ к одному объекту метаданных, задавать в них одинаковые условия RLS. В этом случае к тексту запроса будет добавлено только одно условие, без объединения по ИЛИ;
  • Либо если это допустимо с точки зрения прикладной области, создать “смешанную” роль – “бухгалтер-кадровик” и прописать ее RLS таким образом, чтобы избежать использования ИЛИ в условии, а пользователя включить в эту одну роль.

Разрешение итогов для периодических регистров сведений

Методическая рекомендация (полезный совет)

1.1. Для периодических регистров сведений рекомендуется разрешить итоги, если выполнены все следующие условия:

  • в регистре ожидается большой объем данных (например, оправданно для регистра с ценами номенклатуры; но не имеет смысла для регистра с курсами валют);
  • в конфигурации предусмотрены частотные запросы к срезам последних на текущий момент времени и/или к срезам первых для получения актуальных данных (т.е. когда не задан период в параметрах виртуальных таблиц СрезПервых и СрезПоследних);
  • при этом остальные условия для виртуальных таблиц СрезПервых и СрезПоследних задаются только на значения измерений (и разделителей, находящихся в режиме Независимо и совместно);
  • в ограничениях доступа к данным регистра используются только измерения (и разделители, находящиеся в режиме Независимо и совместно).

Например, если в конфигурации предусмотрены часто выполняющиеся запросы к регистру ЦеныНоменклатуры для получения текущих цен номенклатуры:

ВЫБРАТЬ
 Номенклатура.Артикул КАК Артикул,
 ЦеныНоменклатуры.Цена КАК Цена,
 ...
ИЗ
 Справочник.Номенклатура КАК Номенклатура
 ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ВидЦены =  &ВидЦены) КАК ЦеныНоменклатуры
ПО ЦеныНоменклатуры.Номенклатура = Номенклатура.Ссылка
 ...

то при соблюдении всех остальных условий, перечисленных выше, установка свойства Разрешить итоги: срез последних существенно ускорит выполнение таких запросов, засчет того, что выборка будет выполняться напрямую из дополнительных таблиц, в которых хранятся только последние значения (для среза последних) и первые значения (для среза первых).

1.2. Кроме того, следует рассмотреть альтернативные варианты по пересмотру запросов к регистру таким образом, чтобы эти условия выполнялись.

Например, если в некоторых случаях данные в регистр ЦеныНоменклатуры записываются будущей датой, а при подборе товаров к этому регистру выполняется запрос всегда на текущую дату (дата явно задана в параметре виртуальной таблицы СрезПоследних), то итоги не будут ускорять выполнение таких запросов. Поскольку итоги строятся только для первых и последних записей регистра.

Однако если при открытии формы подбора товаров анализировать, есть ли регистраторы с будущей датой, и если их нет – выполнять другой запрос к срезу последних без установки даты, то такой запрос будет работать быстрее.

2. Во всех остальных случаях, не следует разрешать итоги для периодических регистров сведений. Прежде всего, если

  • чаще всего (всегда) к виртуальным таблицам среза первых/последних периодического регистра сведений выполняются запросы на конкретный период (например, на дату документа).
  • в условиях для виртуальных таблиц СрезПервых и СрезПоследних чаще всего (всегда) используются подзапросы и соединения (обращения «через точку» к полям связанных таблиц). Например, в этом случае:
ВЫБРАТЬ
...
ИЗ
РегистрСведений.КурсыВалют.СрезПоследних(, Валюта.Код = &КодВалютыСклада) КАК КурсыВалют

3. Не требуется предусматривать в конфигурации отдельного механизма пересчета итогов, так как актуализация таблиц  итогов выполняется автоматически при каждой записи набора записей в регистр.

Исключение составляют отдельные случаи, когда актуализация итогов при записи отключалась принудительно с помощью вызова метода РегистрСведенийМенеджер.УстановитьИспользованиеИтогов(Ложь).

Эффективное обращение к виртуальной таблице «Остатки»

Эффективность обращения к виртуальным таблицам во многом зависит от того, как построено обращение к этой таблице. Стандарт Обращения к виртуальным таблицам описывает общие требования и рекомендации по работе с виртуальными таблицами. В этом стандарте изложены дополнительные рекомендации по повышению эффективности обращения к виртуальной таблице Остатки регистров накопления и бухгалтерии.

При обращении к любой виртуальной таблице платформа 1С:Предприятие генерирует запрос к СУБД, содержащий вложенный запрос. Самым эффективным вложенным запросом для чтения остатков будет чтение хранимой таблицы текущих остатков без применения группировки по измерениям. Платформа 1С:Предприятие сгенерирует такой запрос, если будут соблюдены все перечисленные ниже условия:

  • получение остатков ведется без указания даты;
  • не используется разделение итогов (необходимо учитывать при использовании такого режима может снижаться параллельность записи в регистр.
  • внешний по отношению к виртуальной таблице запрос использует все измерения (в предложении ВЫБРАТЬ или в условиях соединения).

Пример.

Регистр накопления ОстаткиТовара содержит два измерения: Склад и Номенклатура, а также ресурс Количество. Необходимо запросом получить список всей номенклатуры, с указанием количества товаров на конкретном складе.

НЕПРАВИЛЬНО

ВЫБРАТЬ
 СпрНоменклатура.Ссылка КАК Товар, 
 ЕСТЬNULL(ОстаткиТоваров.Остаток, 0 ) КАК Остаток
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
 ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки(&СегодняшняяДата, Склад = &Склад) КАК ОстаткиТоваров
ПО ОстаткиТоваров.Номенклатура = СпрНоменклатура.Ссылка

В этом запросе:

  • в условия виртуальной таблицы передана дата, поэтому будет использована не только хранимые таблицы остатков, но и таблица движений. Т.к. необходимо получить текущие остатки, то дату в запрос передавать не нужно;
  • измерение Склад не используется во внешнем по отношению к виртуальной таблице запросе, поэтому вложенный запрос остатков будет содержать группировку этому измерению.

ПРАВИЛЬНО

ВЫБРАТЬ
 СпрНоменклатура.Ссылка КАК Товар, 
 ЕСТЬNULL(ОстаткиТоваров.Остаток, 0 ) КАК Остаток
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
 ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки(, Склад = &Склад) КАК ОстаткиТоваров
ПО ОстаткиТоваров.Номенклатура = СпрНоменклатура.Ссылка
 И ОстаткиТоваров.Склад = &Склад

Использование временных таблиц

1. В общем случае, временные таблицы рекомендуется использовать для повышения производительности и стабильности выполнения запросов. Их можно использовать для других целей (например, для улучшения архитектуры кода), но при этом, нужно понимать, что это может в некоторых случаях приводить к снижению производительности.

2. Есть ситуации, когда временные таблицы не следует создавать или создание временных таблиц необходимо минимизировать.

2.1. Не следует создавать временные таблицы с очень большим объемом данных (сотни тысяч записей). Иначе это приведет к существенному снижению производительности при записи и исчерпанию свободного места на диске. Если алгоритму требуется работать с большим объемом данных, то он должен выполнять обработку этих данных порциями.

2.2. Следует максимально ограничивать количество данных, выбираемых во временную таблицу. Не следует помещать во временную таблицу больше данных, чем требуется последующим запросам.

2.3. Не следует помещать во временную таблицу поля, которые не используются в последующих запросах, т.к. время и место для их размещения тратится впустую.

2.4. Не следует создавать и удалять временные таблицы в цикле, если можно создать одну временную таблицу до выполнения цикла.

2.5. Не следует копировать одну временную таблицу в другую только ради того, чтобы переименовать первую таблицу во вторую. Вместо этого, следует передавать имя таблицы.

3. Временные таблицы следует всегда индексировать, когда это даст прирост производительности.

3.1. Индекс следует строить если:

3.1.1. Большая временная таблица участвует в соединении (не важно, с какой стороны). В индекс следует добавлять поля, участвующие в условии ПО.

3.1.2. Обращение к временной таблице выполняется в подзапросе конструкции логического оператора В (…). В индекс следует добавлять поля временной таблицы из списка выбора, соответствующие перечисленным с левой стороны логического оператора В (…).

3.2. Маленькие временные таблицы индексировать не нужно (менее 1000 записей).

3.3. Если условий выбора или соединений с временной таблицей больше одного, и только одно из них проверяется часто, то индекс следует строить для наиболее часто проверяемого условия.

Previous Article
Next Article

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.