Оптимизация загрузки в задаче «Остатки на складах» с помощью секционирования в SQL Server

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

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);

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);