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

У меня есть база, OLAP по назначению. В ней хранятся продажи, остатки и т.п.
И ещё там есть таблицы истории значений некоторых признаков на даты в каких-то разрезах.

У меня есть необходимость делать запросы, в которых необходимо узнавать,
каким было значение признака на дату продажи или остатка в каком-то
разрезе (для данного товара и группы магазинов, например).

Каким образом это лучше сделать для повышения быстродействия, т.е. какой
принцип/метод получения этих значений в запросах использовать, или же
как лучше переделать структуру хранения, какие добавить индексы и т.п.?

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

Версия сервера:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
        Dec 28 2012 20:23:12
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Пока есть структура таблиц, описанная ниже (внешние ключи опущены).
Здесь продажи хранятся по складам, а признаки --- по группам магазинов.
В полях даты время всегда равно 00:00:00.

-- Таблица складов.
-- ShopGroupid --- идентификатор группы магазинов, куда относится склад
CREATE TABLE dbo.TEST_InvLocs(
inventlocationid varchar(10) NOT NULL,
ShopGroupId      varchar(10) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX idxmain
    ON dbo.TEST_InvLocs(inventlocationid)

-- Таблица с историей значения, в этом случае --- ABC-анализа по разным моделям.
-- ShopGroupId --- идентификатор группы магазинов
-- ItemId --- код товара
-- RDate --- дата, с которой начинает действовать значение
-- ABCModel --- модель ABC-анализа
-- ABCClass --- собственно значение, в данном случае группа ABC
-- (например, 1 это A, 2 --- B, 3 -- C).
CREATE TABLE dbo.TEST_ABCHistory(
ShopGroupId varchar(10) NOT NULL,
ItemId      varchar(20) NOT NULL,
RDate       datetime NOT NULL,
ABCModel    int NOT NULL,
ABCClass    int NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX idxmain
    ON dbo.TEST_ABCHistory(ABCModel, RDate, shopgroupid, itemid)

-- Таблица продаж по складам.
-- ItemId --- код товара
-- inventlocationid --- код склада
-- RDate --- дата продажи
-- saleq --- проданное количество
-- salea --- сумма продаж
CREATE TABLE dbo.TEST_Sales(
itemid           varchar(20) NOT NULL,
inventlocationid varchar(10) NOT NULL,
rdate            smalldatetime NOT NULL,
saleq            numeric(38, 4) NOT NULL,
salea            numeric(38, 4) NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idxmain
    ON dbo.TEST_Sales(RDate, inventlocationid, itemid)

-- Вот какие-нибудь данные для примера:
INSERT INTO dbo.TEST_invlocs(inventlocationid, shopgroupid)
 VALUES ('Склад1', 'Группа1'),
        ('Склад2', 'Группа1'),
        ('Склад3', 'Группа2'),
        ('Склад4', 'Группа2'),
        ('Склад5', 'Группа3')

INSERT INTO dbo.TEST_ABCHistory(ShopGroupId, itemid, rdate, ABCModel, ABCClass)
VALUES ('Группа1', 'товар1', CAST('20140513' as SMALLDATETIME), 1, 1),
       ('Группа1', 'товар2', CAST('20140514' as SMALLDATETIME), 1, 2),
       ('Группа2', 'товар3', CAST('20140515' as SMALLDATETIME), 1, 3),
       ('Группа2', 'товар4', CAST('20140516' as SMALLDATETIME), 4, 1),
       ('Группа3', 'товар5', CAST('20140517' as SMALLDATETIME), 4, 2)

INSERT INTO dbo.TEST_Sales(itemid, inventlocationid, rdate, saleq, salea)
VALUES ('товар1', 'склад1',CAST('20140515' as SMALLDATETIME), 2.0, 549.00),
       ('товар2', 'склад2',CAST('20140516' as SMALLDATETIME), 1.0, 38.00),
       ('товар3', 'склад3',CAST('20140617' as SMALLDATETIME), 4.0, 2824.90),
       ('товар4', 'склад4',CAST('20140518' as SMALLDATETIME), 3.0, 929.19),
       ('товар5', 'склад5',CAST('20140519' as SMALLDATETIME), 1.0, 50.00)


Количество записей в этих таблицах у меня сейчас такое:
Таблица Кол-во записей
TEST_InvLocs 409
TEST_ABCHistory 9 460 870
TEST_Sales 23 986 664


Вот пример одного из запросов, о которых я говорил:

-- Допустим, у товара есть ABC-группы по моделям 1 и 4 на данную дату.
-- Вот запрос по общим продажам и продажам по группам AA, AB, BA по месяцам:
;WITH A AS (
SELECT s.rdate, s.itemid, s.inventlocationid, s.salea, l.SHOPGROUPID,
       (SELECT TOP 1 ABCCLASS
          FROM TEST_ABCHistory AS abc
         WHERE abc.ITEMID = s.itemid AND abc.SHOPGROUPID = l.SHOPGROUPID
           AND abc.ABCMODEL = 1 AND abc.rdate <= s.rdate
         ORDER BY abc.rdate DESC
       ) AS abc1,
       (SELECT TOP 1 ABCCLASS
          FROM TEST_ABCHistory AS abc
         WHERE abc.ITEMID = s.itemid AND abc.SHOPGROUPID = l.SHOPGROUPID
           AND abc.ABCMODEL = 4 AND abc.rdate <= s.rdate
         ORDER BY abc.rdate DESC
       ) AS abc2
  FROM TEST_Sales AS s
 INNER JOIN TEST_InvLocs AS l
    ON s.inventlocationid = l.INVENTLOCATIONID
WHERE s.rdate between '20140101' and '20140601'
)

SELECT YEAR(RDate) as [year], MONTH(Rdate) as [month],
       sum(salea) AS salea,
       sum(CASE WHEN (abc1 = 1 AND abc2 = 1)
                  OR (abc1 = 1 AND abc2 = 2)
                  OR (abc1 = 2 AND abc2 = 1)
                THEN salea ELSE 0 END) AS S_TOP
  FROM A
 GROUP BY YEAR(RDate), MONTH(Rdate)
 ORDER BY YEAR(RDate), MONTH(Rdate)
OPTION (MAXDOP 1)


Как его (и аналогичные) оптимизировать?

Для примера, сейчас статистика и план по этому запросу такие:

SQL Server parse and compile time:
   CPU time = 16 ms, elapsed time = 18 ms.

(строк обработано: 6)
Table 'Worktable'. Scan count 3652588, logical reads 88887796, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_ABCHistory'. Scan count 2, logical reads 127862, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 1, logical reads 16844, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 336837 ms,  elapsed time = 336825 ms.

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
6	1	  |--Sort(ORDER BY:([Expr1012] ASC, [Expr1013] ASC))	1	2	1	Sort	Sort	ORDER BY:([Expr1012] ASC, [Expr1013] ASC)	NULL	2750	0,01126126	0,04911423	49	31144,58	[Expr1012], [Expr1013], [Expr1014], [Expr1015]	NULL	PLAN_ROW	0	1
6	1	       |--Hash Match(Aggregate, HASH:([Expr1012], [Expr1013]), RESIDUAL:([Expr1012] = [Expr1012] AND [Expr1013] = [Expr1013]) DEFINE:([Expr1014]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [Expr1015]=SUM(CASE WHEN [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)))	1	3	2	Hash Match	Aggregate	HASH:([Expr1012], [Expr1013]), RESIDUAL:([Expr1012] = [Expr1012] AND [Expr1013] = [Expr1013])	[Expr1014]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [Expr1015]=SUM(CASE WHEN [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc].[ABCClass]=(1) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)	2750	0	8,61406	49	31144,52	[Expr1012], [Expr1013], [Expr1014], [Expr1015]	NULL	PLAN_ROW	0	1
1826294	1	            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[itemid], [s].[rdate], [l].[shopgroupid]))	1	4	3	Nested Loops	Left Outer Join	OUTER REFERENCES:([s].[itemid], [s].[rdate], [l].[shopgroupid])	NULL	1272274	0	5,318105	40	31135,9	[s].[salea], [abc].[ABCClass], [abc].[ABCClass], [Expr1012], [Expr1013]	NULL	PLAN_ROW	0	1
1826294	1	                 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[itemid], [s].[rdate], [l].[shopgroupid]))	1	5	4	Nested Loops	Left Outer Join	OUTER REFERENCES:([s].[itemid], [s].[rdate], [l].[shopgroupid])	NULL	1272274	0	5,318105	67	15582,82	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [abc].[ABCClass], [Expr1012], [Expr1013]	NULL	PLAN_ROW	0	1
1826294	1	                 |    |--Hash Match(Inner Join, HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid]))	1	6	5	Hash Match	Inner Join	HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid])	NULL	1272274	0	13,42506	63	28,36853	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [Expr1012], [Expr1013]	NULL	PLAN_ROW	0	1
409	1	                 |    |    |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l]))	1	7	6	Clustered Index Scan	Clustered Index Scan	OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l])	[l].[inventlocationid], [l].[shopgroupid]	409	0,003865741	0,0006069	28	0,004472641	[l].[inventlocationid], [l].[shopgroupid]	NULL	PLAN_ROW	0	1
0	0	                 |    |    |--Compute Scalar(DEFINE:([Expr1012]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1013]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))))	1	8	6	Compute Scalar	Compute Scalar	DEFINE:([Expr1012]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1013]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)))	[Expr1012]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1013]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))	1837165	0	0,1837165	64	14,93899	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea], [Expr1012], [Expr1013]	NULL	PLAN_ROW	0	1
1827613	1	                 |    |         |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000') ORDERED FORWARD)	1	9	8	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000') ORDERED FORWARD	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	1837165	12,73424	2,021039	56	14,75527	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	NULL	PLAN_ROW	0	1
1813886	1826294	                 |    |--Sort(TOP 1, ORDER BY:([abc].[rdate] DESC))	1	16	5	Sort	TopN Sort	TOP 1, ORDER BY:([abc].[rdate] DESC)	NULL	1	0,01126126	0,0001007712	19	15549,01	[abc].[rdate], [abc].[ABCClass]	NULL	PLAN_ROW	0	1272274
27305160	1826294	                 |         |--Index Spool(SEEK:([abc].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [abc].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [abc].[ABCModel]=(1) AND [abc].[rdate] <= [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]))	1	17	16	Index Spool	Eager Spool	SEEK:([abc].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [abc].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [abc].[ABCModel]=(1) AND [abc].[rdate] <= [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate])	NULL	1,294565	477,0604	9,461128	19	1093,413	[abc].[rdate], [abc].[ABCClass]	NULL	PLAN_ROW	0	1272274
9460870	1	                 |              |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc]))	1	18	17	Clustered Index Scan	Clustered Index Scan	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc])	[abc].[shopgroupid], [abc].[itemid], [abc].[rdate], [abc].[ABCModel], [abc].[ABCClass]	9460870	47,06387	10,40711	52	57,47098	[abc].[shopgroupid], [abc].[itemid], [abc].[rdate], [abc].[ABCModel], [abc].[ABCClass]	NULL	PLAN_ROW	0	1
1763414	1826294	                 |--Sort(TOP 1, ORDER BY:([abc].[rdate] DESC))	1	19	4	Sort	TopN Sort	TOP 1, ORDER BY:([abc].[rdate] DESC)	NULL	1	0,01126126	0,000100019	19	15547,64	[abc].[rdate], [abc].[ABCClass]	NULL	PLAN_ROW	0	1272274
7476815	1826294	                      |--Index Spool(SEEK:([abc].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [abc].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [abc].[ABCModel]=(4) AND [abc].[rdate] <= [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]))	1	20	19	Index Spool	Eager Spool	SEEK:([abc].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [abc].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [abc].[ABCModel]=(4) AND [abc].[rdate] <= [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate])	NULL	1	477,0604	9,461128	19	1093,001	[abc].[rdate], [abc].[ABCClass]	NULL	PLAN_ROW	0	1272274
9460870	1	                           |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc]))	1	21	20	Clustered Index Scan	Clustered Index Scan	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc])	[abc].[shopgroupid], [abc].[itemid], [abc].[rdate], [abc].[ABCModel], [abc].[ABCClass]	9460870	47,06387	10,40711	52	57,47098	[abc].[shopgroupid], [abc].[itemid], [abc].[rdate], [abc].[ABCModel], [abc].[ABCClass]	NULL	PLAN_ROW	0	1
27 июн 14, 13:24    [16228746]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
MSSQLBug,

Ну для начала кластерный индекс на таблице TEST_ABCHistory
никак не соответсвует потребностям в сортировке по rdate и выводу ABCClass,
либо его переделывайте либо новый индекс делайте
27 июн 14, 13:47    [16228925]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
WarAnt,
Спасибо. Ну вот могу я, например, так попробовать:
CREATE UNIQUE INDEX idxcover ON dbo.TEST_ABCHistory(ABCModel, shopgroupid, itemid, RDate)
INCLUDE(ABCClass)


И результат:
(строк обработано: 6)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_ABCHistory'. Scan count 3652588, logical reads 14610352, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 1, logical reads 16844, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 51933 ms,  elapsed time = 51931 ms.


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

Я попробовал уже несколько вариантов хранения, индексации и написания запросов, но кардинального улучшения что-то не видно, к сожалению.

Я говорю о том, что если сделать денормализацию, то производительность этого запроса будет примерно такой:
 SQL Server Execution Times:
   CPU time = 3198 ms,  elapsed time = 3207 ms.


Хотелось бы получить какой-то близкий к этому результат.
27 июн 14, 14:15    [16229123]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
MSSQLBug,

Я бы для начала попробовал такой индекс
CREATE INDEX idxcover ON dbo.TEST_ABCHistory(RDate, itemid, ABCModel, shopgroupid)
INCLUDE(ABCClass)
27 июн 14, 14:21    [16229155]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
WarAnt,

Да можно, но чем он должен помочь-то?
Вот результат:

Table 'Worktable'. Scan count 3652588, logical reads 89228885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_ABCHistory'. Scan count 2, logical reads 120546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 1, logical reads 16844, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 330629 ms,  elapsed time = 330636 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
27 июн 14, 15:07    [16229492]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
Ну что ж, после:

-- Не тот тип данных раньше сделал, исправляю:
ALTER TABLE dbo.TEST_ABCHistory ALTER COLUMN RDate SMALLDATETIME NOT NULL
CREATE UNIQUE CLUSTERED INDEX idxmain ON dbo.TEST_ABCHistory(ABCModel, itemid, shopgroupid, RDate)
CREATE UNIQUE CLUSTERED INDEX idxmain ON dbo.TEST_Sales(RDate, itemid, inventlocationid)


Стало вот так:

Table 'TEST_ABCHistory'. Scan count 3652588, logical reads 15523568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 1, logical reads 16852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47752 ms,  elapsed time = 47752 ms.


И это лучшее, чего мне пока удалось добиться.
Мне не нравится соотношения времён выполнения: 47 секунд к 3 (при денормализации).
Неужели это лучшее, чего можно достигнуть?
30 июн 14, 09:45    [16236944]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
Перейдите на нормальные целочисленные суррогатные ключи во всех таблицах измерений и в фактах, соответственно. Потом, зачем вам smalldatetime, если гранулярность вашей таблицы фактов - день? Используйте в данном случае date.
Ну и почитайте внимательно Кимбала. Для начала...
30 июн 14, 10:18    [16237050]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
Далее.
В таблице TEST_ABCHistory, помимо поля RDate, заведите еще и поле, например EndDate, которое будет указывать на дату окончания действия какого-то значения атрибута. Тогда вы уберете ненужные сортировки, типа таких:
ORDER BY abc.rdate DESC
(при том, что в индексе стоит ASC).
30 июн 14, 10:27    [16237093]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

Эх, знал я, что кто-нибудь это посоветует. ;)

Ну вот, например, как-то так:
ALTER TABLE dbo.TEST_ABCHistory ADD RDateEnd SMALLDATETIME NOT NULL DEFAULT(0)

UPDATE dbo.TEST_ABCHistory 
SET RDateEnd = ISNULL((SELECT TOP 1 RDate 
    FROM dbo.TEST_ABCHistory as a
    WHERE a.itemid = TEST_ABCHistory.itemid and a.ABCModel = TEST_ABCHistory.ABCModel
      and a.SHOPGROUPID = TEST_ABCHistory.SHOPGROUPID and a.rdate > TEST_ABCHistory.rdate 
    ORDER BY  a.rdate ASC)-1, '20500101')

ALTER TABLE dbo.TEST_ABCHistory
  ADD CONSTRAINT DatesInterval CHECK (RDate<=RDateEnd)

CREATE UNIQUE CLUSTERED INDEX idxmain 
    ON dbo.TEST_ABCHistory(ABCModel, itemid, shopgroupid, RDate, RDateEnd)


Ну и изменённый запрос:

;WITH A AS (
SELECT s.rdate, s.itemid, s.inventlocationid, s.salea, l.SHOPGROUPID, 
       (SELECT ABCCLASS
		  FROM TEST_ABCHistory AS abc
		 WHERE abc.ITEMID = s.itemid AND abc.SHOPGROUPID = l.SHOPGROUPID 
           AND abc.ABCMODEL = 1 AND s.rdate BETWEEN abc.rdate and abc.RDateEnd
		) AS abc1, 
       (SELECT ABCCLASS
		FROM TEST_ABCHistory AS abc
		WHERE abc.ITEMID = s.itemid AND abc.SHOPGROUPID = l.SHOPGROUPID 
          AND abc.ABCMODEL = 4 AND s.rdate BETWEEN abc.rdate and abc.RDateEnd
		) AS abc2
  FROM TEST_Sales AS s
 INNER JOIN TEST_InvLocs AS l 
    ON s.inventlocationid = l.INVENTLOCATIONID
WHERE s.rdate between '20140101' and '20140601' 
)

SELECT YEAR(RDate) as [year], MONTH(Rdate) as [month], sum(salea) AS salea, 
       sum(case when (abc1 = 1 and abc2 = 1) 
                  OR (abc1 = 1 and abc2 = 2) 
                  OR (abc1 = 2 and abc2 = 1) 
                then salea else 0 end) as S_TOP
FROM A
GROUP BY YEAR(RDate), MONTH(Rdate)
ORDER BY YEAR(RDate), MONTH(Rdate)
OPTION (MAXDOP 1)


И, естественно, результат хуже:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_ABCHistory'. Scan count 3652588, logical reads 14869733, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 1, logical reads 16852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 70684 ms,  elapsed time = 70686 ms.
30 июн 14, 10:55    [16237206]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

> Перейдите на нормальные целочисленные суррогатные ключи во всех таблицах измерений и в фактах, соответственно.
> Потом, зачем вам smalldatetime, если гранулярность вашей таблицы фактов - день? Используйте в данном случае date.
И что, от этого изменятся соотношения времён выполнения этих запросов?
Типы данных мне приходят из моей ERP, менять их не хотелось бы, да и вряд ли от этого будет большой выигрыш.

> Ну и почитайте внимательно Кимбала. Для начала...
Что именно вы имеете в виду?
30 июн 14, 11:02    [16237240]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
MSSQLBug
> Перейдите на нормальные целочисленные суррогатные ключи во всех таблицах измерений и в фактах, соответственно.
> Потом, зачем вам smalldatetime, если гранулярность вашей таблицы фактов - день? Используйте в данном случае date.
И что, от этого изменятся соотношения времён выполнения этих запросов?
Типы данных мне приходят из моей ERP, менять их не хотелось бы, да и вряд ли от этого будет большой выигрыш.

В вашей ERP могут быть какие угодно типы данных и ключевые поля. Огромные проблемы у вас возникнут, если у вас станет несколько источников. Сейчас у вас один источник - ваша ERP, проблем нет. А если источников станет несколько, причем гетерогенных, что станете делать со своими ключами, связанными по смыслу с сущностями из вашей ERP? Например, если вторым источником данных станут текстовые файлы?
Ключи в хранилище никоим образом не должны быть связаны с ключами из OLTP-систем. Делайте суррогатные хотя бы из этих соображений.

MSSQLBug
> Ну и почитайте внимательно Кимбала. Для начала...
Что именно вы имеете в виду?

Я имею в виду, что нужно читать классиков в организации хранилищ данных. Кимбал и Инмон - это и есть классики :)
30 июн 14, 11:16    [16237316]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
MSSQLBug
SELECT
  YEAR(RDate) as [year],
  MONTH(Rdate) as [month],



Это что за извраты в хранилище? У вас должна быть таблица-календарь, к ней и соединяйтесь, не надо вот этих YEAR и MONTH.

MSSQLBug
ORDER BY YEAR(RDate), MONTH(Rdate)


А эта сорировка куда вам нужна?

Вообще немного необычный запрос для хранилища. Кто использует этот запрос?
30 июн 14, 11:26    [16237387]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

Отвлекаясь от темы, я вот тут поцитирую Википедию:

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

Так что:

> А если источников станет несколько, причем гетерогенных, что станете делать со своими ключами,
> связанными по смыслу с сущностями из вашей ERP? Например, если вторым источником данных станут текстовые файлы?

IMHO, это будет значить, что в моей организации что-то пошло не так. ;)
30 июн 14, 11:30    [16237416]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

> Это что за извраты в хранилище? У вас должна быть таблица-календарь, к ней и соединяйтесь, не надо вот этих YEAR и MONTH.

А у меня периоды должны быть произвольными, это просто пример. И чем мне в этой ситуации поможет таблица-календарь (предварительно агрегированными данными, я так понимаю)?

И вообще, у меня реляционные таблицы, а не таблицы фактов хранилища, и мне как раз денормализацией заниматься не хотелось бы, если возможно.

> А эта сортировка куда вам нужна?
В вывод, естественно.

> Вообще немного необычный запрос для хранилища. Кто использует этот запрос?
В смысле? Отчёт использует...
30 июн 14, 11:37    [16237492]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
MSSQLBug,

Дело ваше, как считате нужным именно вам, так и делайте.
30 июн 14, 11:39    [16237523]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
MSSQLBug
случайно заглянул,

> Это что за извраты в хранилище? У вас должна быть таблица-календарь, к ней и соединяйтесь, не надо вот этих YEAR и MONTH.

А у меня периоды должны быть произвольными, это просто пример. И чем мне в этой ситуации поможет таблица-календарь (предварительно агрегированными данными, я так понимаю)?


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

MSSQLBug
> Вообще немного необычный запрос для хранилища. Кто использует этот запрос?
В смысле? Отчёт использует...

А отчет сам сортировать не умеет?
30 июн 14, 11:45    [16237576]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
случайно заглянул
Guest
select
  s.rdate
 ,s.itemid
 ,s.inventlocationid
 ,s.salea
 ,l.SHOPGROUPID
 ,abc1.ABCCLASS
 ,abc2.ABCCLASS
from
  TEST_Sales as s
inner join TEST_InvLocs  as l on  s.inventlocationid = l.INVENTLOCATIONID
inner join TEST_ABCHistory abc1 on abc1.ABCMODEL = 1 and abc1.ITEMID = s.itemid and abc1.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc1.rdate and abc1.RDateEnd
inner join TEST_ABCHistory abc2 on abc2.ABCMODEL = 4 and abc2 on abc2.ITEMID = s.itemid and abc2.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc2.rdate and abc2.RDateEnd
where
  s.rdate between '20140101' and '20140601' 
30 июн 14, 11:49    [16237609]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

> Какими такими предварительно агрегированными данными? Календарь это и есть календарь.
> В фактах вы храните ссылку на первичный ключ из календаря и там вам сразу будет и год и месяц
> и неделя и праздники и выходные и ваще чо хотите еще... А дает это гибкость анализа.
> В вашем случае хотя бы то, что не надо будет вычислять год и месяц на лету.

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

> А отчет сам сортировать не умеет?
А зачем на клиенте сортировать данные, когда серверу это почти ничего не стоит (индекс-то есть)?

Но мы отвлеклись от темы, мне кажется...
Меня интересует именно общий метод хранения и получения значений на дату для подобных запросов без денормализации.
30 июн 14, 12:02    [16237705]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Вам надо осваивать аналитические (оконные функции) см http://msdn.microsoft.com/ru-ru/library/ms189461.aspx
30 июн 14, 12:19    [16237820]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
случайно заглянул,

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

select YEAR(s.RDate), MONTH(s.Rdate), sum(salea), sum(case when (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 1) 
                  OR (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 2) 
                  OR (abc1.ABCCLASS = 2 and abc2.ABCCLASS = 1) 
                then salea else 0 end) as S_TOP
 from TEST_Sales as s
inner join TEST_InvLocs  as l on  s.inventlocationid = l.INVENTLOCATIONID
LEFT join TEST_ABCHistory abc1 
   on abc1.ABCMODEL = 1 and abc1.ITEMID = s.itemid 
  and abc1.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc1.rdate and abc1.RDateEnd
LEFT join TEST_ABCHistory abc2 
   on abc2.ABCMODEL = 4 and abc2.ITEMID = s.itemid 
  and abc2.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc2.rdate and abc2.RDateEnd
where
  s.rdate between '20140101' and '20140601'
GROUP BY YEAR(s.RDate), MONTH(s.Rdate)
ORDER BY YEAR(s.RDate), MONTH(s.Rdate)
OPTION (MAXDOP 1)


И результат:

Table 'Worktable'. Scan count 302867, logical reads 14439245, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_ABCHistory'. Scan count 2, logical reads 48728, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 409, logical reads 14495, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 85972 ms,  elapsed time = 86069 ms.


Впрочем, без (MAXDOP 1) результат интереснее, спасибо:

Table 'TEST_ABCHistory'. Scan count 10, logical reads 49411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_InvLocs'. Scan count 5, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TEST_Sales'. Scan count 409, logical reads 14599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 49703 ms,  elapsed time = 13181 ms.
30 июн 14, 12:37    [16237924]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
a_voronin,

> Вам надо осваивать аналитические (оконные функции) см http://msdn.microsoft.com/ru-ru/library/ms189461.aspx

Мне прямо вспоминается:


На американском форуме задаёшь вопрос, тебе дают ответ.
На израильском форуме задаёшь вопрос, тебе задают встречный вопрос.
На русском форуме задаёшь вопрос, тебе долго рассказывают, какой ты дурак.


Давайте без этого, а? ;) Что Вы конкретно предлагаете?
30 июн 14, 12:41    [16237957]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
Glory
Member

Откуда:
Сообщений: 104751
MSSQLBug
Впрочем, без (MAXDOP 1) результат интереснее, спасибо:

Планы то выполнения меняются ?
30 июн 14, 12:47    [16237994]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
Glory,

Да вроде меняются, выкладываю:

С MAXDOP:
Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
6	1	select YEAR(s.RDate), MONTH(s.Rdate), sum(salea), sum(case when (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 1) 
                  OR (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 2) 
                  OR (abc1.ABCCLASS = 2 and abc2.ABCCLASS = 1) 
                then salea else 0 end) as S_TOP
 from TEST_Sales as s
inner join TEST_InvLocs  as l on  s.inventlocationid = l.INVENTLOCATIONID
LEFT join TEST_ABCHistory abc1 
   on abc1.ABCMODEL = 1 and abc1.ITEMID = s.itemid 
  and abc1.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc1.rdate and abc1.RDateEnd
LEFT join TEST_ABCHistory abc2 
   on abc2.ABCMODEL = 4 and abc2.ITEMID = s.itemid 
  and abc2.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc2.rdate and abc2.RDateEnd
where
  s.rdate between '20140101' and '20140601'
GROUP BY YEAR(s.RDate), MONTH(s.Rdate)
ORDER BY YEAR(s.RDate), MONTH(s.Rdate)
OPTION (MAXDOP 1)	1	1	0	NULL	NULL	NULL	NULL	2987,768	NULL	NULL	NULL	241,2737	NULL	NULL	SELECT	0	NULL
6	1	  |--Sort(ORDER BY:([Expr1008] ASC, [Expr1009] ASC))	1	2	1	Sort	Sort	ORDER BY:([Expr1008] ASC, [Expr1009] ASC)	NULL	2987,768	0,01126126	0,05390966	49	241,2737	[Expr1008], [Expr1009], [Expr1010], [Expr1011]	NULL	PLAN_ROW	0	1
6	1	       |--Hash Match(Aggregate, HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009]) DEFINE:([Expr1010]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [Expr1011]=SUM(CASE WHEN ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2)) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)))	1	3	2	Hash Match	Aggregate	HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009])	[Expr1010]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [Expr1011]=SUM(CASE WHEN ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2)) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)	2987,768	0	15,79467	49	241,2086	[Expr1008], [Expr1009], [Expr1010], [Expr1011]	NULL	PLAN_ROW	0	1
1826294	1	            |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([s].[itemid], [l].[shopgroupid])=([abc1].[itemid], [abc1].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc1].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc1].[RDateEnd]))	1	4	3	Merge Join	Left Outer Join	MANY-TO-MANY MERGE:([s].[itemid], [l].[shopgroupid])=([abc1].[itemid], [abc1].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc1].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc1].[RDateEnd])	NULL	2346159	4,165404	23,09995	40	225,4139	[s].[salea], [abc1].[ABCClass], [abc2].[ABCClass], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
1826294	1	                 |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([abc2].[itemid], [abc2].[shopgroupid])=([s].[itemid], [l].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc2].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc2].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc2].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc2].[RDateEnd]))	1	5	4	Merge Join	Right Outer Join	MANY-TO-MANY MERGE:([abc2].[itemid], [abc2].[shopgroupid])=([s].[itemid], [l].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc2].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc2].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc2].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc2].[RDateEnd])	NULL	1272274	0,000313	7,505053	67	165,2017	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [abc2].[ABCClass], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
1791479	1	                 |    |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc2]), SEEK:([abc2].[ABCModel]=(4)) ORDERED FORWARD)	1	6	5	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc2]), SEEK:([abc2].[ABCModel]=(4)) ORDERED FORWARD	[abc2].[shopgroupid], [abc2].[itemid], [abc2].[rdate], [abc2].[ABCClass], [abc2].[RDateEnd]	1791479	8,901027	1,970784	48	10,87181	[abc2].[shopgroupid], [abc2].[itemid], [abc2].[rdate], [abc2].[ABCClass], [abc2].[RDateEnd]	NULL	PLAN_ROW	0	1
1826294	1	                 |    |--Sort(ORDER BY:([s].[itemid] ASC, [l].[shopgroupid] ASC))	1	7	5	Sort	Sort	ORDER BY:([s].[itemid] ASC, [l].[shopgroupid] ASC)	NULL	1272274	0,01126126	118,424	63	146,8245	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
1826294	1	                 |         |--Hash Match(Inner Join, HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid]))	1	8	7	Hash Match	Inner Join	HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid])	NULL	1272274	0	13,42506	63	28,38927	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
409	1	                 |              |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l]))	1	9	8	Clustered Index Scan	Clustered Index Scan	OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l])	[l].[inventlocationid], [l].[shopgroupid]	409	0,003865741	0,0006069	28	0,004472641	[l].[inventlocationid], [l].[shopgroupid]	NULL	PLAN_ROW	0	1
0	0	                 |              |--Compute Scalar(DEFINE:([Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))))	1	10	8	Compute Scalar	Compute Scalar	DEFINE:([Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)))	[Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))	1837165	0	0,1837165	64	14,95973	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea], [Expr1008], [Expr1009]	NULL	PLAN_ROW	0	1
1827613	1	                 |                   |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000') ORDERED FORWARD)	1	11	10	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000') ORDERED FORWARD	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	1837165	12,75498	2,021039	56	14,77602	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	NULL	PLAN_ROW	0	1
5429512	1	                 |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc1]), SEEK:([abc1].[ABCModel]=(1)) ORDERED FORWARD)	1	19	4	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc1]), SEEK:([abc1].[ABCModel]=(1)) ORDERED FORWARD	[abc1].[shopgroupid], [abc1].[itemid], [abc1].[rdate], [abc1].[ABCClass], [abc1].[RDateEnd]	5429890	26,97376	5,973036	48	32,94679	[abc1].[shopgroupid], [abc1].[itemid], [abc1].[rdate], [abc1].[ABCClass], [abc1].[RDateEnd]	NULL	PLAN_ROW	0	1


И без:
Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
6	1	select YEAR(s.RDate), MONTH(s.Rdate), sum(salea), sum(case when (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 1) 
                  OR (abc1.ABCCLASS = 1 and abc2.ABCCLASS = 2) 
                  OR (abc1.ABCCLASS = 2 and abc2.ABCCLASS = 1) 
                then salea else 0 end) as S_TOP
 from TEST_Sales as s
inner join TEST_InvLocs  as l on  s.inventlocationid = l.INVENTLOCATIONID
LEFT join TEST_ABCHistory abc1 
   on abc1.ABCMODEL = 1 and abc1.ITEMID = s.itemid 
  and abc1.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc1.rdate and abc1.RDateEnd
LEFT join TEST_ABCHistory abc2 
   on abc2.ABCMODEL = 4 and abc2.ITEMID = s.itemid 
  and abc2.SHOPGROUPID = l.SHOPGROUPID and s.rdate between abc2.rdate and abc2.RDateEnd
where
  s.rdate between '20140101' and '20140601'
GROUP BY YEAR(s.RDate), MONTH(s.Rdate)
ORDER BY YEAR(s.RDate), MONTH(s.Rdate)	1	1	0	NULL	NULL	NULL	NULL	2987,768	NULL	NULL	NULL	126,5852	NULL	NULL	SELECT	0	NULL
6	1	  |--Parallelism(Gather Streams, ORDER BY:([Expr1008] ASC, [Expr1009] ASC))	1	2	1	Parallelism	Gather Streams	ORDER BY:([Expr1008] ASC, [Expr1009] ASC)	NULL	2987,768	0	0,04787904	49	126,5852	[Expr1008], [Expr1009], [Expr1010], [Expr1011]	NULL	PLAN_ROW	1	1
6	4	       |--Sort(ORDER BY:([Expr1008] ASC, [Expr1009] ASC))	1	3	2	Sort	Sort	ORDER BY:([Expr1008] ASC, [Expr1009] ASC)	NULL	2987,768	0,002815315	0,01347742	49	126,5373	[Expr1008], [Expr1009], [Expr1010], [Expr1011]	NULL	PLAN_ROW	1	1
6	4	            |--Hash Match(Aggregate, HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009]) DEFINE:([Expr1010]=SUM([partialagg1012]), [Expr1011]=SUM([partialagg1013])))	1	4	3	Hash Match	Aggregate	HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009])	[Expr1010]=SUM([partialagg1012]), [Expr1011]=SUM([partialagg1013])	2987,768	0	0,07443649	49	126,521	[Expr1008], [Expr1009], [Expr1010], [Expr1011]	NULL	PLAN_ROW	1	1
24	4	                 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1008], [Expr1009]))	1	5	4	Parallelism	Repartition Streams	PARTITION COLUMNS:([Expr1008], [Expr1009])	NULL	11951,07	0	0,04857108	49	126,4466	[Expr1008], [Expr1009], [partialagg1012], [partialagg1013]	NULL	PLAN_ROW	1	1
24	4	                      |--Hash Match(Partial Aggregate, HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009]) DEFINE:([partialagg1012]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [partialagg1013]=SUM(CASE WHEN ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2)) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)))	1	6	5	Hash Match	Partial Aggregate	HASH:([Expr1008], [Expr1009]), RESIDUAL:([Expr1008] = [Expr1008] AND [Expr1009] = [Expr1009]), DEFINE:([partialagg1012]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [partialagg1013]=SUM(CASE WHEN ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2)) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END))	[partialagg1012]=SUM([AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea]), [partialagg1013]=SUM(CASE WHEN ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2) AND [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc1].[ABCClass]=(2)) AND ([AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(1) OR [AxOlap].[dbo].[TEST_ABCHistory].[ABCClass] as [abc2].[ABCClass]=(2)) THEN [AxOlap].[dbo].[TEST_Sales].[salea] as [s].[salea] ELSE (0.0000) END)	11951,07	0	4,304107	49	126,398	[Expr1008], [Expr1009], [partialagg1012], [partialagg1013]	NULL	PLAN_ROW	1	1
1826294	4	                           |--Hash Match(Left Outer Join, HASH:([s].[itemid], [l].[shopgroupid])=([abc1].[itemid], [abc1].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc1].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc1].[RDateEnd]))	1	7	6	Hash Match	Left Outer Join	HASH:([s].[itemid], [l].[shopgroupid])=([abc1].[itemid], [abc1].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc1].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc1].[RDateEnd])	NULL	2346159	0	26,88527	40	122,0939	[s].[salea], [abc1].[ABCClass], [abc2].[ABCClass], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
1826294	4	                                |--Bitmap(HASH:([s].[itemid], [l].[shopgroupid]), DEFINE:([Bitmap1015]))	1	8	7	Bitmap	Bitmap Create	HASH:([s].[itemid], [l].[shopgroupid])	[Bitmap1015]	1272274	0	17,97417	67	56,36003	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [abc2].[ABCClass], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
1826294	4	                                |    |--Hash Match(Right Outer Join, HASH:([abc2].[itemid], [abc2].[shopgroupid])=([s].[itemid], [l].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc2].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc2].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc2].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc2].[RDateEnd]))	1	9	8	Hash Match	Right Outer Join	HASH:([abc2].[itemid], [abc2].[shopgroupid])=([s].[itemid], [l].[shopgroupid]), RESIDUAL:([AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc2].[itemid]=[AxOlap].[dbo].[TEST_Sales].[itemid] as [s].[itemid] AND [AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc2].[shopgroupid]=[AxOlap].[dbo].[TEST_InvLocs].[shopgroupid] as [l].[shopgroupid] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]>=[AxOlap].[dbo].[TEST_ABCHistory].[rdate] as [abc2].[rdate] AND [AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate]<=[AxOlap].[dbo].[TEST_ABCHistory].[RDateEnd] as [abc2].[RDateEnd])	NULL	1272274	0	17,97417	67	56,36003	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [abc2].[ABCClass], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
1791479	4	                                |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([abc2].[itemid], [abc2].[shopgroupid]))	1	10	9	Parallelism	Repartition Streams	PARTITION COLUMNS:([abc2].[itemid], [abc2].[shopgroupid])	NULL	1791479	0	3,444291	48	12,83801	[abc2].[shopgroupid], [abc2].[itemid], [abc2].[rdate], [abc2].[ABCClass], [abc2].[RDateEnd]	NULL	PLAN_ROW	1	1
1791479	4	                                |         |    |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc2]), SEEK:([abc2].[ABCModel]=(4)) ORDERED FORWARD)	1	11	10	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc2]), SEEK:([abc2].[ABCModel]=(4)) ORDERED FORWARD	[abc2].[shopgroupid], [abc2].[itemid], [abc2].[rdate], [abc2].[ABCClass], [abc2].[RDateEnd]	1791479	8,901027	0,492696	48	9,393723	[abc2].[shopgroupid], [abc2].[itemid], [abc2].[rdate], [abc2].[ABCClass], [abc2].[RDateEnd]	NULL	PLAN_ROW	1	1
1826294	4	                                |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[itemid], [l].[shopgroupid]))	1	12	9	Parallelism	Repartition Streams	PARTITION COLUMNS:([s].[itemid], [l].[shopgroupid])	NULL	1272274	0	0,1600237	63	25,54783	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
1826294	4	                                |              |--Hash Match(Inner Join, HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid]))	1	13	12	Hash Match	Inner Join	HASH:([l].[inventlocationid])=([s].[inventlocationid]), RESIDUAL:([AxOlap].[dbo].[TEST_InvLocs].[inventlocationid] as [l].[inventlocationid]=[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid])	NULL	1272274	0	8,759792	63	25,3878	[s].[itemid], [s].[rdate], [s].[salea], [l].[shopgroupid], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
409	4	                                |                   |--Bitmap(HASH:([l].[inventlocationid]), DEFINE:([Bitmap1014]))	1	14	13	Bitmap	Bitmap Create	HASH:([l].[inventlocationid])	[Bitmap1014]	409	0	0,02892953	28	0,03294699	[l].[inventlocationid], [l].[shopgroupid]	NULL	PLAN_ROW	1	1
409	4	                                |                   |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([l].[inventlocationid]))	1	15	14	Parallelism	Repartition Streams	PARTITION COLUMNS:([l].[inventlocationid])	NULL	409	0	0,02892953	28	0,03294699	[l].[inventlocationid], [l].[shopgroupid]	NULL	PLAN_ROW	1	1
409	4	                                |                   |         |--Clustered Index Scan(OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l]))	1	16	15	Clustered Index Scan	Clustered Index Scan	OBJECT:([AxOlap].[dbo].[TEST_InvLocs].[idxmain] AS [l])	[l].[inventlocationid], [l].[shopgroupid]	409	0,003865741	0,000151725	28	0,004017466	[l].[inventlocationid], [l].[shopgroupid]	NULL	PLAN_ROW	1	1
0	0	                                |                   |--Compute Scalar(DEFINE:([Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))))	1	17	13	Compute Scalar	Compute Scalar	DEFINE:([Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)))	[Expr1008]=datepart(year,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0)), [Expr1009]=datepart(month,CONVERT_IMPLICIT(datetime,[AxOlap].[dbo].[TEST_Sales].[rdate] as [s].[rdate],0))	1837165	0	0,04592913	64	16,59506	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea], [Expr1008], [Expr1009]	NULL	PLAN_ROW	1	1
1827576	4	                                |                        |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([s].[inventlocationid]))	1	18	17	Parallelism	Repartition Streams	PARTITION COLUMNS:([s].[inventlocationid])	NULL	1837165	0	3,288894	56	16,54913	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	NULL	PLAN_ROW	1	1
1827576	4	                                |                             |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000'),  WHERE:(PROBE([Bitmap1014],[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid])) ORDERED FORWARD)	1	19	18	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_Sales].[idxmain] AS [s]), SEEK:([s].[rdate] >= '2014-01-01 00:00:00.000' AND [s].[rdate] <= '2014-06-01 00:00:00.000'),  WHERE:(PROBE([Bitmap1014],[AxOlap].[dbo].[TEST_Sales].[inventlocationid] as [s].[inventlocationid])) ORDERED FORWARD	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	1837165	12,75498	0,5052597	56	13,26024	[s].[itemid], [s].[inventlocationid], [s].[rdate], [s].[salea]	NULL	PLAN_ROW	1	1
2521203	4	                                |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([abc1].[itemid], [abc1].[shopgroupid]))	1	27	7	Parallelism	Repartition Streams	PARTITION COLUMNS:([abc1].[itemid], [abc1].[shopgroupid])	NULL	5429890	0	10,3816	48	38,84862	[abc1].[shopgroupid], [abc1].[itemid], [abc1].[rdate], [abc1].[ABCClass], [abc1].[RDateEnd]	NULL	PLAN_ROW	1	1
2521203	4	                                     |--Clustered Index Seek(OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc1]), SEEK:([abc1].[ABCModel]=(1)),  WHERE:(PROBE([Bitmap1015],[AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid],[AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid])) ORDERED FORWARD)	1	28	27	Clustered Index Seek	Clustered Index Seek	OBJECT:([AxOlap].[dbo].[TEST_ABCHistory].[idxmain] AS [abc1]), SEEK:([abc1].[ABCModel]=(1)),  WHERE:(PROBE([Bitmap1015],[AxOlap].[dbo].[TEST_ABCHistory].[itemid] as [abc1].[itemid],[AxOlap].[dbo].[TEST_ABCHistory].[shopgroupid] as [abc1].[shopgroupid])) ORDERED FORWARD	[abc1].[shopgroupid], [abc1].[itemid], [abc1].[rdate], [abc1].[ABCClass], [abc1].[RDateEnd]	5429890	26,97376	1,493259	48	28,46702	[abc1].[shopgroupid], [abc1].[itemid], [abc1].[rdate], [abc1].[ABCClass], [abc1].[RDateEnd]	NULL	PLAN_ROW	1	1


Glory, а как Вы считаете, в общем случае лучше хранить две даты или одну (именно для подобных запросов, если не учитывать заполнение таблиц)?
30 июн 14, 13:06    [16238132]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
MSSQLBug
Guest
То есть, можно считать, что наилучший способ хранения хронологических данных в каких-то разрезах (измерениях)
с целью оптимизации запросов к ним за периоды такой?

-- Таблица истории значения, разрезы и типы данных по вкусу, 
-- два поля даты --- начало и конец периода действия.
-- Лучше, наверное, хранить период как [дата1; дата2).
CREATE TABLE ValueNHistory (
AnalysisModel int NOT NULL, -- первый разрез (модель анализа)
shopgroupid varchar(10) NOT NULL, -- второй разрез (группа магазинов)
itemid varchar(20) NOT NULL, -- третий разрез  (номенклатура)
rdate smalldatetime NOT NULL, -- дата начала действия значения
RDateEnd smalldatetime NOT NULL, -- дата завершения действия значения
ValueN int NOT NULL -- собственно показатель
) 

ALTER TABLE ValueNHistory WITH CHECK ADD CONSTRAINT DatesInterval CHECK ((RDate <= RDateEnd))
-- Индекс:
CREATE UNIQUE CLUSTERED INDEX idxmain ON dbo.ValueNHistory(AnalysisModel, itemid, shopgroupid, rdate)

-- Допустим, таблица продаж (с её датами и разрезами будут сопоставляться значения).
-- Для примера, продажи хранятся не в тех разрезах, что история значения:
CREATE TABLE dbo.TEST_Sales(
itemid varchar(20) NOT NULL, -- первый разрез
inventlocationid varchar(10) NOT NULL, --  второй разрез, сопоставляется с историей через промежуточную таблицу
rdate smalldatetime NOT NULL, -- дата продажи
salea numeric(38, 4) NOT NULL -- сумма реализации
)
-- И индекс (хранится общая сумма продаж по разрезам за дату):
CREATE UNIQUE CLUSTERED INDEX idxmain ON TEST_Sales(rdate, itemid, inventlocationid)

-- Сопоставление inventlocationid -> ShopgroupId:
CREATE TABLE InvLocs(
inventlocationid varchar(10) NOT NULL PRIMARY KEY,
shopgroupid varchar(10) NOT NULL
)

-- И такие запросы (например, ежедневные продажи в зависимости от значений на тот момент):
select s.Rdate, sum(salea), 
         sum(case when (v1.ValueN = 1 and v2.ValueN = 1) 
                          OR (v1.ValueN = 1 and v2.ValueN = 2) 
                          OR (v1.ValueN = 2 and v2.ValueN = 1) 
                        then salea else 0 end) as S_TOP
 from Sales as s
inner join InvLocs as l 
    on s.inventlocationid = l.INVENTLOCATIONID
LEFT join ValueNHistory v1 
   on v1.AnalysisModel = 1 and v1.ITEMID = s.itemid and v1.SHOPGROUPID = l.SHOPGROUPID 
 and s.rdate >= v1.rdate and s.rdate < v1.RDateEnd
LEFT join ValueNHistory v2 
   on v2.AnalysisModel = 4 and v2.ITEMID = s.itemid and v2.SHOPGROUPID = l.SHOPGROUPID 
  and s.rdate >= v2.rdate and s.rdate < v2.RDateEnd
where s.rdate >= '20140101' and s.rdate < '20140501'
GROUP BY s.RDate
ORDER BY s.RDate
1 июл 14, 10:06    [16241933]     Ответить | Цитировать Сообщить модератору
 Re: Хранение хронологических данных и запросы к ним  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
MSSQLBug
То есть, можно считать, что наилучший способ хранения хронологических данных в каких-то разрезах (измерениях)
с целью оптимизации запросов к ним за периоды такой?
-- два поля даты --- начало и конец периода действия.
CREATE TABLE ValueNHistory (
...
rdate smalldatetime NOT NULL, -- дата начала действия значения
RDateEnd smalldatetime NOT NULL, -- дата завершения действия значения
...
) 
..
CREATE UNIQUE CLUSTERED INDEX idxmain ON dbo.ValueNHistory(AnalysisModel, itemid, shopgroupid, rdate)
..
-- Сопоставление inventlocationid -> ShopgroupId:
CREATE TABLE InvLocs(
inventlocationid varchar(10) NOT NULL PRIMARY KEY,
shopgroupid varchar(10) NOT NULL
)
...
 from Sales as s
inner join InvLocs as l on s.inventlocationid = l.INVENTLOCATIONID
LEFT join ValueNHistory v1 on v1.AnalysisModel = 1 and v1.ITEMID = s.itemid and v1.SHOPGROUPID = l.SHOPGROUPID 
 and s.rdate >= v1.rdate and s.rdate < v1.RDateEnd
LEFT join ValueNHistory v2 on v2.AnalysisModel = 4 and v2.ITEMID = s.itemid and v2.SHOPGROUPID = l.SHOPGROUPID 
  and s.rdate >= v2.rdate and s.rdate < v2.RDateEnd
where s.rdate >= '20140101' and s.rdate < '20140501'
Ни один постоялец форума не заглянул ещё, поэтому имеем то что имеем.
Стандартная задача на форуме, мусолилось сотни раз.

Тут несколько ошибок.
1. Хранить так по периодам - это скан, префикс конечно будет, но ограничение по дате только с одной стороны (по колонке rdate).
2. Зачем делать такую связку, когда можно тупо сослаться на ID строки версии явно. И не надо тянуть это веретено "разрезов" (в FK, запросах). И хиты кэша будут лучше.

Провели какой-то расчёт (поиск версии) один раз (во время продаж), вот тогда и зафиксируйте (ссылка на ID версии).
В довесок, более надёжное указание и связывание данных. А то так поменял диапазоны и куяк "шеф, всё пропало".
1 июл 14, 16:16    [16244589]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить