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

Откуда: Львов
Сообщений: 504
Здравствуйте,
Есть 2 абсолютно одинаковые таблицы с композитным кластерным PK (из 5 полей) с абсолютно одинаковыми данными. Есть простейший LEFT JOIN с условием соединения по тем же 5-ти полям которые в кластерных индексах. Всю жизнь думал что SQL декларативный язык ;-) и порядок перечисления полей в ON не имеет значения. Оказывается имеет ))
0. Создание тестовых данных
drop table table1;
create table table1
(
    id1 int not null,
    id2 int not null,
    id3 int not null,
    id4 int not null,
    id5 int not null
);
create unique clustered index ix1 on table1(id1, id2, id3, id4, id5)

drop table table2;
create table table2 (
    id1 int not null,
    id2 int not null,
    id3 int not null,
    id4 int not null,
    id5 int not null
);
create unique clustered index ix2 on table2(id1, id2, id3, id4, id5);

insert into table1 (id1, id2, id3, id4, id5)
select top 10000
       row_number()over(order by 1/0)/1000,
       row_number()over(order by 1/0),
       row_number()over(order by 1/0),
       row_number()over(order by 1/0),
       row_number()over(order by 1/0)
from master..spt_values t1, master..spt_values t2
where t1.type = 'P' and t2.type = 'P'

insert into table2 (id1, id2, id3, id4, id5)
select top 10000
       row_number()over(order by 1/0)/1000,
       row_number()over(order by 1/0),
       row_number()over(order by 1/0),
       row_number()over(order by 1/0),
       row_number()over(order by 1/0)
from master..spt_values t1, master..spt_values t2
where t1.type = 'P' and t2.type = 'P'


1. Запрос 1. Все поля в ON идут по порядку как в кластерных индексах. Как и ожидалось, имеем Merge Join в плане
select
       t1.id1,
       t1.id2,
       t1.id3,
       t1.id4,
       t1.id5,
       t2.id5
from table1 t1
left join table2 t2 on
		t1.id1 = t2.id1 and
		t1.id2 = t2.id2 and
		t1.id3 = t2.id3 and
		t1.id4 = t2.id4 and
		t1.id5 = t2.id5
where
	t1.id1 = 6


2. Запрос 2. Поменяйте местами id3 и id4, имеем Hash Match в плане. Чудеса!
select
       t1.id1,
       t1.id2,
       t1.id3,
       t1.id4,
       t1.id5,
       t2.id5
from table1 t1
left join table2 t2 on
		t1.id1 = t2.id1 and
		t1.id2 = t2.id2 and
		t1.id4 = t2.id4 and
		t1.id3 = t2.id3 and
		t1.id5 = t2.id5
where
	t1.id1 = 6
2 янв 19, 17:11    [21777619]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
aleks222
Member

Откуда:
Сообщений: 486
Что мануалы и минуют тя глупые вопросы.

Оптимизатор выбирает "наилучший" план, но он ограничен во времени выбора.
2 янв 19, 18:52    [21777652]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
В обоих случаях
Optimization Level = FULL, 
Reason For Early Termination Of Statement Optimization = Good Enough Plan Found

никаких таймаутов здесь...
2 янв 19, 21:30    [21777692]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
aleks222
Member

Откуда:
Сообщений: 486
Богдан Гоцкий
никаких таймаутов здесь...


Ты суслика видишь? А он есть!
3 янв 19, 07:45    [21777796]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3040
Богдан Гоцкий,

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

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

Версия сервера-то какая, кстати? А то, может, вы там от 2005-го чудес ждете.
3 янв 19, 08:35    [21777799]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
В данном случае лечить хинтами ничего не нужно, достаточно просто в ON перечислить условия в том же порядке что и поля в кластерном индексе. Интерес чисто академический. Все интернеты доказывают что порядок условий в ON, WHERE и GROUP BY несущественен и ищут доказательства обратного. Я показал обратное и был сильно удивлен. В моей более чем 15-ти летней практике с сиквелом - первый раз такое вижу. Сначала думал что особенность версии сиквела, но нет, стабильно воспроизводиться на 2012, 2014, 2016, 2017 серверах. Более того, если запрос переписать через WHERE - такое же поведение. Получается что порядок предикатов в WHERE тоже играет роль. А для декларативного языка коим является SQL это дико неожиданно, согласитесь.
3 янв 19, 08:59    [21777801]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3040
Богдан Гоцкий
В данном случае лечить хинтами ничего не нужно, достаточно просто в ON перечислить условия в том же порядке что и поля в кластерном индексе.
Нет гарантии, что это будет работать всегда. Чтобы это понимать, достаточно знать, как в SQL работает оптимизатор. Он не перебирает все возможные варианты планов, он перебирает их до тех пор, пока не вылетит по таймауту, или пока не найдет good enough с его точки зрения. И это поведение документировано.

Случай не частый, к счастью, но я тоже встречался с таким.
3 янв 19, 09:06    [21777805]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
А с хинтом, так вообще дичь полнейшая, оптимизатор добавил 2 абсолютно ненужные сортировки:
select
       t1.id1,
       t1.id2,
       t1.id3,
       t1.id4,
       t1.id5,
       t2.id5
from table1 t1
left merge join table2 t2 on
		t1.id1 = t2.id1 and
		t1.id2 = t2.id2 and
		t1.id4 = t2.id4 and
		t1.id3 = t2.id3 and
		t1.id5 = t2.id5
where
	t1.id1 = 6


Картинка с другого сайта.
3 янв 19, 09:06    [21777807]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3040
Я бы в таком случае отказался от составного ключа и добавил бы суррогатный identity. Все-таки джойн по пяти полям - это жесть. У вас там варехаус, что ли?
3 янв 19, 09:10    [21777810]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
invm
Member

Откуда: Москва
Сообщений: 8440
Богдан Гоцкий,

Видимо это баговина или просто бай дизайн.

Если уберете where получите merge в обоих случаях.

С where предикат t1.id1 = t2.id1 исключается и вместо этого имеем два index seek с id1 = 6 с упорядоченными наборами на выходе.
Но во втором случае оптимизатор почему-то считает, что для merge необходимо переупорядочивание.
3 янв 19, 10:23    [21777820]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
L_argo
Member

Откуда:
Сообщений: 643
автор
А для декларативного языка коим является SQL это дико неожиданно
Для ИТ вообще ничего не может быть неожиданного. :)
Тем более для МС.

Все коды пишут живые люди. А кодов - многие миллионы строк.
Документированных ошибок - тысячи. Недокументированных не меньше.

зы: "Все современные программы - бета-версии" (с)
3 янв 19, 10:29    [21777822]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10656
Блог
Почитайте, как и по каким колонкам строиться статистика и как она используется для построения плана оптимизатором, тогда и поймёте, как влияет порядок предикатов...
9 янв 19, 12:53    [21780479]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
waszkiewicz
Member

Откуда:
Сообщений: 981
Александр Гладченко,
а вот еще бы и ссылок годных на "почитать"?
9 янв 19, 15:44    [21780692]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Александр Гладченко,

читал, похоже не в статистике тут дело. Больше похоже на багу во время построения дерева реляционных операторов. В обоих случаях дерево должно быть одинаковым. Создание статистик по {id1, id2, id4, id3, id5} на обоих таблицах никакой роли не сыграло.
9 янв 19, 23:12    [21781104]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2194
Александр Гладченко
Почитайте, как и по каким колонкам строиться статистика и как она используется для построения плана оптимизатором, тогда и поймёте, как влияет порядок предикатов...
Ну, статистика не имеет к этому никакого отношения. Скорее просто "лень" оптимизатора.
10 янв 19, 04:13    [21781173]     Ответить | Цитировать Сообщить модератору
 Re: Порядок полей в ON имеет значение !!??  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3546
Богдан Гоцкий,

автор
Всю жизнь думал что SQL декларативный язык ;-) и порядок перечисления полей в ON не имеет значения. Оказывается имеет ))

не придирки ради, но истины для....
с SQL все в порядке он вам результат запроса гарантирует, а не план. Работу оптимизатора и планы запроса SQL слава богу не описывает.
А то потом начитавшись таких топиков, студенты рассказывают что вот в SQL баг есть.
10 янв 19, 09:02    [21781252]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить