Содержание:

1. Какие планы запросов 1С строит оптимизатор для оптимальных и неоптимальных запросов

         Рассмотрим несколько примеров по оптимизации запросов 1С, в частности посмотрим какие планы запросов 1С строит оптимизатор для оптимальных и неоптимальных запросов, увидим разницу между ними.

         Роль оптимизатора запросов можно изобразить так:

Но в данной статье сделаем больше акцент на плане запроса 1С. Именно на его основе выполняется в итоге запрос. Поскольку в основном приходится работать с СУБД MS SQL Server – рассмотрим планы запроса 1С на этой площадке.

Как получить план запроса 1С? План запроса 1С можно получить, выполняя трассировку запроса с помощью инструмента MS SQL Profiler вендора MS, то есть собирая с помощью различных фильтров данные, возвращаемые компонентой Database Engine и которые являются показателями процесса выполнения запроса 1С.

Но SQL Profiler постепенно уходит в историю, поскольку забирает на себя больше ресурсов чем Extended Events. Поэтому сейчас актуален анализ событий с помощью инструмента Extended Events (Расширенные события). SQL Profiler больше подходил для тестовых баз 1С, в то время как Extended Events может осуществлять сбор данных на рабочей базе 1С без заметного влияния на производительность.

Пример графического изображения плана запроса 1С

Элементы на схеме, которые связываются линиями со стрелками называются операторами (плана запроса 1С). Получение данных в плане запроса 1С происходит справа-налево, согласно линиям со стрелкой. Но запрашиваются данные слева-направо. Понятно, что таблицы соединяются между собой попарно.

         Какие-то операторы возвращают в результирующий набор – строки построчно, а некоторые – только все строки, после обхода всей таблицы. Всего существует более 100 операторов плана запроса 1С.

         При этом операторы классифицируются как логические и физические.

         Логические операции описывают что надо сделать на концептуальном уровне. Физические операторы уже осуществляют действия, например, проводят вычисления или осуществляют соединения.

         У физических операторов есть стоимость. Она указана на схеме. И обратите внимание что сумма стоимости всех операторов всегда равна 100%. Чем тоньше стрелки между операторами плана запроса 1С – тем быстрее выполняется формирование набора строк на выходе.

         Оптимизатор принимает решение на основе многих параметров. Играют роль и количество строк в выборке таблицы, и уровень сложности выборки строк, плотность данных в страницах и другие моменты.

Чтобы каждый раз не строить План запроса 1С заново– оптимизатор помещает его в cash и хранит там до обновления статистики. Перед построением нового плана оптимизатор сопоставляет параметры запроса с тем, что находится в cash.

         Настроим сбор событий с помощью Extended Events. Для этого откроем среду MS Visual Studio.

         У меня русифицированный вариант среды, поэтому открываем ветку иерархии

         Управление -> Расширенные события -> Сеансы

         В контекстном меню выбираем “Новый мастер сеанса” – открывается помощник создания сеанса.

         Заполняем закладки помощника, какие-то пропускаем:

“Введение”

“Задание свойств сеанса”. Имя сеанса: _Query_Plan.

“Выбор шаблона”.

“Выбор событий для захвата”. Query_post_execution_showplan.

“Захват глобальных полей”.

[v] database name

[v] sql text

“Установка фильтров событий сеанса”.

“Укажите хранилище данных сеанса”.

“Сводка”. Готово

Закрыть

         Еще надо учесть, что таблицы в MS SQL Server называются по-другому нежели в 1С.

         Для идентификации объектов в MS SQL Server можно использовать, например, обработку Структура_ИБ.epf

Нам понадобятся не все события, собираемые в Extended Events, поэтому настроим дополнительно фильтры.

Откроем для созданного события _Query_Plan контекстное меню 1С, выберем “Свойства”, закладку “События”, Кнопка “> Настройка”, на панели “Выбранные события” выделяем событие Query_post_execution_showplan - на панели “Параметры событий” открываем закладку “Фильтр (предикат)” и находясь на области списка вызываем правой кнопкой мыши контекстное меню 1С – “Добавить предложение”. В колонке “Поле” – двойной клик, из ниспадающего списка выбираем

Sqlserver.database_name, в колонке “Оператор” - equal_i_sql_unicode_string Значение: Expert2023 – так называется наша база. Фильтр для наименования базы 1С установлен.

Теперь добавляем еще один фильтр (снова “Добавить предложение” и т.д.) В колонке “Поле” – двойной клик, из ниспадающего списка выбираем

Sqlserver.sql_text, в колонке “Оператор” - like_i_sql_unicode_string Значение: %_Reference405%

         То есть фильтр для текста запроса 1С, обратите внимание что Оператор уже не на равенство, как в первом фильтре, а на совпадение фрагмента текста, а совпасть должно имя справочника Номенклатура в 1С– в MS SQL Server это _Reference405.

         Фильтр для текста запроса 1С настроен. Ok.

Теперь остается только применить созданное событие с настроенными фильтрами для просмотра плана запроса 1С. Для этого запустим событие _Query_Plan:

         В MS Visual Studio выбираем ИмяСервера -> Управление -> Расширенные события -> Сеансы -> _Query_Plan

         Открываем контекстное меню 1С -> Запустить сеанс

         Снова контекстное меню 1С -> Наблюдать за данными, передаваемыми в режиме реального времени

         Это наши сети для ловли плана – фильтры же настроены!  

2.     Примеры запросов 1С

         Итак, рассмотрим несколько примеров с запросами.

         ИЛИ в секции ГДЕ запроса 1С

         Вместо использования ИЛИ, лучше разбить исходный запрос на соответствующее количеству операндов - количество запросов 1С.

         1С: Предприятие

         В консоли запросов исполним нерекомендуемый вариант:

    ВЫБРАТЬ

         Номенклатура.Наименование КАК Наименование

ИЗ

         Справочник.Номенклатура КАК Номенклатура

ГДЕ

         Артикул = "М-87"  ИЛИ Артикул = "М-809"

         Переключаемся на MS Visual Studio, открываем закладку “План запроса 1С”:

Запрос не выглядит сложным, однако оператор ИЛИ в секции ГДЕ вносит существенные коррективы в план запроса 1С – он состоит из 12 операторов!      

Рассмотрим, что происходит справа-налево.

Оператор Constant scan помещает в запрос константные строки. Они образовались в результате присвоения в запросе конкретных значений реквизиту Артикул. Вычисление скалярного выражения происходит для каждого элемента в условии запроса. Это одновременно логический и физический оператор.

Оператор Concatenation копирует полученные строки в выходной поток. Также является логическим и физическим оператором. Далее следует снова Вычисление скалярного выражения, в контекстном меню 1С можно увидеть список выходных столбцов.

Оператор Сортировка сортирует входящие строки. Тоже логический и физический оператор. Обратим внимание что в скобках указано Top N Sort – это сортировка, где требуются только первые N строк. Интервал слияния проводит слияние интервалов – столбцов строк. Далее следуют Вложенные циклы. При соединениях вложенными циклами выполняется поиск во внутренней таблице каждой строки внешней таблицы, в данном случае с помощью индекса.

         Теперь выполним в консоли запросов правильный вариант:

     ВЫБРАТЬ

         Номенклатура.Наименование КАК Наименование

         ИЗ

         Справочник.Номенклатура КАК Номенклатура

ГДЕ

         Артикул = "М-87"           

         ОБЪЕДИНИТЬ ВСЕ

             ВЫБРАТЬ

         Номенклатура.Наименование КАК Наименование

         ИЗ

         Справочник.Номенклатура КАК Номенклатура

ГДЕ

         Артикул = "М-809"  

3. План запроса в MS Visual Studio на платформе системы 1С Предприятие

         Смотрим план запроса в MS Visual Studio на платформе системы 1С Предприятие:

План запроса 1С содержит 7 операторов, почти вдвое меньше чем у неоптимального запроса, хотя запрос выглядит сложнее! Поскольку объем выборки данных незначительный – то время выполнения запросов 1С здесь не играет роли. Однако на больших объемах данных – разница может быть заметной. А во время работы с базой исполняется не один запрос, да к тому же запросы могут исполняться параллельно, например, регламентное задание может быть запущено. И пользователь работает в базе не один. Все это в сумме при неоптимальном запросе может привести к снижению производительности 1С.

Но главное – при оптимальном плане – меньше нагрузка на процессор.

СУБД создает индекс по полю Артикул, поскольку он используется в условии запроса. Поскольку количество возвращаемых строк операторами “Поиск в индексе” и “Поиск ключа” небольшое (а именно по одной строке), то оптимизатор “решает” соединять их с помощью оператора “Вложенные циклы” или Nested Loops. В финале оператор Concatenation объединяет полученный результат – в запросе было использовано ОБЪЕДИНИТЬ ВСЕ.

Получение поля “Ссылка” через точку

         Изменим для события _Query_Plan фильтр для параметра Sqlserver.sql_text: в колонке “Значение” указываем %_Document1303% поскольку запрос обращается к основной таблице документа ПриобретениеТоваровУслуг, а именно так идентифицируется эта таблица на стороне СУБД.

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

апроса с обращением к полю Ссылка реквизита 1С Склад в условии ГДЕ, который итак имеет ссылочный тип. В качестве параметра выберем, например, Центральный склад:

ВЫБРАТЬ

         ПриобретениеТоваровУслуг.Дата КАК Дата,

         ПриобретениеТоваровУслуг.Номер КАК Номер,

         ПриобретениеТоваровУслуг.Контрагент КАК Контрагент       

ИЗ

         Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг      

ГДЕ

         ПриобретениеТоваровУслуг.Склад.Ссылка = &Ссылка

У данного запроса – такой план:

         Оператор “Вложенные циклы” (Nested Loops) собирает результирующий набор строк построчно, проверяя условие соединения таблиц Склад и ПриобретениеТоваровУслуг. То есть поле Ссылка приводит к сканированию еще и таблицы Склад.

         Затем выполним рекомендуемый вариант запроса – исключим поле Ссылка из обращения к реквизиту Склад документа ПриобретениеТоваровУслуг:

ВЫБРАТЬ

         ПриобретениеТоваровУслуг.Дата КАК Дата,

         ПриобретениеТоваровУслуг.Номер КАК Номер,

         ПриобретениеТоваровУслуг.Контрагент КАК Контрагент   

ИЗ

         Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг      

ГДЕ

         ПриобретениеТоваровУслуг.Склад = &Ссылка

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

         Здесь план запроса 1С состоит из одного оператора, который находит строки только в таблице ПриобретениеТоваровУслуг, хотя и был прочитан весь индекс.

         Подведем итоги. Мы научились настраивать сбор событий с помощью Extended Events, в частности планов запросов 1С. Рассмотрели из каких элементов состоит план запроса 1С. И увидели, как влияет оптимизация запросов 1С на создаваемый оптимизатором план запроса 1С.

Специалист компании "Кодерлайн"

Ильдар Мингалеев