Тестирование 3х подходов:
Код + статистика 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
USE tempdb
GO
CREATE TABLE [dbo].[Period] (
[Object] Int
,[From] DateTime
,CONSTRAINT [PK_Period] PRIMARY KEY (
[Object]
,[From]
)
,[To] DateTime
,[Duration] Int -- AS (DateDiff(Minute,[From],[To])) PERSISTED
,[Period] Geometry -- AS Geometry::STLineFromText('LineString(0 '
-- + Convert(VarChar,DateDiff(Minute,0,[From])) + ', 0 '
-- + Convert(VarChar,DateDiff(Minute,0, [To])) + ')', 0) PERSISTED
)
CREATE UNIQUE INDEX [UQ_Period_Day] ON [dbo].[Period] ([From],[Object]) INCLUDE ([To]) WHERE ([Duration] < 1440) -- 1.5 Gb
CREATE UNIQUE INDEX [UQ_Period_Other] ON [dbo].[Period] ([From],[Object]) INCLUDE ([To]) WHERE ([Duration] >= 1440) -- 0 (фейл теста)
CREATE UNIQUE INDEX [UQ_Period_All] ON [dbo].[Period] ([From],[Object]) INCLUDE ([To]) -- 1.5 Gb
CREATE SPATIAL INDEX [IX_Period_Sp] ON [dbo].[Period] ([Period]) WITH ( -- 9 Gb
BOUNDING_BOX = (0,53121600,1,63113760)
,GRIDS = (HIGH,HIGH,HIGH,HIGH)
)
GO
;WITH Random (Rnd) AS (
SELECT Tan(-Rand(CheckSum(NewID()))*90) -- Типа Гаусово
FROM master.dbo.spt_values A
,master.dbo.spt_values B
,master.dbo.spt_values C
WHERE C.[type] = 'P' AND C.number < 10
), Data AS (
SELECT Row_Number()OVER(ORDER BY (SELECT NULL)) % 10000 AS [Object]
,DateAdd(Day,Row_Number()OVER(ORDER BY (SELECT NULL)) / 10000,'20010101 08:00') AS [Date]
,550 + [Rnd] AS [Duration]
FROM Random
WHERE Abs([Rnd]) < 500
) INSERT dbo.Period
SELECT [Object]
,[Date]
,DateAdd(Minute,[Duration],[Date])
,Convert(Int,[Duration])
,Geometry::STLineFromText('LineString(0 '
+ Convert(VarChar,DateDiff(Minute,0,[Date])) + ',0 '
+ Convert(VarChar,DateDiff(Minute,0,[Date]) + [Duration]) + ')',0)
FROM Data
-- 62 719 613
UPDATE STATISTICS dbo.Period
GO
SET NOCOUNT ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- SELECT Min([From]), Max([From]) FROM dbo.Period
DECLARE @From DateTime = '20010718' -- '20070718' -- '20170718'
,@To DateTime = '20010730' -- '20070730' -- '20170730'
,@Object Int
-- Пространственный
SELECT @Object = [Object] -- 947
FROM dbo.Period P WITH(Index(IX_Period_Sp))
WHERE Geometry::STLineFromText('LineString(0 '
+ Convert(VarChar,DateDiff(Minute,0,@From)) + ',0 '
+ Convert(VarChar,DateDiff(Minute,0,@To )) + ')',0)
.STIntersects(P.Period) = 1
-- Пара ограниченных
;WITH Query AS (
SELECT *
FROM dbo.Period
WHERE Duration >= 1440
AND [From] <= @To
AND [To] >= @From
UNION ALL
SELECT *
FROM dbo.Period
WHERE Duration < 1440
AND [From] <= @To
AND [From] >= DateAdd(Minute,-1440,@From)
AND [To] >= @From
)SELECT @Object = [Object] -- 27
FROM Query
-- Тупой
SELECT @Object = [Object] -- 260
FROM dbo.Period
WHERE [From] <= @To
AND [To] >= @From
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
Таблица "extended_index_325576198_384000". Число просмотров 125, логических чтений 3067, физических чтений 0, упреждающих чтений 0
Таблица "Period". Число просмотров 0, логических чтений 487641, физических чтений 0, упреждающих чтений 0
Время ЦП = 12329 мс, затраченное время = 931 мс.
Таблица "Period". Число просмотров 16, логических чтений 432, физических чтений 0, упреждающих чтений 0
Время ЦП = 45 мс, затраченное время = 18 мс.
Таблица "Period". Число просмотров 17, логических чтений 197761, физических чтений 0, упреждающих чтений 0
Время ЦП = 7380 мс, затраченное время = 474 мс.
--------------------------------------------------------------------------------
Таблица "extended_index_325576198_384000". Число просмотров 124, логических чтений 3061, физических чтений 0, упреждающих чтений 0
Таблица "Period". Число просмотров 0, логических чтений 488717, физических чтений 98, упреждающих чтений 54
Время ЦП = 12321 мс, затраченное время = 971 мс.
Таблица "Period". Число просмотров 17, логических чтений 432, физических чтений 0, упреждающих чтений 0
Время ЦП = 31 мс, затраченное время = 16 мс.
Таблица "Period". Число просмотров 17, логических чтений 78431, физических чтений 0, упреждающих чтений 0
Время ЦП = 2965 мс, затраченное время = 199 мс.
--------------------------------------------------------------------------------
Таблица "extended_index_325576198_384000". Число просмотров 124, логических чтений 3088, физических чтений 0, упреждающих чтений 0
Таблица "Period". Число просмотров 0, логических чтений 488312, физических чтений 207, упреждающих чтений 27
Время ЦП = 12201 мс, затраченное время = 984 мс.
Таблица "Period". Число просмотров 17, логических чтений 432, физических чтений 0, упреждающих чтений 0
Время ЦП = 47 мс, затраченное время = 16 мс.
Таблица "Period". Число просмотров 17, логических чтений 6862, физических чтений 0, упреждающих чтений 0
Время ЦП = 280 мс, затраченное время = 31 мс.
Индекс:ТупойПара ограниченныхПространственныйРазмер:1.5G1.5G(+0)9GВермя L:474(7380)18(45)931(12329)IO L:1977614323067+487641Вермя M:199(2965)16(31)971(12321)IO M:784314323061+488717Вермя R:31(280)16(47)984(12201)IO R:68624323088+488312
Недостатки:
1. Нельзя в WHERE индекса вешать даже PERSISTED вычисляемую колонку
2. Geometry (CLR) не могут быть PERSISTED
3. В пространственных индексах нет INCLUDE и не могут работать без обращения к кластерному
Вывод: Чем дальше в лес тем толще партизаны. |
