[스크랩] [펌] asp에서 디비작업 최적화하기

[펌] http://flashcafe.org/bbs/board.php?bo_table=sqlserver_study&wr_id=312&sfl=&stx=&sst=wr_hit&sod=desc&sop=and&page=1

 

홈피디자인닷컴

 

 

흔히 ASP의 VBscript-x성능은 PHP비해 나쁘지 않지만, PHP+Mysql은 특별하게 빨라서 전체적인 웹포퍼먼스는
그쪽이 좋다고들 말합니다(물론 Mysql감당할만한 사이트인 경우겠지만)
ASP에서는 역시 DB작업과 관련되어 최적화에 최적화를 하는 수밖엔 없는거죠.
제가 그간 사이트제작과, 서버관리를 하면서 느낀 DB의 최적화 10가지 방법을 간단히 적어보겠습니다.
(설명이 길어 이후 존칭을 생략합니다)

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

1. cachesize를 지정한다.

 

ASP의 주요임무는 DB에서 쿼리셋을 가져와서 렌더링 하는 것이다.
그 구조를 자세히 생각해보자(커서가 DB측에 있는 일반적인 경우)

   SQLserver(또는 기타DB) → IIS의 asp프로세스 → ADO.recordset 쿼리셋을 DB와 synk
   → movenext때마다 DB서버와 연동 → 매결과를 asp프로세스 메모리에 적재
   → 최종결과를 HTML로 렌더링해서 IIS에 전달 → 클라이언트에게 flush

 

대략 이렇다. 단절된 레코드셋을 사용하거나 getRows로 배열로 환원하지 않는다면

 

'movenext때마다 DB서버와 연동'

 

이란 엄청난 일이 매 페이지마다 일어난다(커넥션은 풀링하지만)
그렇다고 맨날 배열로 참조하기도 싫고 단절된 레코드셋을 만들기도 싫다면 어떻게 해야할까?

'ADO.recordset 쿼리셋을 DB와 synk' 이 부분을 'ADO.recordset 쿼리셋 전체를 웹서버메모리에 적재'

이렇게 해주면 'movenext때마다 DB서버와 연동' 이란 작업은 'movenext때마다 메모리참조'로 바뀌게 되어 비
약적인 성능향상이 이루어진다. 하지만 얻는게 있으면 잃는게 있는법이다. 속도는 향상되나 그만큼의 캐쉬메
모리가 IIS서버에게 필요하다.

따라서 아무 쿼리셋이나 전부 메모리에 적재하는 것은 어리석다. 그렇다고 특정 byte만큼 적재하다간 레코드
의 일부만 적재되고 짤리는 일이 있어서 말이 안된다.
가장 현명한 캐쉬방법은 '적당한 레코드를 캐쉬에 적재한다' 로 생각해볼 수 있다.
즉 100개의 레코드를 부른다면 앞에 30개정도는 캐쉬에서 처리하고 뒤에 70개만 디비와 synk하는 방법도 있다

ADO.recordset은 이러한 유연한 사고를 지원한다.

 

      recordset.casheSize=캐쉬할 레코드수

 

이러한 형식으로 사용할 수 있으며 기본값은 1이다.
즉 'select count(*) from board'와 같은 레코드가 1개만 반환되는 경우는 이미 캐쉬에 잡힌다는 뜻이다.
하지만 게시판에 흔히 쓰이는 'select top 10 * from board'와 같은 쿼리는 캐쉬를 따로 잡아줘야한다.
cacheSize를 사용할때 주의할 점은 반드시 레코드셋을 오픈하기 전에 설정해야만 의미가 있다는 것이다(당
연하게도!)

 

   strSQL="select top 30 * from board"
   rs.cacheSize=30
   rs.open strSQL,conn,0,1,1

 

이런 식으로 사용되어야한다. 쿼리결과가 정확하게 몇개의 레코드를 반환할지 모를 경우에도 유용하다.
대충 생각하기에 100개 안쪽이라면

 

   rs.cacheSize=100

 

이렇게 설정하면 100개만큼 메모리를 낭비하는가? 답은 아니다이다.
생성된 레코드수만큼만 캐쉬하는 것이다.

마지막으로 cacheSize를 사용할때 가장 유의할 점은 캐쉬될 용량이다.
레코드의 수라곤해도 그 레코드하나의 용량에 따라 캐쉬에 할당될 메모리의 양은 천차만별이다.

 

   select userid,count(*) from board group by userid

 

이런 쿼리는 사실 cacheSize=500을 해도 그닥 두려울게 없다. 캐쉬될 용량은 다음과 같이 계산해볼 수 있다.

 

   1record   용량 userid(12byte)+count(4byte)=16byte
   캐쉬전체 용량 16byte * 500 = 8000byte = 8kbyte

 

잘해봐야 8k가 두렵지는 않다. 하지만 아래 쿼리를 보자.

 

   select rowid,subject,contents,regdate,userid,hit from board

 

이것은 게시판을 읽어드릴때 일반적인 쿼리다. 100개만 캐쉬한다면 용량이 어떻게 될까.

 

   1record   용량 rowid(4)+subject(100)+contents(평균1000)+regdate(4)+userid(12byte)+hit(2) = 1122byte
   캐쉬전체 용량 1122byte * 100 = 112,200byte ≒ 112kbyte

 

이렇게 되면 얘기가 다르다. 10명의 동시접속자만 붙어도 1메가씩 떨어져나간다. 무서운 것이다.
따라서 캐쉬사이즈는 항상 그 용량을 고려해서 설정하는 습관을 들이도록 하자.
------------------------------------------------------------------------------

 

 

2. ADO페이징을 사용하지 않는다.

 

이 부분은 길게 설명하자면 한없이 길어지지만 간단히 설명하자면
.PageSize, .AbsolutePage, .PageCount 시리즈를 사용하지 말라는 것이다.
그럼 어떻게? Mysql처럼 limit를 응용해서 두개의 top를 이용하는 것이다.
원리는 아래와 같다.

  1. 전체 레코드 수를 얻어온다. ex) RowCount=rs(0) - select count(*) from board

  2. 다음의 변수를 셋팅한다.
     PageSize - 한페이지 몇개의 로우가 보일지(직접할당 'PageSize=13')
     AbsolutePage - 현재 페이지(직접할당 'AbsolutePage=13')
     PageCount - 전체 페이지수(계산식 'PageCount = CInt((RowCount-1)/PageSize)+1' )

  3. 위의 변수를 이용해 쿼리를 구성한다.
     쿼리예) board테이블의 keyField가 rowid인 경우
     "select top " & PageSize & " * from board " &_
     "where rowid not in ( select top " & ((AbsolutePage-1)*PageSize) & " rowid from board)"

 

 

왜 이용하지 말라고 하는가하면 레코드셋의 페이징은 전체 레코드에 대해서 처리하기 때문에 용량이 큰 테이
블에서는 너무 큰 부하를 주기 때문에 인덱스를 이쁘게 잡은 테이블에서 위의 조건으로 indexSeek을 하는 것
과는 너무나 성능차이가 나기 때문이다.
------------------------------------------------------------------------------

 

 

3. ado객체의 옵션까지 상세히 설정한다.

 

레코드셋이나 커넥션객체를 다룰때 뒤에 인자를 세밀하게 조정하라는 얘기는 대부분의 책에 씌여져있으니
자세히 다루지는 않겠다.
샘플로 쿼리로부터 레코드셋을 오픈하는 경우와 쿼리로 커넥션객체를 작업하는 샘플만 보자.

rs.open strSQL,conn,0,1,1
conn.execute strSQL,,1+&H80

(필자는 먼가 그 상수값을 외우는게 오히려 귀찮아서 걍 원래 변수값을 외워서 쓰고 있다..=.=;)
레코드셋 샘플은 전진전용커서,읽기전용락,커맨드는 일반 텍스트 라는 뜻의 옵션이다(순서대로)
execute 샘플은 커맨드는 일반 텍스트이고 + 레코드는 반환하지 말아라 라는 뜻의 옵션이다.
------------------------------------------------------------------------------

 

 

4. IsClientConnected를 사용한다.

 

Response.IsClientConnected 이란 메써드를 이용하면 asp를 호출한 클라이언트가 여전히 그 창을 닫지 않고
서버의 응답을 기다리고 있는지 확인할 수 있다.
근데 대체 이걸 왜 사용할까? 간단히 예를 들면 복잡한 DB작업이나, 결제모듈등의 작업을 하는 경우 작업의
요청자가 지루함을 이기지 못하고 페이지를 닫아버릴 때가 있다.
이런 경우 여러가지 문제가 야기되기 때문에 긴시간을 처리하는 asp는 마지막에 위 메써드를 이용해서 여전
히 클라이언트가 대기중인지 아닌지를 판별하여 대기중이라면 처리를 완료하고 아니면 롤백시키는 식으로
구조를 짤 수 있다.(정말 시간이 긴 처리라면 중간중간에도 삽입해서 계속 확인해갈 수도 있고^^)
------------------------------------------------------------------------------

 

 

5. getRows와 getString을 활용한다.

 

특별하게 레코드셋으로 필터링하거나 검색할것이 아니라 단순히 루프를 돌릴것이라면 구지 무거운 레코드셋
으로 할 필요도 없고 자원도 빨리 반환할수록 좋다.
일반적으로 getRows를 사용하기 전의 루프문을 보면 다음과 같다.

rs.open strSQL,0,1,1
if not rs.EOF then
 do until rs.EOF
  response.write rs(0)&"-"&rs(1)
  rs.movenext
 loop
end if
rs.close
set rs=nothing

이것을 getRows로 바꾸면 아래와 같다.

rs.open strSQL,0,1,1
if not rs.EOF then arrTemp=rs.getrows
rs.close
set rs=nothing
if isArray(arrTemp) then
  for i=0 to Ubound(arrTemp,2)
    response.write arrTemp(0,i)&"-"&arrTemp(1,i)
  next
end if

 

레코드셋을 반환하는 시점도 훨씬 앞이고 게다가 일괄적으로 하나의 ASP블럭에서 여러가지 테이블 쿼리를
처리하고 디자인 렌더링 부분에서는 배열루프로 처리할 수도 있다.
(물론 훨씬 빠르다)

getString도 많이 사용하는데 주로 단일 필드를 검색한 결과를 문자열로 만들때 편한다.
일반적인 사용방법은 아래와 같다.

SQLstring=rs.getString(2,,",","<BR>","null")

구문이 좀 복잡해보이는가? 2는 adClipString라는 상수대신에 사용한 값인데 거의 대부분의 경우에 저 값이 들
어가야하고, 그 다음 비워둔곳은 레코드 몇개만 치환할까인데 비워두면 다 받아준다.
그 다음은 필드와 레코드의 구분자를 무엇으로할까이고 마지막은 널인 값은 무엇으로 치환하나에 대한 옵션
이다.
------------------------------------------------------------------------------

 

 

6. 연결문자열을 최적화한다.

 

이 글은 아래에도 있는 내용이라 생략하겠다.(MS SQL연결문자열에 Net-Library와 디비서버지정 참조)
------------------------------------------------------------------------------

 

 

7. 통계테이블 활용

 

디비를 설계할때는 항상 비용개념을 생각해봐야한다. 즉 insert나 update가 많은가 아니면 select가 많을 것인
가에 대한 숙제다.
일반적으로 게시판은 select가 압도적으로 많기 때문에 insert시에 비용을 더 책정해주면 select가 아름다워진
다. 즉 count(*), top, max 등을 사용할 부분을 간단히 트리거를 걸어두어 통계만 수집하는 테이블을 따로 작성
해두면 select시에는 단순하게 indexSeek으로 조회할 수 있기 때문에 훨씬 비용이 절감된다.
너무 뜬구름잡는 얘기같으니 트리거의 샘플을 하나보자.

 

 

CREATE TRIGGER insertBoard ON [dbo].[board]
After INSERT,UPDATE
AS
declare @boardid int, @boardrowid int,@boardCNT int,@regdate datetime,@subject varchar(100),@cnt tinyint
   select @boardid=boardid from inserted
   select top 1 @boardrowid=rowid,@regdate=regdate,@subject=subject from board where boardid=@boardid
and active=1
   select @boardCNT=count(*) from board where boardid=@boardid and active=1
   select @cnt=count(*) from boardstat where boardid=@boardid
   if @cnt>0
      update boardStat set boardrowid=@boardrowid,regdate=@regdate,subject=@subject,boardCNT=@boardCNT
where boardid=@boardid
   else
      insert into boardStat(boardid,boardrowid,subject,regdate,boardCNT)values
(@boardid,@boardrowid,@subject,@regdate,@boardCNT)

 

 

위의 샘플은 BOARDID란 값으로 하나의 BOARD테이블에서 여러개의 게시판을 처리해주는 테이블을 가정하
고 짠 트리거인데, 간단히 설명하자면 insert가 되면 inserted에서 값을 확인해서 boardStat란 테이블에 해당레
코드가 있으면 최신값으로 업데이트를 아니면 insert를 하라는 것이다.
위의 트리거가 있으면

 

select count(*) from board where boardid=19 and active=1

 

이런 쿼리는

 

select cnt from boardStat where boardid=19

 

이렇게 바뀌게 된다.

boardid에 인덱스가 이쁘게 잡혀있다면 성능차이는 설명할 필요가 없을것이다.
------------------------------------------------------------------------------

 

 

8. 단절된 레코드셋을 사용한다.

 

웹서버가 여러대인 경우 단절된 레코드 셋을 사용하면 디비서버의 부하를 웹서버가 가져가기 때문에 상당한
성능향상이 일어난다. (하지만 앞서 말했던 getRows보다는 아니다) 간단히 소스의 변화만 보겠다.

call DB_Open()
call RS_Open()
rs.open sql,conn,0,1,1

위의 일반적인 소스를 아래와 같이 바꾼다.

call DB_Open()
call RS_Open()
rs.CursorLocation = adUseClient
rs.Open sql,conn,0,1,1
rs.ActiveConnection = Nothing
------------------------------------------------------------------------------

 

 

9. 간이트랜젝션 사용하기

 

여러가지 상황에서 트랜젝션이 필요한 경우가 많다.
특히 insert나 update등은 한번에 여러개의 테이블에 안전하게 데이터가 안착해야하는 케이스가 많은데,
이런 경우 트랜젝션을 일일히 선언하거나, SP를 짠다는 것은 여간 귀찮은게 아니다.
가장 간단한 형태의 트랜젝션은 바로 쿼리문자열에 세미콜론으로 구분하여 한꺼번에 execute시키는 것이다.
아래와같은 형태가 된다.

strSQL="insert into a (a)values('바보');insert into b (b)values('너도')"
adoCn.execute strSQL,,1+&H80

이러한 구문은 MSsql2000에서 지원되고 저 두개의 쿼리를 하나의 트랜젝션으로 취급해준다.
------------------------------------------------------------------------------

 

 

10. ADOR객체를 사용하기

 

ADODB 객체에는 오만가지 객체가 포함되어있다(Connect,Command,Recordset,Record,Stream)
단지 레코드셋이 필요한데 저런게 다 필요하단말인가(게다가 단일 연결을 한번만 하는 페이지의 경우는 디비
연결시 연결문자열로도 충분하다)
이런 경우 경량화객체인 ADOR객체를 사용하는 것이 좋다(대부분 언제나 좋다)

 

set rsOBJ = server.createobject("ADOR.Recordset")
dbconnSTR = "Provider=SQLOLEDB; Data Source=서버이름;..."
sqlSTR = "select * from test"
rsOBJ.open sqlSTR, dbconnSTR, 0, 1,1

 

 

머 위와같은 정도로 사용하면 될듯. 근데 이상하게 이 객체에 대해서 다루는 책자는 드물다. 록스책중에 하나
있긴한데..

 

출처 : 닷넷 (.NET) 프로그래머 모임

글쓴이 : 심재운 원글보기

메모 :