Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 not exists(...) VS not in(...)VS != all(...)  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
Подскажите пожалуйста какой из вариантов лучше и эффективней использовать в следующей задаче?
not exists(...) или not in(...) или != all(...)(!= some() ) или вообще через join?

в запросе создается временная таблица, наполняется данными.
затем она должна заполнится еще данными. минуя уже вставленные записи.
тоесть во второй вставке идёт параметр
where "not in first table"
...
и тут надо выбрать что лучше?

и еще можно ли вставлять в ту же таблицу, записи исключая в неё уже вставленные
если делать это с таблицей "переменной" то ругается на LOCK
если делать это с временной таблицей то тоже ругается но уже по другому.
24 май 11, 20:13    [10702628]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
aleks2
Guest
1. Лучше всего OUTER JOIN.

2. Изучите матчасть
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/d2297805-412b-47b5-aeeb-53388349a5b9.htm
24 май 11, 20:27    [10702662]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
aleks2
2. Изучите матчасть
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/d2297805-412b-47b5-aeeb-53388349a5b9.htm

но у меня нет индексов.
это всё временные таблицы.
24 май 11, 20:42    [10702713]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

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

для таких задач лучше всего NOT EXISTS, потому как NOT IN имеет забавные побочные эффекты. JOIN здесь использовать совсем не нужно, он пудрит мозг оптимизатору и дает худший план.
24 май 11, 20:55    [10702762]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
iljy
Newber,

для таких задач лучше всего NOT EXISTS, потому как NOT IN имеет забавные побочные эффекты. JOIN здесь использовать совсем не нужно, он пудрит мозг оптимизатору и дает худший план.

Спасибо, думаю его и буду использовать.
Почитал много материала , про VS эти inn'ов и exist'ов.
а можно ли его будет использовать в такой логике.
создаётся временная табл
вставляются записи.
затем снова вставляются записи..
where not exists(и проверяем эти записи в этой же таблице в которую вставляем)
24 май 11, 21:04    [10702799]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

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

можно конечно, почему нет?
24 май 11, 22:46    [10703108]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
iljy,

странная штука происходила, пришлось воспользоваться left join
если выполняется запрос через "exec sp_executesql N'"
и там была конструкция insert into #tmp select...
потом еще раз insert into #tmp select... where not exists(select from #tmp)
ругалось на
Could not continue scan with NOLOCK due to data movement.
пришлось заменить на left join
и в where #joined_tmp is null

странно в общем :)
25 май 11, 00:59    [10703451]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

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

а это все потому, что пользоваться NOLOCK нельзя никогда (за исключением крайне редких случаев, когда все-таки можно). Вы модифицируете таблицу, и при этом же ее сканируете с NOLOCK, естественно проблемы влегкую начнуться, и такой вылет по ошибке - это самая безобидная из них. А когда у вас начнется активная работа с таблицей и будут данные пропадать или левые результаты в выобрке появляться - вот тут наплачетесь. И left join ничего не гарантирует, просто дал худший план, при котором этот эффект менее выражен.
25 май 11, 08:38    [10703716]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
iljy,

ну я экспериментировал с блокировками, но не смог исправить, не очень много с ними работал просто.
подскажите пожалуйста как надо правильней написать было?
25 май 11, 09:54    [10704105]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Newber
iljy,

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

Исправить что? Мертвые блокировки? Надо граф смотреть.
25 май 11, 10:27    [10704311]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy
JOIN здесь использовать совсем не нужно, он пудрит мозг оптимизатору и дает худший план.
Может дать, не надо пугать, словно панацея. Вот без exists обхожусь если можно. В моих запросах exists ничего не даёт. Всё зависит от кривизны написанной системы.
Возможно есть запросы что с exist хуже, мало ли.

Лучше тынц дайте. (но только не опять на вырожденные случаи)

outer JOIN и если вдруг криво, то NOT Exists
Кому как нраица.
------------------------------------------------
Точно так же у меня было пару случаев когда "T2 ON T1.X = T2.Y" вываливало, при этом "T2 ON T2.Y = T1.X" идеально.
С тех пор так и пишу и ни одного нарекания.
А iap писал, что у него что-то типа наоборот было.
И какой вывод?!

Не удивлюсь если ваш тынц или специфичен или уже нормально работает на последней версии (Denaly?).
Опять таки, я не против NOT Exists.
25 май 11, 10:58    [10704622]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

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

все просто - NOT IN/NOT EXISTS позволяет использование в плане одного из операторов Left Anti Semi Join, а LEFT JOIN обычно превращается в пару Left Join + Filter. На большом количестве данных разница может быть заметна. Возможно последние версии оптимизатора решают эту проблему, не изучал. Вполне возможно, что удастся подобрать пример, когда LEFT JOIN будет эффективнее за счет большей гибкости - можно явно указать тип соединения.
25 май 11, 14:37    [10706907]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy
Left Anti Semi Join
Как вы планы смотрите?
25 май 11, 15:28    [10707398]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior
iljy
Left Anti Semi Join
Как вы планы смотрите?

Ээээ... не понял вопроса... Пишу два запроса с идентичной семантикой, один с NOT EXISTS, другой с LEFT JOIN, и смотрю.
25 май 11, 15:41    [10707518]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy
смотрю.
Где?
Графический, текстовый или иной?
25 май 11, 16:43    [10708177]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Всё, вижу.
Но и вижу проблемы, скорее в отображении плана, хотя всё может быть.
25 май 11, 16:46    [10708209]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Просто Anti Semi Join это логический оператор.
25 май 11, 16:47    [10708215]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Огромная печаль.

Всё нормально - и это плохо. Лучше б в одних случаях/проектах по возможности везде NOT EXIST (да пусть хоть с проблемами), ну в остальных LEFT. Тогда может разницу собственной задницей чуствовал. А так ничего сказать не могу - нуль без палки.

Но почему Anti Semi Join это логический оператор???

Но тут две печали.
Ради 0.0001% они раздувают язык плана, а нормально сделать Row_Number()OVER не могут и не делают Top(N)OVER.

Джим Грей, ну надо было тебе этот пепел выбрасывать. Без тебя эти суки вообще не работают.
А-а-а-а-а-а-а
25 май 11, 17:33    [10708604]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior
Но почему Anti Semi Join это логический оператор???

? А в чем собстенно проблема? Физически это может быть Loop-Merge-Hash Join, но логически он пропускает только строки, не имеющие пары (справа или слева соответственно). И, за счет уменьшения количества физических операторов и передачи данных между ними, на больших объемах (особенно если всего строк в соединении много, а удовлетворяющих условию - не очень) получаем заметный выигрыш.
26 май 11, 08:05    [10710670]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
В том-то я смысл, что я не особо врубаюсь в смысл слов физический/логический.
Мне казалось, что физический имеет отражение в конкретных процедурах/алгоритмах ядра, а логический чисто информативное понятие без какого конкретного отображение (и может даже не отображаться в плане).

Но при данном ракурсе понимаешь что это неверно, и логический оператор также имеет чёткое отражение.
В данном случае есть два похожих, но не идентичных оператора JOIN и оба имеют разное отражение в исполняемом коде.
Т.е. разделение этих понятия немного путает.

Замечаешь, что MS при проектировании отображении плана исполнения взяла золотую середину: как бы есть элементы языка и как бы есть исполняемый код и как-то надо это связать. Не рыба не мясо.

Лично мне интересен исполняемый код и только он, мне до *опы все эти "абиснялки" для дошкольников. Эх была бы опция в отображении, тогда б наглядно било по глазам. И было видно все ошибки: от пользователя до разработчика сервера, от проектировщика ядра до архитектора языка.
Но как для основного анализа запроса всё нормально.
--------------------------------------------------------------------------------------------
Повторюсь - чувствую нужду в новых алгоритмах поведения и вакуум обсуждений в этом направлении.
Вот еле надыбал обсуждение, последний абзац: 6238252 Никто не прореагировал. Вся надежда на iljy.
26 май 11, 10:39    [10711251]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Повторюсь - чувствую нужду в новых алгоритмах поведения и вакуум обсуждений в этом направлении.
Вот еле надыбал обсуждение, последний абзац: 6238252 Никто не прореагировал. Вся надежда на iljy.
Вот ещё: 8332918
26 май 11, 10:42    [10711267]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

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

логический оператор - это модификация физического. Точных определений я нигде не нарыл, но имхо принцип такой: если физические операторы NestedLoops, MergeJoin, HashJoin, они различаются принципами работы (цикл, слияние упорядоченых потоков, использование хеш-таблицы, это довольно серьезная разница. Также например HashJoin является буферизующим в отличие от двух других). А логический - это логика поведения, а именно - при каком условии полученная в результате запись отдается дальше.
Оптимизатор рассуждает так: логический оператор - это оператор, принципиально определяющий логику поведения (Join/SemiJoin/AntiSemiJoin, Left-Right/Full). Есть правила эквивалентных преобразований комбинаций логических операторов (насколько я знаю, пока замены Left/Right Join+Filter(is null) на AntiSemiJoin в правилах не было, да и не всегда можно принять решение о такой замене - вдруг null может и для существующей строки получиться?). А затем для логического оператора может быть выбрано конкретное физическое воплощение(Left/Right, Hash/Merge/Loop) для оценки стоимости.

Что касается "поверхностных сканов" - да, мне их тоже иногда не хватает, и тоже не вижу проблем в реализации.
26 май 11, 12:08    [10712005]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy, сенкс. Картина вырисовывается.
Видится проблематика мышление-язык-реализация.
Так и хочется писать: [CROSS | LEFT | FULL | SEMI | NOT] JOIN
Ибо чаще всего выстаивается линейная связка в виде звезды или т.п.
Ошибки восприятия, всё-таки надо писать Exists.

Вот пример:
WHERE	   A IS     NULL AND NOT Exists(<B>)
	OR A IS NOT NULL AND     Exists(<B>)
Неудобно, приходится писать опять же во FROM. А если считать что <B> выдаёт одну строку, то LEFT JOIN получше катит.

iljy
не всегда можно принять решение о такой замене - вдруг null может и для существующей строки получиться?
Запросы уже разные, это не причём.

Вот ещё надыбал об вариантах JOIN-на: 10448941 (начиная MERGE JOIN)

PS: Всегда мечтал об Open проекте, где показаны всевозможные атомарные алгоритмы (операторы) + эвристики оптимизации. Сиди и играйся, изучай, комбинируй и экспериментируй.
26 май 11, 19:53    [10715868]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mnior
PS: Всегда мечтал об Open проекте, где показаны всевозможные атомарные алгоритмы (операторы) + эвристики оптимизации. Сиди и играйся, изучай, комбинируй и экспериментируй.

http://sqlblog.com/blogs/paul_white/archive/2010/07/29/inside-the-optimiser-constructing-a-plan.aspx
Особенно интересна 4я часть статьи
http://sqlblog.com/blogs/paul_white/archive/2010/07/31/inside-the-optimiser-constructing-a-plan-part-4.aspx
26 май 11, 20:07    [10715899]     Ответить | Цитировать Сообщить модератору
 Re: not exists(...) VS not in(...)VS != all(...)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy, Мега спасибо.
Теперь всё это разгребать. :)
27 май 11, 15:51    [10720869]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить