Оптимизатор и Nested Loops

Excel
Дата: 19.02.2010 17:53:57
Выбрать клиенов (маленькая таблица) которых нет в лога (большая)

select b.ClientCode
from branchs b (nolock)
where b.id not in (select distinct(no) from sv_LogInfo where ProcessID = 10)

Оптимизатор предлагает вложенный цикл со сканом по большой таблице:

  |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([b].[Id]))
|--Clustered Index Scan(OBJECT:([BASE].[dbo].[Branchs].[IBranchId] AS [b]))
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([BASE].[dbo].[LogInfo].[ILogInfoDate] AS [x]), WHERE:([BASE].[dbo].[Branchs].[Id] as [b].[Id]=[BASE].[dbo].[LogInfo].[No] as [x].[No] AND [BASE].[dbo].[LogInfo].[ProcessId] as [x].[ProcessId]=(10)))

Как бы заставить его сначала выбрать distinct(no), а затем перейти к клиентам?
Crimean
Дата: 19.02.2010 17:58:52
в аську бы сткунулся, да :)
или мне или олегу
Crimean
Дата: 19.02.2010 18:08:41
p.s.

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

select b.ClientCode
from branchs b
where b.id not in (select i.no from LogInfo i where i.ProcessID = 10)
option ( merge join )

p.s.

если все еще на 2000 - добавить with (nolock) к обоим таблицам
msLex
Дата: 19.02.2010 18:20:40
Crimean
p.s.

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

select b.ClientCode
from branchs b
where b.id not in (select i.no from LogInfo i where i.ProcessID = 10)
option ( merge join )

p.s.

если все еще на 2000 - добавить with (nolock) к обоим таблицам


может hash лучше?
Excel
Дата: 19.02.2010 18:28:01
Crimean,

Да, спасибо. Подошло.

PS: Мой оптимизатор решил, что дешевле спросить тут :)
Excel
Дата: 19.02.2010 18:30:30
msLex,

А покажите как hash?

Я привел упрощенный запрос, в реальном у меня от перестановки одного условия реактивный хешмеш заменяется на бесконечный нестед лупс.
Excel
Дата: 19.02.2010 18:38:38
В реальном запросе и мердж и хеш дали одинаково высокие результаты.

"На практике хинты используются очень редко."
Дошла очередь :)


Ещё раз спасибо.
Crimean
Дата: 19.02.2010 18:43:57
> может hash лучше?

а может и лучше.. все от данных зависит :)

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

на моих тестовых данных hash и merge стоят условно одинаково, merge чуть быстрее, скажем 6:4.5
а запроса без хинта я не дождался - на 15 минутах отменил
Excel
Дата: 19.02.2010 19:42:54
select b.id, max(l.date)
from branchs b
left join sv_LogInfo as l  
	on (l.no = b.id 
	and l.ProcessID = 10)
where b.kind = 6 and b.mode & 128 = 0
Group by b.id, b.ClientCode, b.name
order by max(l.date) desc


интересно что план зависил от того куда поместить l.ProcessID = 10
если в where то использовался хеш, а если в on - то брался нестед лупс, и ответа дождаться было не реально :)
msLex
Дата: 20.02.2010 11:30:41
Excel
select b.id, max(l.date)
from branchs b
left join sv_LogInfo as l  
	on (l.no = b.id 
	and l.ProcessID = 10)
where b.kind = 6 and b.mode & 128 = 0
Group by b.id, b.ClientCode, b.name
order by max(l.date) desc


интересно что план зависил от того куда поместить l.ProcessID = 10
если в where то использовался хеш, а если в on - то брался нестед лупс, и ответа дождаться было не реально :)




если l.ProcessID = 10 поместить в where то left join превратиться в inner join.