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

Публикация № 1073908

Программирование - Практика программирования

44
Другие статьи на эту тему объясняют, что такое план выполнения запроса, но не рассказывают о том, как его получить. Эта заметка призвана заполнить этот пробел. Её цель - популяризировать общедоступные инструменты получения плана запроса среди разработчиков, которые ещё не начали их использовать.

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

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

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

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

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

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

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

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

 

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

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

Если говорить о запросах 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 (псевдокод)
Таблица1 = Документ.ПриобретениеТоваровУслуг.Товары;
Таблица2 = Документ.ПриобретениеТоваровУслуг.Товары;

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

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

 

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

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

 

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

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

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

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

44

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. Evg-Lylyk 2593 10.06.19 20:04 Сейчас в теме
2. json 2327 10.06.19 21:32 Сейчас в теме
(1) твоя реклама уже стала навящивой.
В каждой бочке ты уже со своей консолью.

Делай продукт так, чтобы его рекомендовали другие, как это делает Tormozit c его инструментами разработчика, которому большой респект.
А самопиар - это штука спорная, уважения не вызывает
Zero_nv; АлександрЯрославичъ; mivari; andrvyst; CyberCerber; wowik; +6 Ответить
10. OerlandHue 13.06.19 04:39 Сейчас в теме
(2)
навящивой.
В каждой бочке ты уже со своей консолью.

Делай продукт так, чтобы его рекомендовали другие, как это делает Tormozit c его инструментами разработчика, которому большой респект.

Нормальная тема самопиар. Как вы предлагаете узнать о продукте, если о нем никто не рассказывает.
Evg-Lylyk; +1 Ответить
11. json 2327 13.06.19 06:37 Сейчас в теме
(10)
Обсуждение данного вопроса не имеет отношения к данной публикации.

Считаю неправильным холиварить на эту тему в комментариях к данной статье
12. Evg-Lylyk 2593 13.06.19 09:21 Сейчас в теме
(11) Ссылка которую я привел Анализ планов запросов
3. wowik 579 11.06.19 09:18 Сейчас в теме
4. capitan 1225 11.06.19 09:34 Сейчас в теме
За одно только оформление можно плюс поставить )
5. androidT1C 72 11.06.19 10:09 Сейчас в теме
"Управление -> Расширенные события -> Сеансы."

А что делать. если нет пункта "Расширенные события" после раскрытия плюсика на ветке "Управление"? MS SQL 2008.
7. e][tend 11.06.19 11:28 Сейчас в теме
(5) Хотел сказать что расширенные события появились в более поздних версиях SQL Server начиная с 12, потом проверил и понял, что народ и 2008 как то их использует.
9. DataReducer 131 11.06.19 16:37 Сейчас в теме
(5) SQL Server 2008 - это первая версия, в которой появилась подсистема Расширенных событий, но, к сожалению, ещё без графического интерфейса. В этой версии можно использовать SQL Server Profiler. Как его задействовать описано здесь: https://infostart.ru/public/965250/, но кроме событий RPC:Completed и SQL:BatchCompleted нужно включить ещё событие ShowplanXMLStatisticsProfile.
6. kuzyara 775 11.06.19 10:23 Сейчас в теме
не рассказывают о том, как его получить
Как это не рассказывают?

Анализ запросов с помощью SQL Profiler
https://infostart.ru/public/291874/
Отлавливаем запрос 1С в profiler на MS SQL
https://infostart.ru/public/965250/
SQL Server Profiler. В картинках, для самых маленьких
https://infostart.ru/public/663708/
и по Extended Events есть..
https://infostart.ru/public/1056294/
asdf_88; OerlandHue; Дмитрий74Чел; Evg-Lylyk; +4 Ответить
8. DataReducer 131 11.06.19 12:16 Сейчас в теме
(6) 1 ссылка - статья с уже битыми ссылками на изображения
2 ссылка - статья про то как получить фактический текст запроса в Профайлере, планы запроса не упоминаются
3 ссылка - заметки про всё и ни о чём
4 ссылка - отличная (!!!) статья про сбор статистики по запросам, но без использования графических средств
Оставьте свое сообщение