本篇文章着重讨论两个问题:
1、主键设为Int型与Varchar型,性能有何差异?
2、非聚集索引在大数量下能否提高性能?
测试环境:
Cpu: T2300 1.66G 内存:1G
系统: Windows Xp Sp3
数据库: SQL Server 2005
步骤:
一、建立两个表
字段 | A | B | dtA | dtB | 表:TestA |
类型 | int | varchar | datetime | datetime | |
说明 | 主键 | ||||
字段 | A | B | dtA | dtB | 表:TestB |
类型 | int | varchar | datetime | datetime | |
说明 | 主键 | 非聚集索引 |
二、填充数据
declare
@a
int
set @a = 1
use extdata
while ( @a < 5000000 )
begin
insert TestA (A,B,dtA,dtB) values ( @a , convert ( varchar , @a , 112 ), getdate (), getdate ())
insert TestB (A,B,dtA,dtB) values ( @a , convert ( varchar , @a , 112 ), getdate (), getdate ())
set @a = @a + 1
end
set @a = 1
use extdata
while ( @a < 5000000 )
begin
insert TestA (A,B,dtA,dtB) values ( @a , convert ( varchar , @a , 112 ), getdate (), getdate ())
insert TestB (A,B,dtA,dtB) values ( @a , convert ( varchar , @a , 112 ), getdate (), getdate ())
set @a = @a + 1
end
上面操作在我本本上耗时约80分钟
三、测试不同数据类型主键性能
--
-SQL执行计时
declare @date1 datetime
declare @date2 datetime
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testa where A = 2999999
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
-- -SQL执行计时
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testb where B = ' 2999999 '
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
declare @date1 datetime
declare @date2 datetime
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testa where A = 2999999
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
-- -SQL执行计时
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testb where B = ' 2999999 '
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
执行结果如下:
四、测试非聚集性能
--
-SQL执行计时
declare @date1 datetime
declare @date2 datetime
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testa where A = 2999999 or dtb = getdate ()
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
-- -SQL执行计时
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testb where B = ' 2999999 ' or dtb = getdate ()
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
declare @date1 datetime
declare @date2 datetime
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testa where A = 2999999 or dtb = getdate ()
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
-- -SQL执行计时
select @date1 = getdate ()
-- 测试语句
use extdata
select * from testb where B = ' 2999999 ' or dtb = getdate ()
select @date2 = getdate ()
select datediff (millisecond, @date1 , @date2 )
-- 结果是毫秒数
在主键后又加入了一个对字段dtb的比较,其它TestB表中,此字段设为非聚集索引,结果如下:
========================================
测试结果:
1、主键为Int型或Varchar型,性能几乎是一样的。(经过多次对比,最多有10毫秒的差异,并且还是Varchar型的快一点)
2、数据量较大,建立合适的非聚集索引,能大大提高性能。(如上图,差别为400多倍)
本次测试可能不是特别全面,回头有机会补充。