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

Откуда:
Сообщений: 504
Добрый день.
Вопрос больше теоретический:

Есть запрос вида
Select <SelectList>
FROM T1 
LEFT JOIN T2 
ON <Expression>

WHERE <f(T1)>


то есть в WHERE используются только поля T1.
Согласно, msdn условия в разделе WHERE выполняется после условий раздела ON.
Есть ли информация (из практики или официальная), что сервер может выполнить WHERE раньше ON, или это полностью исключено и оптимизатор так не работает?
23 дек 14, 12:55    [17039370]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Хитроглазый,

в той же статье дальше написано
the actual physical execution of the statement is determined by the query processor and the order may vary from this list.


а чем вызван вопрос?
23 дек 14, 13:06    [17039442]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Хитроглазый
Согласно, msdn условия в разделе WHERE выполняется после условий раздела ON.
Только один нюанс - речь о логическом порядке выполнения.
Сервер может применить какой угодно физический порядок,
если конечный результат будет совпадать с логическим.
23 дек 14, 13:37    [17039676]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Хитроглазый,

Есть информация, гуглите по слову predicate push down.

Вот сходу есть статья Конора Канингема ("главного" по оптимизатору в команде сиквела):
How to write non-JOIN WHERE clauses
автор
...When the SQL Server Optimizer starts working on this query, it will do
a number of things very early in the optimization process. One thing is called “simplification”, and most of you can guess what will happen there. One core task in simplication is “predicate pushdown”, where the query is rewritten to push the filter conditions in WHERE clauses towards the tables on which the predicates are defined...

Не знаю насколько для вас важная "официальность", т.к. в официальных статьях я сходу не припомню, MS вроде бы такие вещи официально не документирует. Но в авторитетности Конора, как источника, сомневаться не приходится =)

Что касается прктики, то на практике, оптимизатор всегда старается применить фильтры (как из where, так и из join, так и, например, битмап фильты) на как можно более раннем этапе, чтобы снизить число строк для обработки последующими операторами плана.
23 дек 14, 13:58    [17039826]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Использование скалярной функции, скорее всего, приведет к самому позднему использованию в плане.
23 дек 14, 14:29    [17040166]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Владислав Колосов
Использование скалярной функции, скорее всего, приведет к самому позднему использованию в плане.

Не факт, что "скорее всего". Все зависит...
Да, оптимизатор не сможет продвинуть фильтр на самый низкий уровень, когда фильтрация происходит фактически при сканировании, но он сможет продвинуть фильтр максимально близко.
Кстати, отложить исполнение скалярной функции "до последнего", а не продвигать вниз, как раз иногда было бы умной стратегией.

Пример:
+
use tempdb;
go
if object_id('t1') is not null drop table t1;
if object_id('t2') is not null drop table t2;
if object_id('dbo.uf_f') is not null drop function dbo.uf_f;
go
create table t1(a int identity, b int);
create table t2(a int identity, b int);
go
insert t1(b) select top(1000000) rn = row_number() over(order by (select null)) from sys.columns c1,sys.columns c2,sys.columns c3;
insert t2(b) select top(1000) rn = row_number() over(order by (select null)) from sys.columns c1,sys.columns c2,sys.columns c3;
go
create function dbo.uf_f(@a int)
returns int
as 
begin
	return @a*2;
end
go

Запрос:
set statistics time, xml on
select * from t1 join t2 on t1.a = t2.a where dbo.uf_f(t1.b) is not null;
select * from t1 join t2 on t1.a = t2.a outer apply (select 0 as a) t3 where dbo.uf_f(t1.b+t3.a) is not null;
set statistics time, xml off
go 2

CPU time = 2558 ms,  elapsed time = 2662 ms.
CPU time = 156 ms,  elapsed time = 212 ms.

В 10 раз медленнее.
Планы:
+
Картинка с другого сайта.

В первом случае, оптимизатор передвинул фильтр поближе к оператору доступа, к сканированию и выполнил его до соединения. А т.к. в операторе фильтра у нас скалярная функция, он выполнил скалярную функцию для всех 1 000 000 строк. Как известно каждое выполнение - это переключение контекста - это очень медленно.
Во втором плане, благодаря манипуляции с выражением, оптимизатор не может гарантировать, что перемещение фильтра под соединение не нарушит семантику запроса и оставляет его уже после соединения, благодаря этому, скалярная функция выполняется всего 1000 раз, вместо 1 000 000. И разница в скорости в 10 раз.

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

Я, правда, не понял, при чем тут функция? Я думал, что "WHERE <f(T1)>" - это фильтр по T1 обозначен.
23 дек 14, 15:35    [17040879]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
Владислав Колосов
Member

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

спасибо за разъяснения.
23 дек 14, 16:02    [17041109]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
Хитроглазый
Member

Откуда:
Сообщений: 504
Shakill,
да, Вы правильно поняли, речь как раз об этом;

iap,
в статье написано прямо противоположенное;

Владислав Колосов,
возможно, написал не совсем ясно, речь идет о логическом выражении, в котором присутствую только поля левой таблицы, хотя логика тут таже;

SomewhereSomehow,
спасибо, уже что-то; правда в том примере у Конора речь идет внутреннем соединении, в моем - о внешнем левом, смущает еще и

автор
You know that the QP uses relational algebra equivalence rules to rewrite a query tree (so A join B is equivalent to B join A, filter .a(a join b) == (select * from a where filter.a) join b, etc.

One could imagine that some of the fancier operators may not fit as easily into the relational algebra rewrite rules. (Or, they are just so complex that the cost of trying such rewrites outweighs the benefit)


на сколько я помню, внешние соединения не являются частью реляционной алгербы (или добавились в неё гораздо позже),
а значит правила эквивалентности сложнее и манипулировать ими сложнее, и, следовательно, возможно, что "cost of trying such rewrites outweighs the benefit"
23 дек 14, 16:26    [17041271]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Хитроглазый
iap,
в статье написано прямо противоположенное;
В какой статье?
Надеюсь, не уголовной?
23 дек 14, 16:35    [17041355]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Хитроглазый
на сколько я помню, внешние соединения не являются частью реляционной алгербы

вот нифига себе заявления
23 дек 14, 17:02    [17041572]     Ответить | Цитировать Сообщить модератору
 Re: Фактический физический порядок выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Хитроглазый
iap,
в статье написано прямо противоположенное;

Я хоть и не iap, но мне тоже интересно, где вы увидели противоположное?

Может быть разгадка тут:
Хитроглазый
Владислав Колосов,
возможно, написал не совсем ясно, речь идет о логическом выражении, в котором присутствую только поля левой таблицы, хотя логика тут таже;

Поясните, вы имеете ввиду логику запроса? Если так - iap уже ответил.
Логику, а точнее семантику, т.е. смысл запроса - оптимизатор не меняет. В таком контексте, становится не понятен сам ваш вопрос: "может выполнить WHERE раньше ON, или это полностью исключено и оптимизатор так не работает" - если имеется ввиду смысл запроса, то вам должно быть все равно как работает оптимизатор, вы должны получить один и тот же результат.

Хитроглазый
SomewhereSomehow,
спасибо, уже что-то; правда в том примере у Конора речь идет внутреннем соединении, в моем - о внешнем левом, смущает еще и
автор
You know that the QP uses relational algebra equivalence rules to rewrite a query tree (so A join B is equivalent to B join A, filter .a(a join b) == (select * from a where filter.a) join b, etc.
One could imagine that some of the fancier operators may not fit as easily into the relational algebra rewrite rules. (Or, they are just so complex that the cost of trying such rewrites outweighs the benefit)

на сколько я помню, внешние соединения не являются частью реляционной алгербы (или добавились в неё гораздо позже),
а значит правила эквивалентности сложнее и манипулировать ими сложнее, и, следовательно, возможно, что "cost of trying such rewrites outweighs the benefit"

Ваша правда в том, что внешние соединения гораздо более ограничены в трансформациях, чем внутренние. Именно по-этому, частенько замена одного типа на другой приводит к разным планам и ухудшению/улучшению ситуации (прием, которым часто любит пользоваться aleks2). Но это больше касается перестановок соединений и выбора их начального порядка, и не накладывает фундаментальных ограничений на проталкивание предикатов.

Допустим:
filter .a(a join b) == (select * from a where filter.a) join b
Но, также:
filter .a(a left join b) == (select * from a where filter.a) left join b
Если обратно, то в общем случае:
filter .b(a left join b) != a left join (select * from b where filter.b)
Хотя опять же, какой фильтр, учитывает ли он нулл? Этот тот случай, когда пишут outer join, а потом в where условие, которое исключает null - оптимизатор может преобразовывать такое соединение во внутреннее, давая себе больше свободы для перестановок.

Вы просто проверьте такой запрос (таблицы самые простые, можете сделать сами или например):
select * from t1 left join t2 on t1.a = t2.b where t1.c = 10
-- or
select * from t1 left join t2 on t1.a = t2.b and t2.c = 10

Посмотрите, где у вас предикат t1.c=1 или t2.c = 1. Если он помещен в оператор сканирования как residual predicate - значит оптимизатор протолкнул его вниз. В этом примере, он так и сделал и это логично и не нарушает семантику.

Насчет реляционной алгебры. В первой, оригинальной статье Кодда - "1970 - A relational model of data for large shared data banks (Edgar Codd, IBM Research)" - вообще много чего нет. Если от нее отсчитывать - то очень много каноничных операторов давайте не будем считать реляционными. В классическом учебнике Кристофера Дейта «Введение в системы баз данных» (К. Дж. Дейт «Введение в системы баз данных» - ISBN 0-321-19784-4) перечисляется восемь основных операций реляционной алгебры, сформулированных Эдгаром Коддом. Это: объединение (UNION), пересечение (INTERSECT), разность (MINUS), произведение (TIMES), выборка (WHERE), проекция (ALL BUT), соединение (JOIN) и деление (DIVIDEBY). Однако, в языке T-SQL, нет, например, оператора TIMES, который обозначает произведение двух отношений, зато есть CROSS JOIN, который, по сути, выполняет перемножение двух таблиц (ставит в соответствие каждой строке одной таблицы, каждую строку из другой таблицы). Таким образом, трудно классифицировать логические типы SQL соединений, транслируя понятия классической реляционной алгебры в SQL напрямую. В статье Конора НЕ имеется ввиду, что все трансформации исполняются через эти базовые операции (кстати, интересно, что вообще, в реализации sql, фундаментально, таблица не совсем соотносится с понятием отношение - начните гуглить "sql server relational set vs bag").
Еще не понимаю, зачем вы выделили абзац насчет стоимости - стоимость - отдельная песня. При чем тут она и как это соотносится с вашим первоначальным вопросом?

Если коротко:
автор
Есть ли информация (из практики или официальная), что сервер может выполнить WHERE раньше ON, или это полностью исключено и оптимизатор так не работает?

Инфа есть, ключевые ссылки, цитаты, слова для поиска и примеры - даны.
23 дек 14, 18:44    [17042012]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить