datetime 형태 잘 사용해보기 (trimming techniques)
datetime 타입을 가지고 group by 할때 날짜만 가지고 한다거나 할 경우
일반적으로 아래와 같이 많이들 사용하셨죠... (저도 역시나... ^^)
select convert(varchar(10), getdate(), 120)
블로그를 보다가 매우 간단하나 날짜별 집계를 할때 성능향상을 시켜주는 내용을
찾아서 공유드립니다.
백마디 말 보다는 바로 테스트를 진행해보도록 하겠습니다.
1. 로그 테이블를 테스트 기준으로 잡았습니다. 건수는 약 200만건입니다.
select count(*) from dbo.LogTable WITH (nolock)
Result>
1995925
2-1. 일반적인 방식으로 각 날짜별 플레이횟수를 수집 : CPU 시간 = 3857ms
set statistics io on
set statistics time on
select convert(varchar(10), startdate, 120), count(*)
from dbo.LogTable with (Nolock)
group by convert(varchar(10), startdate, 120)
order by convert(varchar(10), startdate, 120)
set statistics io off
set statistics time off
(377개 행 적용됨)
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'LogTable'. 검색 수 5, 논리적 읽기 수 26926, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
SQL Server 실행 시간:
CPU 시간 = 3857ms, 경과 시간 = 1015ms.
SQL Server 실행 시간:
CPU 시간 = 0ms, 경과 시간 = 1ms.
2-2. Blog에 나온 방식으로 각 날짜별 플레이횟수를 수집 : CPU 시간 = 1125ms
set statistics io on
set statistics time on
select DATEADD( day, DATEDIFF(day, 0, startdate), 0), count(*)
from dbo.CBT_GameRoomLog with (Nolock)
group by DATEADD( day, DATEDIFF(day, 0, startdate), 0)
order by DATEADD( day, DATEDIFF(day, 0, startdate), 0)
set statistics io off
set statistics time off
(377개 행 적용됨)
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'LogTable'. 검색 수 5, 논리적 읽기 수 26926, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
SQL Server 실행 시간:
CPU 시간 = 1125ms, 경과 시간 = 282ms. (약 3배이상 성능 개선됨)
SQL Server 실행 시간:
CPU 시간 = 0ms, 경과 시간 = 1ms.
의견.
Ø CPU 시간을 보시면 알겠지만 3배 이상의 차이가 났습니다. 가능하면 다음부터 datetime타입의 컬럼에서 날짜만
필요한 경우에는 위와 같이 사용하는 것이 좋을 듯 합니다.
Ø 코드가 복잡한 것은 아니라서 보시면 다들아시겠지만 여기서의 중요 포인트는 형변환을 안하고
가능한 현재의 타입을 유지하면서 목적하는 것을 만들어 냈다는 점입니다.내장함수라도 함수사용은
CPU를 많이 사용하므로 쿼리를 만드실 때 가능하면 형변환을 줄이는 것이 좋을 듯 합니다.
Ø 늘 쓰던 부분이라도 성능 개선을 할 수 없는지 고민하는 자세를 가져야겠다는 교훈을 얻었습니다.
@@ 출처
http://www.sqlmag.com/Article/ArticleID/94487/sql_server_94487.html
http://www.sqlmag.com/Article/ArticleID/94819/sql_server_94819.html
http://www.sqlmag.com/articles/index.cfm?articleid=100884&
SQL Server 2012 (Denali) Order by 절의 새로운 기능 추가
SQL Server 2005부터 row_number 함수가 제공됨으로써 오라클처럼 서브쿼리를 활용하여 게시판의 페이징 쿼리를
만들 수 있게 되었습니다.
row_number를 사용할 경우, 서브쿼리를 써야하기 때문에 복잡하게 보인다는 점인데요... 이번에 SQL Server 2011부터
order by 절에 새로운 기능이 추가되어짐으로써 좀 더 간단한 쿼리로 페이징 구현이 가능하게 되어 소개드립니다.
과거 row_number를 사용한 것과 성능차이는 없는지 테스트를 통해서 슬쩍 살펴보도록 하겠습니다.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 페이징 테스트할 임시 테이블 생성
create table tab01 (
col01 int identity (1,1) not null
, col02 varchar(5000)
)
create clustered index idx_tab01_col01 on tab01 (col01)
-- 임시 테이블에 데이터 100만건 입력하기
set nocount on
declare @i int
set @i = 1
begin tran
while (@i < 1000000)
begin
insert into tab01 (col02)
select REPLICATE('ABC', 50);
set @i = @i + 1;
end
commit tran
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 변경된 로직의 쿼리 수행 (시작페이지를 지정한 경우)
declare @startArticleNo int
declare @PageNo int
declare @PageSize int
set @startArticleNo = 950001
set @PageNo = 5
set @PageSize = 5
set statistics profile on
set statistics io on
select *
from dbo.tab01 wtih (nolock)
where col01 >= @startArticleNo
order by col01
offset (@PageNo-1)*@PageSize rows
fetch next @PageSize rows only;
set statistics profile off
set statistics io off
Result>
950021
950022
950023
950024
950025
IO사용량>
Table 'tab01'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
실행계획>
Rows Executes StmtText
5 1 select * from dbo.tab01 wtih (nolock) where col01 >= @startArticleNo order by col01 offset (@PageNo-1)
5 1 |--Top(OFFSET EXPRESSION:(CONVERT_IMPLICIT(bigint,([@PageNo]-(1))*[@PageSize],0)),TOP EXPRESSION:
25 1 |--Clustered Index Seek(OBJECT:([master].[dbo].[tab01].[idx_tab01_col01] AS [wtih]), SEEK
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@ 이전 로직의 쿼리 수행 (시작페이지를 지정한 경우)
declare @startArticleNo int
declare @PageNo int
declare @PageSize int
set @startArticleNo = 950001
set @PageNo = 5
set @PageSize = 5
set statistics profile on
set statistics io on
select x.*
from (
select
top ( @PageNo * @PageSize)
col01
, col02
, ROW_NUMBER() over (order by col01 ) seqNo
from dbo.tab01 with (readuncommitted)
where col01 > @startArticleNo
order by col01 ) x
where x.seqNo between (@PageNo-1) * @PageSize and @PageNo * @PageSize -1
order by x.col01
Result>
950021
950022
950023
950024
950025
IO사용량>
Table 'tab01'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
실행계획>
Rows Executes StmtText
5 1 select x.* from ( select top ( @PageNo * @PageSize) col01 , col02 , ROW_NUMBER() over
5 1 |--Filter(WHERE:([Expr1004]>=CONVERT_IMPLICIT(bigint,([@PageNo]-(1))*[@PageSize],0) AND [Expr1004]
25 1 |--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@PageNo]*[@PageSize],0)))
25 1 |--Sequence Project(DEFINE:([Expr1004]=row_number))
25 1 |--Segment
25 1 |--Clustered Index Seek(OBJECT:([master].[dbo].[tab01].[idx_tab01_col01]),
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[의견]
1. clustered 인덱스로 페이징 처리를 할 경우, row_number를 쓰는 방식에 비하여 읽는 page 수는 동일하나 로직이
단순화되어지면서 order by 로직을 사용하는 것이 조금이지만 효율적인 것으로 보입니다.
(단계가 축소되어짐을 실행계획을 보면 알 수 있음.)
2. nonclustered 인덱스로 페이징 처리를 할 경우, row_number 함수를 사용할 경우 서브쿼리에서는 Key값만 가지고
온 다음에, 최종적으로 화면에 보여줄 글에 대해서만 데이터페이지에 접근할 수 있기 때문에 이전 방식이 좀 더
효율적일 것으로 예상되니 활용시 참고하시면 좋을 듯 합니다.
* Reference
1. http://sqlserverpedia.com/blog/sql-server-bloggers/order-by-in-denali/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29&utm_content=Google+Reader
2. http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx?appId=Dev10IDEF1&l=EN-US&k=k(SQL11.PORTAL.F1)&rd=true