Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
запрос с loop join
select i.name,t.name from instruments i left loop join assettypes t on t.assettype_id = i.assettype_id option (force order,maxdop 1)/SRC]

результат statistics io:
[src]Table 'AssetTypes'.  Scan count 1, logical reads 101519, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Instruments'. Scan count 1, logical reads    536, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

3 последовательных результата statistics time:
   CPU time = 297 ms,  elapsed time = 871 ms.
   CPU time = 422 ms,  elapsed time = 894 ms.
   CPU time = 391 ms,  elapsed time = 1030 ms.

запрос с hash join
select i.name,t.name from instruments i left hash join assettypes t on t.assettype_id = i.assettype_id option (force order,maxdop 1)

результат statistics io:
Table 'Workfile'.    Scan count 0, logical reads 0,   physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'.   Scan count 0, logical reads 0,   physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AssetTypes'.  Scan count 1, logical reads 2,   physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Instruments'. Scan count 1, logical reads 536, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

3 последовательных результата statistics time:
   CPU time = 109 ms,  elapsed time = 1117 ms.
   CPU time = 109 ms,  elapsed time = 943 ms.
   CPU time = 109 ms,  elapsed time = 959 ms.


В таблице instruments 50759 строк.
В таблице assettypes 11 строк, столбец assettype_id - primary key clustered.

При loop join сканируется таблица instruments и для каждой ее строки считываются по две страницы таблицы assettypes - итого 101519 страницы.
Т.е. для loop join характерно малое значение CPU и большое значение Reads.
При hash join один раз считывается вся таблица assettypes (две страницы), создаетcя hash таблица, в которой прописывается hash ключ и RID строки.
Далее поиск в assettypes производится путем вычисления hash ключа, поиска в RID в hash таблице, и чтения нужной записи.
Т.е. для hash join характерно большее значение значение CPU по сравнению с loop, но меньшее значение Reads.

Однако приведенный пример свидетельстует об обратном: hash join выполняется медленнее и это при том, что кол-во чтений на порядки меньше чем при loop. И, как ни страано, использование CPU при hash три раза меньше, чем при loop.
Почему в этом случае hash медленне чем loop? И почему CPU при hash меньше чем при loop?
27 дек 17, 10:02    [21065630]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
вопрос чисто теоретический или таки какое-то реальное применение есть? ))
27 дек 17, 10:10    [21065652]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
Вопрос возник из-за того, что теория расходится с практикой: кол-во чтений меньше, CPU меньше, а общее время выполнения больше.
Это я замерил и привел пример для опытного запроса на небольших таблицах. В реальных запросах, на больших таблицах - отличия очень существенны. Причем сам оптимизатор выбирает hash join, но при принудительном переводе на loop join время исполнения уменьшается, при том что Read (особенно) и CPU больше.
27 дек 17, 10:19    [21065672]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
таки вы запускали тесты друг за другом? ))
27 дек 17, 10:23    [21065698]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
и да вы уверены в своих хинтах?))
а то, то что у вас написано очень смахивает на попытку подогнать оптимизатор под, то что вы хотите от него получить, а не то он должен сделать.
27 дек 17, 10:26    [21065710]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
Да, запускал друг за другом так, чтобы все данные были закешированы и исключить чтение с диска.
dao
и да вы уверены в своих хинтах?))
а то, то что у вас написано очень смахивает на попытку подогнать оптимизатор под, то что вы хотите от него получить, а не то он должен сделать.

Абослютно верно. То, что "должен сделать" оптимизатор, на практике работает медленнее, чем "попытка подогнать оптимизатор под, то что я хоту от него получить".
Так почему при меньшем количестве Reads и CPU соединение с hash выполняется медленнее, чем loop?
27 дек 17, 10:56    [21065797]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
Prolog
Да, запускал друг за другом так, чтобы все данные были закешированы и исключить чтение с диска.
dao
и да вы уверены в своих хинтах?))
а то, то что у вас написано очень смахивает на попытку подогнать оптимизатор под, то что вы хотите от него получить, а не то он должен сделать.

Абослютно верно. То, что "должен сделать" оптимизатор, на практике работает медленнее, чем "попытка подогнать оптимизатор под, то что я хоту от него получить".
Так почему при меньшем количестве Reads и CPU соединение с hash выполняется медленнее, чем loop?


знавал я одного товарисча - он нафигачит хинтов, а потом разбирайся почему банальнейший запрос вешает нехилый такой сервак ))
"А чо? сейчас то работает быстро! а если станет медленно я другие хинты понаставлю" ))

а теперь повангую - есть некий запрос ( несколько запросов такого типа) в большой процедуре , в котором в where есть условия типа ""(@var1 is null or fields1=@var1) and (@var2 is null or fields2=@var2) and ... и т.д." и вот вы хинтами пытаетесь данный запрос ускорить? ))
27 дек 17, 11:06    [21065828]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
Гигабайт Мегабайтович Килобайтов
а теперь повангую - есть некий запрос ( несколько запросов такого типа) в большой процедуре , в котором в where есть условия типа ""(@var1 is null or fields1=@var1) and (@var2 is null or fields2=@var2) and ... и т.д." и вот вы хинтами пытаетесь данный запрос ускорить? ))

Такого нет.
Давайте не будем что-то предполагать, а обсуждать в рамках того запроса, который приведён в первом моем посте.
27 дек 17, 11:21    [21065897]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
aleks222
Guest
Prolog
Гигабайт Мегабайтович Килобайтов
а теперь повангую - есть некий запрос ( несколько запросов такого типа) в большой процедуре , в котором в where есть условия типа ""(@var1 is null or fields1=@var1) and (@var2 is null or fields2=@var2) and ... и т.д." и вот вы хинтами пытаетесь данный запрос ускорить? ))

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


Дык, "для hash join характерно большее значение значение CPU по сравнению с loop, но меньшее значение Reads" - это сферическая истина в вакууме при размере таблиц, стремящемся к бесконечности.

У вас мелкие таблички в памяти.
Со всеми вытекающими.
27 дек 17, 11:45    [21066037]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Prolog
hash join выполняется медленнее и это при том, что кол-во чтений на порядки меньше чем при loop. И, как ни страано, использование CPU при hash три раза меньше, чем при loop.
Почему в этом случае hash медленне чем loop? И почему CPU при hash меньше чем при loop?

Потому что CPU работает не с диском, а с оперативной памятью и с кэшем L3.
Посмотрите под отладчиком процесс MSSQL Server - и Вы увидите, как нерационально в данном конкретном случае идет работа с L3.
Вот почему.
27 дек 17, 11:49    [21066059]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
я не зря спросил в начале - это больше теоретический вопрос или практический.
Потому что с теоретической составляющей - такой вопрос можно повертеть. а с практической вопрос написан не правильно. Отсюда и сомнение в том вы понимаете все хинты которые стоят в данном запросе.
27 дек 17, 12:00    [21066120]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
dao
я не зря спросил в начале - это больше теоретический вопрос или практический.
Потому что с теоретической составляющей - такой вопрос можно повертеть. а с практической вопрос написан не правильно. Отсюда и сомнение в том вы понимаете все хинты которые стоят в данном запросе.

Напишите, как должен звучать правильно вопрос с практической стороны?
Хорошо, согласен, что не понимаю хинты, которые стоят в запросе. Объясните в чём моя ошибка? Что я не так понимаю?
27 дек 17, 12:35    [21066184]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
aleks222
Guest
Prolog
Что я не так понимаю?

Самое главное: ХИНТЫ - АБСОЛЮТНОЕ ЗЛО.

ЗЫ.
Если вы можете не ставить хинты - не ставьте.
Если вы НЕ можете не ставить хинты - не ставьте, все равно.
27 дек 17, 12:39    [21066193]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
вам прям так надо вывести овер 50 к строк? чет очень сомневаюсь
скорей всего есть ещё какие-то условия.

но пускай запрос в лоб - обычный left join и без всяких option, ибо то что написано в option применительно для для данного запроса - чушь.
27 дек 17, 12:53    [21066243]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
aleks222,
Из области: "Жизнь - абсолютное зло: в её результате наступает смерть".
Зачем-то хинты придумали, и с их помощью запросы работают быстрее. И это факт. Без хинтов и медленно - это абсолютное добро, с хинтами и быстро - это абсолютное зло. Может быть вы просто не умеете ими пользоваться?

Вопрос не про хинты. Вопрос про соединения: "Почему при меньшем количестве Reads и CPU соединение с hash выполняется медленнее, чем loop"? Хинты для того, чтобы получить статистику при разных типах соединений.
27 дек 17, 12:58    [21066271]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
В таблице instruments 50759 строк.
В таблице assettypes 11 строк

автор
instruments i left loop join assettypes t

0_o
Если ты хинтуешь луп джойн, будь добр слева указать маленькую таблицу, а справа большую. Ну и заодно FORCE ORDER для надежности.
27 дек 17, 13:01    [21066286]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Добрый Э - Эх
Guest
Prolog,

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

З.Ы.
И тут я буду с ним согласен.... Ты уже статистику пересобрал? Индексы, адексатные твоим условиям отбора и соединения построил? Ничего не помогло? Пошел на крайние меры - использование хинтов?
27 дек 17, 13:03    [21066292]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
dao
вам прям так надо вывести овер 50 к строк? чет очень сомневаюсь
скорей всего есть ещё какие-то условия.

но пускай запрос в лоб - обычный left join и без всяких option, ибо то что написано в option применительно для для данного запроса - чушь.


Теперь я понял: это вы не поняли о чём мой вопрос и всё время пытаетесь ответить на какой-то другой.

Тему закрываем. Считаю, что просто loop и hash
27 дек 17, 13:06    [21066306]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
Тему закрываем. Считаю, что просто loop и hash работают как-то не так, как я предполагаю.
27 дек 17, 13:07    [21066308]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Добрый Э - Эх
Guest
Prolog,

давай тогда зайдем с другого конца.... покажи первоисточники, на основании которых ты сделал выводы об алгоритмах работы NL и HJ
27 дек 17, 13:14    [21066330]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Провел эксперимент.

SELECT .. FROM Users #joint Tickets 

в #USERS 4 записи
в Tickets 250 тысяч

users inner LOOP join 6 % от батча
users inner HASH join 94% от батча

HASH : Scan count 5, logical reads 6565 248 ms
LOOP: Scan count 4, logical reads 609 5ms

Чуда, к счастью, не произошло, всё работает как запланировано.
27 дек 17, 13:17    [21066343]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
aleks222
Prolog
Что я не так понимаю?

Самое главное: ХИНТЫ - АБСОЛЮТНОЕ ЗЛО.

Абсолютное зло не думать над тем, что делаешь. Хинты полезный, нужный, удобный инструмент, если применять с головой.
27 дек 17, 13:19    [21066349]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
Cammomile
aleks222
пропущено...

Самое главное: ХИНТЫ - АБСОЛЮТНОЕ ЗЛО.

Абсолютное зло не думать над тем, что делаешь. Хинты полезный, нужный, удобный инструмент, если применять с головой.

после лет 20 программирования - ты присоединишься к темной стороне )))
27 дек 17, 13:34    [21066398]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Гигабайт Мегабайтович Килобайтов
Cammomile
пропущено...

Абсолютное зло не думать над тем, что делаешь. Хинты полезный, нужный, удобный инструмент, если применять с головой.

после лет 20 программирования - ты присоединишься к темной стороне )))

После того, как какой-нибудь падаван сделает with index, а затем магистр Йода у индекса таблицы disable сделает.
И выдаст сервер сообщение 315, и не сделает ничего, руками в стороны разведя и вздохнув всеми CPU.
И присоединится наш коллега к ситхам, и пошлет Дарта Вейдера с отборным легионом вырезать все хинты из кода...
27 дек 17, 13:43    [21066436]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение loop join с hasj join  [new]
dao
Member

Откуда: Москва
Сообщений: 771
Prolog
dao
вам прям так надо вывести овер 50 к строк? чет очень сомневаюсь
скорей всего есть ещё какие-то условия.

но пускай запрос в лоб - обычный left join и без всяких option, ибо то что написано в option применительно для для данного запроса - чушь.


Теперь я понял: это вы не поняли о чём мой вопрос и всё время пытаетесь ответить на какой-то другой.

Тему закрываем. Считаю, что просто loop и hash

я не пытаюсь ответить на другой вопрос - я пытаюсь понять зачем вы используете хинты .
27 дек 17, 13:47    [21066450]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить