ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Повышение быстродействия выполнения запроса
25 сообщений из 27, страница 1 из 2
  1  все
Повышение быстродействия выполнения запроса
    #38304464
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
Есть таблица:
Код: 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.
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE dbo.[cur](
	[id] bigint IDENTITY(-9223372036854775808,1) NOT NULL,
	[value] [decimal](23, 10) NOT NULL,
	[discount] [decimal](23, 10) NOT NULL,
	[remainder] [decimal](23, 10) NOT NULL,
	[campaign_id] uniqueidentifier NOT NULL,
	[card_id] uniqueidentifier NOT NULL,
	[operation_type_id] char(1) not null
 CONSTRAINT [pk_cur] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) 

go

CREATE NONCLUSTERED INDEX [idx_cur_campaign_id_inc] ON [dbo].[cur]
(
	[campaign_id] ASC
)
INCLUDE ( [remainder],
	[value],
	[discount],
	[operation_type_id],
	[card_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO



В таблице на данный момент 46 485 293 записей.

Выполняю запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select b.campaign_id, b.card_id, 
isnull(sum(isnull(remainder, 0)), 0) as active_balance,
-sum(case when operation_type_id = 'C' then value else 0 end) as credit,
sum(case when operation_type_id = 'D' then value else 0 end) as debet,
sum(case when operation_type_id = 'D' then value else 0 end)-sum(case when operation_type_id = 'C' then value else 0 end) as balance,
isnull(case	when max(isnull(discount, 0)) > 100 then 100
						else max(isnull(discount, 0))
				   end, 0) as discount
from dbo.cur b with (nolock)
group by campaign_id, card_id



Запрос отрабатывает достаточно долго, около 3 минут.

План во вложении и тут:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select b.campaign_id, b.card_id, 
isnull(sum(isnull(remainder, 0)), 0) as active_balance,
-sum(case when operation_type_id = 'C' then value else 0 end) as credit,
sum(case when operation_type_id = 'D' then value else 0 end) as debet,
sum(case when operation_type_id = 'D' then value else 0 end)-sum(case when operation_type_id = 'C' then value else 0 end) as balance,
isnull(case	when max(isnull(discount, 0)) > 100 then 100
						else max(isnull(discount, 0))
				   end, 0) as discount
from dbo.cur b with (nolock)
group by campaign_id, card_id	1	1	0	NULL	NULL	1	NULL	593742	NULL	NULL	NULL	698,3344	NULL	NULL	SELECT	0	NULL
  |--Compute Scalar(DEFINE:([Expr1006]=isnull([Expr1002],(0.0000000000)), [Expr1007]= -[Expr1003], [Expr1008]=[Expr1004]-[Expr1003], [Expr1009]=isnull(CASE WHEN [Expr1005]>(100.0000000000) THEN (100.0000000000) ELSE [Expr1005] END,(0.0000000000))))	1	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1006]=isnull([Expr1002],(0.0000000000)), [Expr1007]= -[Expr1003], [Expr1008]=[Expr1004]-[Expr1003], [Expr1009]=isnull(CASE WHEN [Expr1005]>(100.0000000000) THEN (100.0000000000) ELSE [Expr1005] END,(0.0000000000)))	[Expr1006]=isnull([Expr1002],(0.0000000000)), [Expr1007]= -[Expr1003], [Expr1008]=[Expr1004]-[Expr1003], [Expr1009]=isnull(CASE WHEN [Expr1005]>(100.0000000000) THEN (100.0000000000) ELSE [Expr1005] END,(0.0000000000))	593742	0	0,0593742	120	698,3344	[b].[campaign_id], [b].[card_id], [Expr1004], [Expr1006], [Expr1007], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
       |--Parallelism(Gather Streams)	1	3	2	Parallelism	Gather Streams	NULL	NULL	593742	0	3,186168	103	698,275	[b].[campaign_id], [b].[card_id], [Expr1002], [Expr1003], [Expr1004], [Expr1005]	NULL	PLAN_ROW	1	1
            |--Hash Match(Aggregate, HASH:([b].[campaign_id], [b].[card_id]), RESIDUAL:([Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] = [Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] AND [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id] = [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id]) DEFINE:([Expr1002]=SUM([partialagg1016]), [Expr1003]=SUM([partialagg1017]), [Expr1004]=SUM([partialagg1018]), [Expr1005]=MAX([partialagg1019])))	1	4	3	Hash Match	Aggregate	HASH:([b].[campaign_id], [b].[card_id]), RESIDUAL:([Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] = [Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] AND [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id] = [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id])	[Expr1002]=SUM([partialagg1016]), [Expr1003]=SUM([partialagg1017]), [Expr1004]=SUM([partialagg1018]), [Expr1005]=MAX([partialagg1019])	593742	0	27,87358	103	695,0888	[b].[campaign_id], [b].[card_id], [Expr1002], [Expr1003], [Expr1004], [Expr1005]	NULL	PLAN_ROW	1	1
                 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([b].[campaign_id], [b].[card_id]))	1	5	4	Parallelism	Repartition Streams	PARTITION COLUMNS:([b].[campaign_id], [b].[card_id])	NULL	1187484	0	7,508313	103	667,2152	[b].[campaign_id], [b].[card_id], [partialagg1016], [partialagg1017], [partialagg1018], [partialagg1019]	NULL	PLAN_ROW	1	1
                      |--Hash Match(Partial Aggregate, HASH:([b].[campaign_id], [b].[card_id]), RESIDUAL:([Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] = [Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] AND [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id] = [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id]) DEFINE:([partialagg1016]=SUM([Loyalty_DS].[dbo].[cur].[remainder] as [b].[remainder]), [partialagg1017]=SUM([Expr1011]), [partialagg1018]=SUM([Expr1012]), [partialagg1019]=MAX([Loyalty_DS].[dbo].[cur].[discount] as [b].[discount])))	1	6	5	Hash Match	Partial Aggregate	HASH:([b].[campaign_id], [b].[card_id]), RESIDUAL:([Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] = [Loyalty_DS].[dbo].[cur].[campaign_id] as [b].[campaign_id] AND [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id] = [Loyalty_DS].[dbo].[cur].[card_id] as [b].[card_id]), DEFINE:([partialagg1016]=SUM([Loyalty_DS].[dbo].[cur].[remainder] as [b].[remainder]), [partialagg1017]=SUM([Expr1011]), [partialagg1018]=SUM([Expr1012]), [partialagg1019]=MAX([Loyalty_DS].[dbo].[cur].[discount] as [b].[discount]))	[partialagg1016]=SUM([Loyalty_DS].[dbo].[cur].[remainder] as [b].[remainder]), [partialagg1017]=SUM([Expr1011]), [partialagg1018]=SUM([Expr1012]), [partialagg1019]=MAX([Loyalty_DS].[dbo].[cur].[discount] as [b].[discount])	1187484	0	201,3933	103	659,7069	[b].[campaign_id], [b].[card_id], [partialagg1016], [partialagg1017], [partialagg1018], [partialagg1019]	NULL	PLAN_ROW	1	1
                           |--Compute Scalar(DEFINE:([Expr1011]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='C' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END, [Expr1012]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='D' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END))	1	7	6	Compute Scalar	Compute Scalar	DEFINE:([Expr1011]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='C' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END, [Expr1012]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='D' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END)	[Expr1011]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='C' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END, [Expr1012]=CASE WHEN [Loyalty_DS].[dbo].[cur].[operation_type_id] as [b].[operation_type_id]='D' THEN [Loyalty_DS].[dbo].[cur].[value] as [b].[value] ELSE (0.0000000000) END	4,648529E+07	0	2,324265	91	458,3136	[b].[discount], [b].[remainder], [b].[campaign_id], [b].[card_id], [Expr1011], [Expr1012]	NULL	PLAN_ROW	1	1
                                |--Index Scan(OBJECT:([Loyalty_DS].[dbo].[cur].[idx_cur_campaign_id_inc] AS [b]))	1	8	7	Index Scan	Index Scan	OBJECT:([Loyalty_DS].[dbo].[cur].[idx_cur_campaign_id_inc] AS [b])	[b].[value], [b].[discount], [b].[remainder], [b].[campaign_id], [b].[card_id], [b].[operation_type_id]	4,648529E+07	430,4224	25,56699	79	455,9894	[b].[value], [b].[discount], [b].[remainder], [b].[campaign_id], [b].[card_id], [b].[operation_type_id]	NULL	PLAN_ROW	1	1



Версия сервера:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Уважаемые участники форума, подскажите, каким образом сократить время выполнения запроса до приемлемого?
...
Рейтинг: 0 / 0
20.06.2013, 11:59
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304490
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
индексированное представление не предлагать?
...
Рейтинг: 0 / 0
20.06.2013, 12:10
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304514
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
Мистер Хенки,
а как оно поможет?
...
Рейтинг: 0 / 0
20.06.2013, 12:18
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304550
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
_ч_Мистер Хенки,
а как оно поможет?
все сведется к сканированию индекса представления, вместо агрегирования . Полагаю количество операций чтения уменьшится равно как и потребление процессорного времени, что должно привести к убыстрению запроса.
...
Рейтинг: 0 / 0
20.06.2013, 12:29
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304559
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_,

Действительный план лучше выкладывать файлом в формате xml/sqlplan.
А что за значок там такой на хэш-джойне, не предупреждение о спилл, случайно? Как отличаются действительное число строк от оценок? Если отличаются, можно попробовать создать многоколоночную статистику:
Код: sql
1.
create statistics s_campaign_id_card_id on dbo.cur(campaign_id,card_id);


Это поможет оптимизатору лучше оценить кардинальность группировки.

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

Также такой запрос очень хорошо подходит для Columnstore индекса. Минус в том, что в текущей реализации он неизменяем (что фактически приводит к невозможности изменения таблицы), так что минусом будет необходимость продумывания стратегии обновления.

Можно еще попробовать избавиться от repartition streams, соединив при помощи apply таблицу с таблицей в которой будут дистинктные значения campaign_id, card_id, - но не знаю, насколько это у вас применимо, нужно экспериментировать.
...
Рейтинг: 0 / 0
20.06.2013, 12:33
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304627
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Индексированное представление сделать не получится -- наличествует функция max.
...
Рейтинг: 0 / 0
20.06.2013, 12:55
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304704
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
invmИндексированное представление сделать не получится -- наличествует функция max.
тогда придется считать отдельно это поле, будет ли так быстрее - сложно сказать, но попробовать проверить можно.
...
Рейтинг: 0 / 0
20.06.2013, 13:26
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304790
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
Мистер Хенки, т.е. Вы предлагаете сделать индексированное представление, в котором будет вызываться мой запрос?
Я боюсь, что это плохой вариант, т.к. во-1 эта таблица самая большая в БД, а во-вторых есть функции max.

SomewhereSomehow , план во вложении .

А что за значок там такой на хэш-джойне, не предупреждение о спилл, случайно?

Это предупреждение о использовании tempbd, что не удивительно при таких объемах данных.

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

В любом случае, спасибо Вам за ответы, буду эксперементировать.
...
Рейтинг: 0 / 0
20.06.2013, 13:59
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38304852
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_Это предупреждение о использовании tempbd, что не удивительно при таких объемах данных.
Это и есть spill. Т.е. слив в темпдб.

Посмотрите, какая огромная разница в оценках. Сервер ошибается более чем в 10 раз. Соответственно и памяти выделает в 10 раз меньше чем нужно. Так что, я бы не спешил грешить на объемы.
Если мультиколоночную статистику сервер при оценке не использовал, то посмотрите, какая статистика у вас по колонкам [Loyalty_DS].[dbo].[cur].campaign_id и [Loyalty_DS].[dbo].[cur].card_id. Есть ли она, актуальная ли она? Подробная ли?
Может попробовать обновить эти две статистики с опцией with fullscan.
Если все в порядке и это не помогает, попробуйте включить флаг 2301
2301 Флаг трассировки: включить дополнительные решения для оптимизации поддержки
MSФлаг трассировки 2301 позволяет дополнительно оптимизации, которые зависят от запросов поддержки принятия решений. Этот параметр применяется для поддержки принятия решений обработки больших наборов данных.

Можно включить флаг трассировки 2301 при запуске или во время сеанса пользователя. При включении флага трассировки 2301 при запуске, флаг трассировки имеет глобальную область действия. При включении флага трассировки 2301 в сеансе пользователя, флаг трассировки имеет областью действия сеанса.
Он включает расширенное моделирование в оптимизаторе и изменяет оценку во многих случаях.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select b.campaign_id, b.card_id, 
isnull(sum(isnull(remainder, 0)), 0) as active_balance,
-sum(case when operation_type_id = 'C' then value else 0 end) as credit,
sum(case when operation_type_id = 'D' then value else 0 end) as debet,
sum(case when operation_type_id = 'D' then value else 0 end)-sum(case when operation_type_id = 'C' then value else 0 end) as balance,
isnull(case	when max(isnull(discount, 0)) > 100 then 100
						else max(isnull(discount, 0))
				   end, 0) as discount
from dbo.cur b with (nolock)
group by campaign_id, card_id
option(querytraceon 2301)

...
Рейтинг: 0 / 0
20.06.2013, 14:28
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305040
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
SomewhereSomehow,
Спасибо Вам большое за ответы.

Я создал отдельно статистику по card_id, по campaign_id и по card_id,campaign_id. Обновил её. Не помогло.

Запустил запрос с опцией option (querytraceon 2301)

План в xml прикреплен.

К сожалению, время выполнения сильно не поменялось.
...
Рейтинг: 0 / 0
20.06.2013, 16:05
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305050
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_,
Это интересно. План не прикрепился, кстати.
...
Рейтинг: 0 / 0
20.06.2013, 16:09
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305102
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
Sorry, вот план
...
Рейтинг: 0 / 0
20.06.2013, 16:37
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305125
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_,

Да, интересно, оценка улучшилась, но не на порядок. Можете выполнить скрипт и приложить получившийся xml-ник?
 

Код: 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.
declare @stats table(name sysname, cols varchar(max), stream varbinary(max), r bigint, p bigint);
declare @stat table(stream varbinary(max), r bigint, p bigint);

declare @stat_name sysname, @stat_id bigint, @sql varchar(max), @cols varchar(max);
declare cur cursor local read_only forward_only static for
select name, stats_id from sys.stats where [object_id] = object_id('dbo.[cur]','U');
open cur;
fetch from cur into @stat_name, @stat_id;
while @@fetch_status = 0 begin
	
	set @cols = (select col = col_name([object_id],column_id)+', ' from sys.stats_columns where [object_id] = object_id('dbo.[cur]','U') and stats_id = @stat_id
	for xml path(''),type).value('.','varchar(max)');
	
	set @sql = 'dbcc show_statistics (''dbo.[cur]'',<@stat_name>) with stats_stream;';
	set @sql = replace(@sql,'<@stat_name>',@stat_name);
	insert @stat exec (@sql);	
	insert @stats select @stat_name, @cols, * from @stat;
	delete from @stat;
	
	fetch from cur into @stat_name, @stat_id;
	
end
close cur;
deallocate cur;
select name, cols, stream = convert(varchar(max),stream,1), r, p from @stats for xml path('stat'),root('cur_stats'), type;

...
Рейтинг: 0 / 0
20.06.2013, 16:49
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305165
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
SomewhereSomehow,

Во вложении
...
Рейтинг: 0 / 0
20.06.2013, 17:13
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305819
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_,

Спасибо. Теперь есть репро, попробую посмотреть в чем там дело.
...
Рейтинг: 0 / 0
21.06.2013, 10:25
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38305967
Фотография buser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
buser
Участник
Дык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)
...
Рейтинг: 0 / 0
21.06.2013, 11:39
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306019
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
buserДык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)

Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.
...
Рейтинг: 0 / 0
21.06.2013, 12:02
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306024
Фотография Сергей Викт.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Викт.
Участник
_ч_buserДык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)

Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.
При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения места
...
Рейтинг: 0 / 0
21.06.2013, 12:03
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306051
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
Сергей Викт._ч_Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения местаможно выбирать количество хранимых агрегатов. то есть хранить не для каждой записи, а для некоторых контрольных точек (день/неделя/месяц) и в расчётах опираться на них
...
Рейтинг: 0 / 0
21.06.2013, 12:16
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306066
Фотография Сергей Викт.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Викт.
Участник
ShakillСергей Викт.пропущено...
При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения местаможно выбирать количество хранимых агрегатов. то есть хранить не для каждой записи, а для некоторых контрольных точек (день/неделя/месяц) и в расчётах опираться на них
Безусловно, всё зависит от конкретных задач и потребностей.
...
Рейтинг: 0 / 0
21.06.2013, 12:25
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306068
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
Еще забыл добавить одну вещь.

Если в таблице 2 млн записей, то запрос вернет ~ 700 тыс. строк, т.е. на каждый card_id, campaign_id по 2-3 записи. Не знаю, важно ли это.

Код: sql
1.
2.
select card_id, campaign_id, count(*) from dbo.cur
group by campaign_id, card_id



в среднем будет так:
00000000-0000-0000-0000-00000006CBAA 93D5874A-9435-E211-A324-00155DFA260E 2
00000000-0000-0000-0000-0000000A0FEA 93D5874A-9435-E211-A324-00155DFA260E 1
00000000-0000-0000-0000-00000003976A 93D5874A-9435-E211-A324-00155DFA260E 2
00000000-0000-0000-0000-0000000A0BAA 93D5874A-9435-E211-A324-00155DFA260E 3
00000000-0000-0000-0000-00000006C98A 93D5874A-9435-E211-A324-00155DFA260E 1
00000000-0000-0000-0000-00000005F54A 93D5874A-9435-E211-A324-00155DFA260E 3
00000000-0000-0000-0000-000000082DCA 93D5874A-9435-E211-A324-00155DFA260E 9
...
Рейтинг: 0 / 0
21.06.2013, 12:26
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306074
Фотография Сергей Викт.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Викт.
Участник
_ч_Еще забыл добавить одну вещь.

Если в таблице 2 млн записей, то запрос вернет ~ 700 тыс. строк, т.е. на каждый card_id, campaign_id по 2-3 записи. Не знаю, важно ли это.

Код: sql
1.
2.
select card_id, campaign_id, count(*) from dbo.cur
group by campaign_id, card_id



в среднем будет так:
00000000-0000-0000-0000-00000006CBAA 93D5874A-9435-E211-A324-00155DFA260E 2
00000000-0000-0000-0000-0000000A0FEA 93D5874A-9435-E211-A324-00155DFA260E 1
00000000-0000-0000-0000-00000003976A 93D5874A-9435-E211-A324-00155DFA260E 2
00000000-0000-0000-0000-0000000A0BAA 93D5874A-9435-E211-A324-00155DFA260E 3
00000000-0000-0000-0000-00000006C98A 93D5874A-9435-E211-A324-00155DFA260E 1
00000000-0000-0000-0000-00000005F54A 93D5874A-9435-E211-A324-00155DFA260E 3
00000000-0000-0000-0000-000000082DCA 93D5874A-9435-E211-A324-00155DFA260E 9
Ну если рассматривать вариант хранения агрегированных значений, то важно, т.к. чем меньше результатов агрегации. тем меньше размер таблицы.
...
Рейтинг: 0 / 0
21.06.2013, 12:29
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306401
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
Посмотрел планы и статистику.


Что увидел хорошего:
1. Оценки улучшились, для второго хэша 187К на вход и 593К на выход против 2000К на вход 1015К на выход. Соответственно - пропал спилл.
2. Оценки, на выходе, совпадают с реальностью 1015К vs 1015K - это радует.

Что плохого:
3. Оценки на этапе локальной агрегации сильно плохи. Реально 13149К против 2030К. Тут к сожалению, мы упираемся в ограничение модели. На этапе оптимизации, оптимизатор оценивает кардинальность самой первой глобальной агрегации как 1 015 180. Когда он разбивает агрегацию на две, локальную и глобальную, локальную (так что будет выполняться каждым потоком) моделирует как число доступных потоков * глобальная кардинальность, предполагая однородность данных. Число доступных потоков - половина он DOP. получается 1 015 180* (4/2) = 2 030 360. В реальности получается данные, внутри каждого локального куска, группируются вовсе не с такой плотностью, на которую рассчитывал оптимизатор и имеем сильное превышение.

1. Можно попробовать, сделать рерайт запроса, сджойнив его с временной таблицей, в которую предварительно записать card_id, campaign_id или их сочетания - нужно экспериментировать. Что-то похожее описано тут .

2. Можно попробовать, заменить hash group на stream group указав в конце запроса option(order group). Возможно, при таком типе группировки, искажения будут минимальны, в отличие от хэш группы. Только если использовать option(order group), нужно чтобы был индекс:
[opt].[dbo].[cur].card_id Ascending; [opt].[dbo].[cur].campaign_id Ascending
Который включает поля:
[opt].[dbo].[cur].value; [opt].[dbo].[cur].discount; [opt].[dbo].[cur].remainder; [opt].[dbo].[cur].campaign_id; [opt].[dbo].[cur].card_id; [opt].[dbo].[cur].operation_type_id
Т.е. по сути , добавить в ваш индекс idx_cur_campaign_id_inc, первым полем card_id.

прим.
Ради любопытства, кстати, можно попробовать отключить локальную/глобальную агрегацию, чтобы был один агрегат.
Выполнить запрос с флагом option(querytraceon 8665). Только в продакшне этого не оставляйте, это ради спортивного интереса, как изменится время выполнения запроса, если сервер будет агрегировать в одном месте.

Если способ 1 и 2 никак не помогут, а то что в примечании сделает только хуже, то скорее всего, больше ничего посоветовать не смогу, во всяком случае пока. Нужно будет еще подумать. В принципе план норм., за исключением плохих оценок в промежуточной агрегации. Можно начать смотреть на другие параметры сервера, например, ждет ли запрос долго пока ему выделится память.
...
Рейтинг: 0 / 0
21.06.2013, 15:23
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306524
_ч_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_ч_
Участник
SomewhereSomehow,

Огромное Вам спасибо за внимание и интерес к теме.

Я переделал индекс idx_cur_campaign_id_inc и включил в него поле card_id.

Без option(order group) уже вполне терпимые 1мин и 27 сек на 43 млн записей.



Ради интереса включил опцию querytraceon 8665, время выполнения увеличилось до почти 2 минут.
С order group примерно тоже самое, что и без, но с новым индексом.

Спасибо
...
Рейтинг: 0 / 0
21.06.2013, 16:33
| Ответить | Цитировать | Написать  
Повышение быстродействия выполнения запроса
    #38306577
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
_ч_,
Не за что, мне и самому интересны такие вопросы.


Вот, теперь мне нравится соотношение оценок и реального числа строк. Оценки от индекса не поменялись, т.к. статистика была, и по-прежнему моделируются как 2 030 360. Но, после построения подходящего индекса, оптимизатор сам (даже без подсказки order group) отказался от hash agg и использует stream agg (теперь в плане Stream Aggregate), что и было целью.
На мой взгляд, план получился вполне нормальный, больше тут, имхо, вряд ли что вытянешь.
...
Рейтинг: 0 / 0
21.06.2013, 16:59
| Ответить | Цитировать | Написать  
25 сообщений из 27, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Повышение быстродействия выполнения запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&tid=1706484]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 183ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 19ms
total:  314ms
созд. / загр.: 314ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]