SQL Server分布式数据库性能测试
作者:网络转载 发布时间:[ 2016/6/24 13:52:51 ] 推荐标签:SQL server 性能测试 数据库
我使用三台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
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11