软件测试

收藏 / 订阅

SQL Server分布式数据库性能测试-尚脑IT教育软件测试

  我使用三台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]GOCREATE view [dbo].[view_commits]asselect [CommitID],[AuthorID],[CreatedDate],[CreatedDateKey]from dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150900 and 20160000union ALLselect [CommitID],[AuthorID],[CreatedDate],[CreatedDateKey]from db3.tdw.dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150000 and 20150500union ALLselect [CommitID],[AuthorID],[CreatedDate],[CreatedDateKey]from db2.tdw.dbo.commits c with(nolock)where c.[CreatedDateKey] between 20150500 and 20150900WITH 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
喜欢 (88) or分享 (0)
首页  上一页  12345678910  下一页 尾页 共73条记录 1/10

网友评论:7

  1. 小编 1年前 (2015-03-22) #5

    软件测试入门 :mrgreen:

留言主题 *

您的姓名 *

电子信箱:

电话号码:

请你留言: