Почему вообще работает мой запрос? или Ещё раз о планах запросов

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

Формулировку задачи и тексты запросов из публикации-первоисточника я немного отредактировал.

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

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

ВЫБРАТЬ
ПриобретениеТоваровУслугТовары.Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика,
ПриобретениеТоваровУслугТовары.Ссылка.Дата,
ПриобретениеТоваровУслугТовары.Цена
ИЗ
Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
ГДЕ
ПриобретениеТоваровУслугТовары.Ссылка В
(ВЫБРАТЬ ПЕРВЫЕ 1
ПриобретениеТоваровУслугТоварыУсловие.Ссылка
ИЗ
Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТоварыУсловие
ГДЕ
ПриобретениеТоваровУслугТоварыУсловие.Номенклатура = ПриобретениеТоваровУслугТовары.Номенклатура
И ПриобретениеТоваровУслугТоварыУсловие.Характеристика = ПриобретениеТоваровУслугТовары.Характеристика
УПОРЯДОЧИТЬ ПО
ПриобретениеТоваровУслугТоварыУсловие.Ссылка.Дата УБЫВ)

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

ВЫБРАТЬ
ПриобретениеТоваровУслугТовары.Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика,
МАКСИМУМ(ПриобретениеТоваровУслугТовары.Ссылка.Дата) КАК Дата
ПОМЕСТИТЬ ВТ_Последние
ИЗ
Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
СГРУППИРОВАТЬ ПО
ПриобретениеТоваровУслугТовары.Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Характеристика,
Дата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Последние.Номенклатура,
ВТ_Последние.Характеристика,
ВТ_Последние.Дата,
ПриобретениеТоваровУслугТовары.Цена
ИЗ
ВТ_Последние КАК ВТ_Последние
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
ПО ВТ_Последние.Номенклатура = ПриобретениеТоваровУслугТовары.Номенклатура
И ВТ_Последние.Характеристика = ПриобретениеТоваровУслугТовары.Характеристика
И ВТ_Последние.Дата = ПриобретениеТоваровУслугТовары.Ссылка.Дата

ВЫБРАТЬ
ПриобретениеТоваровУслугТовары.Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика,
ПриобретениеТоваровУслугТовары.Ссылка.Дата,
ПриобретениеТоваровУслугТовары.Цена
ИЗ
Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ПриобретениеТоваровУслугТовары.Номенклатура КАК Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика КАК Характеристика,
МАКСИМУМ(ПриобретениеТоваровУслугТовары.Ссылка.Дата) КАК Дата
ИЗ
Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
СГРУППИРОВАТЬ ПО
ПриобретениеТоваровУслугТовары.Номенклатура,
ПриобретениеТоваровУслугТовары.Характеристика) КАК ВложенныйЗапрос
ПО ПриобретениеТоваровУслугТовары.Номенклатура = ВложенныйЗапрос.Номенклатура
И ПриобретениеТоваровУслугТовары.Характеристика = ВложенныйЗапрос.Характеристика
И ПриобретениеТоваровУслугТовары.Ссылка.Дата = ВложенныйЗапрос.Дата

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

Важно понимать, что для построения плана выполнения СУБД использует не только текст запроса, но и структуру метаданных (наличие индексов), а также статистику по самим данным.

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

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

Итак, как же нам получить план запроса? Для этого мы воспользуемся подсистемой Расширенных событий (Extended Events) MS SQL Server, а также обычной консолью запросов, опубликованной на 1С:ИТС.

Начнём с настройки SQL-сервера. Открываем Management Studio и в дереве объектов переходим на ветвь Управление -> Расширенные события -> Сеансы. Создаём новый сеанс (без использования мастера). В окне нового сеанса задаём его имя и на странице "События" выбираем единственное событие — query_post_execution_showplan.

Для события query_post_execution_showplan устанавливаем параметры: ставим галочку напротив поля "sql_text". Также можем установить фильтр по названию базы.

Теперь нам нужно запустить созданный сеанс и открыть окно его данных. Это делается из контекстного меню сеанса (команды "Запустить сеанс" и "Наблюдать за данными, передаваемыми в режиме реального времени").

Выполним запрос в консоли запросов 1С и подождём пока в таблице данных сеанса появится соответствующее нашему запросу событие:

Здесь мы можем увидеть фактический текст запроса, который выполняет СУБД, и посмотреть его план в графическом формате. Откроем план выполнения запроса №1:

Эти же самые данные консоль запросов 1С может получить из технологического журнала. Для этого поставьте галочку "Показывать план выполнения запроса" на форме консоли, выполните запрос и нажмите "Открыть".

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

Итак, что же мы можем сказать о запросе №1, зная план его выполнения?

Прежде всего то, что для основного соединения таблиц (выделено на плане запроса) используется способ "Вложенные циклы" ("Nested Loops"). Между какими таблицами происходит соединение? Между таблицами Документ.ПриобретениеТоваровУслуг.Товары (назовём её "верхняя" по расположению на плане) и производной таблицей, формируемой посредством некоторых операций (назовём эту таблицу "нижняя").

Самое время сказать несколько слов о том, что из себя представляет соединение "Nested Loops". Представим себе цикл по всем записям одной из таблиц ("внешней"), на каждой итерации которого осуществляется цикл по всем записям другой таблицы ("внутренней"). Если в ходе такого перебора обнаруживается соответствие условиям соединения, то текущие записи обеих таблиц заносятся в результирующую таблицу. Это и называется вложенными циклами.

Обычно на момент начала выполнения операции соединения обе таблицы уже сформированы, но что происходит в нашем случае? Для того, чтобы сформировать нижнюю (внутреннюю) таблицу нам нужно выполнить отбор по номенклатуре и характеристике (см. текст запроса), значения которых передаются из верхней (внешней) таблицы. Кроме того, нам нужно отсортировать нижнюю таблицу по дате. И это на каждой итерации цикла, то-есть 406 раз!

Таблица1 = Документ.ПриобретениеТоваровУслуг.Товары;
Таблица2 = Документ.ПриобретениеТоваровУслуг.Товары;

Результат = Новый Таблица();
Для Каждого Строка1 Из Таблица1 Цикл
ВнутренняяТаблица = Таблица2.Отбор(Строка1.Номенклатура, Строка1.Характеристика);
ВнутренняяТаблица = ВнутренняяТаблица.Сортировать(Дата);
ВнутренняяТаблица = ВнутренняяТаблица.ПолучитьСтроку(1);
ВнутренняяТаблица = ВнутренняяТаблица.Отбор(Строка1.Ссылка); // Остаётся 1 или 0 строк

Для Каждого Строка2 Из ВнутренняяТаблица Цикл
Если Строка1.Ссылка = Строка2.Ссылка
И Строка1.Номенклатура = Строка2.Номенклатура
И Строка1.Характеристика = Строка2.Характеристика Тогда
Результат.Добавить(Строка1, Строка2);
КонецЕсли;
КонецЦикла;
КонецЦикла;

Не сложно догадаться, что вариант запроса №1 не является самым оптимальным из предложенных. Теперь посмотрим на планы выполнения остальных запросов.

 

 План выполнения запроса №3

Имена объектов метаданных 1С и количество строк в таблицах получены с помощью консоли запросов 1С.

Обратите внимание, что когда в запросе мы получаем значение через точку от поля ссылочного типа (в нашем случае — дату), мы неявно добавляем в фактический запрос как минимум ещё одну таблицу. Если поле имеет составной тип, будут созданы избыточные соединения с таблицами всех объектов, входящих в этот тип. Способы обхода читайте на 1С:ИТС.

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

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