我使用三台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 |
