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

Выбрать клиенов (маленькая таблица) которых нет в лога (большая)

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), а затем перейти к клиентам?

в аську бы сткунулся, да :)
или мне или олегу

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) к обоим таблицам

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 лучше?

Crimean,

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

PS: Мой оптимизатор решил, что дешевле спросить тут :)

msLex,

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

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

В реальном запросе и мердж и хеш дали одинаково высокие результаты.

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


Ещё раз спасибо.

> может hash лучше?

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

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

на моих тестовых данных hash и merge стоят условно одинаково, merge чуть быстрее, скажем 6:4.5
а запроса без хинта я не дождался - на 15 минутах отменил

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 - то брался нестед лупс, и ответа дождаться было не реально :)

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.