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

Откуда:
Сообщений: 555
Коллеги! Сабж! ))
Привожу упрощённый пример, но суть сохраняется.
Есть таблица
create table #t1 
(
	id1 int, id2 int, id3 int, vol1 int, vol2 int, cost int
)

insert into #t1 (id1,id2,id3,vol1,vol2, cost) 
select 1,2,3,5,6,4
union all
select 1,1,3,5,0,4
union all
select 2,2,3,5,6,5
union all
select 1,2,3,5,6,4
union all
select 2,2,3,7,6,4
union all
select 1,2,3,5,6,8
union all
select 3,2,3,1,3,9
union all
select 1,3,3,5,6,4
union all
select 1,2,3,5,6,5
union all
select 2,2,4,5,2,4

.. и к ней выборка вида
select 
	sum(vol1) over(partition by id1) as sum_1,
	sum(vol2) over(partition by id2) as sum_2,
	sum(cost) over(partition by id2, id3) as sum_3
from #t1


В реальности всё гораздо сложнее и десятки миллионов записей и больше полей в выборке, но.. там тоже есть три значения получаемые аналитическими функциями с разными "partition by" и они пожирают более 40% плана запроса и не дают использовать индексы. Когда-то с подобным сталкивался, но не могу вспомнить как обходил эту проблему. Подскажите, кто в курсе!
18 янв 16, 18:45    [18697862]     Ответить | Цитировать Сообщить модератору
 Re: Наведите на ум, коллеги!  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Индексы можно задействовать (и тем самым избавиться от сортировок) вот так:
+
create table #t1 
(
	id int identity primary key, id1 int, id2 int, id3 int, vol1 int, vol2 int, cost int
);

insert into #t1 (id1,id2,id3,vol1,vol2, cost) 
select 1,2,3,5,6,4
union all
select 1,1,3,5,0,4
union all
select 2,2,3,5,6,5
union all
select 1,2,3,5,6,4
union all
select 2,2,3,7,6,4
union all
select 1,2,3,5,6,8
union all
select 3,2,3,1,3,9
union all
select 1,3,3,5,6,4
union all
select 1,2,3,5,6,5
union all
select 2,2,4,5,2,4;
go

create index IX_#t1__id1 on #t1 (id1) include (vol1);
create index IX_#t1__id2 on #t1 (id2) include (vol2);
create index IX_#t1__id2__id3 on #t1 (id2, id3) include (cost);
go

set statistics xml on;

select
    a.id1, a.id2, a.id3, 
	b.sum_1,
	c.sum_2,
	d.sum_3
from
 #t1 a join
 (select id, sum(vol1) over(partition by id1) from #t1) b(id, sum_1) on b.id = a.id join
 (select id, sum(vol2) over(partition by id2) from #t1) c(id, sum_2) on c.id = a.id join
 (select id, sum(cost) over(partition by id2, id3) from #t1) d(id, sum_3) on d.id = a.id;

set statistics xml off;
go

drop table #t1;
go

А еще лучше сделать три индексированных агрегирующих представления.
18 янв 16, 19:09    [18697970]     Ответить | Цитировать Сообщить модератору
 Re: Наведите на ум, коллеги!  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Тьфу, торможу. Вот так:
+
create table #t1 
(
	id1 int, id2 int, id3 int, vol1 int, vol2 int, cost int
);

insert into #t1 (id1,id2,id3,vol1,vol2, cost) 
select 1,2,3,5,6,4
union all
select 1,1,3,5,0,4
union all
select 2,2,3,5,6,5
union all
select 1,2,3,5,6,4
union all
select 2,2,3,7,6,4
union all
select 1,2,3,5,6,8
union all
select 3,2,3,1,3,9
union all
select 1,3,3,5,6,4
union all
select 1,2,3,5,6,5
union all
select 2,2,4,5,2,4;
go

create index IX_#t1__id1 on #t1 (id1) include (vol1);
create index IX_#t1__id2 on #t1 (id2) include (vol2);
create index IX_#t1__id2__id3 on #t1 (id2, id3) include (cost);
go

set statistics xml on;

select
    a.id1, a.id2, a.id3, 
	b.sum_1,
	c.sum_2,
	d.sum_3
from
 #t1 a join
 (select id1, sum(vol1) over(partition by id1) from #t1) b(id1, sum_1) on b.id1 = a.id1 join
 (select id2, sum(vol2) over(partition by id2) from #t1) c(id2, sum_2) on c.id2 = a.id2 join
 (select id2, id3, sum(cost) over(partition by id2, id3) from #t1) d(id2, id3, sum_3) on d.id2 = a.id2 and d.id3 = a.id3;

set statistics xml off;
go

drop table #t1;
Но надо смотреть, что будет выгодней - ваш первоначальный запрос или индексы + хеш джоины.
18 янв 16, 19:21    [18698000]     Ответить | Цитировать Сообщить модератору
 Re: Наведите на ум, коллеги!  [new]
Mairos
Member

Откуда:
Сообщений: 555
invm, благодарю! Буду смотреть и сравнивать! ))
19 янв 16, 10:37    [18699900]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить