alter database [hutai]
add filegroup [FG20070630]
alter database [hutai]
add filegroup [FG20071231]
alter database [hutai]
add filegroup [FG20080630]
alter database [hutai]
add filegroup [FG20081231]
alter database [hutai]
add filegroup [FG20090630]
go
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20070630',
--创建文件组
FILENAME = N'E:\MealRecords\MealRecord0706.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20070630]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20071231',
FILENAME = N'E:\MealRecords\MealRecord0712.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20071231]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20080630',
FILENAME = N'E:\MealRecords\MealRecord0806.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20080630]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20081231',
FILENAME = N'E:\MealRecords\MealRecord0812.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20081231]
ALTER DATABASE [hutai] ADD FILE ( NAME = N'FG20090630',
FILENAME = N'E:\MealRecords\MealRecord0906.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG20090630]
--创建分区函数
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('2007-06-30 23:59:59.997',
'2007-12-31 23:59:59.997',
'2008-06-30 23:59:59.997',
'2008-12-31 23:59:59.997')
--创建分区架构
CREATE PARTITION SCHEME [MealSMSPS]
AS PARTITION OrderDateRangePFN
TO ([FG20070630],[FG20071231],[FG20080630],[FG20081231],[FG20090630])
--创建分区表
CREATE TABLE [MealRecords] (
[id] [int] NOT NULL ,
[ver_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[pos_sequ] [int] NULL ,
[card_sequ] [int] NOT NULL ,
[opcard_id] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clock_id] [int] NOT NULL ,
[emp_id] [nvarchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[card_id] [nvarchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sign_time] [datetime] NOT NULL ,
[mark] [tinyint] NULL ,
[flag] [tinyint] NULL ,
[card_times] [int] NOT NULL ,
[card_consume] [money] NOT NULL ,
[card_balance] [money] NOT NULL ,
[kind] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[passed] [bit] NULL ,
[mealtype] [smallint] NULL ,
[op_ymd] [datetime] NULL ,
[difine_sequ] [int] NULL ,
[op_user] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[id_value] [int] NULL ,
[other_id] [int] NULL ,
CONSTRAINT [PK_MealRecords_new] PRIMARY KEY CLUSTERED
(
[sign_time],
[card_id],
[card_times],
[card_balance],
[card_sequ]
) WITH FILLFACTOR = 95 ON [PRIMARY]
)ON MealSMSPS([sign_time])
导入历史记录:922W,数据分布主要集中在2007.6月,其它月份记录比较平均。
数据库服务器为普通PC,无多硬盘和多CPU。
分区表测试结果
时间跨度 | 原单一表 | 分区表 | 查询记录量 |
|
2007.4.1-2007.6.30 | 5分06秒 | 5分34秒 | 669万 |
|
2007.7.1-2007.12.31 | 1分33秒 | 1分44秒 | 179万 |
|
2008.1.1-2008.6.30 | 34秒 | 32秒 | 67万 |
|
2007.4.1-2007.4.30 | <1秒 | 〈1秒 | 5051 |
|
2007.5.1-2007.5.31 | 〈1秒 | 〈1秒 | 1145 |
|
2007.6.1-2007.6.10 | 4秒 | 4秒 | 5万 |
|
2007.6.10-2007.6.20 | 5分26秒 | 5分19秒 | 650万 |
|
2007.6.20-2007.6.30 | 7秒 | 7秒 | 10万 |
|
|
|
|
|
|
查询SQL语句样本:
SELECT M.emp_id, M.card_id, E.emp_fname,D.Depart_id,D.depart_name,
M.clock_id, M.Pos_Sequ, M.Card_Sequ, M.sign_time, M.flag,
M.card_times, M.card_consume, M.card_balance, M.kind ,M.MealType
FROM MealRecords AS M inner JOIN (Employee AS E inner join Departs D on E.depart_id=D.depart_id) ON M.emp_id=E.emp_id
WHERE (M.sign_time >= '2007-04-01') And (M.sign_time <= '2007-06-30')
从结果来看,在普通PC的条件下,采用SQL2005的分区表不能带来查询性能的提升,反而会有性能损失。对于此疑问,查了相关的资料发,发现情况确实如此。
http://topic.csdn.net/u/20080510/18/527597d5-1012-482a-afd6-b476cf0a8acc.html?seed=1493667992
在现有的条件下,没有更好的服务器来做相关的测试工作!对于用SQL2005分区表来提升整个数据库系统的性能暂时停止。
研究下:SAN和NAS。