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

Откуда: Moscow
Сообщений: 610
Доброго времени суток!

СУБД: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Есть такой запрос:

SELECT      R.IDReestr, CurDeb.IDCurrency AS IDCurrencyComission, 
			CurDeb.Number AS CurrencyComission, DComm.SummaDeb AS SummaComission, 
			DV.TypeValue
FROM   dbo.mVOReestr AS R WITH (NOLOCK) INNER JOIN
	dbo.mBLDocuments AS DComm WITH (NOLOCK) ON DComm.IDFinOper = R.IDPayIO INNER JOIN
	dbo.mBLDocValues AS DV WITH (NOLOCK) ON DV.IDDocument = DComm.IDDocument 
						AND DV.TypeValue IN (1009, 1013, 1038) INNER JOIN
	dbo.mBLTreeAccount AS TADeb WITH (NOLOCK) ON TADeb.IDAccount = DComm.IDAccountDeb INNER JOIN
	dbo.mBLCurrency AS CurDeb WITH (NOLOCK) ON CurDeb.IDCurrency = TADeb.IDCurrency



Таблица mBLDocuments содержит порядка 10млн строк. Оптимизатор использует Hash join при обьединении mBLDocuments и mBLDocValues. Я бы хотел что бы использовалось merge jion. На сколько я знаю условия MERGE это простое условие объединения и отсортированные столбцы по которым идет соединение.
Итак вопрос, возможно ли merge join при условии соединения:

IDDocument = DComm.IDDocument  AND DV.TypeValue IN (1009, 1013, 1038) 
31 май 12, 14:59    [12644154]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
andrew shalaev,

а когда пробовали явно написать "INNER MERGE JOIN", что получилось?
31 май 12, 15:04    [12644217]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Дмитрий_sql
Member

Откуда:
Сообщений: 35
Shakill,

http://msdn.microsoft.com/en-us/library/ms173815.aspx
31 май 12, 15:05    [12644240]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Shakill
а когда пробовали явно написать "INNER MERGE JOIN", что получилось?


Когда прописал с хинтом то оптимайзер решил:

1. .mVOReestr и mBLDocuments соединить hash join
2. Отсортировать полученный набор
3. Соединить mBLDocValues и полученный набор с помощью merge

такой план не является выгодным. Вообще писать хинтом не очень, мой вопрос скорее в том, какие условия надо создать что бы оптимайзер сам выбрал merge join без хинтов
31 май 12, 15:17    [12644359]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
andrew shalaev
Shakill
а когда пробовали явно написать "INNER MERGE JOIN", что получилось?


Когда прописал с хинтом то оптимайзер решил:

1. .mVOReestr и mBLDocuments соединить hash join
2. Отсортировать полученный набор
3. Соединить mBLDocValues и полученный набор с помощью merge

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

А условия - ну разве что сортировка хорошо паралелиться
31 май 12, 15:23    [12644428]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
а вообще готов спорить, что оптимизатор прав если статистика верная
31 май 12, 15:24    [12644440]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
andrew shalaev,
что и даже индекс есть по IDDocument и TypeValue, отсортированный в порядке DComm.IDDocument ?
31 май 12, 15:30    [12644489]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
denis2710
что и даже индекс есть по IDDocument и TypeValue, отсортированный в порядке DComm.IDDocument ?


У таблицы mBLDocValues есть кластерный индекс, как раз по полям IDDocument и TypeValue. Следовательно она отсортирована по полю IDDocument
31 май 12, 15:35    [12644558]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а попробовать так, если знаете какой план нужен:

from t1
inner hash join t2 on
inner merge join t3 on
inner loop join t4 on
option( force order )

и - да - можно пользовать скобки в конструкции from для определения порядка обработки
31 май 12, 15:47    [12644734]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
andrew shalaev,
может все же план запроса выложите.
31 май 12, 15:48    [12644740]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
denis2710
andrew shalaev,
может все же план запроса выложите.


только если так

К сообщению приложен файл. Размер - 32Kb
31 май 12, 16:09    [12645005]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Нужно ли это в джоине:
DV.TypeValue IN (1009, 1013, 1038) 


?
31 май 12, 16:14    [12645057]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
andrew shalaev,
из разряда "лечу по фотографии".
IX_mdv_TypeValue,IX_mdoc_iddoc_incl скрипты на создание.
Ну если оооочень секретно,то не надо.Шпионы кругом!!
31 май 12, 16:22    [12645150]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
denis2710
andrew shalaev,
из разряда "лечу по фотографии".
IX_mdv_TypeValue,IX_mdoc_iddoc_incl скрипты на создание.
Ну если оооочень секретно,то не надо.Шпионы кругом!!


CREATE NONCLUSTERED INDEX [IX_mDoc_IDDoc_include] ON [dbo].[mBLDocuments] 
(
	[IDAccountDeb] ASC,
	[IDDocument] ASC,
	[IDFinOper] ASC
)
INCLUDE ( [SummaDeb]) 



CREATE NONCLUSTERED INDEX [IX_mDV_TypeValue] ON [dbo].[mBLDocValues] 
(
	[TypeValue] ASC
)
31 май 12, 16:31    [12645251]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
pkarklin
Нужно ли это в джоине:
DV.TypeValue IN (1009, 1013, 1038) 


?


Вынес
DV.TypeValue IN (1009, 1013, 1038) 
в предложение WHERE, но общей картины это не изменило
31 май 12, 16:33    [12645264]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
andrew shalaev
pkarklin
Нужно ли это в джоине:
DV.TypeValue IN (1009, 1013, 1038) 


?


Вынес
DV.TypeValue IN (1009, 1013, 1038) 
в предложение WHERE, но общей картины это не изменило




почитайте про merge join и посмотрите на свои индексы.
31 май 12, 16:45    [12645409]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
для MERGE join надо иметь два отсортированных по полям соединения(эквивалентностью) множества. При таких индексах сортировки изначально нет, а делать ее на лету видимо слишком дорого. Может быть при таких индексах будет более оправдан данный вид соединения
CREATE NONCLUSTERED INDEX [IX_mDoc_IDDoc2_include] ON [dbo].[mBLDocuments] 
(
	[IDDocument] ASC,
        [IDAccountDeb] ASC,
	
	[IDFinOper] ASC
)
INCLUDE ( [SummaDeb]) 



CREATE NONCLUSTERED INDEX [IX_mDV_IDDocument] ON [dbo].[mBLDocValues] 
(
	[IDDocument] ASC
)
INCLUDE([TypeValue])
31 май 12, 17:13    [12645744]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Мистер Хенки,
ну вот зачем так сразу все рассказывать?
31 май 12, 17:18    [12645781]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
denis2710
Мистер Хенки,
ну вот зачем так сразу все рассказывать?

здесь форум друзей, нет?
Коллега вот недавно жаловался(не мне), что на скуле никто толком ответить не может, все только посылают или туманно намекают
31 май 12, 17:24    [12645826]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Мистер Хенки
для MERGE join надо иметь два отсортированных по полям соединения(эквивалентностью) множества. При таких индексах сортировки изначально нет, а делать ее на лету видимо слишком дорого. Может быть при таких индексах будет более оправдан данный вид соединения
CREATE NONCLUSTERED INDEX [IX_mDoc_IDDoc2_include] ON [dbo].[mBLDocuments] 
(
	[IDDocument] ASC,
        [IDAccountDeb] ASC,
	
	[IDFinOper] ASC
)
INCLUDE ( [SummaDeb]) 



CREATE NONCLUSTERED INDEX [IX_mDV_IDDocument] ON [dbo].[mBLDocValues] 
(
	[IDDocument] ASC
)
INCLUDE([TypeValue])



Спасибо! С такими индексами получается правильный джоин.

Мистер Хенки
здесь форум друзей, нет?
Коллега вот недавно жаловался(не мне), что на скуле никто толком ответить не может, все только посылают или туманно намекают


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



Меня смущает одно, у таблицы mBLDocValues есть кластерный индекс:
CREATE CLUSTERED INDEX [XPKmBLDocValues] ON [dbo].[mBLDocValues] 
(
	[IDDocument] ASC,
	[TypeValue] ASC
)


У таблицы mBLDocuments есть кластерный индекс

CREATE UNIQUE CLUSTERED INDEX [XPKmBLDocuments] ON [dbo].[mBLDocuments] 
(
	[IDDocument] ASC
)


Почему же оптимизатор не использовал их?
31 май 12, 17:49    [12646048]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Мистер Хенки,
автор
здесь форум друзей, нет?
я сомневаюсь.Я бы сказал коллег.
да я к тому,что человек вроде "что-то" знает (хотя 12645264 настораживает),работает(пишет секретный проект ),так почему бы ему самому дальше не разобраться,тем более вроде как сказали на что смотреть.
Если ему "не надо" было бы,то да,дать ответ и все,а тут вроде как вопрос несколько интересней,чем как написать запрос с группировкой и т.д и т.п.Но это чисто мое мнение.
31 май 12, 17:49    [12646050]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
andrew shalaev
Почему же оптимизатор не использовал их?

может стоит обновить статистику тогда update statistics with fullscan
31 май 12, 17:53    [12646097]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить оптимизатор выбрать merge join  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Мистер Хенки
andrew shalaev
Почему же оптимизатор не использовал их?

может стоит обновить статистику тогда update statistics with fullscan

Вот те на..
Да потому что он использовал покрывающие индексы.
Почитайте про структуру,использование кластерных и не кластерных индексов.
31 май 12, 18:00    [12646158]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить