날짜 group by 누가 누가 더 빠를까요?

N모사 XX 님의 월척에 걸린....민석, 상현, 교성.......한명 더 있었는데.....누구지?

 

drop table tblx
go

create table tblx (idx int, d datetime)
go

insert into tblx
select top 1000000 ROW_NUMBER() over(order by (select 1)) , GETDATE()
  from sysobjects a
     , sysobjects b
     , sysobjects c
     , sysobjects d
go

insert into tblx
select top 1000000 ROW_NUMBER() over(order by (select 1)) , GETDATE() +1
  from sysobjects a
     , sysobjects b
     , sysobjects c
     , sysobjects d
go

insert into tblx values (1, '2012-09-15 23:59:59.993')
insert into tblx values (1, '2012-09-13 23:59:59.997')
insert into tblx values (1, '2012-09-14 23:59:59.997')
insert into tblx values (1, '2012-09-15 23:59:59.997')
insert into tblx values (1, '2012-09-16')
insert into tblx values (1, '2012-09-17')
insert into tblx values (1, '2012-09-18')
go

create clustered index cl_tblx on tblx (d)
go

set statistics time on
go

 

-- 1등

SELECT
 CONVERT(datetime,cast(CAST (d as float)as int))
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY cast(CAST (d as float)as int)
order by 1
option (maxdop 1)

 

-- 2등
SELECT
 DATEADD( day, DATEDIFF(day, 0, d), 0)
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY DATEADD( day, DATEDIFF(day, 0, d), 0)
order by 1
option (maxdop 1)

 

-- 3등?

convert (char(8),d, 112)

CPU 시간 = 1560밀리초, 경과 시간 = 1646밀리초 -- 버리삼......아래 달리기 리스트에 들어가지도 못 함

 

-- 결과 틀릴수 있음
SELECT
 cast(CAST (d as int) as datetime)
,COUNT(*)
FROM dbo.tblx WITH(NOLOCK)
GROUP BY CAST (d as int)
order by 1
option (maxdop 1)