1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE myTable (
a numeric( 19 , 0 ) NOT NULL,
b numeric( 1 , 0 ) DEFAULT 0 NOT NULL,
c numeric( 1 , 0 ) DEFAULT - 1 NOT NULL,
Time datetime NOT NULL,
e numeric( 9 , 0 ) NULL,
CONSTRAINT PK_myTable PRIMARY KEY(a,b,c,Time)
WITH max_rows_per_page = 0 , reservepagegap = 0
)
LOCK ALLPAGES
GO
Индексы всяки выдумывал, не помогало. Сейчас на таблице вот такие:
1.
2.
3.
4.
PK_myTable clustered, unique located on default a,b,c,Time
INDEX_myTable_TS nonclustered located on default Time, e
INDX_myTable_TIME nonclustered located on default Time
IndexmyTable_ALL nonclustered located on default Time, b, c, e, a
Конечно я с самим primary key накосячил, при расширении добавилось e, которое записывается в group by. А также Time нужно на 1е место, тк оно изменяется с шагом в 1 день
a = {1..00} от 1 до бесконечности) до numeric(19),
b = {1,2}
c={1,2,3},
e={1,2,3,4,5}. теоретически может дойти до 100, практически до 10.
Пример строки:
a b c Time e
1 ,2, 3, '2010-12-03', 1
За день появляется около 6 тыс записей. Инфа возрастает по времени и a,b,c
--с утра незакешированный запрос выполнялся 3.30 минут
1.
2.
3.
4.
5.
6.
select e, b, count(distinct a)
from myTable
where Time >'2006-01-01' and Time <'2010-10-01'
group by e, b
go
План запроса:
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.
W (1):
W (2): QUERY PLAN FOR STATEMENT 1 (at line 1).
W (3):
W (4):
W (5): STEP 1
W (6): The type of query is INSERT.
W (7): The update mode is direct.
W (8):
W (9): FROM TABLE
W (10): myTable
W (11): Nested iteration.
W (12): Index : IndexmyTable_ALL
W (13): Forward scan.
W (14): Positioning by key.
W (15): Index contains all needed columns. Base table will not be read.
W (16): Keys are:
W (17): Time ASC
W (18): Using I/O Size 2 Kbytes for index leaf pages.
W (19): With LRU Buffer Replacement Strategy for index leaf pages.
W (20): TO TABLE
W (21): Worktable1.
W (22):
W (23): STEP 2
W (24): The type of query is SELECT (into Worktable2).
W (25): GROUP BY
W (26): Evaluate Grouped COUNT AGGREGATE.
W (27):
W (28): FROM TABLE
W (29): Worktable1.
W (30): Nested iteration.
W (31): Table Scan.
W (32): Forward scan.
W (33): Positioning at start of table.
W (34): Using I/O Size 2 Kbytes for data pages.
W (35): With MRU Buffer Replacement Strategy for data pages.
W (36): TO TABLE
W (37): Worktable2.
W (38):
W (39): STEP 3
W (40): The type of query is SELECT.
W (41):
W (42): FROM TABLE
W (43): Worktable2.
W (44): Nested iteration.
W (45): Table Scan.
W (46): Forward scan.
W (47): Positioning at start of table.
W (48): Using I/O Size 2 Kbytes for data pages.
W (49): With MRU Buffer Replacement Strategy for data pages.
W (50):
W (51): The sort for Worktable1 is done in Serial
1.
2.
3.
4.
5.
--запрос выполнялся после предыдущего 30 сек.
select e, b,c, count(distinct a) from myTable
where Time >'2006-01-01' and Time <'2010-10-01'
group by e, b, c
go
План запроса:
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.
W (1):
W (2): QUERY PLAN FOR STATEMENT 1 (at line 2).
W (3):
W (4):
W (5): STEP 1
W (6): The type of query is INSERT.
W (7): The update mode is direct.
W (8):
W (9): FROM TABLE
W (10): myTable
W (11): Nested iteration.
W (12): Index : IndexmyTable_ALL
W (13): Forward scan.
W (14): Positioning by key.
W (15): Index contains all needed columns. Base table will not be read.
W (16): Keys are:
W (17): Time ASC
W (18): Using I/O Size 2 Kbytes for index leaf pages.
W (19): With LRU Buffer Replacement Strategy for index leaf pages.
W (20): TO TABLE
W (21): Worktable1.
W (22):
W (23): STEP 2
W (24): The type of query is SELECT (into Worktable2).
W (25): GROUP BY
W (26): Evaluate Grouped COUNT AGGREGATE.
W (27):
W (28): FROM TABLE
W (29): Worktable1.
W (30): Nested iteration.
W (31): Table Scan.
W (32): Forward scan.
W (33): Positioning at start of table.
W (34): Using I/O Size 2 Kbytes for data pages.
W (35): With MRU Buffer Replacement Strategy for data pages.
W (36): TO TABLE
W (37): Worktable2.
W (38):
W (39): STEP 3
W (40): The type of query is SELECT.
W (41):
W (42): FROM TABLE
W (43): Worktable2.
W (44): Nested iteration.
W (45): Table Scan.
W (46): Forward scan.
W (47): Positioning at start of table.
W (48): Using I/O Size 2 Kbytes for data pages.
W (49): With MRU Buffer Replacement Strategy for data pages.
W (50):
W (51): The sort for Worktable1 is done in Serial
Пробовал ставить mru, lru. Приоритет у mru, но лучше вообще не указывать.
Думал насчет параллелизма. Только реализовать пока что невозможно.
Требование: чтобы запрос выполнялся за 5 сек.
