1 만든날: 2007-07-07

2 소스

3 실행결과

문득 페이징쿼리에 대한 여러가지 기법들이 머리속에 슉~ 지나갔다. not in이나 order by 를 이용하는 방법, 또는 페이지의 첫번째 Row를 찾는 방법등이 생각한다. 어찌되었건 페이지번호가 증가할 수록 느려지는 페이징 쿼리를 개선할 방법이 없을까 생각했다. 그래서 sql server 2005의 cte를 이요해보기로 했다. cte의 재귀쿼리 성능이 좋지 않음을 알고 있으나, 그정도는 참아줄만하므로 쓰기로 했다. 계층형이니까...

기존의 게시판 페이징 구현방법과는 다르게 페이지번호가 늘어나도 쿼리의 성능은 비슷하다. 다만 일반적으로 하나의 게시물에 많아야 10개 이상의 답변이 달리지 않는다는 가정하에서다. 물론 거의 모든 게시물의 리플은 10개 이하일테고..포탈사이트의 경우 이슈가 되는 것은 아마도 수천개의 또는 수만개의 리플이 달릴 것이다..뭐..이정도도 쓸만할꺼 같다...뭐..쓸만하지 않나 싶다...허허..50 만 건 넣고 테스트해보았는데...쓸만하다..어찌되었건 확실히 테스트가 더 필요하기는 하다..아마도...조낸 포탈이 아니면...대충은 쓸만할끼여...인덱스를 좀 손봐야 겠군..허허..2007년 07넌 10일...아...마지막 페이지 만들어내기가 상당히 빡시구만...허허

*/
--테이블 생성 및 데이터 입력
create database sample
go

use sample
go
--drop table bbs
create table bbs(
	seq int identity(1,1) primary key nonclustered
,	question_num int 
,	parent_num int
,	cust_id varchar(20)
,	title varchar(255)
,	contents varchar(7000)
);

create clustered index cix_question_num on bbs(question_num);
--create index nix_parent_num on bbs(parent_num);
--create index nix_seq on bbs(seq);
go

insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 0, 'yasicom', '질문', 'sql이 머에요?');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 1, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 3, 'yasicom', '답변', '답변입니다.');

insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 0, 'yasicom', '질문', 'sql이 머에요?');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 2, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(2, 1, 'yasicom', '답변', '답변입니다.');

insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 5, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(1, 4, 'yasicom', '답변', '답변입니다.');

set nocount on 
set statistics io off
declare @i int
set @i = 3

begin tran
while(@i <= 500000)
begin
	insert bbs(question_num, parent_num, cust_id, title, contents) 
	values(@i, 0, 'yasicom', '질문', 'sql이 머에요?');

	set @i = @i + 1
end
commit
go


insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 499989, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 499989, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500012, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500011, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(499980, 500014, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 400020, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500016, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500017, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500018, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500019, 'yasicom', '답변', '답변입니다.');
insert bbs(question_num, parent_num, cust_id, title, contents) values(400010, 500017, 'yasicom', '답변', '답변입니다.');
go

create proc usp_select_bbs
	@page_num int
,	@page_row_cnt int
as
--set statistics io on
--declare 
--	@page_num int
--,	@page_row_cnt int
--,	@max_question_num int;
--
--set @page_num = 45;
--set @page_row_cnt = 10;
--set @max_question_num = (select max(question_num) from bbs);

declare 
	@max_question_num int
,	@begin int
,	@end int
,	@end_ck int
,	@cnt int

set @max_question_num = (select max(question_num) from bbs);
set @begin = @max_question_num - ((@page_num) * @page_row_cnt);
set @end =	@max_question_num - ((@page_num - 1) * @page_row_cnt);
set @end_ck = case when @end > 0 then 0 else 1 end;
set @begin = case when @end > 0 then @begin else 1 end;
set @end = case when @end > 0 then @end else @page_row_cnt end;

if @end_ck = 0 --마지막페이지가 아니면...
begin
	with t_recursive(seq, question_num, parent_num, cust_id, title, contents, level, sort)
	as
	(
		--Root.. 
		select seq, question_num, parent_num, cust_id, title, contents, 0 as level, cast(seq as varchar(8000)) sort
		from bbs
		where parent_num = 0 --root의 조건
		and question_num between @begin and @end

		union all

		select a.seq, a.question_num, a.parent_num, a.cust_id, a.title, 
			a.contents, b.level + 1 as level, cast(b.sort as varchar(4000)) + cast(a.seq as varchar(4000))
		from bbs a inner join t_recursive b --재귀쿼리
		on a.parent_num = b.seq
		and a.question_num between @begin and @end
	)
	select top(@page_row_cnt)
		seq
	,	question_num
	,	cust_id
	,	case when parent_num > 0 then '[' + cast(parent_num as varchar) + ']' else '' end + 
		cast(replicate(' RE: ', level) + cast(title as varchar) as varchar(250)) as tree_title
	,	contents
	from t_recursive a
	order by 
		question_num desc
	,	case when level = 0 then '0' else sort end
	option(maxdop 1);
end else --마지막페이지이면..
begin
	with t_recursive(seq, question_num, parent_num, cust_id, title, contents, level, sort)
	as
	(
		--Root.. 
		select seq, question_num, parent_num, cust_id, title, contents, 0 as level, cast(seq as varchar(8000)) sort
		from bbs
		where parent_num = 0 --root의 조건
		and question_num between @begin and @end

		union all

		select a.seq, a.question_num, a.parent_num, a.cust_id, a.title, 
			a.contents, b.level + 1 as level, cast(b.sort as varchar(4000)) + cast(a.seq as varchar(4000))
		from bbs a inner join t_recursive b --재귀쿼리
		on a.parent_num = b.seq
		and a.question_num between @begin and @end
	)
	select 
		seq
	,	question_num
	,	cust_id
	,	case when parent_num > 0 then '[' + cast(parent_num as varchar) + ']' else '' end + 
		cast(replicate(' RE: ', level) + cast(title as varchar) as varchar(250)) as tree_title
	,	contents
	,	row_number() over(order by question_num desc, case when level = 0 then '0' else sort end) rownum
	into #temp
	from t_recursive a
	order by 
		question_num desc
	,	case when level = 0 then '0' else sort end
	option(maxdop 1);

	set @cnt = @@rowcount;

	select
		seq
	,	question_num
	,	cust_id
	,	tree_title
	,	contents
	from #temp
	where rownum > (@cnt / @page_row_cnt) * @page_row_cnt;
end
go

3번째 page를 10개의 게시물을 보고자 할 경우

테이블 'bbs'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0
테이블 'Worktable'. 검색 수 2, 논리적 읽기 수 77, 물리적 읽기 수 0, 미리 읽기 수 0
테이블 'bbs'. 검색 수 17, 논리적 읽기 수 68, 물리적 읽기 수 0, 미리 읽기 수 0

10000번째 page를 10개의 게시물을 보고자 할 경우

테이블 'bbs'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0
테이블 'Worktable'. 검색 수 2, 논리적 읽기 수 83, 물리적 읽기 수 0, 미리 읽기 수 0
테이블 'bbs'. 검색 수 18, 논리적 읽기 수 72, 물리적 읽기 수 0, 미리 읽기 수 0

ㅓㅘ -- ㅏ 2020-02-24 16:37:50

훌륭한 로직입니다. 저도 이렇게 짤 수 있게 되면 좋겠습니다.

그런데 이 로직의 경우는 게시글 삭제는 고려되지 않은 걸까요? where question_num begin between end

와 같이 될 경우, 최대 행 10까지 지정해놓고 1~10까지 question_num이 있을 때 9번이 사라지면 1~8번과 10번만 출력되는 것 같습니다. -- ! 2023-06-22 12:58:04