我使用三台SQL Server 2012 搭建分布式数据库,将一年的1.4亿条数据大致均匀存储在这三台Server中,每台Server 存储4个月的数据,Physical Server的配置基本相同,内存16G,双核 CPU 3.6GHz。
1,创建分区视图,Linked Server的Alias是db2 和 db3,Catalog 是 tdw(test data warehouse)。
CREATE TABLE [dbo].[Commits] ( [CommitID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [AuthorID] [bigint] NOT NULL, [CreatedDate] [datetime2](7) NOT NULL, [CreatedDateKey] [int] NOT NULL, CONSTRAINT [PK__Commits_CommitID] PRIMARY KEY CLUSTERED ( [CommitID] ASC, [CreatedDateKey] ASC ) ) ON [PRIMARY] GO CREATE view [dbo].[view_commits] as select [CommitID] ,[AuthorID] ,[CreatedDate] ,[CreatedDateKey] from dbo.commits c with(nolock) where c.[CreatedDateKey] between 20150900 and 20160000 union ALL select [CommitID] ,[AuthorID] ,[CreatedDate] ,[CreatedDateKey] from db3.tdw.dbo.commits c with(nolock) where c.[CreatedDateKey] between 20150000 and 20150500 union ALL select [CommitID] ,[AuthorID] ,[CreatedDate] ,[CreatedDateKey] from db2.tdw.dbo.commits c with(nolock) where c.[CreatedDateKey] between 20150500 and 20150900 WITH check OPTION; GO |
2,查询性能测试
Test1,使用Basic Table测试,cost:79s
select count(0) from dbo.commits_total c with(nolock) where day(c.[CreatedDate])=1 |
Test2,使用分区视图测试,cost=134s,跟Test1有明显的差距。
select count(0) from dbo.view_commits c with(nolock) where day(c.[CreatedDate])=1 |