2 июня в 13:26

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

В данной статье приведено решение оптимизации на Transact SQL задачи расчета остатки на складах. Применено: партицирование таблиц и материализованных представлений.

Постановка задачи


Задачу необходимо решить на SQL Server 2014 Enterprise Edition (x64). В фирме есть много складов. В каждом складе ежедневно по нескольку тысяч отгрузок и приемок продуктов. Есть таблица движений товаров на складе приход/расход. Необходимо реализовать:

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

Указанные расчеты предполагаются выполняться по расписанию с разными диапазонами дат и должны работать в приемлемое время. Т.е. если необходимо вывести таблицу с остатками за последний час или день, то время выполнения должно быть максимально быстрым, равно как и если необходимо вывести за последние 3 года эти же данные, для последующей загрузки в аналитическую базу данных.

Технические подробности. Сама таблица:

create table dbo.Turnover
(
	id int identity primary key,
	dt datetime not null,
	ProductID int not null,
	StorehouseID int not null,
	Operation smallint not null check (Operation in (-1,1)), -- +1 приход на склад, -1 расход со склада
	Quantity numeric(20,2) not null,
	Cost money not null
)

Dt — Дата время поступления/списания на/со склада.
ProductID — Продукт
StorehouseID — склад
Operation — 2 значения приход или расход
Quantity — количество продукта на складе. Может быть вещественным если продукт не в штуках, а, например, в килограммах.
Cost — стоимость партии продукта.

Исследование задачи


Создадим заполненную таблицу. Для того что бы ты мог вместе со мной тестировать и смотреть получившиеся результаты, предлагаю создать и заполнить таблицу dbo.Turnover скриптом:

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 < 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет
)
, storehouse as
(
	select 1 id
	union all
	select id+1
	from storehouse
	where id < 100 -- количество складов
)
select
	identity(int,1,1) id,
	dateadd(minute, t.id, convert(datetime,'20060101',120)) dt,
	1+abs(convert(int,convert(binary(4),newid()))%1000) 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

У меня этот скрипт на ПК с SSD диском выполнялся порядка 22 минуты, и размер таблицы занял около 8Гб на жестком диске. Ты можешь уменьшить количество лет, и количество складов, для того что бы время создания и заполнения таблицы сократить. Но какой-то неплохой объем для оценки планов запросов рекомендую оставить, хотя бы 1-2 гигабайта.

Сгруппируем данные до часа

Далее, нам нужно сгруппировать суммы по продуктам на складе за исследуемый период времени, в нашей постановке задачи это один час (можно до минуты, до 15 минут, дня. Но очевидно до миллисекунд вряд ли кому понадобится отчетность). Для сравнений в сессии (окне) где выполняем наши запросы выполним команду — set statistics time on;. Далее выполняем сами запросы и смотрим планы запросов:

select top(1000)
	convert(datetime,convert(varchar(13),dt,120)+':00',120) as dt, -- округляем до часа
	ProductID,
	StorehouseID,
	sum(Operation*Quantity) as Quantity
from dbo.Turnover
group by
	convert(datetime,convert(varchar(13),dt,120)+':00',120),
	ProductID,
	StorehouseID

image

Стоимость запроса — 12406
(строк обработано: 1000)
Время работы SQL Server:
Время ЦП = 2096594 мс, затраченное время = 321797 мс.

Если мы сделаем результирующий запрос с балансом, который считается нарастающим итогом от нашего количества, то запрос и план запроса будут следующими:

select top(1000)
	convert(datetime,convert(varchar(13),dt,120)+':00',120) as dt, -- округляем до часа
	ProductID,
	StorehouseID,
	sum(Operation*Quantity) as Quantity,
	sum(sum(Operation*Quantity)) over
		(
			partition by StorehouseID, ProductID
			order by convert(datetime,convert(varchar(13),dt,120)+':00',120)
		) as Balance
from dbo.Turnover
group by
	convert(datetime,convert(varchar(13),dt,120)+':00',120),
	ProductID,
	StorehouseID

image

Стоимость запроса — 19329
(строк обработано: 1000)
Время работы SQL Server:
Время ЦП = 2413155 мс, затраченное время = 344631 мс.

Оптимизация группировки

Здесь достаточно все просто. Сам запрос без нарастающего итога можно оптимизировать материализованным представлением (index view). Для построения материализованного представления, то что суммируется не должно иметь значение NULL, у нас суммируются sum(Operation*Quantity), или каждое поле сделать NOT NULL или добавить isnull/coalesce в выражение. Предлагаю создать материализованное представление.

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
		convert(datetime,convert(varchar(13),dt,120)+':00',120),
		ProductID,
		StorehouseID
go

И построить по нему кластерный индекс. В индексе порядок полей укажем так же как и в группировке (для группировки столько порядок не важен, важно что бы все поля группировки были в индексе) и нарастающем итоге (здесь важен порядок — сначала то, что в partition by, затем то, что в order by):

create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (StorehouseID, ProductID, dt)
with (data_compression=page) — 19 min

Теперь после построения кластерного индекса мы можем заново выполнить запросы, изменив агрегацию суммы как в представлении:

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

select top(1000)
	convert(datetime,convert(varchar(13),dt,120)+':00',120) as dt, -- округляем до часа
	ProductID,
	StorehouseID,
	sum(isnull(Operation*Quantity,0)) as Quantity,
	sum(sum(isnull(Operation*Quantity,0))) over
		(
			partition by StorehouseID, ProductID
			order by convert(datetime,convert(varchar(13),dt,120)+':00',120)
		) as Balance
from dbo.Turnover
group by
	convert(datetime,convert(varchar(13),dt,120)+':00',120),
	ProductID,
	StorehouseID


Планы запросов стали:

imageСтоимость 0.008

imageСтоимость 0.01

Время работы SQL Server:
Время ЦП = 31 мс, затраченное время = 116 мс.
(строк обработано: 1000)
Время работы SQL Server:
Время ЦП = 0 мс, затраченное время = 151 мс.

Итого, мы видим, что с индексированной вьюхой запрос сканирует не таблицу группируя данные, а кластерный индекс, в котором уже все сгруппировано. И соответственно время выполнения сократилось с 321797 миллисекунд до 116 мс., т.е. в 2774 раза.

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

Промежуточные балансы

В итоге нам нужно быстрое выполнение следующего запроса:

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

select *
from
(
	select
		dt,
		StorehouseID,
		ProductId,
		Quantity,
		sum(Quantity) over
			(
				partition by StorehouseID, ProductID
				order by dt
			) as Balance
	from dbo.TurnoverHour with(noexpand)
	where dt <= @finish
) as tmp
where dt >= @start

image

Стоимость плана = 3103. А представь что бы было, если бы не по материализованному представлению пошел а по самой таблице.

Вывод данных материализованного представления и баланса по каждому продукту на складе на дату со временем округленную до часа. Что бы посчитать баланс — необходимо с самого начала (с нулевого баланса) просуммировать все количества до указанной последней даты (@finish), а после уже в просуммированном резалтсете отсечь данные позже параметра start.

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

create index ix_dt on dbo.TurnoverHour (dt) include (Quantity) with(data_compression=page); --7 min
И наш запрос будет вида:
set dateformat ymd;
declare
	@start  datetime = '2015-01-02',
	@finish datetime = '2015-01-03'



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

select *
from
(
	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
) as tmp
where dt >= @start
order by StorehouseID, ProductID, dt

Вообще этот запрос имея даже индекс по дате полностью покрывающий все затрагиваемые в запросе поля, выберет кластерный наш индекс и сканирование. А не поиск по дате с последующей сортировкой. Предлагаю выполнить следующие 2 запроса и сравнить что у нас получилось, далее проанализируем что все-таки лучше:

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



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

select *
from
(
	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
) as tmp
where dt >= @start
order by StorehouseID, ProductID, dt

select *
from
(
	select
		dt,
		StorehouseID,
		ProductId,
		Quantity,
		sum(Quantity) over
			(
				partition by StorehouseID, ProductID
				order by dt
			) as Balance
	from dbo.TurnoverHour with(noexpand,index=ix_dt)
	where dt between @start_month and @finish
) as tmp
where dt >= @start
order by StorehouseID, ProductID, dt

Время работы SQL Server:
Время ЦП = 33860 мс, затраченное время = 24247 мс.

(строк обработано: 145608)

(строк обработано: 1)

Время работы SQL Server:
Время ЦП = 6374 мс, затраченное время = 1718 мс.
Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 0 мс.

Из времени видно, что индекс по дате выполняется значительно быстрее. Но планы запросов в сравнении выглядят следующим образом:

image

Стоимость 1го запроса с автоматически выбранным кластерным индексом = 2752, а вот стоимость с индексом по дате запроса = 3119.

Как бы то не было, здесь нам требуется от индекса две задачи: сортировка и выборка диапазона. Одним индексом из имеющихся нам эту задачу не решить. В данном примере диапазон данных всего за 1 день, но если будет период больше, но далеко не весь, например, за 2 месяца, то однозначно поиск по индексу будет не эффективен из-за расходов на сортировку.

Здесь из видимых оптимальных решений я вижу:

  1. Создать вычисляемое поле Год-Месяц и индекс создать (Год-Месяц, остальные поля кластерного индекса). В условии where dt between @start_month and finish заменить на Год-Месяц=@месяц, и после этого уже наложить фильтр на нужные даты.
  2. Фильтрованные индексы — индекс сам как кластерный, но фильтр по дате, за нужный месяц. И таких индексов сделать столько, сколько у нас месяцев всего. Идея близка к решению, но здесь если диапазон условий будет из 2х фильтрованных индексов, потребуется соединение и в дальнейшем все равно сортировка неизбежна.
  3. Секционируем кластерный индекс так, чтобы в каждой секции были данные только за один месяц.

В проекте в итоге я сделал 3-й вариант. Секционирование кластерного индекса материализованного представления. И если выборка идет за промежуток времени одного месяца, то по сути оптимизатор затрагивает только одну секцию, делая ее сканирование без сортировки. А отсечение неиспользуемых данных происходит на уровне отсечения неиспользуемых секций. Здесь если поиск с 10 по 20 число у нас не идет точный поиск этих дат, а поиск данных с 1го по последний день месяца, далее сканирование этого диапазона в отсортированном индексе с фильтрацией во время сканирования по выставленным датам.

Секционируем кластерный индекс вьюхи. Прежде всего удалим из вьюхи все индексы:

drop index ix_dt on dbo.TurnoverHour;
drop index uix_TurnoverHour on dbo.TurnoverHour;

И создадим функцию и схему секционирования:

set dateformat ymd;
create partition function pf_TurnoverHour(datetime) as range right for values (
'2006-01-01', '2006-02-01', '2006-03-01', '2006-04-01', '2006-05-01', '2006-06-01', '2006-07-01', '2006-08-01', '2006-09-01', '2006-10-01', '2006-11-01', '2006-12-01',
'2007-01-01', '2007-02-01', '2007-03-01', '2007-04-01', '2007-05-01', '2007-06-01', '2007-07-01', '2007-08-01', '2007-09-01', '2007-10-01', '2007-11-01', '2007-12-01',
'2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01', '2008-05-01', '2008-06-01', '2008-07-01', '2008-08-01', '2008-09-01', '2008-10-01', '2008-11-01', '2008-12-01',
'2009-01-01', '2009-02-01', '2009-03-01', '2009-04-01', '2009-05-01', '2009-06-01', '2009-07-01', '2009-08-01', '2009-09-01', '2009-10-01', '2009-11-01', '2009-12-01',
'2010-01-01', '2010-02-01', '2010-03-01', '2010-04-01', '2010-05-01', '2010-06-01', '2010-07-01', '2010-08-01', '2010-09-01', '2010-10-01', '2010-11-01', '2010-12-01',
'2011-01-01', '2011-02-01', '2011-03-01', '2011-04-01', '2011-05-01', '2011-06-01', '2011-07-01', '2011-08-01', '2011-09-01', '2011-10-01', '2011-11-01', '2011-12-01',
'2012-01-01', '2012-02-01', '2012-03-01', '2012-04-01', '2012-05-01', '2012-06-01', '2012-07-01', '2012-08-01', '2012-09-01', '2012-10-01', '2012-11-01', '2012-12-01',
'2013-01-01', '2013-02-01', '2013-03-01', '2013-04-01', '2013-05-01', '2013-06-01', '2013-07-01', '2013-08-01', '2013-09-01', '2013-10-01', '2013-11-01', '2013-12-01',
'2014-01-01', '2014-02-01', '2014-03-01', '2014-04-01', '2014-05-01', '2014-06-01', '2014-07-01', '2014-08-01', '2014-09-01', '2014-10-01', '2014-11-01', '2014-12-01',
'2015-01-01', '2015-02-01', '2015-03-01', '2015-04-01', '2015-05-01', '2015-06-01', '2015-07-01', '2015-08-01', '2015-09-01', '2015-10-01', '2015-11-01', '2015-12-01',
'2016-01-01', '2016-02-01', '2016-03-01', '2016-04-01', '2016-05-01', '2016-06-01', '2016-07-01', '2016-08-01', '2016-09-01', '2016-10-01', '2016-11-01', '2016-12-01',
'2017-01-01', '2017-02-01', '2017-03-01', '2017-04-01', '2017-05-01', '2017-06-01', '2017-07-01', '2017-08-01', '2017-09-01', '2017-10-01', '2017-11-01', '2017-12-01',
'2018-01-01', '2018-02-01', '2018-03-01', '2018-04-01', '2018-05-01', '2018-06-01', '2018-07-01', '2018-08-01', '2018-09-01', '2018-10-01', '2018-11-01', '2018-12-01',
'2019-01-01', '2019-02-01', '2019-03-01', '2019-04-01', '2019-05-01', '2019-06-01', '2019-07-01', '2019-08-01', '2019-09-01', '2019-10-01', '2019-11-01', '2019-12-01');
go
create partition scheme ps_TurnoverHour as partition pf_TurnoverHour all to ([primary]);
go
Ну и уже известный нам кластерный индекс только в созданной схеме секционирования:
create unique clustered index uix_TurnoverHour on dbo.TurnoverHour (StorehouseID, ProductID, dt) with (data_compression=page) on ps_TurnoverHour(dt); --- 19 min
И теперь посмотрим, что у нас получилось. Сам запрос:
set dateformat ymd;
declare
	@start  datetime = '2015-01-02',
	@finish datetime = '2015-01-03'

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

select *
from
(
	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
) as tmp
where dt >= @start
order by StorehouseID, ProductID, dt
option(recompile);

image

Время работы SQL Server:
Время ЦП = 7860 мс, затраченное время = 1725 мс.
Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 0 мс.
Стоимость плана запроса = 9.4

По сути данные в одной секции выбираются и сканируются по кластерному индексу достаточно быстро. Здесь следует добавить то, что когда запрос параметризирован, возникает неприятный эффект parameter sniffing, лечится option(recompile).
@abkurenkov
карма
2,0
рейтинг 12,0
Похожие публикации
Самое читаемое Разработка

Комментарии (31)

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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

                      И правда...

  • 0
    count_big(*) qty
    
    это для чего?
    • 0

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

  • +1
    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
    это нормально?
    • 0

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

      • +2
        я к тому что
        @start_month;
        подразумевает 2015-02-01
        • 0

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

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

            2015-02-01 00:00:00.000
            • 0

              Вот да. Прекрасно и независимо от региональных настроек.

          • 0
            считаю неправильным, собирать дату через текст
            от сервера вполне возможно прилетит sp_executesql а там параметры для дат вроде бы текстовые
  • 0
    Возможно я под вечер подустал, но:
    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 текущего запроса с остатками на начало месяца
    • 0
      мне кажется где то не хватает UNION ALL текущего запроса с остатками на начало месяца
      кажется я непонятно написал.
      По моему у автора итоговый запрос абсолютно неправильный, так как не выдает остатков.
    • 0
      Здесь, очевидно, помогут промежуточные рассчитанные балансы. Например, на 1е число каждого месяца или на каждое воскресенье. Имея такие балансы, задача сводится к тому, что нужно будет суммировать ранее рассчитанные балансы и рассчитать баланс не от начала, а от последней рассчитанной даты. Для экспериментов и сравнений построим дополнительный не кластерный индекс по дате:

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


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

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

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

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

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

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

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

  • –1
    в общем abkurenkov, для завершения «Остатки на складах» нужно к текущему балансу (накопительному итогу) добавить «остатки на начало месяца» который получается так «select dateadd(day, 1-day(@start), start)».
    union all или left outer join в помощь
  • +1
    да и вообще, если нужно данные на конец 2015-01-03 дня, то данное условие невыведет данные на конец 03 числа, а только 3 число и 00 секунд.
    Лучше так: «dt < „2015-01-04“», то есть, строго меньше следующего дня
  • 0
    В общем, раз автор не торопится доделывать то вот решение без секционирования.
    Но доделанное с 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);
    

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

      Ну и поиграться с датами, а то там костыли.

Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.