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

Откуда:
Сообщений: 1368
В чем разница запросов когда пишут :
delete x from [dbo].[tbl1] x
where exists
(select 1
from [dbo].[tbl2] x2
 where x.col1=x2.col1


и
delete x from [dbo].[tbl1] x
inner join [dbo].[tbl2] x2 on  x.col1=x2.col1


по мне так её нет.
и второе если в таблицах миллионы строк, удаляется так же около миллиона строк(//3 таблицы), то операция соединения этих двух таблиц какая должна быть? У меня в плане стоит NestedLoops, но теоретически же должно быть HashJoin?
17 июн 15, 13:03    [17781728]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
а со статистикой все хорошо?
17 июн 15, 13:04    [17781737]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
смотрю_тут
Member

Откуда:
Сообщений: 1368
Mike_za
а со статистикой все хорошо?

на момент вызова что со статистикой не могу сказать.
Перед этим должно куча изменений, но мне интересно, что должно быть?
И разницы в запросах все таки нет или при каких -то данных что будет?
17 июн 15, 13:11    [17781789]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
Glory
Member

Откуда:
Сообщений: 104751
смотрю_тут
по мне так её нет.

SQL - это декларативный язык.
Вы говорите серверу, что вы хотите сделать, а не как он должен это делать
17 июн 15, 13:39    [17782011]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
смотрю_тут
Member

Откуда:
Сообщений: 1368
Glory
смотрю_тут
по мне так её нет.

SQL - это декларативный язык.
Вы говорите серверу, что вы хотите сделать, а не как он должен это делать

спасибо, но я спрашивал, есть ли различия в этих запросах на конечных результатах?

"как он должен это сделать" - как вы знаете тоже можно управлять.
17 июн 15, 14:18    [17782257]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
Glory
Member

Откуда:
Сообщений: 104751
смотрю_тут
спасибо, но я спрашивал, есть ли различия в этих запросах на конечных результатах?

Может вы об этом подумали, но не спросили. Потому что вы спросили "В чем разница запросов когда пишут :"

смотрю_тут
"как он должен это сделать" - как вы знаете тоже можно управлять.

Если вы про хинты, то сервер может их просто проигнорировать или отказаться использовать
17 июн 15, 14:21    [17782278]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Glory
смотрю_тут
по мне так её нет.

SQL - это декларативный язык.
Вы говорите серверу, что вы хотите сделать, а не как он должен это делать


А как же всякие хинты, которыми мы пытаемся наставить сервер на путь план истинный
17 июн 15, 14:22    [17782282]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
Glory
пропущено...

SQL - это декларативный язык.
Вы говорите серверу, что вы хотите сделать, а не как он должен это делать


А как же всякие хинты, которыми мы пытаемся наставить сервер на путь план истинный

Вот вам все время тоже дают хинты про ваши посты. Они всегда заставляют вас делать то, что вам говорят ?
17 июн 15, 14:24    [17782292]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6201
a_voronin
А как же всякие хинты, которыми мы пытаемся наставить сервер на путь план истинный

По-всякому бывает. Например, бывает вот так:

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.
17 июн 15, 15:28    [17782859]     Ответить | Цитировать Сообщить модератору
 Re: В чем разница exists inner join  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
смотрю_тут,

У вас в одном вопросе смешаны разные смыслы. Лучше разбить ваш вопрос на под вопросы.

1. Есть ли разница как писать в предложении delete, при помощи exists или inner join с точки зрения смысла запроса.
2. Есть ли разница как писать в предложении delete, при помощи exists или inner join с точки зрения физического выполнения запроса сервером. Или перефразируя, могут ли отличаться планы таких запросов.
3. Почему у меня в плане Nested Loops, когда я ожидал Hash Match.

По порядку.

1.
SQL декларативный язык, мы описываем результат, т.е. смысл запроса. Когда запрос пишется через exists, смысл явно понятен, удали мне из таблицы 1, то что есть в таблице 2 по такому условию. Когда запрос пишется через соединение – что мы говорим? Соедини таблицы и удали результат соединения? Но в результате соединения могут получиться дубли, например, удаляем из таблицы 1, где есть одна строка со значением 1. В таблице 2 две строки со значением 1. Что мы должны удалить, два раза одну и ту же строку? Т.е. смысл немного не ясен.

Поэтому, открываем документацию:
автор
FROM table_source

Задает дополнительное предложение FROM. Это расширение языка Transact-SQL для инструкции DELETE позволяет задавать данные из <table_source> и удалять соответствующие строки из таблицы в первом предложении FROM.

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

Т.е. согласно документации, это некоторое расширение, которое, может быть использовано вместо (т.е. эквивалентно по смыслу) вложенного подзапроса.

Вот ответ на ваш первый вопрос.

Другое дело, что эквивалентно оно тогда, когда вы написали одинаковые по смыслу запросы, т.е. нужно понимать смысл подзапроса.

Например, эквивалентны ли следующие запросы (на бд opt):

delete t1 from t1 join t2 on t1.c = t2.c;
delete t1 from t1 where exists (select * from t2 where t1.c = t2.c);

А эти?
delete t1 from t2 join t1 on t1.c = t2.c;
delete t1 from t2 where exists (select * from t1 where t1.c = t2.c);


2.
Оптимизатор разбирает текст в дерево операторов. Далее начинаются всевозможные упрощения и преобразования этого дерева, но с сохранением его смысла. Если бы оптимизатор имел все возможные правила упрощения и все возможные правила преобразования, а также бесконечные ресурсы и время на их применение к дереву операторов – то была бы гарантия того, что одинаковые по смыслу, но разные по синтаксису запросы, всегда будут оптимизированы одинаково для одинаковых данных. Но в реальном мире мы ограничены текущей имплементацией оптимизатора и имеющимися ресурсами – так что потенциально, разные запросы (exists vs join в delete) могут привести к разным планам и, значит, разной производительности.

Но верно и обратное, разные по написанию запросы получают одинаковые планы. Например, многие знают логическую операцию Semi Join, она применяется там, где не надо выполнять полное соединение, в том же exists. И наоборот, exists иногда может быть реализован через полное соединение.
На примере:

use opt;
go
set showplan_xml on;
go
-- Semi Join (Hash)
delete t1 from t1 join t2 on t1.c = t2.c;
delete t1 from t1 where exists (select * from t2 where t1.c = t2.c);

-- Inner Join (Merge)
delete t1 from t1 join t2 on t1.a = t2.b;
delete t1 from t1 where exists (select * from t2 where t1.a = t2.b);

-- No Join (FK)
delete t1 from t1 join t2 on t1.b = t2.b;
delete t1 from t1 where exists (select * from t2 where t1.b = t2.b);
go
set showplan_xml off;
go


В первом оба Semi join, даже тот, который через join, во втором, оба через Inner, даже тот что с exists. В третьем вообще нет соединения т.к. оно было исключено на этапе упрощения.

3.
Выбирается то, что 1) может выполнить логическую операцию 2) дешевле.

Попробуйте испольовать хинт option(hash join) посмотрите стоимость запроса в условных единицах и сравните с тем, который выбирается без хинтов. Скорее всего, она окажется выше. Это короткий ответ на то, почему выбирается Loops Join. Длинный ответ подразумевает всякие разные аспекты стоимостной модели, про которые я тут уже много писал, нет желания повторять.
17 июн 15, 18:14    [17783902]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить