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

Откуда:
Сообщений: 1427
Есть таблица:
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 записей.

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

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 минут.

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


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)


Уважаемые участники форума, подскажите, каким образом сократить время выполнения запроса до приемлемого?

К сообщению приложен файл. Размер - 32Kb
20 июн 13, 11:59    [14458818]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
индексированное представление не предлагать?
20 июн 13, 12:10    [14458913]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Мистер Хенки,
а как оно поможет?
20 июн 13, 12:18    [14458985]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
_ч_
Мистер Хенки,
а как оно поможет?

все сведется к сканированию индекса представления, вместо агрегирования . Полагаю количество операций чтения уменьшится равно как и потребление процессорного времени, что должно привести к убыстрению запроса.
20 июн 13, 12:29    [14459099]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_,

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

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

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

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

Можно еще попробовать избавиться от repartition streams, соединив при помощи apply таблицу с таблицей в которой будут дистинктные значения campaign_id, card_id, - но не знаю, насколько это у вас применимо, нужно экспериментировать.
20 июн 13, 12:33    [14459141]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Индексированное представление сделать не получится -- наличествует функция max.
20 июн 13, 12:55    [14459361]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
invm
Индексированное представление сделать не получится -- наличествует функция max.

тогда придется считать отдельно это поле, будет ли так быстрее - сложно сказать, но попробовать проверить можно.
20 июн 13, 13:26    [14459589]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Мистер Хенки, т.е. Вы предлагаете сделать индексированное представление, в котором будет вызываться мой запрос?
Я боюсь, что это плохой вариант, т.к. во-1 эта таблица самая большая в БД, а во-вторых есть функции max.

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

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

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

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

В любом случае, спасибо Вам за ответы, буду эксперементировать.

К сообщению приложен файл (Plan.xml - 36Kb) cкачать
20 июн 13, 13:59    [14459937]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_
Это предупреждение о использовании 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 в сеансе пользователя, флаг трассировки имеет областью действия сеанса.

Он включает расширенное моделирование в оптимизаторе и изменяет оценку во многих случаях.
+
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)
20 июн 13, 14:28    [14460181]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
SomewhereSomehow,
Спасибо Вам большое за ответы.

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

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

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

К сожалению, время выполнения сильно не поменялось.
20 июн 13, 16:05    [14460935]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_,
Это интересно. План не прикрепился, кстати.
20 июн 13, 16:09    [14460967]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Sorry, вот план

К сообщению приложен файл (Plan.xml - 36Kb) cкачать
20 июн 13, 16:37    [14461177]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_,

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

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;
20 июн 13, 16:49    [14461240]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
SomewhereSomehow,

Во вложении

К сообщению приложен файл (1.xml - 23Kb) cкачать
20 июн 13, 17:13    [14461358]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_,

Спасибо. Теперь есть репро, попробую посмотреть в чем там дело.
21 июн 13, 10:25    [14463797]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
Дык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)
21 июн 13, 11:39    [14464459]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
buser
Дык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)


Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.
21 июн 13, 12:02    [14464693]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
_ч_
buser
Дык тры минуты - вполне ничего :) Сократить тоже можно... хранить агрегаты в отдельной таблице и пересчитывать значения по мере поступления... (это как альтернатива индексированного представления )
Забавный эффект option (maxdop 1)


Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.

При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения места
21 июн 13, 12:03    [14464706]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
Сергей Викт.
_ч_
Мы как раз от хранения и ушли в пользу вьюхи и перерасчета агрегатов. На небольшом количестве данных (около миллиона) всё работает за доли секунд, а когда всего лишь 46 млн записей работает аж 3 минуты.
При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения места
можно выбирать количество хранимых агрегатов. то есть хранить не для каждой записи, а для некоторых контрольных точек (день/неделя/месяц) и в расчётах опираться на них
21 июн 13, 12:16    [14464807]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Shakill
Сергей Викт.
пропущено...
При большом объёме данных хранить агрегаты выгодней с точки зрения времени отработки запроса, но невыгодно с точки зрения места
можно выбирать количество хранимых агрегатов. то есть хранить не для каждой записи, а для некоторых контрольных точек (день/неделя/месяц) и в расчётах опираться на них

Безусловно, всё зависит от конкретных задач и потребностей.
21 июн 13, 12:25    [14464882]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Еще забыл добавить одну вещь.

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

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
21 июн 13, 12:26    [14464887]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
_ч_
Еще забыл добавить одну вещь.

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

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

Ну если рассматривать вариант хранения агрегированных значений, то важно, т.к. чем меньше результатов агрегации. тем меньше размер таблицы.
21 июн 13, 12:29    [14464924]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Посмотрел планы и статистику.
Картинка с другого сайта.

Что увидел хорошего:
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 никак не помогут, а то что в примечании сделает только хуже, то скорее всего, больше ничего посоветовать не смогу, во всяком случае пока. Нужно будет еще подумать. В принципе план норм., за исключением плохих оценок в промежуточной агрегации. Можно начать смотреть на другие параметры сервера, например, ждет ли запрос долго пока ему выделится память.
21 июн 13, 15:23    [14466467]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
_ч_
Member

Откуда:
Сообщений: 1427
SomewhereSomehow,

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

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

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



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

Спасибо

К сообщению приложен файл (New_index.xml - 32Kb) cкачать
21 июн 13, 16:33    [14467017]     Ответить | Цитировать Сообщить модератору
 Re: Повышение быстродействия выполнения запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
_ч_,
Не за что, мне и самому интересны такие вопросы.

Картинка с другого сайта.
Вот, теперь мне нравится соотношение оценок и реального числа строк. Оценки от индекса не поменялись, т.к. статистика была, и по-прежнему моделируются как 2 030 360. Но, после построения подходящего индекса, оптимизатор сам (даже без подсказки order group) отказался от hash agg и использует stream agg (теперь в плане Stream Aggregate), что и было целью.
На мой взгляд, план получился вполне нормальный, больше тут, имхо, вряд ли что вытянешь.
21 июн 13, 16:59    [14467212]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить