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

Откуда: Украина, Запорожье
Сообщений: 77
В каких случаях быстрее JOIN, а в каких - APPLY?
19 янв 10, 16:46    [8210131]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
Вы так говорите, как будто они всегда для одних и тех же целей используются
19 янв 10, 16:49    [8210159]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Arcan
Member

Откуда: Украина, Запорожье
Сообщений: 77
Бывает, что и для одних.
19 янв 10, 16:51    [8210182]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
Мне никогда не приходилось использовать apply вместо join из соображений скорости
Равно как и наоборот

Если Вы приведете примеры таких задач, тестовые данные, замеры скорости...
думаю, не только я буду Вам признателен
19 янв 10, 17:00    [8210266]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Arcan
В каких случаях быстрее JOIN, а в каких - APPLY?
вот здесь есть поучительный примерчик :-)
19 янв 10, 17:01    [8210275]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
да, и еще вот тут нашёл, форум 778 ("Оптимизация"), мессаги начиная с 06 Nov 2008 09:59:18.
19 янв 10, 17:11    [8210370]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ошибка в восприятии, по мне так из-за императивного мышления.
Нету таких "команд" как JOIN и APPLY, "команд" ваще нету. Все эти выражения "компилируются" в конкретные планы запросов. Разница в планах запросов определяется логикой/"смысловой нагрузкой" ключевого слова, и генератором плана, точнее "мастерством" его создателя.

Будет чертовски интересно, если вы приведёте запросы c идентичными планами, но работающие не идентично по скорости.

Если вы спрашиваете про "разницу в генерации планов", то это зависит от версии сервера, его настроек, настроек соединения, и конкретно от запроса. Планы генерируется не "идеальные" даже не из-за плохого программирования генератора планов, но из-за математической природы - поиска оптимального плана за конечное приемлемое время.
19 янв 10, 22:33    [8211782]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: JOIN vs APPLY  [new]
Valerii79
Member

Откуда: Кишинев, Молдавия
Сообщений: 395
Ozzy-Osbourne
Arcan
В каких случаях быстрее JOIN, а в каких - APPLY?
вот здесь есть поучительный примерчик :-)




Помогите, пожалуйста, переписать запрос с помощью APPLY!

create table left_side_1 (
  id int,
  description varchar(100),
  joinkey int
)

create table left_side_2 (
  id int,
  description varchar(100),
  joinkey int
)

create table right_side (
  [type] int,
  value int,
  date datetime
)

insert into left_side_1 (id,description,joinkey)
select 1,'One_1',1
insert into left_side_1 (id,description,joinkey)
select 2,'Two_1',1
insert into left_side_2 (id,description,joinkey)
select 1,'One_2',1
insert into left_side_2 (id,description,joinkey)
select 2,'Two_2',1

insert into right_side ([type],value,date)
select 1,1,getdate()
insert into right_side ([type],value,date)
select 1,2,getdate()
insert into right_side ([type],value,date)
select 2,1,getdate()
insert into right_side ([type],value,date)
select 2,2,getdate()

-- неэффективный для большого количества строк запрос
select *
from left_side_1 ls1
join left_side_2 ls2
  on ls1.joinkey = ls2.joinkey
join right_side rs
  on (rs.[Type] = 1 and rs.value = ls1.id)
  or (rs.[Type] = 2 and rs.value = ls2.id)
11 мар 14, 14:03    [15704306]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Valerii79
-- неэффективный для большого количества строк запрос

А, типа, с apply станет сразу эффективный?
11 мар 14, 14:54    [15704811]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Valerii79
Ozzy-Osbourne
пропущено...
вот здесь есть поучительный примерчик :-)




Помогите, пожалуйста, переписать запрос с помощью APPLY!

create table left_side_1 (
  id int,
  description varchar(100),
  joinkey int
)

create table left_side_2 (
  id int,
  description varchar(100),
  joinkey int
)

create table right_side (
  [type] int,
  value int,
  date datetime
)

insert into left_side_1 (id,description,joinkey)
select 1,'One_1',1
insert into left_side_1 (id,description,joinkey)
select 2,'Two_1',1
insert into left_side_2 (id,description,joinkey)
select 1,'One_2',1
insert into left_side_2 (id,description,joinkey)
select 2,'Two_2',1

insert into right_side ([type],value,date)
select 1,1,getdate()
insert into right_side ([type],value,date)
select 1,2,getdate()
insert into right_side ([type],value,date)
select 2,1,getdate()
insert into right_side ([type],value,date)
select 2,2,getdate()

-- неэффективный для большого количества строк запрос
select *
from left_side_1 ls1
join left_side_2 ls2
  on ls1.joinkey = ls2.joinkey
join right_side rs
  on (rs.[Type] = 1 and rs.value = ls1.id)
  or (rs.[Type] = 2 and rs.value = ls2.id)
select *
from left_side_1 ls1
join left_side_2 ls2 on ls1.joinkey = ls2.joinkey
join right_side rs on rs.value = ls1.id
where rs.[Type] = 1
UNION ALL
select *
from left_side_1 ls1
join left_side_2 ls2 on ls1.joinkey = ls2.joinkey
join right_side rs on rs.value = ls2.id
where  rs.[Type] = 2
И индексы ещё нужны
11 мар 14, 14:59    [15704859]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Valerii79
Member

Откуда: Кишинев, Молдавия
Сообщений: 395
Гавриленко Сергей Алексеевич
Valerii79
-- неэффективный для большого количества строк запрос

А, типа, с apply станет сразу эффективный?


Знаю мало про APPLY, поэтому и попросил подсказать.
11 мар 14, 17:11    [15705949]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Valerii79
Знаю мало про APPLY, поэтому и попросил подсказать.

Оптимизатор имеет техники переключения join в apply и обратно. Они не совершенны. Иногда есть разница при рерайте.
Но мой совет прислушайтесь к:
iap
И индексы ещё нужны
11 мар 14, 23:15    [15707653]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
Оптимизатор имеет техники переключения join в apply и обратно. Они не совершенны. Иногда есть разница при рерайте.
WTF?
Не понимаю этого, как может быть такое если любой запрос сначала перестраивается во внутреннее представление, в котором уже нет никакого JOIN и APPLY и "манипуляции" у оптимизатора только по перестроению физического плана. И нет никакого APPLY в нём - это всего лишь недостающий кусочек логического отношения, которое ничего нового в физику манипуляций с данными не привносит.

Или вы, SomewhereSomehow, просто сказали так чтобы понял Valerii79?
Искажёно в искажённом восприятии.
12 мар 14, 17:48    [15712378]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior,
привет.

Нет, есть физический оператор PhyOp_Apply, а есть PhyOp_LoopsJoin.

Есть правила по преобразованию одного в другое, можете глянуть сами:
select * from sys.dm_exec_query_transformation_stats where name like '%Apply%' and (name like '%NL%' or name like '%JN%')

name Моя расшифровка
ApplyToNL Apply в Nested Loops
ApplyCnstToNL Константа в Apply в Nested Loops
LSJNtoApply Левое полусоединение в Apply
LASJNtoApply Левое анти-полусоединение в Apply
LOJNtoApply Левое внешнее соединение в Apply
JNtoApplySTVF Соединение в Apply для Streaming Table Valued Function
... ...


Вот пример:
use opt;
go
set showplan_xml on;
go
select * from t1 cross join t2 option(loop join, querytraceon 3604, querytraceon 8607);
select * from t1 join t2 on t1.a = t2.b option(loop join, querytraceon 3604, querytraceon 8607);
select * from t1 join t2 on t1.a = t2.b option(loop join, querytraceon 3604, querytraceon 8607, queryruleoff AppIdxToApp); --отключаем правило преобразующее в apply
go
set showplan_xml off;

*** Output Tree: ***
        PhyOp_LoopsJoin x_jtInner 
            PhyOp_Range TBL: t1(1) ASC  Bmk ( QCOL: [opt].[dbo].[t1].a) IsRow: COL: IsBaseRow1001 
            PhyOp_Spool LAZY
                PhyOp_Range TBL: t2(1) ASC  Bmk ( QCOL: [opt].[dbo].[t2].b) IsRow: COL: IsBaseRow1004 

*** Output Tree: ***
        PhyOp_Apply lookup TBL: t1 (0) (x_jtInner)
            PhyOp_Range TBL: t2(1) ASC  Bmk ( QCOL: [opt].[dbo].[t2].b) IsRow: COL: IsBaseRow1004 
            PhyOp_Range TBL: t1(1) ASC  Bmk ( QCOL: [opt].[dbo].[t1].a) IsRow: COL: IsBaseRow1001 
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [opt].[dbo].[t2].b
                    ScaOp_Identifier QCOL: [opt].[dbo].[t1].a

*** Output Tree: ***
        PhyOp_LoopsJoin x_jtInner 
            PhyOp_Range TBL: t1(1) ASC  Bmk ( QCOL: [opt].[dbo].[t1].a) IsRow: COL: IsBaseRow1001 
            PhyOp_Spool LAZY
                PhyOp_Range TBL: t2(1) ASC  Bmk ( QCOL: [opt].[dbo].[t2].b) IsRow: COL: IsBaseRow1004 
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [opt].[dbo].[t2].b
                ScaOp_Identifier QCOL: [opt].[dbo].[t1].a

А в плане везде NL соединение.

Вот немного по apply из Microsoft SQL Server 2008 Internals (Kalen Delany):
Картинка с другого сайта.
Ну и я уже немного упоминал про это где-то в заметках, не помню в которой.

Факт такой, что оптимизатор может менять операторы физической реализации соединения NL туда-сюда (правда, нам об этом явно не сообщают).
12 мар 14, 19:35    [15712990]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ипать.

Не - не - не, этого мало! Стоять! Давайте разбираться до самого конца!
Сразу извинюсь что нет скуля чтобы это пощупать, чуть позже. И конечно за свою манеру излагания.

Да, в XML заложено тонны инфы (не удивлюсь если там пасхалки есть), Но меня интересует смысл. Притом по всем категориям.

1. Есть реляционная теория отношений.
2. Есть Язык. SQL и MS-SQL в частности.
3. Есть теория транзакционности и несколько её моделей, и несколько вариантов реализации
4. Есть теория вычислений (с дофига дисциплинами, не перечислишь)
5. Есть модель реализации "подхода MS"
6. Есть реализация этой модели реализации
7. Есть средства трассировки (сюда можно планы приписать)
8. Есть документация, мануалы, популяризирующие книги
9. Есть люди которые делают всё это, и каждый делает разное, и главное делает ошибки, допущения и манипуляции.
А ещё:
10. Есть практика использования продукта и подходов
11. Планы и видения развития продукта и т.п. ...

И тут главное не запутать это между собой. В этом многомерном не метрическом пространстве.

Так вот. Все вот эти JOIN и т.п. не ложатся ровным слоем на реальный мир и на природу вычислений, а точнее на тот текущий подход динамического программирования наработанный MS.
Раскрыть для себя NestedLoops, Merge и Hash соединения, это поцарапать верхушку айсберга.
Между запросом и планом стоит пропасть из кучи механизмов, "оптимизатор" это так, размытое гуманитарное понятие.

SomewhereSomehow
есть физический оператор PhyOp_Apply, а есть PhyOp_LoopsJoin.
nameМоя расшифровка
ApplyToNLApply в Nested Loops
ApplyCnstToNLКонстанта в Apply в Nested Loops
LSJNtoApplyЛевое полусоединение в Apply
LASJNtoApplyЛевое анти-полусоединение в Apply
LOJNtoApplyЛевое внешнее соединение в Apply
JNtoApplySTVFСоединение в Apply для Streaming Table Valued Function
......
И вот тут очень важно уточнить. Связано ли вообще Apply физического оператора и APPLY языковой конструкции SQL?!

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

Так вот, "цикл со связью" можно удачно описать словом Apply. Оно неплохо описывает физический смысл процесса (как) со свойством.
А вот APPLY в реляционной модели, нет, правильно - языковая конструкция SQL, описывает смысл (что) задачи.
И это две совершенно разные вещи.
Хотя почти каждый протянет очевидную прямую между ними, это не повод объявлять это двумя сторонами одной сущности.
То что ветер очевидно колышет деревья - это всё таки разные вещи.

SomewhereSomehow, я обладаю малой долей информации, чтобы приписывать мои изыскания к реальному движку MS. Но как бы стараюсь представить возможные
1. его модели (MS)
2. эффективные модели

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

APPLY в первую очередь расширение логики языка. И позиционировать его как хинт можно только в крайнем случае. Но само же понятие "turning" их между собой, вообще полностью убивает его как хинт в MS, которые носят не рекомендательный, а принудительный характер.

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


То что оптимизатор может делать преобразования над структурой текущего оптимизируемого плана, "позиционируя" тело цикла как зависимый ящег, хотя таковым не является (неиспользуемый параметр функции) - не меняет смысл задачи. А по мне не должен его так позиционировать. Да и физически процессору уже фиолетово для чего используется цикл, как элемент подстановки (Apply) или тупо как цикл на малом объёме данных.

По мне, напиши обычный JOIN через APPLY, парсер обязан/должен гарантировать что вернёт оптимизатору в обоих случаях идентичную структуру запроса. Если не так - вешать разрабов за яйца.
Допускать "творчество" горе-проггеров оптимизатору - запутывать его, т.е. уже не эффективно и не стабильно. Игра в испорченный телефон.

Если до оптимизатора доходят (1) уже предварительный план, а не чисто суть запроса, (2) разный план в зависимости от способа написания (JOIN/APPLY). То это ахтунг - нарушен принцип потенциала развития, ибо только чистые системы - т.е. декларативные обладают этой возможностью, любая императивщина убивает практически сразу всё на корню, притормаживая/останавливая этот процесс.

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

SomewhereSomehow
sys.dm_exec_query_transformation_stats
Если это статистические показатели (надо ещё посмотреть), то оно показывает как ошибается проггер в написании запросов, как ошибаются разрабы при генерации предварительных планов, как силько используются один и тот же код для разных задач. Т.е. хрен что выявишь. :)

SomewhereSomehow, надеюсь вы поняли какая куча вопросов было тут поднято и вы внесёте ясность в это "описание".
13 мар 14, 03:18    [15714655]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
SomewhereSomehow
Member

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

Если я понял вас правильно, то вкратце, суть вопросов можно сформулировать так:
1) есть ли связь между элементом языка APPLY, логическим оператором APPLY и физическим оператором APPLY
2) какова логика преобразований APPLY <-> JOIN

Прежде всего, нужно уточнить терминологию, для этого немного бэкграунда.

Есть четыре вида операторов: логические, физические, операторы плана, исполняемые (итераторы).

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

Далее на примере рассмотрим что происходит на каждой стадии. Возьмем два семантически одинаковых запроса.

select * from t1 cross apply(select * from t2) t2 option(loop join, querytraceon 3604, querytraceon 8605, querytraceon 8607);
select * from t1 cross join t2 option(loop join, querytraceon 3604, querytraceon 8605, querytraceon 8607);


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

*** Converted Tree: ***
LogOp_Apply (x_jtInner)
    LogOp_Get TBL: t1 t1
    LogOp_Project
        LogOp_Get TBL: t2 t2
*** Converted Tree: ***
LogOp_Join
    LogOp_Get TBL: t1 t1
    LogOp_Get TBL: t2 t2

Обратите внимание, что в дереве разные операторы (в первом LogOp_Apply, во втором LogOp_Join). Это просто потому, что запросы имеют разный текст и распарсились в разные деревья, что логично.

Далее происходят разного рода упрощения, а потом начинает работать оптимизатор, его задача, перевести логичские операторы в физические, сохраняя семантику, и выбрать наименьшие по стоимости. Это происходит при помощи правил преобразования (статистика использования которых показана в представлении ). В данном случае, к обоим логическим операторам применяется правило JNtoNL, которое переводит и логический оператор LogOp_Apply и логический оператор LogOp_Join в физический оператор PhyOp_LoopsJoin.
В результате имеем два одинаковых дерева уже физических операторов:

*** Output Tree: ***
PhyOp_LoopsJoin x_jtInner
    PhyOp_Range TBL: t1
    PhyOp_Spool LAZY
        PhyOp_Range TBL: t2

*** Output Tree: ***
PhyOp_LoopsJoin x_jtInner 
    PhyOp_Range TBL: t1
    PhyOp_Spool LAZY
        PhyOp_Range TBL: t2


Как раз тот случай, когда не зависит от того как мы написали запрос, оптимизатор внутри все свел к одному.

Разумеется, такое можно проделать не всегда. Например, возьмем запрос, в котором есть действительно коррелированный параметр.

select * from t1 cross apply(select top(t1.b) * from t2 where t1.a = t2.b) t2 option(loop join, querytraceon 3604, querytraceon 8607, querytraceon 8605);

Сокращенно деревья выглядят так:
*** Converted Tree: ***
LogOp_Apply (x_jtInner)
    LogOp_Get TBL: t1
    ...        

*** Output Tree: ***
PhyOp_Apply (x_jtInner)
    PhyOp_Range TBL: t1(1) ASC  Bmk ( QCOL: [opt].[dbo].[t1].a) IsRow: COL: IsBaseRow1001 
    ...


Т.е. оптимизатор перевел логический оператор LogOp_Apply в физический оператор PhyOp_Apply. Если мы запретим ему это делать отключив правило ответственное за этот перевод, то план просто не будет получен, поскольку выполнить требования данного запроса без этого оператора будет нельзя, а мы его запретили.

select * from t1 cross apply(select top(t1.b) * from t2 where t1.a = t2.b) t2 option(loop join, querytraceon 3604, querytraceon 8607, querytraceon 8605,queryruleoff ApplyToNL);

Msg 8622, Level 16, State 1, Line 3
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.


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

select * from t1 cross apply(select * from t2 where t1.a = t2.b) t2 option(loop join, querytraceon 3604, querytraceon 8607);
select * from t1 cross apply(select * from t2 where t1.a = t2.b) t2 option(loop join, querytraceon 3604, querytraceon 8607, queryruleoff AppIdxToApp); --отключаем правило преобразующее в apply

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

Такое же дерево с физическим оператором apply будет сгенерировано и для запроса:
select * from t1 join t2 on t1.a = t2.b option(loop join, querytraceon 3604, querytraceon 8607);


PhyOp_Apply lookup TBL: t1 (0) (x_jtInner)
    PhyOp_Range TBL: t2(1)
    PhyOp_Range TBL: t1(1)


Обратите внимание на слово lookup, в случае, с коррелированным параметром его не было. Почему так происходит, потому что есть такая оптимизация, которая называется index nested loops join и сервер ее рассматривает.

Далее цитирую MSDN:
Understanding Nested Loops Joins
In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. All these variants are considered by the query optimizer.


В данном случае, грубо говоря
"naive nested loops join" - это PhyOp_LoopsJoin.
"index nested loops join" - это PhyOp_Apply lookup.

Псевдо код для "naive nested loops join" (взято из Craig Freedman's SQL Server Blog - Nested Loops Join):
for each row R1 in the outer table
    for each row R2 in the inner table
        if R1 joins with R2
            return (R1, R2)

Псевдо код для "index nested loops join" (как это понимаю я, разумеется код оптимизатора я не видел, так что это именно мое понимание):
for each row R1 in the outer table
	perform seek in the inner table by join key
		return (R1, R2=Seek(inner))


Подытожим, что мы имеем на данном этапе.

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

Будут ли всегда JOIN и APPLY приводится к одному дереву там где это возможно - не обязательно. Оптимизатор имеет механизмы самоограничения, с целью уменьшить время оптимизации и число применяемых правил меняется от запроса к запросу, от стадии к стадии, так что будет ли дерево одно и то же, зависит от того будет ли применено соответствующее правило.

Но это еще не план и не то что будет выполняться!

Оператора плана CXteApply попросту нету (CXteApply), на этапе преобразования в операторы плана PhyOp_LoopsJoin , PhyOp_Apply и PhyOp_Apply lookup и преобразуются в один оператор CXteNLJoin, но с разными свойствами, в зависимости от того из какого физического оператора он был создан.

Мы это можем видеть в плане как разные свойства оператора плана NL, например Outer References или Predicate.

Но и это еще тоже не то, что будет выполняться.

В момент выполнения происходит генерация еще одного дерева (то что называется Executable Plan), в котором оператор NL преобразуется в итератор CQScanNLJoinNew. Вот уже он выполняет непосредственные итерации по строкам.

Для полноты картины можно добавить, что языковой элемент APPLY может быть реализован не только через NL, но например через Hash Join или Merge Join, опять же если это позволяет сделать семантика.

select * from t1 cross apply(select * from t2 where t1.a = t2.b) t2 option(hash join, querytraceon 3604, querytraceon 8605, querytraceon 8607);
select * from t1 cross apply(select top(t1.b) * from t2 where t1.a = t2.b) t2 option(hash join, querytraceon 3604, querytraceon 8607, querytraceon 8605);


*** Converted Tree: ***
LogOp_Apply (x_jtInner)
    LogOp_Get TBL: t1 t1 
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: t2

*** Output Tree: ***
PhyOp_HashJoinx_jtInner
    PhyOp_Range TBL: t1(1) 
    PhyOp_Range TBL: t2(1) 


А для второго, увы
*** Converted Tree: ***
LogOp_Apply (x_jtInner)
    LogOp_Get TBL: t1
    LogOp_Top NoTies
    ...

Msg 8622, Level 16, State 1, Line 36
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.


Если я понял ваши вопросы неверно, то постарайтесь формулировать их более конкретно, в одном, двух предложениях.
13 мар 14, 07:20    [15714742]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow, вам памятник надо ставить за усердие. Это же сколько труда это писать и разжёвывать!
Вы наверно хотели чтобы не только мы понимали друг друга, но и остальные смысл не теряли и подтягивались?
Вообще вам надо на аватарку тэг повесить "Хранитель". :)

SomewhereSomehow
Если я понял вас правильно, то вкратце, суть вопросов можно сформулировать так:
1) есть ли связь между элементом языка APPLY, логическим оператором APPLY и физическим оператором APPLY
2) какова логика преобразований APPLY <-> JOIN
Ну в целом не только. Это было побочный вопрос, из тиа:
"Да вы всё в целом правильно угадали", "Да, только вот нюансы" или "Не, тут совсем всё по другому ...".

Мне намного больше интересует оценка на сколько далёк подход MS от идеала, и как оно должно быть - в идеале. Полемика нужна. И тут больше вопросов и мыслей вслух.
На что опять я постараюсь вас подбить. Не особо заботясь о тех кто в этом плохо кумекает, ссори.

Да, вы подтвердили мои слова только русским и разжёванным языком. Что меня огорчило как факт, помимо восхищения детальностью.
Вы сказали сухо - "что есть на самом деле", полностью исключив оценку. Не придраться. Профессионально.
Теперь на основании примера картина сильно устаканилась и отбросило много вариантов.
И опять возникает вопрос - Как вы до этого дошли.
SomewhereSomehow
Если я понял вас правильно, то вкратце, суть вопросов можно сформулировать так:
1) есть ли связь между элементом языка APPLY, логическим оператором APPLY и физическим оператором APPLY
2) какова логика преобразований APPLY <-> JOIN
Связь естественно есть. Хоть между "щелчком в нос" и "взмахом хвоста". Главное подчеркнуть что это совершенно разные вещи.
1. PhyOp_Apply живёт своей независимой от APPLY жизнью, и существовал задолго до появления сего оператора, и никак не провоцировал его появление. (1. Я повторяюсь, 2. Подчёркивая оценку и смысл)
2. "параметр связи" есть логическое свойство запроса. Которое никак не зависит от способа написания запроса (JOIN/APPLY) и его исполнения.
Поэтому:
SomewhereSomehow
Обратите внимание, что в дереве разные операторы (в первом LogOp_Apply, во втором LogOp_Join). Это просто потому, что запросы имеют разный текст и распарсились в разные деревья, что логично.
Совершенно не логично. Это не точно, поверхностно, но допустимо - но это особенность подхода MS.
SomewhereSomehow
select * from t1 cross apply(select * from t2) t2;
select * from t1 cross join t2;
Вы правильно сделали что привели эти запросы. Оно по семантике "не Apply", т.е. независимы, и происходит полное перемножение. И парсер ещё на этой стадии может сразу понять, что запрос внутри APPLY не имеет никакой связи с внешней конструкцией, что уже можно поставить правильные предварительные операторы и уже отбрасывает кучу вариантов, в случае CROSS - отсутсвие WHERE говорит так же об этом.
SomewhereSomehow
коррелированный параметр
Я бы больше наседал на более точные вещи:
1. зависимость источника данных (XML.nodes() - явно зависимый, нелинейный подзапрос - неявно зависимый, а вот простой запрос из таблы - независимый источник)
2. Связанность этого (не явно зависимого) источника с вшешним (CROSS vs INNER/LEFT)

Вот это вот нежелание парсера до конца "понять" семантику запроса и прибешивает. Именно поэтому понимаешь почему скуль тупит и не может увидеть некоторые очевидные вещи. И вместо того чтобы не перебирать заранее несопоставимые семантике планы. В итоге целый ряд оптимизаций напрочь потеряны из-за большого разрыва в количестве шагов преобразований.

Особенность/подход в стратегии поиска планов понятен (догадываешься). Принцип "оптимистических запросов", т.е. считается что запросы составлены грамотно и близки к конечному смыслу выполнения, т.е. недалеки от FORCE ORDER. Поэтому первоначальный план (Logical Operators и Phisical Operators) близок к тексту запроса. А далее пляшет оптимизатор.

НО. Я считаю это неверным, не Enterprize-ным ограничением. Ибо скуль - это неминуемо автоматический генератор планов. Сам его не напишешь за мест скуля (USE PLAN не считаем, да и всё равно он контролируется). И этот автогенератор можно и нужно использовать по полной - перекладывая работу с программиста на программу (компилятор).
Что я кстати и делаю, используя многократное использование кода (представлений) по полной.
И если MS позиционируется как отличный оптимизатор - то доверять структуре запроса нет смысла - "тебе, дорогой скуль, заранее подают неудобоваримый запрос", об этом заранее знает как разработчики запросов так и разработчики скуля.
Т.е. стратегия "оптимистических запросов" теряет смысл.

А коль так, то "тупой парсер" и подход широких/общих преобразований (за неимением точных описаний семантики) я считаю расточительным.

Можно рассматривать хинты как две вещи:
1. Подсказки первого варианта плана, и ограничение вариантов
2. Дополнительная семантическая информация о задаче, которая не ложится в рамках стандартного языка структур.
И только второй является декларативным и более мощным и основательным подходом.


SomewhereSomehow, мы как знатоки написания планов, практически сразу и заранее представляем в голове план, который должен получится, без всяких переборов и редко ошибаемся. При этом может точно сразу сказать на основании каких свойств объектов и элементов запроса мы пришли к такой структуре.
А вот то что скуль допускает ахтунговые планы - это говорит только об одном - слабая реализаций компилятора. И очевидно почему.

PS: Я конечно допускаю ограниченность семантического описания по Гёделю. Но я пока не вижу тут проблем и отсутствие возможностей.
Тем более что ЛПП "уделывает" Гёделя. :)
14 мар 14, 05:25    [15721492]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
SomewhereSomehow
Member

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

Спасибо, да я постарался описать как работает, по возможности воздерживаясь от оценок.
Что касается парсера, то он парсит, а за понимание, что по семантике не apply, отвечают другие компоненты, в итоге же мы получили одинаковые деревья и планы. Можно ли было реализовать логику определения таких случаев в парсере, наверное да, но так уж решили разделить ответственность между компонентами, работает как работает. Что касается был ли phyop_apply до apply как элемента языка, можно посмотреть это в 2000. Но я не знаю, будут ли там работать флаги по выводу деревьев, инстанса под рукой нет, а разворачивать лениво, т.к. особого любопытства нет. 2000 остался для меня в прошлом. Знаю, что много кардинальных изменений в оптимизаторе было именно при переходе от 2000 к 2005, так что может быть всякое, гадать не возьмусь.

Даже если иногда видно что "вот тут оптимизатор мог бы быть поумнее", а "вот тут есть баг", в целом, оптимизатор, на мой взгляд, неплохо справляется со своими задачами. Особенно, если следовать хорошим практикам. Есть конечно промашки, но радует тот факт, что MS не ушел полностью в маркетинговые фишки и предпринимает усилия повысить качество планов. Инвестировали же ресурсы в разработку нового Cardinality Estimation Framework. Наверняка, со временем, когда люди будут постепенно переползать на 2014 и будет появляться много новых типов рабочих нагрузок, постепенно будет вскрываться больше регрессий планов или недоработок, которые не вскрылись на этапе синтетических тестов или у тестовых клиентов. Но как говорят сами MS, главное, что они сделали новую платформу более гибкой и расширяемой, так что есть надежда что эта часть будет развиваться. Со старым Cardinality Estimation Framework такое было очень трудно осуществимо, опять же, как они сами пишут, нельзя было что-то поправить в одном месте, не уронив что-то в другом. Так было из-за особенностей дизайна архитектуры данного компонента, который был выбран много лет назад, во времена версии 7.0.

В общем, надежды есть, а как оно будет развиваться на практике, что называется - будем посмотреть.
14 мар 14, 09:10    [15721731]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Valerii79
Member

Откуда: Кишинев, Молдавия
Сообщений: 395
iap,

Благодарю! То, что надо!
14 мар 14, 17:19    [15725877]     Ответить | Цитировать Сообщить модератору
 Re: JOIN vs APPLY  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
Что касается парсера, то он парсит, а за понимание, что по семантике не apply, отвечают другие компоненты, в итоге же мы получили одинаковые деревья и планы.
SomewhereSomehow
Оптимизатор имеет техники переключения join в apply и обратно. Они не совершенны.
Специально положил эти высказывания рядом. :)
SomewhereSomehow
Можно ли было реализовать логику определения таких случаев в парсере, наверное да, но так уж решили разделить ответственность между компонентами, работает как работает.
Вода мокрая конечно, но ...
Жаль, культура полемики канула в лету.
SomewhereSomehow
Что касается был ли phyop_apply до apply как элемента языка ...
Мне кажется это сверх очевидным как наличия "index nested loops join" ещё до 2000го сервера. Независимо о того какой набор char-ов прописано в Description.
SomewhereSomehow
на мой взгляд, неплохо справляется со своими задачами.
Хорошее враг лучшему.
SomewhereSomehow
Особенно, если следовать хорошим практикам.
Мой абзац про повторное использование кода был не понят видимо.
SomewhereSomehow
в разработку нового Cardinality Estimation Framework
1. К сожалению оценить это могут только единицы как вы. Но плохо что они как раз уходят от полемики. Общие слова всё ближе к тем же маркетологам.
2. Мне это как-то всё более фиолетово, когда лошадь очевидно бежит не в том направлении.
SomewhereSomehow
Наверняка, со временем, когда будет появляться много новых типов рабочих нагрузок, постепенно будет вскрываться больше регрессий планов или недоработок, которые не вскрылись на этапе синтетических тестов или у тестовых клиентов.
Ой да ладно. Было и есть столько зафиксированных недоработок, сиди и ковыряй, сиди о обсуждай почему что да как. Но культура взаимодействия с клиентом видимо окончательно рухнула. Возможно превратилась в междусобойчик.
SomewhereSomehow
Но как говорят сами MS, главное, что они сделали новую платформу более гибкой и расширяемой, так что есть надежда что эта часть будет развиваться. В общем, надежды есть, а как оно будет развиваться на практике, что называется - будем посмотреть.
Кто-то будет ждать, может к 2030 MS скажет что Cardinality Estimation Framework 2005 го был очень не эффективным и мы придумали что-то "передовое". :)
А кто-то не ждёт ...

Ещё раз спасибо, огромное!
14 мар 14, 21:47    [15727151]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить