sql server 中 bigint 和 datetime 性能比较
-- 创建表 create table Test_tbl ( ID varchar(40) primary key nonclustered, IntCol int, DateCol datetime ) --================================================================================== -- 【100w数据测试】 --================================================================================== -- 创建100w测试数据 declare @j int declare @data float declare @style bigint set @j = 1 while @j<1000000 begin set @style = cast(replace(replace(replace(convert(varchar(30),GETDATE(),120),'-',''),':',''), ' ', '') as bigint) insert into Test_tbl(ID, IntCol, DateCol) values(NEWID(),@style, getdate()) set @j = @j + 1 end declare @d datetime set @d = getdate() declare @i int print '【100w数据 查询100次测试】' -- 测试性能1,datetime ------------------------------------------------------------------------------------- set nocount on -- 不显示受影响行数 set @i=0 while @i <20 begin select top 1 * from Test_tbl where DateCol>getdate() set @i = @i+1 end ------------------------------------------------------------------------------------- select [语句执行时间(毫秒)]=datediff(ms, @d, getdate()) set nocount off print '100w数据 date 语句执行时间(毫秒):' + CONVERT(varchar(30), datediff(ms, @d, getdate())) -- 测试性能2,int ------------------------------------------------------------------------------------- set nocount on -- 不显示受影响行数 set @i=0 while @i <20 begin select top 1 * from Test_tbl where IntCol>20151212030303 set @i = @i+1 end ------------------------------------------------------------------------------------- select [语句执行时间(毫秒)]=datediff(ms, @d, getdate()) set nocount off print '100w数据 int 语句执行时间(毫秒):' + CONVERT(varchar(30), datediff(ms, @d, getdate())) --================================================================================== -- 【1000w数据测试】 --================================================================================== -- 创建900w测试数据,累计1000w set @j = 1 while @j<9000000 begin set @style = cast(replace(replace(replace(convert(varchar(30),GETDATE(),120),'-',''),':',''), ' ', '') as bigint) insert into Test_tbl(ID, IntCol, DateCol) values(NEWID(),@style,getdate()) set @j = @j + 1 end print '【1000w数据 查询100次测试】' -- 测试性能1,datetime ------------------------------------------------------------------------------------- set nocount on -- 不显示受影响行数 set @i=0 while @i <100 begin select top 1 * from Test_tbl where DateCol>getdate() set @i = @i+1 end ------------------------------------------------------------------------------------- select [语句执行时间(毫秒)]=datediff(ms, @d, getdate()) set nocount off print '1000w数据 date 语句执行时间(毫秒):' + CONVERT(varchar(30), datediff(ms, @d, getdate())) -- 测试性能2,int ------------------------------------------------------------------------------------- set nocount on -- 不显示受影响行数 set @i=0 while @i <100 begin select top 1 * from Test_tbl where IntCol>20151212030303 set @i = @i+1 end ------------------------------------------------------------------------------------- select [语句执行时间(毫秒)]=datediff(ms, @d, getdate()) set nocount off print '1000w数据 int 语句执行时间(毫秒):' + CONVERT(varchar(30), datediff(ms, @d, getdate()))
欢迎在评论区留下你宝贵的意见,不论好坏都是我前进的动力(cnblogs 排名提升)!
如果喜欢,记得点赞、推荐、关注、收藏、转发 ... ;)