【讨论】SQL server 2005分区后的效率
请问各位问题:
1. 建立93个文件组和对应的文件
2. 分区函数:CREATE PARTITION FUNCTION [GpsRangePFN](int) AS RANGE LEFT FOR VALUES (0, 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, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92)
3. 分区方案:CREATE PARTITION SCHEME [GpsRangePScheme] AS PARTITION [GpsRangePFN] TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22], [FG23], [FG24], [FG25], [FG26], [FG27], [FG28], [FG29], [FG30], [FG31], [FG32], [FG33], [FG34], [FG35], [FG36], [FG37], [FG38], [FG39], [FG40], [FG41], [FG42], [FG43], [FG44], [FG45], [FG46], [FG47], [FG48], [FG49], [FG50], [FG51], [FG52], [FG53], [FG54], [FG55], [FG56], [FG57], [FG58], [FG59], [FG60], [FG61], [FG62], [FG63], [FG64], [FG65], [FG66], [FG67], [FG68], [FG69], [FG70], [FG71], [FG72], [FG73], [FG74], [FG75], [FG76], [FG77], [FG78], [FG79], [FG80], [FG81], [FG82], [FG83], [FG84], [FG85], [FG86], [FG87], [FG88], [FG89], [FG90], [FG91], [FG92], [FG93], [PRIMARY])
4. 表结构CREATE TABLE [dbo].[RealTime](
[id] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
[telephone] [varchar](20) NULL,
[OrderTime] [datetime] NOT NULL,
[Distance] [bigint] NULL,
[pic] [image] NULL,
[DutyStr] [varchar](10) NULL,
[DayFlag] AS (datediff(day,CONVERT([datetime],'1900-01-01',(111)),[OrderTime])%(93)) PERSISTED
) ON GpsRangePScheme(DayFlag)
GO
CREATE CLUSTERED INDEX [IX_RealTime_Partition] ON [dbo].[RealTime]([telephone], [OrderTime], [DayFlag]) on GpsRangePScheme(DayFlag)
GO
测试过程和结果
1. Select * from RealTime where OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59'和Select * from RealTime where OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' and dayFlag >= 14 and dayFlag <= 18,后者加上dayFlag字段的速度远远大于前者。
2. Select * from RealTime where telephone = '1301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' 和 Select * from RealTime where telephone = '861301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' and dayFlag >= 14 and dayFlag <= 18,这两句的速度是一样的。但是看查询计划中的常量扫描(Constant Scan)的值,前一个SQL语句是把93个分区全部扫描,而后一个只是把14~18分区扫描了一遍,但是为什么两个SQL语句所用的时间都是一样的呢??是因为聚集索引??还是因为我做的分区有问题??
3. 分区表的SQL语句(select * from realTime where telephone = '1301120120' and OrderTime >= '2008-01-01 00:00:00' and OrderTime <= '2008-01-05 23:59:59' )查询花费时间和未分区表的花费时间相同。。。