Соединение вложенными циклами

31.05.21

База данных - HighLoad оптимизация

Nested loops и отсутствующие индексы. Статья написана по мотивам вебинара Виктора Богачева.

Есть один секрет DBA, который всегда работает. Я узнал этот секрет на вебинаре, лично убедился в его эффективности и давно хотел сделать публикацию, но толчком послужило видео. Автор видео (и вебинара) – Виктор Богачев. Он занимается реальной просветительской деятельностью для тех, кто хочет расти и развиваться.

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

0-3         Поиск недостающих индексов в представлении sys.dm_db_missing_index по фрагментам запроса
3-6         Покрывающий индекс, MS SQL Index include columns
6-9         Добавить непериодический регистр, создающий нужный индекс
9-12        Индексирование с дополнительным упорядочиванием
12-14       Пример соединения по полю "КлючСвязи"

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

SELECT TOP 10
[Total Cost] = ROUND(Stat.avg_total_user_cost * Stat.avg_user_impact * (Stat.user_seeks + Stat.user_scans),0),
Stat.avg_user_impact,
TableName = Detail.statement,
[EqualityUsage] = Detail.equality_columns,
[InequalityUsage] = Detail.inequality_columns,
[Include Cloumns] = Detail.included_columns
FROM sys.dm_db_missing_index_groups Groups
INNER JOIN sys.dm_db_missing_index_group_stats Stat
ON Stat.group_handle = Groups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details Detail
ON Detail.index_handle = Groups.index_handle
ORDER BY [Total Cost] DESC;

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

Последний пример актуален для всех, кто использует типовую конфигурацию УТ11, документы «ЗаданиеНаПеревозку» поэтому рассмотрим его подробнее, используя технологический журнал:

Шаг первый: С помощью функции ПолучитьСтруктуруДанных() найдем имя в СУБД поля «КлючСвязи», в нашей базе:

Имя таблицы хранения     Имя поля хранения    Метаданные
Document515.VT3776       Fld3784              Документ.ЗаданиеНаПеревозку.ТабличнаяЧасть.Маршрут.Реквизит.КлючСвязи
Document515.VT3788       Fld3790              Документ.ЗаданиеНаПеревозку.ТабличнаяЧасть.Распоряжения.Реквизит.КлючСвязи

С точки зрения 1С табличная часть – дочерний объект, поэтому в тексте запроса события SDBL пишется Document515.VT3788, а с точки зрения SQL – отдельная таблица, поэтому в тексте запроса события DBMSSQL пишется Document515_VT3788

При анализе недостающих индексов в MS SQL в нашей базе поле Fld3784 (КлючСвязи) попадало в TOP по стоимости.

Шаг второй: настроить logcfg.xml с отбором по содержимому запроса и планом DBMSSQL (Кстати, на сайте Infostart до сих пор не было примера настройки like property)

<?xml version="1.0"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
<log location="C:\Log\Logs\UT" history="24">
<event>
<eq property="name" value="DBMSSQL"/>
<like property="sql" value="%Fld3784%"/>
</event>
<property name="all"/>
</log>
<plansql />
</config>

Шаг третий: собрать и расшифровать технологический журнал до изменения

Описание числовых столбцов находится на ИТС:

  • Rows
  • Executes,
  • EstimateRows,
  • EstimateIO,
  • EstimateCPU,
  • AvgRowSize,
  • TotalSubtreeCost,
  • EstimateExecutions,
  • StmtText.

Ниже фрагмент журнала, содержащий поле Fld3784 (КлючСвязи):

47, 49, 1, 0, 0.0542, 103, 1.69, 1,

Nested Loops(Left Outer Join, WHERE:([ut-main].[dbo].[_Document515_VT3788].[_Fld3790] as [T3].[_Fld3790]=[ut-main].[dbo].[_Document515_VT3776].[_Fld3784] as [T5].[_Fld3784]))

Поле Fld3784 (КлючСвязи) используется при соединении табличных частей, оператор Nested Loops, обработано 47 строк, загрузка CPU 0.0542, стоимость 1.69.

Контекст запроса Обработка. ГрафикТранспортаИСервиса.Форма.Форма.Модуль.ОбновитьСписокРаспоряженийНаДоставку

По контексту найдем запрос, фрагмент ниже:

Выбрать
...
ИЗ Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаданиеНаПеревозку.Маршрут КАК ЗаданиеНаПеревозкуМаршрут
ПО ЗаданиеНаПеревозкуРаспоряжения.КлючСвязи = ЗаданиеНаПеревозкуМаршрут.КлючСвязи

Одно условие соединения.

Шаг четвертый: Добавим индекс по полю Fld3784 (КлючСвязи), соберем журнал

План запроса изменился, фрагмент ниже

52, 52, 1.01, 0.00313, 0.000158, 33, 0.108, 35.9,

Index Seek(OBJECT:([ut-main].[dbo].[_Document515_VT3776].[_Document515_VT3776_1] AS [T5]), SEEK:([T5].[_Fld1420]=[@P3] AND [T5].[_Fld3784]=[ut-main].[dbo].[_Document515_VT3788].[_Fld3790] as [T3].[_Fld3790]) ORDERED FORWARD)

Поле Fld3784 (КлючСвязи) используется при соединении табличных частей, оператор Index Seek, обработано 52 строки, загрузка CPU 0.000158, стоимость 0. 108.

Контекст запроса Обработка. ГрафикТранспортаИСервиса.Форма.Форма.Модуль.ОбновитьСписокРаспоряженийНаДоставку

При сопоставимом объеме данных загрузка CPU уменьшилась в 343 раза, стоимость в 15 раз.

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

Шаг пятый: используем штатный индекс табличной части. На нашем знакомом вебинаре я узнал: чтобы улучшить запрос, его нужно сделать более конкретным. Отключим созданный индекс по полю Fld3784 (КлючСвязи), а в запрос добавим условие по ссылке, фрагмент ниже:

Выбрать
...
ИЗ Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаданиеНаПеревозку.Маршрут КАК ЗаданиеНаПеревозкуМаршрут
ПО ЗаданиеНаПеревозкуРаспоряжения.КлючСвязи = ЗаданиеНаПеревозкуМаршрут.КлючСвязи
И ЗаданиеНаПеревозкуРаспоряжения.Ссылка = ЗаданиеНаПеревозкуМаршрут.Ссылка

Два условия соединения. Этот способ позволяет не создавать индекс, но влияет только на один запрос.

План запроса изменился, фрагмент ниже

47, 47, 1, 0.00387, 0.000169, 88, 0.215, 54.9,

Clustered Index Seek(OBJECT:([ut-main].[dbo].[_Document515_VT3776].[_Document515_VT3776_SK] AS [T5]), SEEK:([T5].[_Fld1420]=[@P3] AND [T5].[_Document515_IDRRef]=[ut-main].[dbo].[_Document515_VT3788].[_Document515_IDRRef] as [T3].[_Document515_IDRRef]),  WHERE:([ut-main].[dbo].[_Document515_VT3788].[_Fld3790] as [T3].[_Fld3790]=[ut-main].[dbo].[_Document515_VT3776].[_Fld3784] as [T5].[_Fld3784]) ORDERED FORWARD)

Поле Fld3784 (КлючСвязи) используется при соединении табличных частей, оператор Clustered Index Seek, обработано 47 строк, загрузка CPU 0.000169, стоимость 0. 215.

Контекст запроса Обработка. ГрафикТранспортаИСервиса.Форма.Форма.Модуль.ОбновитьСписокРаспоряженийНаДоставку

Оператор Clustered Index Seek имеет параметры SEEK и WHERE, то есть сначала использует индекс, для поиска по полю «Ссылка», потом происходит частичное сканирование для поиска по полю «КлючСвязи».

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

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

  1. Анализируется использование временных таблиц.
  2. Методика может применяться  для других СУБД.
  3. Виден контекст выполнения (место вызова) запроса.
  4. Не нужен доступ к серверу СУБД.

Соберем технологический журнал с настройками

<?xml version="1.0"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
<log location="C:\Log\Logs\UT" history="24">
<event>
<eq property="name" value="DBMSSQL"/>
<like property="planSQLText" value="%Nested Loops%"
</event>
<property name="all"/>
</log>
<plansql />
</config>

Для анализа технологического журнала применим скрипт

time egrep -e '^[0-9]{5,9}.*((Nested Loops)|(Clustered Index Seek.*WHERE))' -h -R --include '21030909.log' \
| sed "s/\bT[0-9][0-9][0-9]\b/.+/g; s/\bT[0-9][0-9]\b/.+/g; s/\bT[0-9]\b/.+/g" \
| sed "s/Join,/Join./g; s/Nested/,Nested/g; s/Clustered/,Clustered/g" \
| sed 's/\],/../g; s/\]/./g; s/\[/./g' \
| sed -e 's/),/../g' \
| sed -e 's/)/./g' \
| sed -e 's/(/./g' \
| awk -F',' '{Cpu[$10]+=$5; if ($1>100) {Rows[$10]+=$1}; Text[$10] = "^[0-9]{4,9}.*"$10"$"} \
END {for (i in Text) {printf "\n%15d\t%15d\t%90-s", Rows[i], Cpu[i], Text[i]}}' \
| sort -rnb \
| head -n20 > result.txt

1. Начинаем замер времени, выбираем из каталога файлы по маске, фрагменты плана запроса содержат Nested Loops или Clustered Seek.*WHERE и начинаются с числа большего 999. То есть оператор обработал большое количество строк запроса. Подробнее смотрите Шаг третий.

2. Имена временных таблиц заменяем на .+ чтобы унифицировать текст запросов.

3. Расставляем запятые, чтобы удобно разбирать awk

4. - 7. Заменяем символы скобок на точки

8. Оператором awk группируем строки плана по оператору, суммируем количество строк запросов и CPU.

9. Группированные строки выводим в таблицу, но добавляем специальные символы до и после фрагмента плана $10

10. Сортируем по убыванию количества строк

11. Выводим первые 20 строк в файл result.txt, например


 486080	0	^[0-9]{4,9}.*Nested Loops.Inner Join. OUTER REFERENCES:..Expr1030.. .Expr1031.. .Expr1032...$
 455114	0	^[0-9]{4,9}.*Nested Loops.Inner Join. OUTER REFERENCES:...+..._Fld18379_TYPE.. ..+..._Fld18379_RTRef.. ..+..._Fld18379_RRRef...$
 373088	0	^[0-9]{4,9}.*Nested Loops.Inner Join. OUTER REFERENCES:...+..._IDRRef.. .Expr1037.. WITH ORDERED PREFETCH.$
 156234	0	^[0-9]{4,9}.*Nested Loops.Inner Join. OUTER REFERENCES:..Expr1027.. .Expr1028.. .Expr1029...$
 128007	0	^[0-9]{4,9}.*Nested Loops.Inner Join.$          
 114289	0	^[0-9]{4,9}.*Nested Loops.Inner Join. OUTER REFERENCES:..Expr1012.. .Expr1013.. .Expr1011...$

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

Если файл слишком большой, разбейте его на меньшие файлы bash split. Лично меня количество строк запросов, обработанных за час Nested Loops шокировало. Буду создавать индексы или индексировать временные таблицы. Посмотрю по комментариям: дополнять эту статью или оформить отдельную.

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

Удачи всем !

P.S. Удалось получить разрешение от Виктора Богачева на публикацию, см. комментарий № 18.

 

технологический bash context регулярные Богачев grep egrep awk gawk cat sed print printf echo sort logcfg

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

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

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2959    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5094    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

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

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7624    158    ZAOSTG    67    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5956    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8845    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5095    a.doroshkevich    20    

72

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

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

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

11.10.2023    16163    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. ildary 21 12.03.21 14:25 Сейчас в теме
Ещё одна прекрасная статья, спасибо!
2. w.r. 643 13.03.21 16:43 Сейчас в теме
Можно и без тех журнала. В MsSQL выбрать топ медленных запросов. Посмотреть запросы на наличие join и по каким полям. Дальше расшифровать с помощью структуры базы данных. Посмотреть есть ли индексы на этих полях и если нет, то создать
3. vasilev2015 2686 13.03.21 18:03 Сейчас в теме
(2) Здравствуйте !

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

Добавить индекс проще, чем переписать запрос.

Это частный случай не отменяет правило:
"Оптимизация топ медленных запросов приносит максимальную пользу".
15. w.r. 643 15.03.21 17:36 Сейчас в теме
(3)

Если запрос не «медленный» и выполняется относительно быстро, то и смысла проводить преждевременную оптимизацию нет

Есть золотое правило «работает - не трогай!»
EMelihoff; +1 Ответить
16. vasilev2015 2686 15.03.21 17:42 Сейчас в теме
(15)
выполняется относительно быстро


Есть несколько показателей "здоровья" системы.

Здесь речь про загруженность CPU. Несколько "быстрых" запросов могут все испортить.

Кому-то пригодится, как мне.
Lacoste4life; akR00b; +2 Ответить
21. w.r. 643 16.03.21 14:17 Сейчас в теме
(16)

Пока не портят смысла лезть нет и творить индексы там, где они не нужны
4. berezdetsky 620 13.03.21 19:30 Сейчас в теме
(3) "Преждевременная оптимизация" - корень чего? :-)

Добавить индекс проще, но при этом растут затраты на запись. А вот анализ выгоды уже может стать сложнее, чем переписать запрос.
mitia.mackarevich; +1 Ответить
6. vasilev2015 2686 13.03.21 21:58 Сейчас в теме
(4) Здравствуйте !

Да, индексы влекут накладные расходы.

Но использовать представление missingindex - общепринятая практика.

Посмотреть со стороны технологического журнала было интересно.
17. w.r. 643 15.03.21 18:08 Сейчас в теме
(4)

В Postgres ещё хуже. Там особая архитектура, где строка таблицы tuple по сути отдельная сущность. При обновлении любого поля строки перестраиваются все индексы, которые относятся к таблице. Даже если поле не относится к индексируемому никак

Поэтому лишние индексы здесь добавляют даже избыточную нагрузку на запись в бд
5. BackinSoda 13.03.21 19:42 Сейчас в теме
Зашёл про вложенные циклы почитать, а тут про индексы ))
7. vasilev2015 2686 13.03.21 21:58 Сейчас в теме
(5) Здравствуйте !

Переименовал "Соединение вложенными циклами"
8. Yashazz 4709 13.03.21 22:09 Сейчас в теме
Простите, а Богачёв разрешал вам лично выкладывать такие материалы в публичный доступ?
9. vasilev2015 2686 13.03.21 22:13 Сейчас в теме
(8) Здравствуйте, Яков !

Материал сделан с использованием общедоступного видео.

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

Подробности в личку.
14. Yashazz 4709 15.03.21 11:14 Сейчас в теме
(9) Нет, подробности сюда. Потому что все курсы Богачёва, просмотренные мной в своё время, имели прямое и недвусмысленное указание на запрет публичного распространения без письменного разрешения правообладателя. Поэтому, автор, обоснуйте свою позицию, приведите доказательства. А то банальным плагиатом пахнет.
18. vasilev2015 2686 15.03.21 20:57 Сейчас в теме
Здравствуйте, Яков !

Как правило, самые трудные вопросы - самые интересные.

В приложении - скрин электронного письма с разрешением на публикацию.
Прикрепленные файлы:
user1534961; +1 Ответить
20. Yashazz 4709 16.03.21 13:29 Сейчас в теме
(14) Вопрос снят, автор получил разрешение. Извиняюсь за резковатый тон.
10. triviumfan 92 14.03.21 23:55 Сейчас в теме
Целая статья про 1 запрос в УТ11?!
Очередная ошибка разработчиков УТ, в конфе есть места где и по ссылке соединение. Когда-то и я натыкался на него (https://forum.infostart.ru/forum34/topic202686).
А вообще, хоть индекса и нет, но запрос отрабатывает мгновенно, поэтому я и не стал создавать индекс вручную и даже переписывать запросы. На новом серваке так вообще никаких проблем.

Поле Fld3784 (КлючСвязи) используется при соединении табличных частей, оператор Clustered Index Seek, обработано 47 строк, загрузка CPU 0.000169, стоимость 0.215.

Обработано 47 строк. Беда...
11. vasilev2015 2686 15.03.21 08:54 Сейчас в теме
(10) Здравствуйте !

Спасибо за комментарий :-)) Сорок семь строк - это супер !!

Старался показать последовательность рассуждений.

Во второй части статьи ищем и находим операторы сканирования от 999 строк.

А это почти в двадцать раз больше, чем сорок семь :-)) Рост налицо !
12. kiset 15.03.21 09:42 Сейчас в теме
Приятно видеть, что ты занимаешься тем, что приносит и пользу, и удовольствие. :)
Спасибо за статью, было любопытно почитать.
13. vasilev2015 2686 15.03.21 09:51 Сейчас в теме
(12) Спасибо на добром слове.
19. vasilev2015 2686 15.03.21 21:04 Сейчас в теме
22. akR00b 22 08.09.21 08:48 Сейчас в теме
Очень нравятся Ваши статьи! Давайте еще!
Оставьте свое сообщение