'웹/웹.도움말'에 해당되는 글 21건

터미널서비스 세션유지로 접속 못할 때

웹/웹.도움말

터미널서비스 세션이 유지되고 있어서 서버에 터미널로 접속하지 못할 때     


1. 시작의 실행에서 "\\123.123.123.123\c$" 를 칩니다.
이 때 \\는 네트웍 드라이브를 의미하며, 123.123.123.123 은 서버의 아이피를 입력하시면 됩니다. c$는 C드라이브를 의미 합니다.

2. 로그인 창이 뜨면 관리자계정(administrator)을 입력합니다. 로그인 성공시 C드라이브 폴더가 뜹니다.

3. 이렇게 인증절차가 끝나면 명령프롬프트(실행에서 "cmd")에서 다음과 같이 입력합니다.

"tsdiscon 1 /server:123.123.123.123"
- tsdiscon: Terminal Service DISCONnection 의 도스 명령어
- 1: 터미널 서비스는 최대 2개의 세션값을 가질수 있으며 숫자는 세션 번호를 의미합니다.
- /server: 서버를 지정할때 사용하는 옵션입니다.
- 123.123.123.123: 해당 서버의 IP를 입력합니다.

위와 같은 방법으로 세션 2 값도 실행해 줍니다.

4. "tsdiscon 2 /server:123.123.123.123"
5. 이제 터미널 서비스로 접속을 합니다.

6. 시작=>관리도구=>터미널서비스관리자에서 끊어져 있는 세션이 있나 살펴본뒤 아직 연결된 계정이 있다면 오른쪽 마우스를 클릭하며 연결끊기를 해줍니다.

7. 연결을 끊었을 경우(대부분 도스창에서 명령 입력시 세션이 끊깁니다) 오른쪽 마우스를 클릭하여 원래대로를 클릭해줍니다.

8. 약 2-30초 후에 세션이 재 설정됩니다.


Name
Password
Homepage
Secret

windows 2000 터미널 서비스 원격으로 끊기

웹/웹.도움말

windows 2000에서 터미널 서비스 원격으로 끊기
 
1. 터미널 서비스 원격으로 끊기
- 터미널 서비스를 원격으로 끊기 위해서는 139/tcp(netbios) , 445/tcp(Microsoft-DS SMB 파일 공유) 포트가 열려있어야함

1.1 준비사항 *


- 세션 끊으려는 서버의 IP
- 세션 끊으려는 서버의 id/password
- 윈도우 2000 서버 이상 추천 (윈도우 2000 프로페셔널에는 tsdiscon명령어가 없으나, 자료실 다운받은후 프로에서 작업가능, tsdiscon은 윈도우 2000 서버 이상에만 있는 명령어)


1.2 실행방법 *

- 시작 -> 실행 cmd 창에서 아래를 입력

net use \\211.41.x.x (IP) /user:admin(어드민 권한 사용자아이디) * (*는 암호 입력하겠다는뜻임)
\\211.41.x.x에대한 암호를 입력하십시오 : **** (패스워드 입력)


- cmd창에서 query user /server:211.41.x.x 입력
사용자이름  세션이름  ID  상태  유휴시간  로그온시간 
paran console  0  활성  .  10:10:00
paran rdp-tcp#66 1  활성  .  10:10:00
admin    2  활성  none  10:10:00

- id를 확인후 (콘솔이 0번, 터미널이 1, 2번 사용) 해당 id를 종료
- cmd창에서 tsdiscon 1 /server:211.41.x.x 입력 -> 해당 터미널 접속세션 끊김

* 그냥 tsdiscon 명령어를 입력하면 local 터미널 접속 세션이 종료 됨

2. 실행화면

*세션종료과정
*해당세션이 종료 되었음

Name
Password
Homepage
Secret

MS-SQL 구문 정리

웹/웹.도움말

테이블,칼럼 만들기 : create table 이름(

                          칼럼이름 문자열(숫자열),

                          칼럼이름 문자열(숫자열)

                          )                                          -- 테이블,칼럼은 같이해야 만들어짐


where 구문


where 칼럼='레코드'                                               -- 한칼럼 레코드 분류해서 가져오기

 

테이블 지우기 : drop table 이름


특정칼럼 지우기 : alter table 테이블이름

                      drop column 칼럼이름

                    

칼럼 추가하기 : alter table 테이블이름

                    add 칼럼명 문자열(숫자열)


칼럼  수정하기 : alter table 테이블이름                   -- 칼럼명이 수정 되는게 아니다. 자료형수정              

                    alter column 칼럼명 문자열(숫자)         

                          


레코드 만들기 : insert into 테이블이름(칼럼이름,칼럼이름)      -- into 생략가능

                   values('레코드명','레코드명')


레코드 전체 지우기 : delete 테이블이름       


레코드 추가하기 : insert 테이블이름(상수칼럼) values(들어갈숫자)  -- 레코드 추가할시엔 숫자로만 해야한다.

 

특정 레코드 필드명 수정하기 : update 테이블이름 set 칼럼='수정할레코드명'

                                     where 특정칼럼=특정레코드

 

레코드 삭제하기 : delete from 테이블이름

                         where 칼럼='레코드'  



--------------------------------------------------------------------------------------------------------


update ex set 등급='준회원'       -- 칼럼행 수정하기



create table ex(                      -- 테이블,칼럼 만들기 (같이 실행되어야 함)
번호 int,
이름 char(10),
아이디 char (12),
주소 varchar(20),
학과 char(10),
성적 int,
)


drop table ex                         -- 테이블 지우기

delete from ex                       -- 레코드 전체 지우기

select *  from ex                    -- 테이블 불러오기
order by 성적 desc                  -- 성적 내림차순


select  * from ex                    
where 주소='서울'                   -- 서울에만 사는 레코드 불러오기

 

insert into ex(번호,이름,아이디,주소,학과,성적)    -- into 생략 가능
values('1','홍길동','Hong','서울','Ms','100')


insert into ex(번호,이름,아이디,주소,학과,성적)
values('2','김하나','Hana','대전','Ub','80')


insert into ex(번호,이름,아이디,주소,학과,성적)
values('3','박길동','Kildong','대구','Ms','70')


insert into ex(번호,이름,아이디,주소,학과,성적)
values('4','김두나','Duna','부산','Ub','90')


insert into ex(번호,이름,아이디,주소,학과,성적)
values('5','박세나','sena','서울','Ms','90')


select * from ex
where 학과='Ms'
order by 성적 desc             -- ms반 학생 점수 높은사람 내림차순으로 뽑기


select top 2 with ties * from ex
order by 성적 desc                       -- 상위 2명 뽑기 동점자도 더뽑기


select * from ex
where 이름 like '%김%'             -- 이름 명단에 김씨가 들어간 사람만 뽑기..


alter table ex
add 전화번호 char(20)                -- 칼럼 추가하기


alter table ex
add 비밀번호 int                         -- 칼럼추가하기


update ex set 비밀번호='2222',전화번호='123456789'       -- 레코드 수정하기
where 번호='6'

update ex set 비밀번호='4513',전화번호='987654321'
where 번호='2'


update ex set 비밀번호='1263',전화번호='232355551'
where 번호='3'


update ex set 비밀번호='7777',전화번호='987654321'
where 번호='4'


update ex set 비밀번호='9999',전화번호='123457876'
where 번호='5'


alter table ex
alter column 비밀번호 char(10)        -- 칼럼 공간을 늘려주기

select * from ex


alter table ex                           -- 칼럼 추가하기
add 등급 char(10)


update ex set 등급='준회원'       -- 레코드 수정하기


update ex set 등급='운영자'        -- 레코드 수정하기


where 번호='1'

insert ex(번호) values(7)           -- 레코드 추가하기


sp_help ex             -- 테이블 정보보기


delete from ex
where 번호='7'                     -- 레코드 지정해서 삭제하기


상품정보
  번호  상품명 가격 제조사 분류
    1     청바지 100  동대문 의류       상품명은 15가지   제조사 분류 10가지


create table 상품정보(                 -- 테이블 , 칼럼 만들기
번호 int,
상품명 char(15),
가격 int,
제조사 char(10),  
분류 char(10),
)


drop table 상품정보            -- 칼럼 지우기

select * from 상품정보         -- 테이블 불러오기


insert into 상품정보(번호,상품명,가격,제조사,분류)        -- 레코드 만들기
values('1','청바지','1000','동대문','의류')

insert into 상품정보(번호,상품명,가격,제조사,분류)
values('2','티셔츠','2500','나이키','의류')

insert into 상품정보(번호,상품명,가격,제조사,분류)
values('3','가방','3000','시장','잡화')

alter table 상품정보
add 상품코드 char(5)


update 상품정보 set  상품코드='a1'      -- 레코드 수정하기
where 번호='1'


update 상품정보 set  상품코드='b1'
where 번호='2'


update 상품정보 set  상품코드='c1'
where 번호='3'


delete from 상품정보             -- 레코드 삭제하기
where 번호='3'                             


select * from 상품정보
order by 가격 desc                    -- 가격내림차순

 

select *from 상품정보
where 분류='의류'                     -- 의류만 나오기


select *from 상품정보
where 상품코드 like '%a%'         -- a만 들어간 상품코드 출력해주기


select * from 상품정보
order by 가격 desc                 -- 상품중 가장 비싼것


insert into 상품정보(번호,상품명,가격,제조사,분류,상품코드)        -- 레코드 추가하기
values('3','청바지','5000','명품','의류','a2')


select distinct 상품명 from 상품정보   -- 중복 빼고 출력해주기

Name
Password
Homepage
Secret

효율적인 페이징 기법

웹/웹.도움말

안녕하세요. 마리입니다. ^^;

 

이 팁은 ADO와 SQL에 관한 팁입니다. 많은 페이징 쿼리가 있지만....

 

성능때문에 말들이 참 많았죠? 이 팁 또한 그러한 말들 중에 대안을 내 놓은 그런 팁인듯 합니다.

 

참고 많이 하시구요. 어려우신 부분은 질문을 해주세요. 맨 아래쪽 SP가 진국인데...

 

초보분들이 보시기에는 복잡한 듯 합니다.

 

 

SQL Server에서 사용할 수 있는 서버측 페이징기법

Andrew Rosca

웹 애플리케이션은 일반적으로 사용자에게 많은 양의 정보를 제공하기 위해 페이징 기법을 사용한다.
예를 들어 인터넷 검색엔진은 사용자의 쿼리 결과로 대용량의 결과값을 반환한다.
이 때 검색엔진이 한번에 결과값 전체를 반환하게 되면 결과값을 받는 클라이언트측 시스템에 과부하가 발생할 수 있다.
하지만 페이징 기법을 사용하게 되면, 반환되는 결과값을 클라이언트측과 서버측 양쪽에서 관리할 수 있을 만큼의 고정된 크기의 블록으로 구분하여 한 번에 이동시키는 정보의 양을 줄일 수 있다. 애플리케이션에서는 한 번 에 소수의 레코드만 사용자에게 보내게 되며, 결과값 중에서 사용자가 필요로 하는 정보만 반환하게 된다.

페이징 기법을 사용하면 데이터를 사용자가 좀 더 이해하고 표현하기 쉽게 해 줄 뿐만 아니라, 대량의 정보를 조회하고 표현하기 위해서 시스템에 불필요한 과부하가 발생하여 결국 시스템의 성능에 악영향을 미치지 않도록 통제하기 때문에 전체적인 시스템 성능을 향상시키게 된다.
정상적으로 시스템에 반환된 결과값 레코드가 페이징되었다면, 검색엔진을 사용하는 사용자는 대부분 맨 처음 한 페이지 또는 일부 페이지만 조회하게 될 것이다.

불행하게도 많은 프로그래머들이 페이징 관련해서 성능측면에서 매우 중요한 고려사항에 대해서 잘 모르고 있다.
IIS와 SQL Server를 사용하는 환경에서는,
AbsolutePage, PageSize, PageCount와 같은,
표준 ADO RecordSet 페이징 기능을 사용하는 것이 가장 일반적인 페이징 기법이다.
소량(수십 또는 수백 레코드 정도)의 데이터에 대해서는 이러한 기능을 사용하면 정상적으로 동작하고, 시스템의 성능측면에서도 부하를 발생시키지 않는다. 하지만 레코드 수가 증가하게 되면, 이러한 기능을 사용하게 되면 효율성이 감소하게 되고 전체적인 애플리케이션의 성능에 악영향을 미치게 된다.

대량의 발주정보를 조회해야 하는 구매조달관련 애플리케이션, 수 천명의 회원이 동시에 접속하는 미팅 웹사이트, 고객의 검색조건에 따라 수백 개의 상품의 정보를 표시해야 하는 대규모 전자상거래 웹사이트와 같이, 대용량 데이터를 처리해야 하는 애플리케이션의 경우에는 좀 더 개선된 서버측 페이징 기술이 필요하게 된다. 이번 호의 기사에서는 수백만 개의 행정보를 포함하는 테이블에서도 사용할 수 있는 페이징 기법에 대해서 소개하고자 한다.


ADO RecordSet 페이징 기법의 한계


대용량의 레코드를 페이징하기 위해서 ADO RecordSet의 페이징기법을 사용할 때 발생하는 문제의 원인은 ADO에서 데이터를 처리하는 방법 때문이다.
ADO 기술구조에서는 데이터베이스에서 정보를 조회하기 위해 서 조회의 대상이 되는 데이터에 대한 포인터를 관리해야 할 필요가 있다.
데이터에 대한 포인터를 커서 라고 하며, 클라이언트측(예를 들어 ASP 페이지)에서는 각 레코드를 건별로 조회하게 된다.

ADO RecordSet 개체는 서버측 커서(기본값)와 클라이언트측 커서 유형을 지원한다.
서버측 커서를 사용하게 되면 모든 데이터는 그대로 SQL Server에 두고, 해당 데이터가 필요한 시점에 순서에 따라 각 레코드를 조회하게 된다.
클라이언트측 커서를 사용하게 되면 필요한 모든 데이터를 클라이인트로 전송한 다음, 클라이언트측 커서를 사용하여 클라이언트측 버퍼 메모리에 있는 데이터를 레코드별로 조회하게 된다.
검색엔진 예제에서처럼 쿼리의 결과값 중에서 일부분만을 표시하거나 사용해야 하는 경우라면 SQL 서버가 클라이언트에서 요청하는 페이지만 전송하고 전체 결과값 중 나머지 레코드는 데이터베이스 서버에 그대로 남겨두게 되는, 서버측 커서를 사용하는 것이 효율적이다.
서버측 커서를 사용하게 되면 클라이언트로 전송되는 레코드 수가 특정 페이지를 구성하는 20~30 레코드 정도로 제한된다는 것이다.

PageCount 와 같은 일부 레코드셋 페이징 기능을 사용하기 위해서는 클라이언트측 커서를 사용해야 한다.
클라이언트 커서를 사용할 수 있도록 ADO를 설정하기 위해서는 RecordSet의 ClientLocation 속성을 adUseServer에서 adUseClient로 변경해 주면 된다.
[리스트 1]의 VB 코드는 RecordSet 개체에서 클라이언트측 커서와 서버측 커서를 사용하는 방법에 대한 예제가 나타나 있다. ClientLocation 속성을 asUseClient 로 변경하게 되면, 사용자 쿼리의 결과로 반환되는 데이터에서 필요로 하는 페이지 수를 판단하기 위해 결과값 전체가 클라이언트로 전송된다.

예를 들어 데이터베이스로부터 5000 레코드를 반환하는 쿼리를 실행했다고 가정하자.
애플리케이션에서 서버측 커서를 사용하게 되면 반환되는 레코드를 한 페이지당 20 레코드씩으로 페이징하고, 사용자가 1 페이지만 보고 있는 경우라면 애플리케이션에서는 클라이언트로 맨 처음 페이지를 구성하는 20 레코드만을 전송하면 된다.
그 다음 사용자가 두번째 페이지로 이동하면 애플리케이션에서는 21~40번 레코드만 클라이언트로 전송하게 된다. 반면에 클라이언트측 커서를 사용하게 되면 ADO에서는 비록 사용자가 단지 첫 페이지에 해당하는 20 레코드만 필요한 경우라도 5000 레코드 전체를 클라이언트로 전송하게 된다.
이렇게 전체 레코드를 전송하게 되면 결과값이 사용자에게 나타나는 시간이 지연되게 되고, 반환되는 레코드 수가 매우 많은 경우에는 성능에 심각한 악영향을 미칠 수 있다.

다른 페이징 기법

ADO RecordSet 페이징 기법과 관련한 문제가 애플리케이션의 성능에 영향을 미치게 되었기 때문에 필자는 수천 레코드를 페이징해야 하는 웹 애플리케이션에서 사용할 수 있는 다른 페이징 기법을 찾아 보았다.

SQL 서버의 인덱스를 활용하여 전체 결과집합 중에서 상위의 레코드를 선택하는 방법을 소개하고자 한다.

다음은 Northwind 데이터베이스의 Orders 테이블에서 상위 10개의 레코드를 선택하는 쿼리이다.

SELECT TOP 10 * FROM Orders

위의 구문을 활용하면 전체 결과집합에서 10 개의 레코드 단위로 결과값이 반환되게 할 수 있다.
주어진 페이지에 해당하는 레코드만 선택하기 위해서는, 한 페이지에 몇 개의 레코드를 포함시킬 것인지 결정하고, 실제 사용자가 몇 번째 페이지의 정보를 조회하기를 원하는지에 대한 페이지 카운트를 알고 있어야 한다.
예를 들어 한 페이지에 10 개의 레코드가 포함되고, 사용자가 전체 결과값 집합 중에서 3 페이지를 조회하고자 하는 경우라면 다음과 같은 쿼리를 사용하면 된다.

SELECT TOP 10 * FROM Orders WHERE OrderID NOT IN (SELECT TOP 20 OrderID FROM Orders)

위의 쿼리는 맨 처음 20 개의 레코드 이후에 존재하는 10개의 레코드, 즉 21~30번까지의 레코드를 반환한다. 위의 쿼리를 절차코드로 일반화하게 되면 다음과 같이 표현할 수 있다.

SELECT TOP page_size * FROM Orders WHERE OrderID NOT IN (SELECT TOP (page_size * (current_page - 1)) OrderID FROM Orders)

위의 쿼리는 대량의 레코드를 반환하는 경우라도 잘 동작하지만, 반환할 페이지의 숫자가 많아질수록 전체적인 성능은 감소하게 된다.
문제의 원인은 IN 연산자에 포함되는 쿼리의 결과값이 많아지면서 비효율성이 증가하기 때문이다. 예를 들어 한 페이지당 10개의 레코드를 반환하는 결과집합 중에서 500번째 페이지를 조회하기 위해 쿼리를 실행하게 되면, IN 연산자의 대상이 되는 서브쿼리에는 다음과 같은 문장이 포함되게 된다.

(SELECT TOP 4990 OrderID FROM Orders)

마지막 10개의 레코드를 조회하기 위해 서버에서는 4990 개의 OrderID와 각 OrderID를 비교해야만 한다.
불필요한 비교작업이 많이 발생하게 된다.
물론 SQL Server의 경우에는 이러한 경우 데이터를 좀 더 빠르고 효율적인 방법으로 검색하기 위해서 인덱스를 사용하여 빠르게 쿼리를 처리하게 된다.
(OrderID가 기본키로 설정되어 있기 때문에, SQL Server는 기본적으로 인덱스를 사용하게 된다.)
조회하고자 하는 페이지 수가 증가함에 따라 성능면에서 느려지게 된다고 하더라도 이러한 조회성능의 감소현상은 조회하고자 하는 페이지 수가 매우 큰 경우에만 인식할 수 있게 된다.
이러한 점증적인 성능의 감소현상은 대부분의 경우 사용자가 맨 처음부분의 일부 페이지만 조회하게 되고, 맨 처음부분의 일부 페이지를 조회 할 경우에는 매우 빠른 성능을 보장할 수 있기 때문에 크게 중요한 관심의 대상이 되지 않는다.
애플리케이션이션에서 적절하게 데이터를 정렬하고 필터링한다면 사용자는 찾고자 하는 정보를 거의 한 두 페이지 이내에서 찾게 된다. 만약 맨 처음 부분의 페이지에서 필요로 하는 데이터를 찾지 못한 경우에는 대부분의 사용자는 예제에서처럼 500 페이지까지 원하는 데이터를 찾고자 계속 다음 페이지를 찾아보는 것이 아니라 다른 정렬 및 필터링 조건으로 새로운 쿼리를 하게 된다.

앞에서 언급한 것과 같이 검색작업을 수행할 때, 테이블의 기본키가 어떤 컬럼에 설정되었는지가 매우 중요한 역할을 하게 된다.
기본키에는 각 레코드를 유일하게 식별할 수 있는 컬럼이 포함되어야 하며, 쿼리를 실행할 때 기본키를 기준으로 해당 레코드를 쿼리의 결과값으로 선택할 것인지 무시할 것인지를 판단하게 된다.
앞의 일반화된 쿼리에는 정렬 및 기본키에 관련한 WHEHE 절이나 ORDER BY 절 내용이 누락되어 있다. 목록 2에는 이러한 요소를 포함시킨 일반화된 쿼리가 나타나 있다.

정렬은 일반적으로는 비효율적인 작업이며, 쿼리의 성능을 저하시키는 원인이 되기도 한다.
SQL 서버의 경우, 인덱스가 설정된 컬럼에 대해서는 매우 효율적으로 정렬 및 필터링 작업을 할 수 있고, SQL 서버가 항상 기본키 컬럼에는 인덱스를 생성하기 때문에 쿼리를 좀 더 최적화하여 빠르게 실행한다.
기본키에 설정된 인덱스를 최대한 활용하기 위해서, [리스트 3]과 같이 테이블의 전체 컬럼을 선택하지 않고, 먼저 기본키의 조건으로 대상이 되는 레코드를 검색한 다음에 결과값에 포함되어 있는 기본키값으로 다시 해당 레코드에 대한 전체 컬럼을 찾아오게 할 수도 있다.
테이블에 기본키 인덱스가 설정되어 있고, 인덱스가 설정된 필드에 대해서만 정렬 및 필터링 작업을 하게 되면, 조회하고자 하는 레코드를 찾기 위해 인덱스 페이지만을 사용하게 된다. 이렇게 쿼리의 결과값에 필요한 전체 필드가 인덱스에 포함되어 있는 경우를 커버된 인덱스라고 한다.
동일한 쿼리를 실행시킨 경우라도 해당 쿼리가 커버된 인덱스를 사용하는 경우가 일반 테이블에 대해서 쿼리하는 경우보다 더 빠르게 된다. 맨 마지막으로 선택된 결과값에 해당하는 나머지 정보를 조회하기 위한 작업을 수행할 때에는(즉, SELECT * 부분), 기본키 인덱스를 사용하여 SQL Server가 해당 인덱스를 바로 찾을 수 있기 때문에 매우 효율적으로 쿼리를 수행하게 된다.

[리스트3]에 나타나 있는 쿼리는 결과집합 중에서 특정 페이지를 매우 효율적이고, 단순한 방법으로 조회하게 된다. 물론, GROUP BY나 HAVING 절을 추가하여 사용할 수도 있다.
쿼리를 좀 더 단순화하기 위해서 [리스트 4]와 같이 SELECT_WITH_PAGING 라는 저장 프로시저를 생성하여, 기능을 캡슐화하였다.

SELECT_WITH_PAGING 저장프로시저에는 fields_to_return (string), primary_key (string), table_name (string), page_number (integer, default 1), page_size (integer), get_record_count (true/false), filter_conditions (string), sort_columns (string), group_by (string)와 같은 매개변수(데이터형)가 입력되게 된다.

예를 들어 한 페이지당 레코드 수를 10개로 지정하고 Northwind 데이터베이스의 Order 테이블으로부터 주문일자로 정렬하여 CustomerID, ShipName을 조회한 다음, 결과값 중 세번째 페이지를 조회하기 위해서는 다음 문장을 실행하면 된다.

EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'

위의 쿼리를 실행하게 되면 입력된 조건에 따라 필터링되어 반환되는 전체 레코드 수가 두번째 레코드셋으로 반환된다. 전체 레코드 수는 사용자에게 전체 페이지 수를 표시하려고 할 때 유용하게 사용되며, 대부분의 경우 사용자는 단순하게 이전페이지 또는 다음페이지로 표시되는 것보다는 전체 페이지 수 중에서 현재 조회하고 있는 페이지의 번호를 표시하는 방법을 더 선호한다.
조건에 해당하는 전체 레코드 수를 조회하기 위해서 필자는 여섯번째 매개변수를 1로 설정하였다.
만약 여섯번째 매개변수를 설정하지 않으면 데이터베이스로부터 페이지당 레코드로 제한된 10개의 레코드만을 반환하게 되기 때문에 전체 레코드 수가 몇 개인지는 알 수 없게 된다.

table_name 매개변수에는 두 개 또는 그 이상의 테이블에 대한 조인을 설정하는 문장이 올 수도 있고, 필요에 따라 서브쿼리도 올 수 있다.
예를 들어, 다음 두 문장은 table_name 매개변수에 모두 사용될 수 있다.

'Northwind.dbo.Orders A JOIN Northwind.dbo.Customers B ON A.CustomerID = B.CustomerID' '(SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > ''8/1/1996'') AS tbl'

이번 호의 기사에서 소개한 페이징 기법은 레코드의 수가 매우 많은 경우에 서버측 페이징을 처리하기 위해 매우 단순하고, 효율적으로 사용할 수 있으며, 필자의 경우에는 수백만 행이 포함되어 있는 테이블에 대해서도 사용한 경험이 있다. 예를 들어 2천 5백만 레코드가 있는 테이블에 대해서 맨 처음 일부 페이지를 조회하는 쿼리를 실행할 때, ADO RecordSet 페이징 기법을 사용했을 때에는 거의 40초가 걸렸으나 이번 호에 소개한 저장프로시저를 사용한 경우에는 1초로 수행시간을 단축할 수 있었다.
이처럼 이번 호에 소개한 페이징 기법은 조회의 대상이 되는 데이터양이 많아서 ADO RecordSet 페이징 기법을 사용하게 되면 성능상 문제가 발생할 수 있는 상황에서 유용한 대안으로 사용할 수 있다.


[리스트 1] ClientLocation 속성을 변경하는 코드
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
' 연결 생성
Set objConn = New ADODB.Connection
objConn.Open "Driver=SQL Server; Server=localhost; Database=Northwind"
Set objRS = New ADODB.Recordset
   ' 클라이언트측 커서를 사용하게 하는 옵션
objRS.CursorLocation = adUseClient
   ' 서버측 커서를 사용하게 하는 옵션
   objRS.CursorLocation = adUseServer
objRS.Open "SELECT * FROM Orders", objConn, adOpenStatic,
adLockOptimistic
   ' 이 문장은 페이징을 위해서 필요하지만, 서버측 커서를 사용하는 경우에는 에러의 원인이 된다.
   Debug.Print "Total records: " & objRS.RecordCount

[리스트 2] 정렬을 위한 조건절을 지정한 일반화된 페이징 쿼리
SELECT TOP page_size * FROM table WHERE primary_key NOT IN
    (SELECT TOP page_size * (page_number - 1) primary_key FROM table
     WHERE filter_conditions
     ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field




[리스트 3] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
SELECT * FROM table WHERE primary key IN
    (SELECT TOP page_size primary_key FROM table
     WHERE primary_key NOT IN

          (SELECT TOP page_size * (page_number - 1) primary_key FROM table
           WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
     ORDER BY sort_field)
ORDER BY sort_field


[리스트 4] SELECT_WITH_PAGING 저장 프로시저
CREATE PROCEDURE SELECT_WITH_PAGING (
@strFields varchar(4000),
@strPK varchar(100),
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@strGroup varchar(8000) = NULL)
/* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한다. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* 페이징 조건 정규화 의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1
?- 페이징하지 않고 전체 행을 반환
   SET @blnBringAllRecords = 1
ELSE
   BEGIN
     SET @blnBringAllRecords = 0
     SET @strPageSize = CONVERT(varchar(50), @intPageSize)
     SET @strPageNo = CONVERT(varchar(50), @intPageNo)
     SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
   END
/* 정렬 및 필터링 조건 정규화 정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않도록 하여 쿼리의 성능을 향상시킴.*/
IF @strFilter IS NOT NULL AND @strFilter != ''
   BEGIN
     SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
     SET @strSimpleFilter = ' AND ' + @strFilter + ' '
   END
ELSE
   BEGIN
     SET @strSimpleFilter = ''
     SET @strFilterCriteria = ''
   END
IF @strSort IS NOT NULL AND @strSort != ''
   SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
   SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
   SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
ELSE
   SET @strGroupCriteria = ''
/* 실제 조회작업을 시작 */
IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만을 실행
   BEGIN

     EXEC (
               'SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
               @strGroupCriteria + @strSortCriteria
               )
   END -- 전체 레코드를 반환.
ELSE -- 지정된 페이지를 반환
   BEGIN
     IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실

                                 행된다.
        EXEC (
                  'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTables +
                  @strFilterCriteria + @strGroupCriteria + @strSortCriteria
                 )
     ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
        EXEC (
                   'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
                        (SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
                       ' WHERE' + @strPK + 'NOT IN' + '
                            (SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
                             @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
                             @strSimpleFilter +
                             @strGroupCriteria +
                         @strSortCriteria + ') ' +
                    @strGroupCriteria +
                    @strSortCriteria
                 )
   END -- 특정 페이지를 지정한 경우
/* 전체 레코드 수를 반환하도록 지정된 경우 */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
    EXEC (
               'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
                @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
             )
ELSE
    EXEC (
               'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria

               + @strGroupCriteria)
GO

출처 : SQL2000 매거진

Name
Password
Homepage
Secret

SQL Server 에서 사용할 수 있는 서버측 페이징기법

웹/웹.도움말

SQL Server에서 사용할 수 있는 서버측 페이징기법

Andrew Rosca

웹 애플리케이션은 일반적으로 사용자에게 많은 양의 정보를 제공하기 위해 페이징 기법을 사용한다. 예를 들어 인터넷 검색엔진은 사용자의 쿼리 결과로 대용량의 결과값을 반환한다. 이 때 검색엔진이 한번에 결과값 전체를 반환하게 되면 결과값을 받는 클라이언트측 시스템에 과부하가 발생할 수 있다. 하지만 페이징 기법을 사용하게 되면, 반환되는 결과값을 클라이언트측과 서버측 양쪽에서 관리할 수 있을 만큼의 고정된 크기의 블록으로 구분하여 한 번에 이동시키는 정보의 양을 줄일 수 있다. 애플리케이션에서는 한 번 에 소수의 레코드만 사용자에게 보내게 되며, 결과값 중에서 사용자가 필요로 하는 정보만 반환하게 된다.

페이징 기법을 사용하면 데이터를 사용자가 좀 더 이해하고 표현하기 쉽게 해 줄 뿐만 아니라, 대량의 정보를 조회하고 표현하기 위해서 시스템에 불필요한 과부하가 발생하여 결국 시스템의 성능에 악영향을 미치지 않도록 통제하기 때문에 전체적인 시스템 성능을 향상시키게 된다. 정상적으로 시스템에 반환된 결과값 레코드가 페이징되었다면, 검색엔진을 사용하는 사용자는 대부분 맨 처음 한 페이지 또는 일부 페이지만 조회하게 될 것이다.

불행하게도 많은 프로그래머들이 페이징 관련해서 성능측면에서 매우 중요한 고려사항에 대해서 잘 모르고 있다. IIS와 SQL Server를 사용하는 환경에서는, AbsolutePage, PageSize, PageCount와 같은, 표준 ADO RecordSet 페이징 기능을 사용하는 것이 가장 일반적인 페이징 기법이다. 소량(수십 또는 수백 레코드 정도)의 데이터에 대해서는 이러한 기능을 사용하면 정상적으로 동작하고, 시스템의 성능측면에서도 부하를 발생시키지 않는다. 하지만 레코드 수가 증가하게 되면, 이러한 기능을 사용하게 되면 효율성이 감소하게 되고 전체적인 애플리케이션의 성능에 악영향을 미치게 된다.

대량의 발주정보를 조회해야 하는 구매조달관련 애플리케이션, 수 천명의 회원이 동시에 접속하는 미팅 웹사이트, 고객의 검색조건에 따라 수백 개의 상품의 정보를 표시해야 하는 대규모 전자상거래 웹사이트와 같이, 대용량 데이터를 처리해야 하는 애플리케이션의 경우에는 좀 더 개선된 서버측 페이징 기술이 필요하게 된다. 이번 호의 기사에서는 수백만 개의 행정보를 포함하는 테이블에서도 사용할 수 있는 페이징 기법에 대해서 소개하고자 한다.


ADO RecordSet 페이징 기법의 한계

대용량의 레코드를 페이징하기 위해서 ADO RecordSet의 페이징기법을 사용할 때 발생하는 문제의 원인은 ADO에서 데이터를 처리하는 방법 때문이다. ADO 기술구조에서는 데이터베이스에서 정보를 조회하기 위해 서 조회의 대상이 되는 데이터에 대한 포인터를 관리해야 할 필요가 있다. 데이터에 대한 포인터를 커서 라고 하며, 클라이언트측(예를 들어 ASP 페이지)에서는 각 레코드를 건별로 조회하게 된다.

ADO RecordSet 개체는 서버측 커서(기본값)와 클라이언트측 커서 유형을 지원한다 서버측 커서를 사용하게 되면 모든 데이터는 그대로 SQL Server에 두고, 해당 데이터가 필요한 시점에 순서에 따라 각 레코드를 조회하게 된다.


클라이언트측 커서를 사용하게 되면 필요한 모든 데이터를 클라이인트로 전송한 다음, 클라이언트측 커서를 사용하여 클라이언트측 버퍼 메모리에 있는 데이터를 레코드별로 조회하게 된다. 검색엔진 예제에서처럼 쿼리의 결과값 중에서 일부분만을 표시하거나 사용해야 하는 경우라면 SQL 서버가 클라이언트에서 요청하는 페이지만 전송하고 전체 결과값 중 나머지 레코드는 데이터베이스 서버에 그대로 남겨두게 되는, 서버측 커서를 사용하는 것이 효율적이다. 서버측 커서를 사용하게 되면 클라이언트로 전송되는 레코드 수가 특정 페이지를 구성하는 20~30 레코드 정도로 제한된다는 것이다.


PageCount 와 같은 일부 레코드셋 페이징 기능을 사용하기 위해서는 클라이언트측 커서를 사용해야 한다. 클라이언트 커서를 사용할 수 있도록 ADO를 설정하기 위해서는 RecordSet의 ClientLocation 속성을 adUseServer에서 adUseClient로 변경해 주면 된다. [리스트 1]의 VB 코드는 RecordSet 개체에서 클라이언트측 커서와 서버측 커서를 사용하는 방법에 대한 예제가 나타나 있다. ClientLocation 속성을 asUseClient 로 변경하게 되면, 사용자 쿼리의 결과로 반환되는 데이터에서 필요로 하는 페이지 수를 판단하기 위해 결과값 전체가 클라이언트로 전송된다.


예를 들어 데이터베이스로부터 5000 레코드를 반환하는 쿼리를 실행했다고 가정하자. 애플리케이션에서 서버측 커서를 사용하게 되면 반환되는 레코드를 한 페이지당 20 레코드씩으로 페이징하고, 사용자가 1 페이지만 보고 있는 경우라면 애플리케이션에서는 클라이언트로 맨 처음 페이지를 구성하는 20 레코드만을 전송하면 된다. 그 다음 사용자가 두번째 페이지로 이동하면 애플리케이션에서는 21~40번 레코드만 클라이언트로 전송하게 된다.

 

반면에 클라이언트측 커서를 사용하게 되면 ADO에서는 비록 사용자가 단지 첫 페이지에 해당하는 20 레코드만 필요한 경우라도 5000 레코드 전체를 클라이언트로 전송하게 된다. 이렇게 전체 레코드를 전송하게 되면 결과값이 사용자에게 나타나는 시간이 지연되게 되고, 반환되는 레코드 수가 매우 많은 경우에는 성능에 심각한 악영향을 미칠 수 있다.


다른 페이징 기법

ADO RecordSet 페이징 기법과 관련한 문제가 애플리케이션의 성능에 영향을 미치게 되었기 때문에 필자는 수천 레코드를 페이징해야 하는 웹 애플리케이션에서 사용할 수 있는 다른 페이징 기법을 찾아 보았다.

SQL 서버의 인덱스를 활용하여 전체 결과집합 중에서 상위의 레코드를 선택하는 방법을 소개하고자 한다.

다음은 Northwind 데이터베이스의 Orders 테이블에서 상위 10개의 레코드를 선택하는 쿼리이다.


SELECT TOP 10 * FROM Orders


위의 구문을 활용하면 전체 결과집합에서 10 개의 레코드 단위로 결과값이 반환되게 할 수 있다. 주어진 페이지에 해당하는 레코드만 선택하기 위해서는, 한 페이지에 몇 개의 레코드를 포함시킬 것인지 결정하고, 실제 사용자가 몇 번째 페이지의 정보를 조회하기를 원하는지에 대한 페이지 카운트를 알고 있어야 한다. 예를 들어 한 페이지에 10 개의 레코드가 포함되고, 사용자가 전체 결과값 집합 중에서 3 페이지를 조회하고자 하는 경우라면 다음과 같은 쿼리를 사용하면 된다.


SELECT TOP 10 * FROM Orders WHERE OrderID NOT IN (SELECT TOP 20 OrderID FROM Orders)


위의 쿼리는 맨 처음 20 개의 레코드 이후에 존재하는 10개의 레코드, 즉 21~30번까지의 레코드를 반환한다. 위의 쿼리를 절차코드로 일반화하게 되면 다음과 같이 표현할 수 있다.


SELECT TOP page_size * FROM Orders WHERE OrderID NOT IN (SELECT TOP (page_size * (current_page - 1)) OrderID FROM Orders)


위의 쿼리는 대량의 레코드를 반환하는 경우라도 잘 동작하지만, 반환할 페이지의 숫자가 많아질수록 전체적인 성능은 감소하게 된다. 문제의 원인은 IN 연산자에 포함되는 쿼리의 결과값이 많아지면서 비효율성이 증가하기 때문이다. 예를 들어 한 페이지당 10개의 레코드를 반환하는 결과집합 중에서 500번째 페이지를 조회하기 위해 쿼리를 실행하게 되면, IN 연산자의 대상이 되는 서브쿼리에는 다음과 같은 문장이 포함되게 된다.


(SELECT TOP 4990 OrderID FROM Orders)


마지막 10개의 레코드를 조회하기 위해 서버에서는 4990 개의 OrderID와 각 OrderID를 비교해야만 한다. 불필요한 비교작업이 많이 발생하게 된다. 물론 SQL Server의 경우에는 이러한 경우 데이터를 좀 더 빠르고 효율적인 방법으로 검색하기 위해서 인덱스를 사용하여 빠르게 쿼리를 처리하게 된다. (OrderID가 기본키로 설정되어 있기 때문에, SQL Server는 기본적으로 인덱스를 사용하게 된다.) 조회하고자 하는 페이지 수가 증가함에 따라 성능면에서 느려지게 된다고 하더라도 이러한 조회성능의 감소현상은 조회하고자 하는 페이지 수가 매우 큰 경우에만 인식할 수 있게 된다. 이러한 점증적인 성능의 감소현상은 대부분의 경우 사용자가 맨 처음부분의 일부 페이지만 조회하게 되고, 맨 처음부분의 일부 페이지를 조회 할 경우에는 매우 빠른 성능을 보장할 수 있기 때문에 크게 중요한 관심의 대상이 되지 않는다. 애플리케이션이션에서 적절하게 데이터를 정렬하고 필터링한다면 사용자는 찾고자 하는 정보를 거의 한 두 페이지 이내에서 찾게 된다. 만약 맨 처음 부분의 페이지에서 필요로 하는 데이터를 찾지 못한 경우에는 대부분의 사용자는 예제에서처럼 500 페이지까지 원하는 데이터를 찾고자 계속 다음 페이지를 찾아보는 것이 아니라 다른 정렬 및 필터링 조건으로 새로운 쿼리를 하게 된다.


앞에서 언급한 것과 같이 검색작업을 수행할 때, 테이블의 기본키가 어떤 컬럼에 설정되었는지가 매우 중요한 역할을 하게 된다. 기본키에는 각 레코드를 유일하게 식별할 수 있는 컬럼이 포함되어야 하며, 쿼리를 실행할 때 기본키를 기준으로 해당 레코드를 쿼리의 결과값으로 선택할 것인지 무시할 것인지를 판단하게 된다. 앞의 일반화된 쿼리에는 정렬 및 기본키에 관련한 WHEHE 절이나 ORDER BY 절 내용이 누락되어 있다. 목록 2에는 이러한 요소를 포함시킨 일반화된 쿼리가 나타나 있다.


정렬은 일반적으로는 비효율적인 작업이며, 쿼리의 성능을 저하시키는 원인이 되기도 한다. SQL 서버의 경우, 인덱스가 설정된 컬럼에 대해서는 매우 효율적으로 정렬 및 필터링 작업을 할 수 있고, SQL 서버가 항상 기본키 컬럼에는 인덱스를 생성하기 때문에 쿼리를 좀 더 최적화하여 빠르게 실행한다. 기본키에 설정된 인덱스를 최대한 활용하기 위해서, [리스트 3]과 같이 테이블의 전체 컬럼을 선택하지 않고, 먼저 기본키의 조건으로 대상이 되는 레코드를 검색한 다음에 결과값에 포함되어 있는 기본키값으로 다시 해당 레코드에 대한 전체 컬럼을 찾아오게 할 수도 있다. 테이블에 기본키 인덱스가 설정되어 있고, 인덱스가 설정된 필드에 대해서만 정렬 및 필터링 작업을 하게 되면, 조회하고자 하는 레코드를 찾기 위해 인덱스 페이지만을 사용하게 된다. 이렇게 쿼리의 결과값에 필요한 전체 필드가 인덱스에 포함되어 있는 경우를 커버된 인덱스라고 한다. 동일한 쿼리를 실행시킨 경우라도 해당 쿼리가 커버된 인덱스를 사용하는 경우가 일반 테이블에 대해서 쿼리하는 경우보다 더 빠르게 된다. 맨 마지막으로 선택된 결과값에 해당하는 나머지 정보를 조회하기 위한 작업을 수행할 때에는(즉, SELECT * 부분), 기본키 인덱스를 사용하여 SQL Server가 해당 인덱스를 바로 찾을 수 있기 때문에 매우 효율적으로 쿼리를 수행하게 된다.


[리스트3]에 나타나 있는 쿼리는 결과집합 중에서 특정 페이지를 매우 효율적이고, 단순한 방법으로 조회하게 된다. 물론, GROUP BY나 HAVING 절을 추가하여 사용할 수도 있다. 쿼리를 좀 더 단순화하기 위해서 [리스트 4]와 같이 SELECT_WITH_PAGING 라는 저장 프로시저를 생성하여, 기능을 캡슐화하였다.


SELECT_WITH_PAGING 저장프로시저에는 fields_to_return (string), primary_key (string), table_name (string), page_number (integer, default 1), page_size (integer), get_record_count (true/false), filter_conditions (string), sort_columns (string), group_by (string)와 같은 매개변수(데이터형)가 입력되게 된다.


예를 들어 한 페이지당 레코드 수를 10개로 지정하고 Northwind 데이터베이스의 Order 테이블으로부터 주문일자로 정렬하여 CustomerID, ShipName을 조회한 다음, 결과값 중 세번째 페이지를 조회하기 위해서는 다음 문장을 실행하면 된다.

EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'


위의 쿼리를 실행하게 되면 입력된 조건에 따라 필터링되어 반환되는 전체 레코드 수가 두번째 레코드셋으로 반환된다. 전체 레코드 수는 사용자에게 전체 페이지 수를 표시하려고 할 때 유용하게 사용되며, 대부분의 경우 사용자는 단순하게 이전페이지 또는 다음페이지로 표시되는 것보다는 전체 페이지 수 중에서 현재 조회하고 있는 페이지의 번호를 표시하는 방법을 더 선호한다. 조건에 해당하는 전체 레코드 수를 조회하기 위해서 필자는 여섯번째 매개변수를 1로 설정하였다. 만약 여섯번째 매개변수를 설정하지 않으면 데이터베이스로부터 페이지당 레코드로 제한된 10개의 레코드만을 반환하게 되기 때문에 전체 레코드 수가 몇 개인지는 알 수 없게 된다.


table_name 매개변수에는 두 개 또는 그 이상의 테이블에 대한 조인을 설정하는 문장이 올 수도 있고, 필요에 따라 서브쿼리도 올 수 있다. 예를 들어, 다음 두 문장은 table_name 매개변수에 모두 사용될 수 있다.


'Northwind.dbo.Orders A JOIN Northwind.dbo.Customers B ON A.CustomerID = B.CustomerID' '(SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > ''8/1/1996'') AS tbl'


이번 호의 기사에서 소개한 페이징 기법은 레코드의 수가 매우 많은 경우에 서버측 페이징을 처리하기 위해 매우 단순하고, 효율적으로 사용할 수 있으며, 필자의 경우에는 수백만 행이 포함되어 있는 테이블에 대해서도 사용한 경험이 있다. 예를 들어 2천 5백만 레코드가 있는 테이블에 대해서 맨 처음 일부 페이지를 조회하는 쿼리를 실행할 때, ADO RecordSet 페이징 기법을 사용했을 때에는 거의 40초가 걸렸으나 이번 호에 소개한 저장프로시저를 사용한 경우에는 1초로 수행시간을 단축할 수 있었다. 이처럼 이번 호에 소개한 페이징 기법은 조회의 대상이 되는 데이터양이 많아서 ADO RecordSet 페이징 기법을 사용하게 되면 성능상 문제가 발생할 수 있는 상황에서 유용한 대안으로 사용할 수 있다.


[리스트 1] ClientLocation 속성을 변경하는 코드
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
' 연결 생성
Set objConn = New ADODB.Connection
objConn.Open "Driver=SQL Server; Server=localhost; Database=Northwind"
Set objRS = New ADODB.Recordset
   ' 클라이언트측 커서를 사용하게 하는 옵션
objRS.CursorLocation = adUseClient
   ' 서버측 커서를 사용하게 하는 옵션
   objRS.CursorLocation = adUseServer
objRS.Open "SELECT * FROM Orders", objConn, adOpenStatic,
adLockOptimistic
   ' 이 문장은 페이징을 위해서 필요하지만, 서버측 커서를 사용하는 경우에는 에러의 원인이 된다.
   Debug.Print "Total records: " & objRS.RecordCount


 

[리스트 2] 정렬을 위한 조건절을 지정한 일반화된 페이징 쿼리
SELECT TOP page_size * FROM table WHERE primary_key NOT IN
(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions
ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field


 

[리스트 3] 기본키에 검색조건을 먼저 설정하는 일반화 쿼리
SELECT * FROM table WHERE primary key IN
(SELECT TOP page_size primary_key FROM table WHERE primary_key NOT IN (SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions ORDER BY sort_field) AND filter_criteria
ORDER BY sort_field)
ORDER BY sort_field


[리스트 4] SELECT_WITH_PAGING 저장 프로시저
CREATE PROCEDURE SELECT_WITH_PAGING (
    @strFields varchar(4000),
    @strPK varchar(100),
    @strTables varchar(4000),
    @intPageNo int = 1,
    @intPageSize int = NULL,
    @blnGetRecordCount bit = 0,
    @strFilter varchar(8000) = NULL,
    @strSort varchar(8000) = NULL,
    @strGroup varchar(8000) = NULL)
/* 매개변수에 따라 반환되는 결과값을 특정 페이지로 정의하거나 전체 행을 모두 반환할 수 있도록 설정한다. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
DECLARE @intRecordcount int
DECLARE @intPagecount int
/* 페이징 조건 정규화
의미있는 페이징 조건이 입력되지 않은 경우, 페이징하지 않고 좀 더 효율적인 방법으로 쿼리를 실행시키기 위해 blnBringAllRecords 플래그를 사용 */
IF @intPageNo < 1
    SET @intPageNo = 1
    SET @strPageNo = CONVERT(varchar(50), @intPageNo)

IF @intPageSize IS NULL OR @intPageSize < 1 -- 페이징하지 않고 전체 행을 반환
     SET @blnBringAllRecords = 1
ELSE
BEGIN
     SET @blnBringAllRecords = 0
     SET @strPageSize = CONVERT(varchar(50), @intPageSize)
     SET @strPageNo = CONVERT(varchar(50), @intPageNo)
     SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
END
/* 정렬 및 필터링 조건 정규화
정렬 및 필터링 조건이 지정되지 않으면, 필터링이나 정렬작업이 수행되지 않도록 하여 쿼리의 성능을 향상시킴.*/

IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
     SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
     SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
     SET @strSimpleFilter = ''
     SET @strFilterCriteria = ''
END

IF @strSort IS NOT NULL AND @strSort != ''
     SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
     SET @strSortCriteria = ''
    

     IF @strGroup IS NOT NULL AND @strGroup != ''
          SET @strGroupCriteria = 'GROUP BY' + @strGroup + ' '
     ELSE
          SET @strGroupCriteria = ''
          /* 실제 조회작업을 시작 */
         IF @blnBringAllRecords = 1 -- 페이징 하지 않고 단순한 SELECT 문장만        을            실행
         BEGIN

              EXEC (
               ' SELECT ' + @strFields + 'FROM' + @strTables + @strFilterCriteria +
                @strGroupCriteria + @strSortCriteria
                     )

        END -- 전체 레코드를 반환.
        ELSE -- 지정된 페이지를 반환
        BEGIN
        IF @intPageNo = 1 -- 맨 처음 페이지를 찾기 때문에 서브쿼리가 없어서 가장 효율적으로 실행된다.
             EXEC (
             'SELECT TOP' + @strPageSize + ' ' + @strFields + 'FROM' + @strTabl    es +
              @strFilterCriteria + @strGroupCriteria + @strSortCriteria
               )
         ELSE -- 특정 페이지를 선택하기 위해 서브쿼리 구조를 실행한다.
 EXEC (
'SELECT' + @strFields + 'FROM' + @strTables + 'WHERE' + @strPK + 'IN' + '
(SELECT TOP' + @strPageSize + ' ' + @strPK + 'FROM' + @strTables +
' WHERE' + @strPK + 'NOT IN' + '
(SELECT TOP' + @strSkippedRows + ' ' + @strPK + 'FROM' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END -- 특정 페이지를 지정한 경우
/* 전체 레코드 수를 반환하도록 지정된 경우 */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria + @strGroupCriteria)
GO

출처 : SQL2000 매거진

Name
Password
Homepage
Secret