|
Оптимизация запроса
#39632217
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 9 956
|
|
Andy_OLAPТак может быть, не рекомендовать over, а таки совсем немного подумать и предложить автору темы заскриптовать view и выложить сюда, чтобы сначала сделать group by kontr_id ,Strt_id ,ab_kart_oid, а потом уже присоединять справочники.Думать никогда не вредит. Особенно о том, что оптимизатор писали люди уж всяко не глупее себя любимого. Специально для Andy_OLAP для изучения и осознания 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.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
use tempdb;
go
create table dbo.r1 (r1_id int identity primary key, r1_Name varchar(200));
create table dbo.r2 (r2_id int identity primary key, r2_Name varchar(200));
create table dbo.r3 (r3_id int identity primary key, r3_Name varchar(200));
create table dbo.t (id int identity primary key, r1_id int not null references dbo.r1 (r1_id), r2_id int not null references dbo.r2 (r2_id), r3_id int not null references dbo.r3 (r3_id), v int);
insert into dbo.r1
(r1_Name)
select top (10)
replicate(cast(newid() as varchar(36)), 5)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
insert into dbo.r2
(r2_Name)
select top (20)
replicate(cast(newid() as varchar(36)), 5)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
insert into dbo.r3
(r3_Name)
select top (30)
replicate(cast(newid() as varchar(36)), 5)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
insert into dbo.t
(r1_id, r2_id, r3_id, v)
select top (500000)
rand(checksum(newid())) * 10 + 1,
rand(checksum(newid())) * 20 + 1,
rand(checksum(newid())) * 30 + 1,
1
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
go
declare @r1_Name varchar(200), @r2_Name varchar(200), @r3_Name varchar(200), @v int, @id uniqueidentifier, @q nvarchar(max);
declare @r table (row_order int identity, id uniqueidentifier primary key, descr varchar(100), m as '/*' + cast(id as varchar(36)) + '*/');
select @id = newid();
insert into @r values (@id, 'group by r1.r1_Name, r2.r2_Name, r3.r3_Name');
select @q = N'/*' + cast(@id as nvarchar(36)) + N'*/' +
N'declare @r1_Name varchar(200), @r2_Name varchar(200), @r3_Name varchar(200), @v int;
select
@r1_Name = r1.r1_Name, @r2_Name = r2.r2_Name, @r3_Name = r3.r3_Name, @v = sum(v)
from
dbo.t join
dbo.r1 on r1.r1_id = t.r1_id join
dbo.r2 on r2.r2_id = t.r2_id join
dbo.r3 on r3.r3_id = t.r3_id
group by
r1.r1_Name, r2.r2_Name, r3.r3_Name
option
(maxdop 1);';
exec sp_executesql @q;
select @id = newid();
insert into @r values (@id, 'group by t.r1_id, t.r2_id, t.r3_id, r1.r1_Name, r2.r2_Name, r3.r3_Name');
select @q = N'/*' + cast(@id as nvarchar(36)) + N'*/' +
N'declare @r1_Name varchar(200), @r2_Name varchar(200), @r3_Name varchar(200), @v int;
select
@r1_Name = r1.r1_Name, @r2_Name = r2.r2_Name, @r3_Name = r3.r3_Name, @v = sum(v)
from
dbo.t join
dbo.r1 on r1.r1_id = t.r1_id join
dbo.r2 on r2.r2_id = t.r2_id join
dbo.r3 on r3.r3_id = t.r3_id
group by
t.r1_id, t.r2_id, t.r3_id, r1.r1_Name, r2.r2_Name, r3.r3_Name
option
(maxdop 1);';
exec sp_executesql @q;
select @id = newid();
insert into @r values (@id, 'joins outside the group by');
select @q = N'/*' + cast(@id as nvarchar(36)) + N'*/' +
N'declare @r1_Name varchar(200), @r2_Name varchar(200), @r3_Name varchar(200), @v int;
select
@r1_Name = r1.r1_Name, @r2_Name = r2.r2_Name, @r3_Name = r3.r3_Name, @v = t.v
from
(select r1_id, r2_id, r3_id, sum(v) as v from dbo.t group by r1_id, r2_id, r3_id) t join
dbo.r1 on r1.r1_id = t.r1_id join
dbo.r2 on r2.r2_id = t.r2_id join
dbo.r3 on r3.r3_id = t.r3_id
option
(maxdop 1);';
exec sp_executesql @q;
alter table dbo.r1 add unique (r1_Name);
alter table dbo.r2 add unique (r2_Name);
alter table dbo.r3 add unique (r3_Name);
select @id = newid();
insert into @r values (@id, 'group by r1.r1_Name, r2.r2_Name, r3.r3_Name + unique constraints');
select @q = N'/*' + cast(@id as nvarchar(36)) + N'*/' +
N'declare @r1_Name varchar(200), @r2_Name varchar(200), @r3_Name varchar(200), @v int;
select
@r1_Name = r1.r1_Name, @r2_Name = r2.r2_Name, @r3_Name = r3.r3_Name, @v = sum(v)
from
dbo.t join
dbo.r1 on r1.r1_id = t.r1_id join
dbo.r2 on r2.r2_id = t.r2_id join
dbo.r3 on r3.r3_id = t.r3_id
group by
r1.r1_Name, r2.r2_Name, r3.r3_Name
option
(maxdop 1);';
exec sp_executesql @q;
select
r.descr, qs.last_elapsed_time, qs.last_logical_reads, qp.query_plan
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) t join
@r r on t.Text like '%' + r.m + '%'
order by
r.row_order;
go
drop table dbo.t, dbo.r1, dbo.r2, dbo.r3;
go
descrlast_elapsed_timelast_logical_readsgroup by r1.r1_Name, r2.r2_Name, r3.r3_Name48577576240group by t.r1_id, t.r2_id, t.r3_id, r1.r1_Name, r2.r2_Name, r3.r3_Name1965432677joins outside the group by1915102677group by r1.r1_Name, r2.r2_Name, r3.r3_Name + unique constraints1958512677 |
|
|
