Comments 32
Как у вас просто всё… Нет территорий, нет мест хранения, нет брака и т.п.…
Извините, статья хорошая, это крик души просто.
После слов:
Теперь после построения кластерного индекса мы можем заново выполнить запросы, изменив агрегацию суммы как в представлении:
разве не надо в одном из запросов использовать таблицу TurnoverHour?

В Enterprise редакции — не обязательно, оптимизатор сам найдет ее. В младших редакциях — надо, причем с обязательным указанием with(noexpand)

partition by StorehouseID, ProductID
Поскольку селективность по ProductID лучше (продуктов больше чем складов), может лучше ProductID на первое место поставить, не знаю ускорит ли это работу для группировок или замедлит, но обычно торговые/складские системы делают поиск по условию по товару или набору товаров, а значит ProductID на первом месте должен ускорить поиск/фильтр где в условии есть товар

Для того, чтобы посчитать оконную функцию — надо сделать разбиение по обоим полям. В такой ситуации не важно что писать первым — оптимизатор сам выберет индекс с лучшей селективностью, если таковой вообще существует.

уточню, я имею ввиду поиск по кластерному/составному индексу, допустим нам нужно узнать сколько на остатках разных чипсов, но не остальных товаров, пишу:
where productId in (1,8,9,101,647) and StorehouseID in (1,5, 7)
если productId в начале, то это ускорит поиск по индексу, для набора товаров, а не для всех товаров в таблице.
Если же первым будет StorehouseID у которого плохая селективность, то конечно подходящего индекса для особого ускорения не будет.

Каким образом от порядка полей в конструкции partition by будет зависеть существование индекса?

Я говорю про порядок полей в индексе, что бы было меньше чтений, нужно выносить более селективное поле в начало списка полей составного/кластерного индекса.
В данном примере уместен порядок полей в индексе productId, StorehouseID, Dt.

Судя по вашему первому комментарию — вы говорите про порядок полей в конструкции partition by StorehouseID, ProductID

а, ну да, я там про индекс не упомянул. Порядок полей в конструкции partition by тоже думаю надо поменять, но и в индексе, одновременно, что бы одинаковые были с partition by.
При наличии where productID in эта манипуляция должна ускорить выборку

Да не играет никакой роли порядок полей в этой конструкции, это абсурд!


Достаточно поменять его в индексе.

Да не играет никакой роли порядок полей в этой конструкции
Я поменял
partition by ProductID, StorehouseID
на
partition by StorehouseID, ProductID

У меня индекс такой:
create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (ProductID, StorehouseID, dt)

появилась сортировка


а значит имеет значение порядок в partition by

Без count_big(*) кластерный индекс на представлении с группировкой не создается, ограничение сервера такое.

set dateformat ymd;
declare
	@start  datetime = '2015-02-28',
	@finish datetime = '2015-02-28'

declare
	@start_month datetime = convert(datetime,convert(varchar(9),@start,120)+'1',120)

select @start_month;

получаю select @start_month; = 2015-02-21 00:00:00.000
это нормально?

Да. Потому что varchar(9), а не 10. Вот логично ли — это уже другой вопрос...

Вообще, лично я считаю неправильным, собирать дату через текст. Отрывая от строкового представления символы — тем более. С другой стороны, навскидку в голове на dateadd/datediff, без участия строк, я это за минуту не собрал, потому пусть. А вот техническая ошибка — да, присутствует...

set dateformat ymd;
declare @start datetime = '2015-02-28'
select dateadd(day, 1-day(@start), @start)

2015-02-01 00:00:00.000
считаю неправильным, собирать дату через текст
от сервера вполне возможно прилетит sp_executesql а там параметры для дат вроде бы текстовые
Возможно я под вечер подустал, но:
dbo.TurnoverHour у нас не содержит остатков на начало месяца, оно содержит дельту за определенный час

select
dt,
StorehouseID,
ProductId,
Quantity,
sum(Quantity) over
(
partition by StorehouseID, ProductID
order by dt
) as Balance
from dbo.TurnoverHour with(noexpand)
where dt between start_month and finish

выдаст накопительную сумму изменений, от нулевого остатка на начало месяца до момента finish
мне кажется где то не хватает UNION ALL текущего запроса с остатками на начало месяца
мне кажется где то не хватает UNION ALL текущего запроса с остатками на начало месяца
кажется я непонятно написал.
По моему у автора итоговый запрос абсолютно неправильный, так как не выдает остатков.
Здесь, очевидно, помогут промежуточные рассчитанные балансы. Например, на 1е число каждого месяца или на каждое воскресенье. Имея такие балансы, задача сводится к тому, что нужно будет суммировать ранее рассчитанные балансы и рассчитать баланс не от начала, а от последней рассчитанной даты. Для экспериментов и сравнений построим дополнительный не кластерный индекс по дате:

Автор разбил задачу на две, после чего посчитал вторую часть, поскольку первая делается по аналогии.


Если вы посмотрите прошлый запрос, который решал задачу целиком — то он работает правильно.

Что бы мы друг друга правильно поняли. Вы считаете что итоговый запрос правильный?
Баланс это остаток на какой то момент времени.
У автора балансы есть только в запросах, в которых не используется between.
Во вьюхе TurnoverHour нет остатков, а только обороты за месяц.
Автор забыл в итоговом запросе посчитать все обороты с начала времен.

Вот это условие, всё поломало, превратив Остатки в обороты за период:
where dt between @start_month and @finish

Сумма оборотов за период НЕ РАВНА сумме оборотов с начала времен.
Только сумма оборотов с начала времен является остатокм.

Я утверждаю что тут нет итогового правильного запроса.
Автор разбил задачи, но НЕ объединил их. (или сделал это неправильно)

Нужно просуммировать всё в TurnoverHour, с начала времен до начала месяца (не включая начало месяца), а затем, накопительно проссумировать всё, от начала месяца, до даты меньшей чем finish+1 (включая начало месяца)

Ну, автор все-таки показывал оптимизацию запроса на примере задачи, а не решал задачу. Что объединения не было — тут не спорю.

в общем abkurenkov, для завершения «Остатки на складах» нужно к текущему балансу (накопительному итогу) добавить «остатки на начало месяца» который получается так «select dateadd(day, 1-day(@start), start)».
union all или left outer join в помощь
<blockquote finish datetime = '2015-01-03'
и
where dt between>@start_month and finishда и вообще, если нужно данные на конец 2015-01-03 дня, то данное условие невыведет данные на конец 03 числа, а только 3 число и 00 секунд.
Лучше так: «dt < „2015-01-04“», то есть, строго меньше следующего дня
В общем, раз автор не торопится доделывать то вот решение без секционирования.
Но доделанное с left join и union all.
Добавлена индексированная вьюха TurnoverMonth.
Если кому особо надо, берите и сравнивайте что вам будет быстрее.
Может abkurenkov стоит это скопировать в статью.

немного переделанное заполнение бд, меньше данных, что бы побыстрее заполнялось
if object_id('dbo.Turnover','U') is not null drop table dbo.Turnover;
go
with times as
(
	select 1 id
	union all
	select id+1
	from times
	where id < 1*155*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет
)
, storehouse as
(
	select 1 id
	union all
	select id+1
	from storehouse
	where id < 3 -- количество складов
)
select
	identity(int,1,1) id,
	dateadd(minute, t.id, convert(datetime,'20161101',120)) dt,
	1+abs(convert(int,convert(binary(4),newid()))%3) ProductID, -- 1000 - количество разных продуктов
	s.id StorehouseID,
	case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход
	1+abs(convert(int,convert(binary(4),newid()))%100) Quantity
into dbo.Turnover
from times t cross join storehouse s
option(maxrecursion 0);
go
--- 15 min
alter table dbo.Turnover alter column id int not null
go
alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page)
go
-- 6 min

create view dbo.TurnoverHour
with schemabinding as
	select
		convert(datetime,convert(varchar(13),dt,120)+':00',120) as dt, -- округляем до часа
		ProductID,
		StorehouseID,
		sum(isnull(Operation*Quantity,0)) as Quantity,
		count_big(*) qty
	from dbo.Turnover
	group by		
		ProductID,
		StorehouseID,
		convert(datetime,convert(varchar(13),dt,120)+':00',120)
go

create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (ProductID, StorehouseID, dt)
go

create view dbo.TurnoverMonth
with schemabinding as
	select
		CAST(dateadd(day, 1-day(dt), dt) as date) as monthT, -- округляем до месяца
		ProductID,
		StorehouseID,
		sum(isnull(Operation*Quantity,0)) as Quantity,
		count_big(*) qty
	from dbo.Turnover
	group by		
		ProductID,
		StorehouseID,
		CAST(dateadd(day, 1-day(dt), dt) as date)

go

create unique clustered index uix_TurnoverMonth on dbo.TurnoverMonth (ProductID, StorehouseID, monthT)
go




join left
set statistics io on;
set statistics time on;

set dateformat ymd;
declare
	@start  datetime = '2016-12-01',
	@finish datetime = '2017-02-28'

declare	@start_month datetime = dateadd(day, 1-day(@start), @start)

select *
from
(
	select
		TurnoverHour.dt,
		TurnoverHour.StorehouseID,
		TurnoverHour.ProductId,
		TurnoverHour.Quantity as deltaQuantity,
		sum(TurnoverHour.Quantity) over
			(
				partition by TurnoverHour.ProductID, TurnoverHour.StorehouseID
				order by dt
			) as Accumulate,

		isnull(totalsBefore.TotalBeforeStartMonth, 0) TotalBeforeStartMonth,
		sum(TurnoverHour.Quantity) over
			(
				partition by TurnoverHour.ProductID, TurnoverHour.StorehouseID
				order by dt
			) + isnull(TotalBeforeStartMonth, 0) as BalanceTotal
	from dbo.TurnoverHour with(noexpand)		
		left join (
			select
			--@start_month-1 as dt,
				ProductID,
				StorehouseID,
				sum(Quantity) as TotalBeforeStartMonth
			from dbo.TurnoverMonth with(noexpand)
			where monthT < @start_month
				--and ProductID = 2 and StorehouseID = 2 
			group by		
				ProductID,
				StorehouseID
		) as totalsBefore on totalsBefore.ProductID = TurnoverHour.ProductID 
					and totalsBefore.StorehouseID = TurnoverHour.StorehouseID
	where dt between @start_month and @finish
		--and TurnoverHour.ProductID = 2 and TurnoverHour.StorehouseID = 2 
) as tmp
where dt >= @start 
--and ProductID = 2 and StorehouseID = 2 
order by ProductID, StorehouseID, dt
option(recompile);



union all
set statistics io on;
set statistics time on;

set dateformat ymd;
declare
	@start  datetime = '2016-12-01',
	@finish datetime = '2017-02-28'

declare	@start_month datetime = dateadd(day, 1-day(@start), @start)

select *
from
(
	select
		dt,
		ProductId,
		StorehouseID,		
		Quantity as deltaQuantity,
		sum(Quantity) over
			(
				partition by ProductID, StorehouseID
				order by dt
			) as BalanceTotal
	from (
	
		select
				@start_month-1 as dt,
				ProductID,
				StorehouseID,
				sum(Quantity) as Quantity
			from dbo.TurnoverMonth with(noexpand)
			where monthT < @start_month
				--and ProductID = 2 and StorehouseID = 2 
			group by		
				ProductID,
				StorehouseID
	
		union all
	
		select 
				TurnoverHour.dt,
				TurnoverHour.ProductId,
				TurnoverHour.StorehouseID,		
				TurnoverHour.Quantity  
			from dbo.TurnoverHour with(noexpand)			
			where dt between @start_month and @finish
				--and ProductID = 2 and StorehouseID = 2 
	) as u
	
) as tmp
where dt >= @start 
--and ProductID = 2 and StorehouseID = 2 
order by ProductID, StorehouseID, dt
option(recompile);

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

Ну и поиграться с датами, а то там костыли.
А если таблицу Turnover хранить как кластерный секционированный колумнстор, не быстрее ли выйдет? Или, скажем так, не универсальнее ли в плане создания прочих, не представленных в примере запросов?
К тому же, если у вас энтерпрайз, он секции колумнстора еще и параллельно сканировать будет.
В 2014, правда, придется немного помучиться с загрузкой, например — грузить в отсоединенную секцию, а потом делать ее свитч в основную таблицу, но, в принципе, нечего особо страшного. Зато вся аналитика — мгновенна без ухищрений.
Only those users with full accounts are able to leave comments. Log in, please.