Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Здравствуйте.
Есть вот такой запрос
select h.LocalEstimateEntryID, bl.OperationID
from dwh_cg.dbo.Budget_D_HierarchicalBus h
left join cg.dbo.BudgetLinks bl
	on h.LocalEstimateEntryID = bl.BudgetItemID /*or h.LocalEstimateID = bl.BudgetID*/

В плане join выполняется методом Merge, и результат получается за пару секунд.
+ План запроса 1
  |--Merge Join(Left Outer Join, MERGE:([h].[LocalEstimateEntryID])=([bl].[BudgetItemID]), RESIDUAL:([DWH_CG].[dbo].[Budget_D_HierarchicalBus].[LocalEstimateEntryID] as [h].[LocalEstimateEntryID]=[CG].[dbo].[BudgetLinks].[BudgetItemID] as [bl].[BudgetItemID]))
       |--Index Scan(OBJECT:([DWH_CG].[dbo].[Budget_D_HierarchicalBus].[IX_Budget_D_HierarchicalBus_LocalEstimateEntryID_LocalEstimateID] AS [h]), ORDERED FORWARD)
       |--Sort(ORDER BY:([bl].[BudgetItemID] ASC))
            |--Index Scan(OBJECT:([CG].[dbo].[BudgetLinks].[_dta_index_BudgetLinks_11_1038626743__K12_K2_K5_K6_K4] AS [bl]))

А если раскомментировать второе условие, то оптимизатор применяет уже nested loops, и результат появляется минут через пять.
+ План запроса 2
  |--Parallelism(Gather Streams)
       |--Nested Loops(Left Outer Join, WHERE:([DWH_CG].[dbo].[Budget_D_HierarchicalBus].[LocalEstimateEntryID] as [h].[LocalEstimateEntryID]=[CG].[dbo].[BudgetLinks].[BudgetItemID] as [bl].[BudgetItemID] OR [DWH_CG].[dbo].[Budget_D_HierarchicalBus].[LocalEstimateID] as [h].[LocalEstimateID]=[CG].[dbo].[BudgetLinks].[BudgetID] as [bl].[BudgetID]))
            |--Index Scan(OBJECT:([DWH_CG].[dbo].[Budget_D_HierarchicalBus].[IX_Budget_D_HierarchicalBus_LocalEstimateEntryID_LocalEstimateID] AS [h]))
            |--Table Spool
                 |--Index Scan(OBJECT:([CG].[dbo].[BudgetLinks].[_dta_index_BudgetLinks_11_1038626743__K12_K2_K5_K6_K4] AS [bl]))

Можно ли избежать nested loops во втором случае (кстати, таких or должно быть еще два)? Какие варианты оптимизации запроса могут быть?

Сообщение было отредактировано: 12 дек 13, 15:28
12 дек 13, 15:12    [15283074]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
option (merge join) ?
12 дек 13, 15:15    [15283108]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
попробовал в первую очередь, сервер ответил
Обработчик запросов не может предоставить план запроса из-за подсказок, определенных в запросе. Заново запустите запрос без указания подсказок и без использования SET FORCEPLAN.
12 дек 13, 15:29    [15283232]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35386
Блог
обновить статистику, если не поможет, то
left merge join
или
left join с одним условием + union + left join с другим условием (тут нужно будет проверить цифры)
12 дек 13, 15:30    [15283235]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
left merge join тоже пробовал, ответ такой же, как и на option(merge join). Интересно, кстати, почему? SET FORCEPLAN установлен в OFF, merge - единственный хинт.
12 дек 13, 15:43    [15283347]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
aleks2
Guest
MyNiGoo
left merge join тоже пробовал, ответ такой же, как и на option(merge join). Интересно, кстати, почему? SET FORCEPLAN установлен в OFF, merge - единственный хинт.


Страдалец, ознакомился ли ты алгоритмом Merge?
Ознакомься - полегчает и глупых вопросов не будет.
12 дек 13, 15:50    [15283393]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
Ознакамливался, фраза "В редких случаях может быть несколько предложений равенства, но столбцы слияния берутся только из некоторых доступных предложений равенства" меня не смутила. А должна была?
12 дек 13, 16:07    [15283499]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
Glory
Member

Откуда:
Сообщений: 104751
MyNiGoo
Ознакамливался, фраза "В редких случаях может быть несколько предложений равенства, но столбцы слияния берутся только из некоторых доступных предложений равенства" меня не смутила. А должна была?

А как сортировать то по двум столбцам заданным через OR ? По очереди что ли ?
12 дек 13, 16:12    [15283531]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
aleks2
Guest
MyNiGoo
Ознакамливался, фраза "В редких случаях может быть несколько предложений равенства, но столбцы слияния берутся только из некоторых доступных предложений равенства" меня не смутила. А должна была?


1. Детсад.
2. Merge (слияние) это возможно только на двух ОДНОЗНАЧНО УПОРЯДОЧЕННЫХ последовательностях.
3. Упорядочить однозначно последовательность по двум полям в связке OR невозможно.
12 дек 13, 16:14    [15283545]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
MyNiGoo
left merge join тоже пробовал, ответ такой же, как и на option(merge join).

ну так это одно и тоже в данном случае :)
12 дек 13, 16:16    [15283556]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
aleks2, то есть случай "некоторых доступных предложений равенства" работает только с and?

как вы думаете, а чем тогда оптимизатор не устроил hash join?
12 дек 13, 16:59    [15283781]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Насколько я понял, у вас такая ситуация и такой (по форме) план.
select
	t1.a, t2.b, t2.c
from
	t1
	left join t2 on t1.b = t2.b or t1.c = t2.c

Картинка с другого сайта.

Merge Join
Merge Join требует хотя бы один экви-предикат, кажется что у вас их целых два, но на самом деле ни одного, поскольку ни один из них не может служить для однозначного критерия по которым происходит слияние. Единственный вариант, который может рассматривать сервер, это слить без всяких условий а потом отфильтровать, вроде такого:
with cte as(
	select 
		a = isnull(t1.a,-1), t2.b, t2.c
	from
		t1
		full join t2 on t1.b = t2.b or t1.c = t2.c
)
select * from cte where cte.a > -1

Картинка с другого сайта.
Но это не самый хороший способ.

Еще варианты

1. Самый простой, перепишите запрос на union:

select t1.a, t2.b, t2.c 
from
	t1
	left join t2 on t1.b = t2.b
union
select t1.a, t2.b, t2.c from
	t1
	left join t2 on t1.c = t2.c;

План будет зависеть от ваших данных и индексов.

2. Создать индексы по соединяемым полям и форсировать поиск
В моем случае уже есть кластерный индекс на поле t2.b, нужно еще по t2.c
create index ix_c on t2(c)

Запрос:
select t1.a, t2.b, t2.c from
t1
left join t2 with(forceseek) on t1.b = t2.b or t1.c = t2.c;

План (в моем случае):
Картинка с другого сайта.

3. (Это был бы мой выбор =)) Редизайн БД. Причина ваших мучений, не очень хороший дизайн базы. Лучше не проектировать таким образом, чтобы пришлось выполнять подобные операции. Если бы были простые таблицы пар (LocalEstimateEntryID, BudgetItemID) и (LocalEstimateID, BudgetID) проблем бы не было.

Сообщение было отредактировано: 12 дек 13, 17:30
12 дек 13, 17:01    [15283791]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo,

Has Join-у тоже нужен хотя бы один экви предикат, причем даже для full join.

п.с.
Уважаемые модераторы, не могли бы картиночку в предыдущем посте поправить, а то ссылка случайно попала в тег SRC, спасибо.
12 дек 13, 17:04    [15283806]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
SomewhereSomehow, полностью согласен по поводу редизайна БД, вчера по этому поводу уже весь мозг проклевал человеку, который сделал так. То, что or "портит" экви-предикат, сразу и не догадался.
Нужные индексы существуют, я вот и удивлялся, чего оптимизатор их никак не использует. Хинтами не увлекаюсь, про forceseek не знал.
Спасибо всем откликнувшимся, +100500 в карму SomewhereSomehow за подробное объяснение и советы.
12 дек 13, 17:52    [15284158]     Ответить | Цитировать Сообщить модератору
 Re: избежать nested loops  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
MyNiGoo
Нужные индексы существуют, я вот и удивлялся, чего оптимизатор их никак не использует. Хинтами не увлекаюсь, про forceseek не знал.

То что видно внизу, в плане (когда соединяются два индекса одной таблицы, а потом идет агрегация), называется index intersection или index union. Это дорогая операция, оптимизатор редко ее рассматривает (по-этому и не использовал индексы, как вы выразились), но когда указана подсказка forceseek, то рассматривает.

Если не хочется использовать хинты, то помогает переписывание через union, но тут нужно быть аккуратным, чтобы не нарушить семантику (т.е. смысл) запроса. Например,тот топорный вариант что привел я с union (просто как идею), явно что-то может нарушить, но тут вы уже сами смотрите на определения колонок, какие уникальные, какие nullable и все тестируйте. Ну, а редизайн, конечно - лучше. Удачи =)
12 дек 13, 18:06    [15284224]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить