SQL Server 2012 (Denali) 신규 기능 Sequence 소개
오라클로 DB를 입문했던 저로써는 매우 반가운 소식이였습니다. 그래서 가볍게 테스트 해 본 결과 공유드립니다.
1. 기본 문법
CREATE SEQUENCE [schema_name . ] sequence_name
[ <sequence_property_assignment> [ ,…®|n ] ]
[ ; ]
<sequence_property_assignment>::=
{
[ AS { built_in_integer_type | user-defined_integer_type } ]
| START WITH <constant>
| INCREMENT BY <constant>
| { MINVALUE <constant> | NO MINVALUE }
| { MAXVALUE <constant> | NO MAXVALUE }
| { CYCLE | NO CYCLE }
| { CACHE [<constant> ] | NO CACHE }
}
NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name
[ OVER (<over_order_by_clause>) ]
2. 시퀀스 객체를 활용한 채번 방식/옵션 변경에 따른 채번 성능 테스트 (참고. 개인 PC에서 테스트 진행함)
2-1. NOCACHE 에서 채번 테스트
CREATE SEQUENCE SeqNo_nocache
START WITH 1
INCREMENT BY 1
no Cache;
GO
declare
@seqval int = 0
, @Cnt int = 0;
select getdate();
while (@Cnt <= 1000000)
begin
SELECT @seqval = NEXT VALUE FOR SeqNo_nocache;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> 약 21초
2010-12-17 20:28:32.870
2010-12-17 20:28:53.127
2-2. CACHE 10/50/100 상태에서 채번 테스트
CREATE SEQUENCE SeqNo_Cache10
START WITH 1
INCREMENT BY 1
Cache 10;
GO
SELECT * FROM sys.sequences WHERE name = 'SeqNo_Cache10'
declare
@seqval int = 0
, @Cnt int = 0;
select getdate();
while (@Cnt <= 1000000)
begin
SELECT @seqval = NEXT VALUE FOR SeqNo_Cache100;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> Cache 10 : 약9초
2010-12-17 20:31:40.393
2010-12-17 20:31:49.953
Result> Cache 50 : 약9초
2010-12-17 20:32:41.960
2010-12-17 20:32:50.643
Result> cache 100 : 약9초
2010-12-17 20:34:24.297
2010-12-17 20:34:33.090
2-3. sp_sequence_get_range 사용하여 채번 (하나씩)
declare
@seqval int = 0
, @Cnt int = 0
, @range_first_value sql_variant
, @range_first_value_output sql_variant;
select getdate();
while (@Cnt <= 1000000)
begin
EXEC sp_sequence_get_range
@sequence_name = N'seqno_cache10'
, @range_size = 1
, @range_first_value = @range_first_value_output OUTPUT ;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> 54초
2010-12-17 20:38:00.330
2010-12-17 20:38:54.593
2-4. sp_sequence_get_range 사용하여 채번 (필요한 범위를 한방에~ )
declare
@seqval int = 0
, @Cnt int = 0
, @range_first_value sql_variant
, @range_first_value_output sql_variant
, @range_last_value_output sql_variant;
select getdate();
EXEC sp_sequence_get_range
@sequence_name = N'seqno_cache10'
, @range_size = 1000000
, @range_first_value = @range_first_value_output OUTPUT
, @range_last_value = @range_last_value_output output;
select @range_first_value_output, @range_last_value_output
select GETDATE();
Result> 0초
2010-12-17 20:41:02.427
2010-12-17 20:41:02.427
3. 하나의 쿼리에서 동일한 시퀀스를 여러번 호출하면 어떻게 될까?
select next value for SeqNo_Cache10, next value for SeqNo_Cache10
Result> 동일한 값이 나온다.
5000151 5000151
4. 시퀀스에 over 구문은 왜 있을까?
4-1. over 절 사용 안하는 경우
select
x.name
, next value for SeqNo_Cache10 as seqNo
from (select top 100 name from sys.columns) x
Result> 이름이 정렬되어지지 않은 상태로 채번되어진다.
4-2. over절 사용하는 경우 (update, merge 구문과 같이 사용 불가함)
select
x.name
, next value for SeqNo_Cache10 over (order by name) as seqNo
from (select top 100 name from sys.columns) x
Result> 이름 정렬순으로 낮은 순서로 채번된다.
5. 시퀀스 번호 재시작하기
ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;
6. 테스트하고 느낀 점
> 캐싱 개수에 상관없이 절대량 처리 속도는 거의 비슷하다. 동시성만 고려하여 캐싱 수치를 적절히 잡으면 될 것 같다.
> 캐싱을 사용할 경우 DB서버를 재시작하면 캐싱된 번호가 사라져버릴꺼라고 생각했는데, 유지되어진다. (좋다~ ^^)
> 대량 범위를 확보하고 싶은 경우 프로시져를 반드시 쓰기를 추천합니다.
> Identity와 Sequence를 동시에 사용할 수 있다니, 기대만빵입니다. 어느 것을 사용할지는 사용자의 선택~
7. Itzik Ben-Gan Blog에 Sequence 에 대한 퀴즈도 있으니 관심 있으신 분은 참고하세요~
*. 참고 자료
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/75978/TSQL-Challenge-with-Denali-s-Sequence-Object.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/2-of-my-favorite-new-t-sql-features-in-sql-server-denali-sql-11/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29&utm_content=Google+Reader
블로그의 "일간 방문자수 보여주기" 구현 사례
이 글을 작성한 목적은 일정 기간동안 컬럼의 값을 증가시키다가 일정기간마다 초기화가 필요한 경우에 활용하면 좋을 듯 하여
블로그 일간 방문자수 보여주기 기능을 사례로 들어서 테스트해 본 것입니다.
원활한 테스트를 위해서 “일간 방문자수 보여주기” 기능에 대한 정의는 아래와 같이 단순화하겠습니다.
1. 임의의 사용자가 블로그에 방문하면 일간 방문자수를 +1 증감시켜준다.
2. 날짜가 변경되면 일간 방문자수를 0으로 초기화되며, 새로 방문자가 올 경우 1로 보여준다.
3. 이전날짜의 방문자수를 따로 보여주지 않는다.
제가 생각해 본 이 방식의 장점은 아래와 같습니다.
1. 매일 0시에 대량의 업데이트를 할 필요가 없다.
2. 접근이 없는 블로그에 대해서는 불필요한 업데이트를 하지 않아도 된다.
3. 웹서비스의 경우 불특정 다수를 상대로 서비스하기 때문에, 사용자가 많아짐에 따라서 순간적인 병목이 발생할 수 있는데요.
필요한 경우에만 데이터를 업데이트 함으로써 부하를 분산할 수 있습니다.
그럼 이제 테스트를 진행하도록 하겠습니다.
use tempdb
go
-- @@ 예제 테이블 생성 및 데이터 추가
drop table BlogMainTable
create table BlogMainTable (
blogId varchar(50) -- 블로그이름
, VisitorCount int -- 오늘 블로그에 방문한 방문자수
, lastModified datetime -- 블로그 최종데이터 변경일자
)
insert into BlogMainTable values ( 'Blog_01', 5, getdate() - 1 ) -- 어제 50명이 방문하고 오늘 방문자가 없는 블로그
insert into BlogMainTable values ( 'Blog_02', 10, getdate()) -- 오늘 10명이 방문한 블로그
-- @@ 어제 방문자가 5명이였고, 오늘 방문자가 없는 블로그 조회 쿼리
select
blogId
, VisitorCount as VisitorCount_real
, case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 최종변경일시가 어제라면 1을 보여줌
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount -- 최종변경일시가 오늘이면 +1된 건수를 보여줌
else 0
end as VisitorCount_view
, lastModified
from BlogMainTable
where blogId = 'Blog_01'
-- @@ 오늘 10명이 방문한 블로그
select
blogId
, VisitorCount as VisitorCount_real
, case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 최종변경일시가 어제라면 1을 보여줌
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount -- 최종변경일시가 오늘이면 +1된 건수를 보여줌
else 0
end as VisitorCount_view
, lastModified
from BlogMainTable
where blogId = 'Blog_02'
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- @@ 실제 적용시에는 업데이트와 조회를 한방에 처리하면 좀 더 비용을 절감할 수 있습니다.
-- @@ 업데이트와 조회를 한번에 처리하는 예제는 아래와 같습니다.
declare
@blogId varchar(50)
, @VisitorCount_before int = 0
, @VisitorCount_view int = 0
, @lastModified datetime
-- 현재 테이블에 저장된 데이터 확인
select * from BlogMainTable where blogId = 'Blog_01'
-- 데이터 변경하기
update bm
set
@blogId = blogId
, @VisitorCount_view = VisitorCount = case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 지난달의 기록이면 노출
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount + 1 -- 이번달의 Sum이면 합계금액 노출
else 0
end
, @lastModified = lastModified = getdate()
from BlogMainTable as bm
where blogId = 'Blog_01'
-- 업데이트된 결과 확인(Blog_01의 경우 어제일자이므로 1로 변경됨)
select @blogId as blogId , @VisitorCount_view as VisitorCount_view, @lastModified as lastModified
자율적인 트랜잭션 (AUTONOMOUS TRANSACTION, 독립적인 트랜잭션)
저는 늘 답변했듯이 오라클을 사용할 경우 autonomous trasaction 처리가 가능하지만 mssql에서는 제공을
하지 않아서 안된다고 이야기드렸는데, 왠지 그 날따라 찜찜하여 찾아보니 linked server를 사용하는 방법이
있어 공유드립니다.
[Autonomous Trasaction이란?]
* 블록에서 자신의 작업을 수행하기 위해서 그 블록 고유의 트랜잭션을 생성하는 경우에 해당하며 그 블록의
트랜잭션의 결과가 자신을 포함하거나 호출한 트랜잭션에의 상태에 의해 영향을 받지 않는 속성을 가지는
트랜잭션을 의미한다.
* 마스터 트랜잭션과는 lock, resource, commit 에 관련된 의존성을 가지지 않는다.
[SQL Server]
-- 1. 연결된서버추가
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback, N'remote proc transaction promotion','false'
-- SQL Server 2008에서 추가된 옵션(분산트랜잭션사용안함)
go
EXEC sp_serveroption loopback, N'RPC OUT', 'TRUE' -- Enable RPC to the given server.
go
-- 2. 임시테이블생성
use tempdb
go
CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))
CREATE TABLE TestAT (id INT PRIMARY KEY)
GO
-- 3. 프로시져생성
CREATE PROCEDURE usp_ErrorLogging
@errNumber INT
AS
INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')
GO
-- 4. 테스트하기(TestAT에 동일한PK를 입력하여 오류를 발생시킴)
DECLARE @ERROR AS INT
INSERT INTO TestAT VALUES (1)
BEGIN TRAN OuterTran
INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error
SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
BEGIN TRAN InnerTran
EXEC loopback.dbTest.dbo.usp_ErrorLogging @ERROR -- 여기서 연결된서버를 활용함
COMMIT TRAN InnerTran
ROLLBACK TRAN OuterTran
END
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTran
GO
Result>
(1개 행이 영향을 받음)
메시지 2627, 수준 14, 상태 1, 줄 6
PRIMARY KEY 제약 조건 'PK__TestAT__3213E83F173876EA'을(를) 위반했습니다. 개체 'dbo.TestAT'에 중복 키를 삽입할 수 없습니다.
문이 종료되었습니다.
(1개 행이 영향을 받음)
-- 5. 로그가 쌓였는지 체크
SELECT * FROM TestAT
SELECT * FROM ErrorLogging
Result>
[Oracle] (간단히 문법만 공유드립니다. ^^)
declare
pragma autonomous_transaction;
begin
commit; -- 자율적인 트랜잭션은 반드시 commit/rollback으로 종료해야 한다.
end;
[출처] (테이블 변수를 활용한 방법 정리되어 있으니 관심 있는 분은 원문 참고 부탁드립니다.)
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx
DB 서버 이전 시 체크리스트 (SQL Server 기준)
DB서버를 신규 장비로 교체하거나 2대를 1대로 합치는 작업을 종종하는데요, 자주하는 작업이 아니라서, 조금씩 놓치는 부분이 발생하게 됩니다.
팀에서 DB서버 이전시 챙겨야 하는 작업에 대해서 함께 정리해 보았습니다.
구분 | 항목 | 체크 |
사전 작업 | SQLIOSim 테스트를 통하여 신규 장비에 물리적인 결함이 없는지 확인(신규장비로 이전시) | |
작업 대상서버에서 이전 작업시 수행되는 배치나 백업 작업이 있는지 확인(부하 / 잠금 등) | ||
데이터를 이전 받는 서버의 디스크 공간은 충분한지 확인 필요 (백업본 보관도 고려 필요) | ||
통계 시스템 등 해당서버에서 데이터를 가지고 가는 부분이 있다면 해당 담당자에게 공유 | ||
이전 준비 작업 (이전해야 할 내용 확인/이관, 신규 장비일 경우 추가 설정 내용) |
DTS 또는 SSIS 작업 이관 | |
hosts 파일에 등록된 내용 확인 및 이관 | ||
서버/클라이언트 네트워크 (alias 등록하여 사용중인 내용) 내용 확인 및 이관 | ||
연결된 서버 설정 확인 및 이관 | ||
DB 계정 설정 확인 및 이관 | ||
윈도우 예약작업 확인 및 이관 | ||
MSDTC 설정 (분산 트랜잭션 사용중인 경우) 확인 및 이관 | ||
execute 로 실행되는 동적쿼리가 있을 경우 그에 대한 실행권한 확인 | ||
IPSec 확인 및 이관 | ||
64bit 장비의 경우 플랜캐시가 커지는 현상이 있어 adhoc 지우는 Job 추가 | ||
서비스팩 현재 서비스중인 서버까지 올려놓을 것 | ||
Default 정렬셋이 동일한지 확인 (사용자 DB만 이관할 경우 문제가 되어질 수 있음.) | ||
임의쿼리에 대한 변경을 할 경우 확인 및 변경 (확인 방법 : 프로필러 + 메모리 내의 Ad-Hoc 플랜캐시영역 확인) |
||
이전 작업 (작업 당일 진행 순서) |
(현업 또는 관련팀) 서비스 점검 상태 확인 | |
(복제가 있었다면) 복제 정지 / 삭제 | ||
예약된 작업 정지 (이 서버 및 이 서버와 연결된 타 서버) | ||
(현업 또는 관련팀) DB에 더 이상 데이터 변경이 없을 때까지 대기, sp_who2 등으로 접속자 확인 |
||
풀 백업 후 장비간 복사 | ||
(시스템담당자) (기존 장비 대체인 경우) 호스트 이름 및 IP 변경 요청 | ||
리스토어 및 DB 액세스 계정 등 확인 & 설정 | ||
(복제가 있었다면) 재구성 | ||
SQL Agent 실행여부 확인, 설정 점검('자동 실행') 및 예약된 작업 시작 | ||
접속 테스트, Application 테스트 진행 | ||
(현업 또는 관련팀) 서비스 오픈 확인 | ||
작업 후 체크 | 모니터링 설정하기 (개별적으로 모니터링 툴이 있는 경우) | |
DB 및 트랜잭션 백업 설정 확인 | ||
OS 예약 작업은 이전되었으며 동작하고 있는가? | ||
DB 모드 (특히 로그) 및 호환성 모드 등의 설정은 적절한가? 로그 파일 크기는 적절한가? | ||
2차 백업 구성 확인: 백업 폴더가 있는 드라이브 체크, 백업용 공유 폴더 설정 체크, | ||
IP / 호스트명이 변경되었다면 백업 서버 설정도 확인 후 변경이 필요함 | ||
작업 후 모니터링 | 성능상의 이슈가 없는지 체크(profiler로 duration, cpu, pagelookup으로 모니터링) | |
perfmon로 주요지표에 대해서 실시간으로 모니터링 (pagelookup, cpu, batchrequest, lock, latch, disk 사용량 등) |
||
시스템 관련 작업 | 64bit 장비의 경우 원격에서 파일 복사시 메모리 뺏김 현상이 있으니 이 부분에 대한 확인 (동적 캐시 관리 서비스 설치, 또는 2차 백업을 끌어가기가 아닌 밀어넣기로 변경) |
Include Client Statistics 안내
EM 툴의 클라이언트 통계 기능 소개 드립니다.
DMV나 프로필러로는 서버의 성능 부하를 확인할 수 있습니다. 네트워크 비용이나 클라이언트에서 데이터 요청 후,
결과를 받는데까지 어디서 얼만큼의 비용이 발생하였는지 확인하기 위해서 해당 기능을 활용하면 좋을 듯 합니다.
개별 실행결과를 바로 비교하여 보여주기 때문에 매우 유용하게 활용할 수 있을 것 같습니다. 메뉴는 Query 메뉴 하단에
존재하고, 버튼은 아래의 이미지에 빨강색 동그라미 친 버튼입니다.
다음에는 진짜 SQL Server 2011의 새로 나온 Contained Databases에 대해서 소개하도록 하겠습니다.
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