Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Cross apply/ Outer apply VS Joins  [new]
Ritzu
Member

Откуда:
Сообщений: 5
Прочитал довольно много информации не особо всё равно могу понять преимуществ и разницы в этих вещах. Да, в apply можно коррелирующий запрос , ок.
Вот вроде бы всё понятно написано НО. Принципиальная разница в чем? Join берёт каждую строку из левой таблицы и сравнивает её с каждой строкой правой, выводит все данные с левой таблички и заNULLивает любые несовпадения с правой таблицы. Но ведь apply тоже самое делает, оценивает записи и если нет совпадения пишет NULL . Резалт сет одинаковый но в Apply появляется Compute Scalar оператор. Никак не могу понять разницы и преимущества того или иного .
Пример:
The first query in Script #3 selects data from Department table and uses an OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is not a match in the Employee table, those rows contain NULL values as you can see in case of row 5 and 6 below. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table. As expected the query returns all rows from Department table, even for those rows for which there is no match in the Employee table.
Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (with estimated operator cost of 0.0000103 or around 0%) before the Nested Loops operator to evaluate and produce the columns of the Employee table.

--Script #3 - OUTER APPLY and LEFT OUTER JOIN

SELECT * FROM Department D
OUTER APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO

SELECT * FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID
GO

Картинка с другого сайта.
22 июн 19, 14:15    [21913354]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
Попробуйте табличную функцию поджоинить с передачей ей параметров.
22 июн 19, 14:54    [21913361]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
А так простым JOINом напишете?
SELECT T.*, T1.*
FROM T
CROSS APPLY(SELECT TOP(1) * FROM TT WHERE T.[Group]=TT.[Group] ORDER BY TT.V DESC) T1;
Я, конечно, понимаю, что с применением ROW_NUMBER()OVER() вы это сделаете.
22 июн 19, 14:57    [21913362]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
aleks222
Member

Откуда:
Сообщений: 952
iap
А так простым JOINом напишете?
SELECT T.*, T1.*
FROM T
CROSS APPLY(SELECT TOP(1) * FROM TT WHERE T.[Group]=TT.[Group] ORDER BY TT.V DESC) T1;
Я, конечно, понимаю, что с применением ROW_NUMBER()OVER() вы это сделаете.


Канешно для полноты щастья нужен идентификатор строки, но что-то вроде
SELECT T.*, TT.*
FROM T inner join TT on T.[Group]=TT.[Group] 
where TT.V = ( SELECT TOP(1) TT.V FROM TT WHERE T.[Group]=TT.[Group] ORDER BY TT.V DESC )
22 июн 19, 15:09    [21913366]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Ritzu
Member

Откуда:
Сообщений: 5
iap
А так простым JOINом напишете?
SELECT T.*, T1.*
FROM T
CROSS APPLY(SELECT TOP(1) * FROM TT WHERE T.[Group]=TT.[Group] ORDER BY TT.V DESC) T1;
Я, конечно, понимаю, что с применением ROW_NUMBER()OVER() вы это сделаете.


я бы был очень признателен если бы вы смогли кратко пояснить разницу на словах
22 июн 19, 15:10    [21913367]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Ritzu
Member

Откуда:
Сообщений: 5
Гавриленко Сергей Алексеевич
Попробуйте табличную функцию поджоинить с передачей ей параметров.

разница в коррелирующих подзапросах и табличных функциях?
22 июн 19, 15:11    [21913368]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
Ritzu
Гавриленко Сергей Алексеевич
Попробуйте табличную функцию поджоинить с передачей ей параметров.

разница в коррелирующих подзапросах и табличных функциях?
А вам мало?
22 июн 19, 15:48    [21913378]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Ritzu
Member

Откуда:
Сообщений: 5
Гавриленко Сергей Алексеевич
Ritzu
пропущено...

разница в коррелирующих подзапросах и табличных функциях?
А вам мало?


нет, мне просто нужно знать разницу между apply и joins. и желательно на где лучше использовать то или другое при равных условиях
22 июн 19, 16:02    [21913383]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
Ritzu
нет, мне просто нужно знать разницу между apply и joins. и желательно на где лучше использовать то или другое при равных условиях
Нужно использовать то, что дает план/производительность лучше в конкретном случае. А если одинаково, то пишите, что нравится, короче, феншуйнее -- сугубо по вашим предпочтениям.
22 июн 19, 16:18    [21913387]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
TheRookie
Member

Откуда:
Сообщений: 27
Ritzu
... мне просто нужно знать разницу между apply и joins

Apply дает возможность использовать в запросе "анонимную" табличную функцию.
Джойны тут вообще не при делах - у них другое предназначение.
22 июн 19, 16:43    [21913397]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
uaggster
Member

Откуда:
Сообщений: 827
Ritzu
Гавриленко Сергей Алексеевич
пропущено...
А вам мало?


нет, мне просто нужно знать разницу между apply и joins. и желательно на где лучше использовать то или другое при равных условиях

А кто Вам сказал, что разница есть?
join - это сокращенный вариант apply.
join соединяет набор записей, стоящий слева, с набором записей, стоящим справа в общий резалтсет, в случае, если предикат в on становится истинным.
apply присоединяет к левому набору записей, к каждой его записи, набор записей образующийся "внутри" выражения apply, и тоже образует общий резалтсет.
cross apply и inner join, имеется ввиду.
Концептуально, это одно и то же, и вариант с join - это упрощённый и специализированный вариант apply.

Собственно, из-за того, что join - это упрощенный и специализированный вариант, в котором обычно функция, согласно которой каждой записи левого набора сопоставляется каждая запись правого - очень простая и "вывернутая наружу потрохами", join работает очень быстро. Для нее энжин знает кучу трюков, позволяющих сократить трудоемкость формирования итогового резалтсета.
А про apply в общем случае, оптимизатору - мало что известно, и жульничать ему гораздо труднее. В общем случае.
Поэтому, обычно, априорно будет быстрее запрос, написанный в варианте join, чем apply.
Например, на больших выборках, выгоднее, см. вышеописанный пример с cross apply (select top (1) * from ... where a.id=b.id order by ...), переписать его через join и оконные функции.
Т.е. сформировать весь набор записей, которые подпадают под условие соединения, пометить каждую запись через Row_Number(), в соответствии с такими же критериями сортировки, а потом отобрать внешним подзапросом все записи с Row_Number = 1.

Но, с учётом того, что оптимизатор очень и очень умный, всё это так... писями по воде виляно.
Нужно взять план и посмотреть.
22 июн 19, 17:01    [21913399]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
aleks222
Member

Откуда:
Сообщений: 952
Ritzu
я бы был очень признателен если бы вы смогли кратко пояснить разницу на словах


Поясняю.
Подавляющему большинству программиЗдов современности коррелированный подзапрос не по мозгу.
Вот и сделали попроще.
22 июн 19, 18:00    [21913408]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Ritzu
Member

Откуда:
Сообщений: 5
uaggster
Ritzu
пропущено...


нет, мне просто нужно знать разницу между apply и joins. и желательно на где лучше использовать то или другое при равных условиях

А кто Вам сказал, что разница есть?
join - это сокращенный вариант apply.
join соединяет набор записей, стоящий слева, с набором записей, стоящим справа в общий резалтсет, в случае, если предикат в on становится истинным.
apply присоединяет к левому набору записей, к каждой его записи, набор записей образующийся "внутри" выражения apply, и тоже образует общий резалтсет.
cross apply и inner join, имеется ввиду.
Концептуально, это одно и то же, и вариант с join - это упрощённый и специализированный вариант apply.

Собственно, из-за того, что join - это упрощенный и специализированный вариант, в котором обычно функция, согласно которой каждой записи левого набора сопоставляется каждая запись правого - очень простая и "вывернутая наружу потрохами", join работает очень быстро. Для нее энжин знает кучу трюков, позволяющих сократить трудоемкость формирования итогового резалтсета.
А про apply в общем случае, оптимизатору - мало что известно, и жульничать ему гораздо труднее. В общем случае.
Поэтому, обычно, априорно будет быстрее запрос, написанный в варианте join, чем apply.
Например, на больших выборках, выгоднее, см. вышеописанный пример с cross apply (select top (1) * from ... where a.id=b.id order by ...), переписать его через join и оконные функции.
Т.е. сформировать весь набор записей, которые подпадают под условие соединения, пометить каждую запись через Row_Number(), в соответствии с такими же критериями сортировки, а потом отобрать внешним подзапросом все записи с Row_Number = 1.

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



Ну вот супер, спасибо , всё как и хотел узнать!
22 июн 19, 18:27    [21913417]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
uaggster
Концептуально, это одно и то же, и вариант с join - это упрощённый и специализированный вариант apply.

Ritzu
Ну вот супер, спасибо , всё как и хотел узнать!

Когда loop join от apply начнет периодически раздражать, придет понимание, что это не всегда одно и то же.

UPD: а, да, что-то мне сходу не соображается головой, как с apply сделать full join.

Сообщение было отредактировано: 22 июн 19, 19:23
22 июн 19, 19:20    [21913427]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
uaggster
Member

Откуда:
Сообщений: 827
Гавриленко Сергей Алексеевич
uaggster
Концептуально, это одно и то же, и вариант с join - это упрощённый и специализированный вариант apply.

Ritzu
Ну вот супер, спасибо , всё как и хотел узнать!

Когда loop join от apply начнет периодически раздражать, придет понимание, что это не всегда одно и то же.

UPD: а, да, что-то мне сходу не соображается головой, как с apply сделать full join.

Гы.
Хороший вопрос.
Хочется, конечно, сказать, что как раньше, когда не было full join, как union из left и right join, но тут ведь какая проблема... это возможно только в том случае, когда корреллируемая выборка существует сама по себе, т.с.

Когда loop join от apply начнет периодически раздражать

А, строго говоря, loop join - это единственный всегда возможный вариант соединения. А всё остальное - как раз трюки, которые возможны не всегда, не везде и не для всех условий.
Собственно, о чем я и говорил.
22 июн 19, 20:00    [21913430]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4721
uaggster
join - это сокращенный вариант apply.


Там, где join эквивалентен apply, нет никакого смысла писать apply.

НО! есть ситуации, где нельзя сделать join, а возможен лишь apply.

Например, выбрать запись по TOP 1 или сделать COALESCE по нескольким полям

SELECT A.*, B.* FROM A 
OUTER APPPLY (
  SELECT TOP 1 
  FROM B
  WHERE A.A_Id = B.A_Id
  ORDER BY X
) B



SELECT A.*, B.* FROM A 
OUTER APPPLY (
  SELECT TOP 1 FROM 
 (
  SELECT 1 AZ X, Name, Name2 
  FROM B
  WHERE  A.A_Id = B.A_Id 
  
  UNION ALL 

  SELECT 2 AS X, Name, Name2 
  FROM C
  WHERE  A.A_Id = C.A_Id 
 ) B 
 ORDER BY X
  

) B


Ну и еще его используют для развертывания табличных функций, НАПРИМЕР ПРИ ПАРЗИНГЕ xml
SELECT  T1.Name ,
        T2.ID ,
        Z.z.query('.')
FROM    @Table1 T1
        LEFT JOIN @Table2 T2 ON T1.ID = T2.ID
        OUTER APPLY T2.Data.nodes('/colloborator/custom_elems/custom_elem[name="name2"]') AS Z ( z )
24 июн 19, 13:38    [21914040]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
a_voronin
НО! есть ситуации, где нельзя сделать join, а возможен лишь apply.
Если не знаете как написать через join, то это вовсе не означает невозможность join'а
Особо показательно будет в вашем первом примере при невозможности задействовать подходящий индекс.
24 июн 19, 14:10    [21914094]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4721
invm
Особо показательно


Вот скажите мне, господин профессор джойнописания. Если нужно выбрать по 1 записи из А, а в В И С куча записей присоединяется по условию , то как вы запишите следующее:


SELECT A.*, B.*, C.* FROM A
OUTER APPPLY (
SELECT TOP 1
FROM B
WHERE A.A_Id = B.A_Id
ORDER BY X
) B
OUTER APPPLY (
SELECT TOP 1
FROM С
WHERE A.A_Id = С.A_Id
ORDER BY X
) С
24 июн 19, 15:04    [21914141]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
a_voronin
то как вы запишите следующее:


SELECT A.*, B.*, C.* FROM A
OUTER APPPLY (
SELECT TOP 1
FROM B
WHERE A.A_Id = B.A_Id
ORDER BY X
) B
OUTER APPPLY (
SELECT TOP 1
FROM С
WHERE A.A_Id = С.A_Id
ORDER BY X
) С
SELECT A.*, B.*, C.* FROM A 
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM B
) B on B.A_id = A.A_id and B.rn = 1
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM C
) C on C.A_id = A.A_id and C.rn = 1
24 июн 19, 15:15    [21914150]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4721
invm
a_voronin
то как вы запишите следующее:


SELECT A.*, B.*, C.* FROM A
OUTER APPPLY (
SELECT TOP 1
FROM B
WHERE A.A_Id = B.A_Id
ORDER BY X
) B
OUTER APPPLY (
SELECT TOP 1
FROM С
WHERE A.A_Id = С.A_Id
ORDER BY X
) С
SELECT A.*, B.*, C.* FROM A 
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM B
) B on B.A_id = A.A_id and B.rn = 1
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM C
) C on C.A_id = A.A_id and C.rn = 1


Хорошо, такой вариант имеет право на существование. Но вопрос, будет ли он оптимальней по производительности, при наличии индексов? Например, если все таблицы колумнсторы. Ведь в вашем случае, нужно вычислять rownumber для всех связанных записей.
24 июн 19, 15:28    [21914168]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
a_voronin
invm
пропущено...
SELECT A.*, B.*, C.* FROM A 
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM B
) B on B.A_id = A.A_id and B.rn = 1
left join (
SELECT
 *, row_number() over (partition by A_id order by X) as rn
FROM C
) C on C.A_id = A.A_id and C.rn = 1


Хорошо, такой вариант имеет право на существование. Но вопрос, будет ли он оптимальней по производительности, при наличии индексов? Например, если все таблицы колумнсторы. Ведь в вашем случае, нужно вычислять rownumber для всех связанных записей.

а apply будет вычислять для кадждой записи ваш топ 1, так себе оптимальность
24 июн 19, 15:30    [21914171]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
TaPaK
а apply будет вычислять для кадждой записи ваш топ 1, так себе оптимальность

при наличии индексов (a_id, X) - вполне
24 июн 19, 15:46    [21914188]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
a_voronin
Но вопрос, будет ли он оптимальней по производительности, при наличии индексов?
Я, по-вашему, просто так упомянул про невозможность индекса?
+ Изучайте
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t1 (id int identity primary key, a float);
create table dbo.t2 (id int identity primary key, t1_id int, b float);

declare @c1 int = 10000, @c2 int = 100;

insert into dbo.t1
 (a)
 select top (@c1)
  rand(checksum(newid()))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (t1_id, b)
 select
  a.id, d.b
 from
  dbo.t1 a cross apply
  (
   select top (@c2)
    rand(checksum(newid()))
   from
    master.dbo.spt_values b cross join
    master.dbo.spt_values c
  ) d(b);
go

declare @q1 nvarchar(max) = N'declare @s float;

select
 @s = a.a + b.b
from
 dbo.t1 a cross apply
 (select top (1) b from dbo.t2 where t1_id = a.id order by b) b
option
 (maxdop 1);
';

declare @q2 nvarchar(max) = N'declare @s float;

select
 @s = a.a + b.b
from
 dbo.t1 a join
 (select t1_id, b, row_number() over (partition by t1_id order by b) as rn from dbo.t2) b on b.t1_id = a.id and b.rn = 1
option
 (maxdop 1);
';

declare @qq1 nvarchar(max) = N'/*q1 with no index*/' + @q1;
declare @qq2 nvarchar(max) = N'/*q2 with no index*/' + @q2;
declare @qq3 nvarchar(max) = N'/*q1 with index*/' + @q1;
declare @qq4 nvarchar(max) = N'/*q2 with index*/' + @q2;

declare @t table (id int identity, q_label nvarchar(30));
insert into @t
values
 (N'q1 with no index'), (N'q2 with no index'), (N'q1 with index'), (N'q2 with index');

exec sp_executesql @qq1;
exec sp_executesql @qq2;

create index IX_t2__t1_id on dbo.t2 (t1_id, b);

exec sp_executesql @qq3;
exec sp_executesql @qq4;

select
 t.q_label, qp.query_plan, qs.last_elapsed_time, qs.last_worker_time, qs.last_logical_reads
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
 sys.dm_exec_sql_text(qs.sql_handle) st join
 @t t on st.text like N'/*' + t.q_label + N'*/%'
order by
 t.id
option
 (recompile);
go

drop table dbo.t1, dbo.t2;
go
24 июн 19, 15:50    [21914192]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Cross apply/ Outer apply VS Joins  [new]
MankoAnd
Member

Откуда:
Сообщений: 3
Сколько бы не читал на форума о пользе Apply. Обычно дополняют "фразой, а так он может" и добавляют агрегированную таблицу или top несколько строк. Да простым Join так не сделать но с подзапросом (получается код на строчку - две длинее) сделать и все равно выполняется несравнимо быстрее чем чере apply:

Вот примеры (с таблицами с миллионами записей), а с outer apply ещё хуже и любой пример который на сайтах показан для apply пробую он работает всегда медленнее чем черз joins и подзапросы

Вариант 1 (5 сек результат 493000)

select LocalSKU, nextPoEta 
from [SEOM08_Staging].[dbo].[Inventory] with (nolock)
inner join
(
	SELECT SKU, MIN(Date) AS nextPoEta
	FROM [EWH].[dbo].[HDM_POHistory] with (nolock)
	WHERE Type = 'E'
			AND Date != '1900-01-01'
	GROUP BY SKU
) as A
on LocalSKU=SKU
COLLATE SQL_Latin1_General_CP1_CI_AS


Вариант 2 (43 мин и только 214 записей показалось из 493000 - устал ждать)

select LocalSKU, nextPoEta 
from [SEOM08_Staging].[dbo].[Inventory] with (nolock)
cross apply 
(
	SELECT SKU, MIN(Date) AS nextPoEta
	FROM [EWH].[dbo].[HDM_POHistory] with (nolock)
	WHERE Type = 'E'
			AND Date != '1900-01-01'
			and LocalSKU=SKU collate SQL_Latin1_General_CP1_CI_AS
	GROUP BY SKU
)	 A


Вариант 3 (результаты близкие к варианту 2)

select LocalSKU, nextPoEta 
from [SEOM08_Staging].[dbo].[Inventory] with (nolock)
cross apply 
(
	SELECT TOP 1 SKU, [Date] AS nextPoEta
	FROM [EWH].[dbo].[HDM_POHistory] with (nolock)
	WHERE Type = 'E'
			AND Date != '1900-01-01'
			and LocalSKU=SKU collate SQL_Latin1_General_CP1_CI_AS
	order by 	[Date]
)	 A


Сообщение было отредактировано: 10 июл 20, 23:31
10 июл 20, 21:36    [22165506]     Ответить | Цитировать Сообщить модератору
 Re: Cross apply/ Outer apply VS Joins  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36968
MankoAnd,

Большое спасибо за информацию! Пойдем сейчас все переписывать на join!
10 июл 20, 23:38    [22165551]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить