Cross apply/ Outer apply VS Joins / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Cross apply/ Outer apply VS Joins

12 сообщений из 37, страница 2 из 2

все

Cross apply/ Outer apply VS Joins

#39978374

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

MankoAnd,

у Вас запросы с cross apply семантически отливаются от первого запроса. Т.е. это разные запросы по смыслу, их нельзя сравнивать.

...

Рейтинг:

0 / 0

11.07.2020, 12:22

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978489

PizzaPizza

Участник

Сообщения: 499

Рейтинг: 0 / 0

Физических оператора объединения по сути всего три:

Hash Match
Merge Join
Nested Loops

Все остальное это варианты объяснений серверу, что вам надо.
Если получается быстрее или медленнее - лучше или хуже объяснили вы.

...

Рейтинг:

0 / 0

11.07.2020, 19:43

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978558

MankoAnd

Гость

Гавриленко Сергей Алексеевич, кстати мне кажется я Вас лично знаю.
Можно иронизировать сколько угодно но пока ни один человек не показал мне запрос c cross apply или outer apply который было бы нельзя написать с join и не потерять производительности. Запросы такого плана дают одинаковый результат но с ечень разным временем

...

Рейтинг:

0 / 0

12.07.2020, 09:44

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978560

MankoAnd

Гость

MankoAnd, Автор поста задал вопрос и ответ так и не получил ответ. Такое впечатление что форум более развлекательный.
Есть ли конкретные данные (желательно большая таблица, чтоб увидеть разницу производительности) и запрос при которых apply будет единственным способом получить какой-то результат с такой скоростью. Другими способами это будет невозможно или медленно?

...

Рейтинг:

0 / 0

12.07.2020, 09:58

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978572

Ennor Tiegael

Участник

Сообщения: 3 069

Рейтинг: 0 / 0

MankoAnd,

Ну вот, например.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

declare @t table(
	TypeId int not null,
	RN int not null,
	IsActive bit not null,
	primary key (TypeId, RN)
);

insert into @t (TypeId, RN, IsActive)
values
	(1, 1, 1),
	(1, 2, 1),
	(1, 3, 1),
	(1, 4, 0),
	(1, 5, 1),
	(1, 6, 1),
	(2, 1, 1),
	(2, 2, 0),
	(2, 3, 1),
	(2, 4, 1),
	(2, 5, 1),
	(2, 6, 1);

declare @x xml = N'<r>
  <t id="1">
    <i v="279.44" />
    <i v="311.45" />
    <i v="21.17" />
    <i v="11.8" />
    <i v="0.29" />
  </t>
  <t id="2">
    <i v="21.17" />
    <i v="279.44" />
    <i v="311.45" />
    <i v="0.29" />
    <i v="11.8" />
    <i v="779" />
  </t>
</r>';

select t.TypeId, t.RN, x.c.value('./@v', 'float') as [Value]
from @t t
	cross apply @x.nodes('/r[1]/t[@id=sql:column("t.TypeId")][1]/i[position()=sql:column("t.RN")][1]') x(c)
where t.IsActive = 1;

Есть таблица и есть входящий XML-фрагмент, которые надо сджойнить по порядковому номеру XML-ноды. Так как XML - не таблица, и в нем порядок нод имеет значение, то в силу данного обстоятельства ноды, разумеется, не пронумерованы.

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

...

Рейтинг:

0 / 0

12.07.2020, 10:53

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978591

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

MankoAnd,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.a (id int identity primary key, x float);
create table dbo.b (id int identity primary key, a_id int, y float, s varchar(50));

declare @ac int = 10000, @bc int = 1000;

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

insert into dbo.b
 (a_id, y, s)
 select
  a.id, t.y, t.s
 from
  dbo.a cross apply
  (
   select top (cast(rand(checksum(newid())) * @bc + 1 as int))
    rand(checksum(newid())),
    cast(newid() as varchar(50))
   from
    master.dbo.spt_values a cross join
    master.dbo.spt_values b
  ) t(y, s);

create index IX_b__a_id on dbo.b(a_id, y);
go

declare @x float, @y float, @s varchar(50);

set statistics xml on;
set statistics time on;

select
 @x = a.x, @y = b.y, @s = b.s
from
 dbo.a cross apply
 (select top (1) y, s from dbo.b where a_id = a.id and y >= a.x order by y) b(y, s);

set statistics xml off;
set statistics time off;
go

drop table dbo.a, dbo.b;
go

Дерзайте.

...

Рейтинг:

0 / 0

12.07.2020, 12:00

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978595

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Ennor Tiegael,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

with x as
(
 select
  row_number() over (partition by a.n order by b.n) as RN,
  a.n.value('@id', 'int') as TypeId,
  b.n.value('@v', 'float') as [Value]
 from
  @x.nodes('/r/t') a(n) cross apply
  a.n.nodes('i') b(n)
)
select
 t.TypeId, t.RN, x.[Value]
from
 @t t join
 x on x.TypeID = t.TypeId and x.RN = t.RN
where
 t.IsActive = 1;

...

Рейтинг:

0 / 0

12.07.2020, 12:13

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978673

Ennor Tiegael

Участник

Сообщения: 3 069

Рейтинг: 0 / 0

invm,

Пара моментов:
1.

Код: sql

partition by a.n order by b.n

- простите, это как? Ну т.е. я вижу, что это работает - в данном конкретном случае. Это вообще где-нибудь документировано? Есть какие-либо гарантии, что оно всегда так работает, или это очередной хак, типа расчета нарастающего итога в апдейте через переменную?
Вывести такой столбец в обычный ORDER BY сиквел не позволяет, и мне не понятно, по чему именно он сортирует, хотя и явно не по текстовому представлению XML-ноды.

2. Ваш запрос, буквально в опубликованном виде, съедает 97% стоимости всего батча, на мой вариант приходятся оставшиеся 3%. Переделка TV на временную таблицу слегка улучшает ситуацию, до уровня 75:25. Единственное, как мне удалось заставить ваш запрос работать дешевле моего, это изменением первого XPath на

Код: sql

'/r[1]/t'

В этом случае отношение query cost становится 9:91 в вашу пользу.

Мда, неудачный пример наверное - слишком многое зависит от внутренних особенностей XML-парсера. Но за вариант спасибо, интересный прикол.

...

Рейтинг:

0 / 0

12.07.2020, 16:53

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978694

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Ennor Tiegael

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

Явно документировано только то, что контекстную ноду нельзя материализовать, но можно использовать в count и is null. Видимо в ранжирующих/оконных функциях тоже можно.
Другого объяснения у меня нет.

Ennor Tiegael

Ваш запрос, буквально в опубликованном виде, съедает 97% стоимости всего батча, на мой вариант приходятся оставшиеся 3%

В данном случае не стоит ориентироваться на стоимость.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.

declare @t table(
	TypeId int not null,
	RN int not null,
	IsActive bit not null,
	primary key (TypeId, RN)
);

insert into @t (TypeId, RN, IsActive)
values
	(1, 1, 1),
	(1, 2, 1),
	(1, 3, 1),
	(1, 4, 0),
	(1, 5, 1),
	(1, 6, 1),
	(2, 1, 1),
	(2, 2, 0),
	(2, 3, 1),
	(2, 4, 1),
	(2, 5, 1),
	(2, 6, 1);

declare @x0 xml = N'<r>
  <t id="1">
    <i v="279.44" />
    <i v="311.45" />
    <i v="21.17" />
    <i v="11.8" />
    <i v="0.29" />
  </t>
  <t id="2">
    <i v="21.17" />
    <i v="279.44" />
    <i v="311.45" />
    <i v="0.29" />
    <i v="11.8" />
    <i v="779" />
  </t>
</r>';

declare @c int = 1000;
declare @x xml;

select
 @x = t.x
from
 (
  select
   row_number() over (order by 1/0) as [@id],
   a.x as [*]
  from
   (values (@x0.query('/r[1]/t/i')), (@x0.query('/r[2]/t/i'))) a(x) cross apply
   (select top (@c) row_number() over (order by 1/0) from master.dbo.spt_values b cross join master.dbo.spt_values c) d(TypeId)
  for xml path('t'), root('r'), type
 ) t(x);

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t(
	TypeId int not null,
	RN int not null,
	IsActive bit not null,
	primary key (TypeId, RN)
);

insert into #t
 (TypeId, RN, IsActive)
 select
  a.TypeId + (b.rn - 1) * 2,
  row_number() over (partition by a.TypeId + (b.rn - 1) * 2 order by 1/0),
  a.IsActive
 from
  @t a cross apply
  (select top (@c) row_number() over (order by 1/0) from master.dbo.spt_values b cross join master.dbo.spt_values c) b(rn)

set statistics time on;

select 
 count(*)
from #t t
	cross apply @x.nodes('/r[1]/t[@id=sql:column("t.TypeId")][1]/i[position()=sql:column("t.RN")][1]') x(c)
where t.IsActive = 1;

with x as
(
 select
  row_number() over (partition by a.n order by b.n) as RN,
  a.n.value('@id', 'int') as TypeId,
  b.n.value('@v', 'float') as [Value]
 from
  @x.nodes('/r/t') a(n) cross apply
  a.n.nodes('i') b(n)
)
select
 count(*)
from
 #t t join
 x on x.TypeID = t.TypeId and x.RN = t.RN
where
 t.IsActive = 1;

set statistics time off;

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SQL Server parse and compile time: 
   CPU time = 15 ms, elapsed time = 22 ms.

(1 row affected)

  SQL Server Execution Times:
   CPU time = 161749 ms,  elapsed time = 51828 ms. 

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 18 ms.

(1 row affected)

  SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 139 ms .

...

Рейтинг:

0 / 0

12.07.2020, 19:00

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978728

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Ennor Tiegael,

Вот "бессюрпризный" вариант

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

with x as
(
 select
  a.n.value('@id', 'int') as TypeId,
  c.n.value('@position', 'int') as RN,
  c.n.value('@v', 'float') as [Value]
 from
  @x.nodes('/r/t') a(n) cross apply
  (select a.n.query('for $i in i return <i position="{count(i[. << $i]) + 1}">{$i[position() = count(i[. << $i]) + 1]/@v}</i>')) b(x) cross apply
  b.x.nodes('i') c(n)
)
select
 count(*)
from
 #t t join
 x on x.TypeID = t.TypeId and x.RN = t.RN
where
 t.IsActive = 1;

...

Рейтинг:

0 / 0

12.07.2020, 22:24

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978752

Ennor Tiegael

Участник

Сообщения: 3 069

Рейтинг: 0 / 0

invm,

Трюк с получением позиции ноды через FLWOR я знаю, и он заметно медленнее. Но конечно да, ваш row_number() бьет по производительности все, что угодно. Мне даже в голову не приходило, что так можно :)

Мда, отстал я в развитии. Вот что бывает, когда переезжаешь в страну, где нет работы по специальности...

...

Рейтинг:

0 / 0

13.07.2020, 04:16

| Ответить | Цитировать | Написать

Cross apply/ Outer apply VS Joins

#39978803

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

Ennor Tiegael

Трюк с получением позиции ноды через FLWOR я знаю, и он заметно медленнее.

По сравнению с вашим вариантом, вполне себе быстро.

Код: sql

declare @t table(
	TypeId int not null,
	RN int not null,
	IsActive bit not null,
	primary key (TypeId, RN)
);

insert into @t (TypeId, RN, IsActive)
values
	(1, 1, 1),
	(1, 2, 1),
	(1, 3, 1),
	(1, 4, 0),
	(1, 5, 1),
	(1, 6, 1),
	(2, 1, 1),
	(2, 2, 0),
	(2, 3, 1),
	(2, 4, 1),
	(2, 5, 1),
	(2, 6, 1);

declare @x0 xml = N'<r>
  <t id="1">
    <i v="279.44" />
    <i v="311.45" />
    <i v="21.17" />
    <i v="11.8" />
    <i v="0.29" />
  </t>
  <t id="2">
    <i v="21.17" />
    <i v="279.44" />
    <i v="311.45" />
    <i v="0.29" />
    <i v="11.8" />
    <i v="779" />
  </t>
</r>';

declare @c int = 1000;
declare @x xml;

select
 @x = t.x
from
 (
  select
   row_number() over (order by 1/0) as [@id],
   a.x as [*]
  from
   (values (@x0.query('/r[1]/t/i')), (@x0.query('/r[2]/t/i'))) a(x) cross apply
   (select top (@c) row_number() over (order by 1/0) from master.dbo.spt_values b cross join master.dbo.spt_values c) d(TypeId)
  for xml path('t'), root('r'), type
 ) t(x);

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t(
	TypeId int not null,
	RN int not null,
	IsActive bit not null,
	primary key (TypeId, RN)
);

insert into #t
 (TypeId, RN, IsActive)
 select
  a.TypeId + (b.rn - 1) * 2,
  row_number() over (partition by a.TypeId + (b.rn - 1) * 2 order by 1/0),
  a.IsActive
 from
  @t a cross apply
  (select top (@c) row_number() over (order by 1/0) from master.dbo.spt_values b cross join master.dbo.spt_values c) b(rn)

set statistics time on;

select 
 count(*)
from #t t
	cross apply @x.nodes('/r[1]/t[@id=sql:column("t.TypeId")][1]/i[position()=sql:column("t.RN")][1]') x(c)
where t.IsActive = 1;

with x as
(
 select
  row_number() over (partition by a.n order by b.n) as RN,
  a.n.value('@id', 'int') as TypeId,
  b.n.value('@v', 'float') as [Value]
 from
  @x.nodes('/r/t') a(n) cross apply
  a.n.nodes('i') b(n)
)
select
 count(*)
from
 #t t join
 x on x.TypeID = t.TypeId and x.RN = t.RN
where
 t.IsActive = 1;

with x as
(
 select
  a.n.value('@id', 'int') as TypeId,
  c.n.value('@position', 'int') as RN,
  c.n.value('@v', 'float') as [Value]
 from
  @x.nodes('/r/t') a(n) cross apply
  (select a.n.query('for $i in i return <i position="{count(i[. << $i]) + 1}">{$i[position() = count(i[. << $i]) + 1]/@v}</i>')) b(x) cross apply
  b.x.nodes('i') c(n)
)
select
 count(*)
from
 #t t join
 x on x.TypeID = t.TypeId and x.RN = t.RN
where
 t.IsActive = 1;

set statistics time off;

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

SQL Server parse and compile time: 
   CPU time = 13 ms, elapsed time = 13 ms.

(1 row affected)

  SQL Server Execution Times:
   CPU time = 167827 ms,  elapsed time = 64312 ms. 

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 20 ms.

(1 row affected)

  SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 148 ms. 

SQL Server parse and compile time: 
   CPU time = 31 ms, elapsed time = 33 ms.

(1 row affected)

  SQL Server Execution Times:
   CPU time = 1124 ms,  elapsed time = 488 ms.

...

Рейтинг:

0 / 0

13.07.2020, 09:48

| Ответить | Цитировать | Написать

12 сообщений из 37, страница 2 из 2

все

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Cross apply/ Outer apply VS Joins

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&gotonew=1&tid=1685887]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	27ms
get topic data:	13ms
get first new msg:	8ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	1ms
others:	274ms

total:	411ms