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

10.06.19

Разработка - Запросы

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

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

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

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

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

Вариант 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 оптимизатор СУБД выбрал для них разные планы выполнения. Производительность запросов также будет различаться. Не буду сейчас рассуждать о преимуществах одного вида запроса над другим (временные таблицы или подзапросы). На этот счет есть разные мнения, которые сходятся в одном: каждый частный случай нужно рассматривать отдельно. Как уже было сказано, способ выполнения запроса зависит не только от его текста, но и от структуры метаданных, самих данных, версии СУБД.

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

См. также

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 руб.

02.09.2020    119921    656    389    

701

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5620    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    1964    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6091    29    mkalimulin    23    

48

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1714    vandalsvq    7    

28

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

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

06.12.2023    5283    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

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

11.10.2023    15957    skovpin_sa    14    

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

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

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

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

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

А что делать. если нет пункта "Расширенные события" после раскрытия плюсика на ветке "Управление"? MS SQL 2008.
7. e][tend 11.06.19 11:28 Сейчас в теме
(5) Хотел сказать что расширенные события появились в более поздних версиях SQL Server начиная с 12, потом проверил и понял, что народ и 2008 как то их использует.
9. DataReducer 302 11.06.19 16:37 Сейчас в теме
(5) SQL Server 2008 - это первая версия, в которой появилась подсистема Расширенных событий, но, к сожалению, ещё без графического интерфейса. В этой версии можно использовать SQL Server Profiler. Как его задействовать описано здесь: https://infostart.ru/public/965250/, но кроме событий RPC:Completed и SQL:BatchCompleted нужно включить ещё событие ShowplanXMLStatisticsProfile.
6. kuzyara 1896 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 302 11.06.19 12:16 Сейчас в теме
(6) 1 ссылка - статья с уже битыми ссылками на изображения
2 ссылка - статья про то как получить фактический текст запроса в Профайлере, планы запроса не упоминаются
3 ссылка - заметки про всё и ни о чём
4 ссылка - отличная (!!!) статья про сбор статистики по запросам, но без использования графических средств
antonio_i; +1 Ответить
Оставьте свое сообщение