ASP DB 액세스 튜닝 사례 및 DBHelper 소개

http://blog.naver.com/saltynut/120023627497

Written by 안재우(Jaewoo Ahn), 닷넷엑스퍼트(.netXpert)

 

이번 글은 ASP에 대한 팁입니다. 근데, 갑자기 뜬금없이 ASP냐구요?

실은 얼마전 회사의 김태영 씨가 요청을 해서 예전 S사의 튜닝 프로젝트에서 만들었던 DBHelper라는 ASP용 데이터 액세스 헬퍼 클래스를 태요 사이트(http://www.taeyo.net)에 공개했습니다.

강좌 페이지는 http://www.taeyo.net/Lecture/20_Tips/DBHelper.asp 이며, 관련된 소스 및 문서는http://www.taeyo.net/Lecture/20_Tips/source/DBHelper.zip에서 다운받을 수 있습니다.

 

위 문서에 있는대로 사실 DBHelper는 별다른 대단한 녀석은 아닙니다. 단지 ASP에서 권장되는 데이터 액세스 기법들을 활용해서 유틸리티 함수들을 묶은 스크립트 클래스에 불과합니다. 사실 클래스 자체보다는 그러한 바탕이 되는 기법들이 무엇이고 왜-어째서 이런 기법들을 사용해야 하는지가 더욱 중요한 것이겠죠.

 

이번 글에서는 실제 튜닝 프로젝트 사례를 바탕으로 튜닝 시나리오, ASP 데이터 액세스 기법 및 팁, 그리고 DBHelper에 대해 설명하도록 하겠습니다.

 

1. 프로젝트의 배경

S사는 Windows 2000, IIS 5.0인 웹 서버 10대, Windows 2000, SQL 2000 Enterprise로 클러스터링된 DB 서버 2대로 운영되는 중형 규모의 ASP 사이트였습니다. S사에서 제기해 온 문제는 서버의 성능이 너무 떨어진다는 것이었습니다. 조사 결과 웹 서버의 부하는 상당히 낮은 편인데 비해 DB 서버의 부하가 상당히 높은 편이었습니다. 사용자가 늘어나면서 이러한 성능 문제는 점점 더 심각해졌고, 그에 따른 Capacity 한계 때문에 S사는 W2K-IIS-ASP-SQL 기반의 플랫폼에 의구심을 가지고 있던 상황이었습니다.

 

논의 결과 2가지 사항에 크게 주목하게 되었습니다.

첫째는 DB 내부의 문제입니다. DB 구조 자체가 잘못 되었거나, 인덱스가 걸려 있지 않거나 혹은 잘못 걸려 있는 경우, 그리고 DB 구조 때문에 쿼리 자체가 상당히 복잡한 경우 등입니다. 즉 이 경우는 효율적인 DB 튜닝을 통해 성능을 개선할 수 있습니다. 테이블 구조 변경, 인덱싱, 자주 사용하는 쿼리를 SP로 변환하는 것 등이 해당 작업이 되겠습니다.

 

둘째는 웹 서버의 부하가 너무 적다는 것입니다. S사의 경우 부하가 매우 DB 서버에 편중되어 있으므로, 이를 웹 서버에 분담시킬 수 있는 방법을 고려하는 것이 바람직하다고 판단되었습니다. 이를 위해서는 애플리케이션의 코드를 분석하고, 데이터 액세스를 어떻게 하고 있는지를 파악하는 것이 필요합니다.

 

작업은 어디까지나 튜닝의 범위 내에서 수행하기로 했습니다. 즉 플랫폼을 교체한다거나 아키텍처를 완전히 갈아엎는다거나, 하드웨어를 추가로 구매하는 것은 고려하지 않기로 했습니다. 이에 따라 DB 튜닝과 애플리케이션 튜닝을 병행해서 작업을 하도록 결정하였습니다.

DB 튜닝은 제 영역이 아니므로 이 글에서는 다루지 않도록 하겠습니다. 이런 쪽은 DB 전문가 분들께 문의해보시는게 빠르겠죠? ^^

 

2. 튜닝의 기초 개념 : 부하의 분산

 

우리가 알아야 할 사항 중 하나는 기본적으로 애플리케이션의 로직 등에 문제가 없다고 가정할 때, 부하는 없어지지 않고 옮겨질 뿐입니다. 예를 들어 전체 부하가 100이라고 할 경우, 이 부하는 웹 서버와 DB 서버들에 각각 나누어져서 존재합니다.

 

따라서 분산 애플리케이션 튜닝에서 해야 할 사항 중 하나는 이러한 부하를 골고루 분산하는 것입니다. 여기에서 감안해야 할 사항은 확장성(Scalability)입니다. 통상적으로 웹 서버는 스케일 아웃(Scale-Out)을 통해 확장을 하기가 용이합니다. 즉 웹 서버의 대수를 늘리고 L4 등을 통해 로드밸런싱으로 묶기만 하면 분산이 가능합니다. 이에 비해 DB 서버의 경우 확장성은 상당히 제한적입니다. 통상적으로 스케일 아웃은 클러스터링을 통해서만 확장이 가능하며 클러스터링을 할 수 있는 서버의 수도 제한될 뿐더러 비용 역시 상당히 비싼 대가를 치러야 합니다. 그렇지 않으면 스케일 업(Scale-Up), 즉 서버의 사양을 업그레이드시키는 것으로만 해결이 가능합니다. 그러나 아쉽게도 Windows 2000 서버는 CPU 개수를 무한히 늘릴 수 없으며, 제한적입니다.

 

DB 서버와 웹 서버는 사양 및 성능에 있어서 분명한 차이가 존재합니다. 일반적으로 DB 서버가 훨씬 더 뛰어난 사양을 가집니다. S사의 DB 서버와 웹 서버의 경우는 CPU 갯수만 하더라도 8CPU와 2CPU라는 성능 차이를 가지고 있었습니다.

 

얘기를 풀어나가기 위해 일단 각 DB 서버의 최대 부하 한계가 50, 최대 웹 서버의 부하 한계가 20이라고 대략 가정해 보도록 합시다.

현재는 DB 서버 쪽이 70, 웹 서버 쪽이 30인 상태였습니다. 이 경우 DB 서버는 1대당 35, 웹 서버는 1대당 기껏 4의 부하만을 가지고 있는 셈입니다. DB 서버는 자기 한계를 70%(35/50)를 이미 사용하고 있으며, 웹 서버는 자기 한계의 20%(4/20)만을 기껏 사용하고 있는 셈입니다.

(35 * DB 서버 2대) + (3 * 웹 서버 10대) = 100

 

우리가 바라는 것은 최소한 DB 서버 쪽을 40, 웹 서버 쪽을 60으로 만드는 것이었습니다. 이 경우 DB 서버는 1대당 20, 웹 서버는 1대당 6의 부하만을 가지면 됩니다. 이 경우 DB 서버는 40%, 웹 서버는 30%로 되는 셈입니다.

(20 * DB 서버 2대) + (6 * 웹 서버 10대) = 100

 

DB 서버 쪽을 30, 웹 서버 쪽을 70으로 만들면 안되나요? 아, 물론 가능할 수도 있습니다. 이 경우 DB 서버는 1대당 15, 웹 서버는 1대당 7의 부하를 가지게 됩니다. 이 경우 DB 서버는 30%, 웹 서버는 35%로 되는 셈입니다.

(15 * DB 서버 2대) + (7 * 웹 서버 10대) = 100

 

대신 마지막으로 알아두어야 할 사항은 이러한 부하를 한쪽으로 몽땅 옮길 수는 없으며, 옮길 수 있는 한계가 존재한다는 것입니다. 최소한 DB 서버에서만 수행가능한 작업, 웹 서버에서만 수행 가능한 작업은 반드시 존재한다는 것이죠. 애플리케이션과 시스템의 성격에 따라 1:9, 2:8, 3:7, ..., 8:2, 9:1 등은 존재할 수 있지만 0:10이나 10:0은 존재할 수 없다는 것을 의미합니다.

 

쉽게 설명하려고 노력했는데 이해가시죠? ^^ 참고로 튜닝에는 부하 분산만이 존재하는 것은 아니라는 것을 알아 두세요.

 

3. 기존 애플리케이션 분석

우선 기존 애플리케이션을 분석하기로 했습니다. S사 사이트는 페이지 수가 상당히 많은 편이며, 구조도 비교적 복잡한 편이었습니다. 그에 따라 디렉터리 구조도 나름대로 체계적으로 나누기 위해 노력한 편이었으며, 공통된 내용들은 .inc로 철저하게 분리해내려고 노력한 흔적이 많이 보였습니다. 즉, 일반적인 ASP 사이트보다 나쁘지는 않은(?) 상황이었다는 것입니다.

 

S사는 DB 액세스를 하기 위해 크게 2개의 inc 파일을 사용하고 있었습니다. 즉 페이지의 앞 부분에 삽입된 inc에서는 ADO Connection 개체를 생성하고, 페이지 내에서는 이 Connection 개체를 가지고 DB 액세스를 수행한 후, 페이지의 뒷 부분에 inc 파일을 삽입하여 Connection을 닫고 Connection 개체를 Nothing 처리하는 전형적인 패턴을 사용하고 있었습니다.

이런 경우 inc 파일만 제대로 넣어준다면 커다란 문제는 생기지 않습니다. 결국 문제는 inc 파일들이 아니라 Connection을 가지고 실제 DB에 액세스하는 코드에 문제가 있는 셈입니다.

 

분석 결과.. 역시 코드가 문제였습니다.

가장 무엇보다 큰 문제는 DB 액세스를 위해 사용하는 코드가 전부 다 제각각이라는 점이었습니다. 사람들마다 사용하는 커서도 제각각이었으며, ADO 관련 상수를 쓰는 사람도 있고 아닌 사람도 있고, 심지어 어떤 사람은 자기가 직접 Connection을 만들어서 사용하는 사람도 있었습니다. 많은 수의 개발자가 작성하고, 시도 때도 없이 유지 보수를 위해 코드를 수정하는 경우에는 허다하게 일어나는 일입니다. 심지어 1번만 수행되면 되는 쿼리를 쓸데없이 while 루프 안에 집어 넣어서 한 페이지 내에서 동일한 쿼리가 수십번 실행되는 페이지도 존재했습니다.

두번째 문제는 대부분의 코드가 DB 서버에 부하를 많이 주는 방식이었습니다. 서버의 자원을 사용하는 Connected Recordset, 서버 사이드 커서 등이 사용되는 경우가 많았습니다.

또한 쿼리 자체도 SP도 아니며, 파라미터화된 쿼리도 아니며, 문자열 조합 쿼리가 거의 대부분이었습니다.

 

결과적으로 공통된 DB 액세스 코딩 패턴을 제공하고, DB 서버의 자원을 적게 쓰는 DB 액세스 기법들을 사용하고, 문자열 조합 쿼리를 파라미터화된 쿼리 또는 SP로 변환하는 것을 권고안으로 제시했습니다.

 

4. ASP 데이터 액세스 기법 살펴보기

여기서는 DBHelper에 쓰인 기법들을 자세히 살펴보도록 하겠습니다. DBHelper는 이러한 3가지를 사용하기 쉽도록 지원하기 위한 산출물인 셈이죠.

 

1) Connected Recordset vs. Disconnected Recordset

 

일반적으로 ADO에서 사용되는 레코드셋은 연결된 레코드셋(Connected Recordset)으로 레코드셋에서 작업을 하기 위해서는 Connection이 열려 있어야 하며, 레코드셋에서 MoveNext 또는 MovePrevious와 같은 작업을 하는 경우 DB와 계속적으로 상호작용을 하게 됩니다.
이 경우 오랜 시간 Connection 자원을 열어놓고 사용하게 되므로 자원적인 낭비와 궁극적으로는 DB 서버의 성능 저하를 초래하며, 커서를 사용하는 동안 DB와의 라운드 트립이 발생하게 됩니다.

 

ADO은 연결된 레코드셋 외에 별도로 비연결 레코드셋(Disconnected Recordset)을 지원합니다. 비연결 레코드셋이라 함은 말 그대로 DB와 연결이 없는 레코드셋을 말합니다. 예를 들어 비연결 레코드셋에서 MoveNext, MovePrevious와 같은 레코드 네비게이션(navigation) 메서드들이 호출되더라도 DB와 어떠한 상호작용도 하지 않습니다.

 

이것이 가능케하기 위해서 비연결 레코드셋은 클라이언트 커서 라이브러리를 사용합니다. 즉, 레코드셋 객체의 CusorLocation 프로퍼티의 값이 adUseClient이어야만 비연결 레코드셋을 만들 수 있습니다. 비연결 레코드셋을 만드는 방법은 다음과 같이 간단하게 레코드셋 객체의 ActiveConnection 속성 값을 Nothing으로 설정하면 됩니다.

 

Dim strConn, strSQL
Dim adoRS

 

strSQL = "SELECT * FROM Suppliers"
strConn = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=NorthWind;User ID=sa;"

 

Set adoRS = Server.CreateObject("ADODB.RecordSet")
Set adoRS.CursorLocation = adUseClient

adoRS.Open strSQL, strConn

Set adoRS.ActiveConnection = Nothing

Disconnected Recordset의 장점은 다음과 같습니다.

- 클라이언트 커서를 사용하며, 자유로운 데이터 액세스가 가능합니다.
- DB 연결을 끊은 상태에서 레코드셋 작업을 수행하므로 DB 서버의 자원(커서, 메모리, 네트워크 연결 등)을 점유하는 시간이 최소화됩니다.
- 끊긴 연결은 언제든지 다시 연결하여, 레코드셋에서 변경된 사항을 DB에 적용할 수 있습니다.
- 클라이언트 커서와 함께 DB 확장성(Scalability)에 큰 도움이 됩니다.

 

DBHelper에서는 기본적으로 Disconnected Recordset을 사용합니다.

 

2) 커서(Cursor)의 사용

ADO에서 지원하는 커서에는 크게 4가지 종류가 있습니다. 이러한 커서를 적절히 사용하면 애플리케이션의 성능을 향상시킬 수 있습니다.

 

종류	특징	비고
Forward-Only	MoveNext만 가능 RecordCount, 페이징 사용 불가 나머지는 Static과 동일	RecordCount나 페이징이 필요 없는 경우 권장 가장 빠른 성능
Keyset	다른 사용자가 추가한 레코드는 볼 수 없음 나머지는 Dynamic과 동일	DB에 부하가 큼
Dynamic	레코드셋의 모든 기능 사용 가능 다른 사용자의 변경 내용을 모두 볼 수 있음	DB에 부하가 가장 큼 성능이 가장 느림
Static	다른 사용자의 변경내용 볼수 없음 그외 레코드셋의 모든 기능 사용 가능	대부분의 경우에 권장

 

위에서 언급한 것처럼 대부분의 경우에는 Static 커서를 사용하는 것이 가장 적합합니다.

 

DBHelper에서는 Static 커서를 사용합니다.

 

3) RecordCount의 사용

Static / Keyset 커서를 사용하는 경우, 레코드셋의 RecordCount 속성을 사용할 수 있습니다. 이 속성은 편리하기는 하지만 신중하게 사용해야 합니다.
RecordCount 속성을 사용하는 경우 SQL 서버는 sp_cursoropen 저장 프로시저를 호출하여 커서를 열고 레코드들을 fetch 해 나가는데, RecordCount를 얻어내기 위해서는 전체 레코드를 최소한 1회 이상 fetch 해야 한다는 것을 의미합니다. 이 경우 불필요한 레코드 fetch가 증가될 수 있습니다.
성능이 문제가 되는 경우, RecordCount보다는 가급적이면 레코드의 개수를 Output 매개변수로반환해주는 SP를 사용하는 것을 권장합니다.

 

‘ Stored Procedure
CREATE PROCEDURE dbo.GetProducts
(  
  @RecordCount int OUTPUT
)
AS
  SELECT * FROM Products
  SELECT @@rowcount as  RecordCount 
RETURN

 

‘ 호출 코드
…
Set adoCmd = CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = "GetProducts"         ‘sp 이름
adoCmd.CommandType = adCmdStoredProc       ‘또는 4
adoCmd.Parameters.Refresh
…
Set rs = adoCmd.Execute
…
rs.Close

 

‘output parameter는 레코드셋을 닫고 난 후에 접근 가능하다.
Set recordCount = adoCmd.Parameters("@RecordCount")
…

4) Client Cursor vs. Server Cursor

 

ADO는 디폴트로 서버 커서, 즉 데이터베이스에서 제공하는 커서를 사용합니다. 그러나, 서버 커서에는 다음과 같은 단점들이 존재합니다.

- 데이터베이스의 커서 자원을 사용합니다.
- 레코드들을 패치할 때마다 서버에 레코드 패치를 위한 네트워크 액세스가 일어납니다.

 

위의 부하를 줄이려면, 클라이언트 커서를 사용하면 됩니다. 클라이언트 커서를 사용하면 조회된 데이터는 모두 클라이언트(웹 서버)로 복사되고, 클라이언트(웹 서버)의 메모리에 저장됩니다. 그 이후 레코드 간의 이동은 클라이언트 메모리에 저장된 레코드셋 내에서 발생됩니다.
클라이언트 커서를 사용하려면 Connection 또는 Recordset 객체의 CursorLocation 속성 값을 adUseClient 상수 혹은 3으로 설정하면 됩니다.

클라이언트 커서를 사용하면 다음과 같은 장점이 있습니다.

- 클라이언트 커서를 사용하면 레코드셋의 커서가 자동적으로 static으로 고정됩니다.
  (단, 클라이언트로 복사하기 위해 DB에서 레코드셋을 읽을 때는 ForwardOnly 커서가 사

   용됩니다)
- 데이터베이스 서버의 자원 소비량이 상대적으로 낮습니다.
- 클라이언트와 데이터베이스 간의 네트워크 라운드 트립이 줄어듭니다.

클라이언트 커서를 사용하는 경우, DB의 자원과 부하는 줄어드는 대신 웹 서버의 자원을 사용하고, 웹 서버에 부하가 옮겨지게 됩니다. 따라서 만약 현재 DB 서버의 부하는 적은데 웹 서버의 부하가 매우 큰 상황이라면 사용하지 않는 것이 좋습니다.

 

DBHelper에서는 기본적으로 클라이언트 커서를 사용합니다.

5) 파라미터화된 쿼리(Parameterized Query)

동적인 쿼리를 작성하기 위해 일반적으로는 문자열 조합에 의한 쿼리를 사용하는 경우가 많습니다. 예를 들면 다음과 같겠죠.

 

strQuery = "SELECT * FROM Orders WHERE OrderDate >= ‘" & startDate & _
          "’ AND OrderDate < ‘" & endDate & "’"
…
rs.Open strQuery, adoConn

 

일단 따옴표(‘)를 빼먹기 쉽다는 문제는 제쳐 두더라도, 이 쿼리는 SQL 서버 내에서 수행될 때 startDate와 endDate의 값이 변경될 때마다 매번 컴파일 과정을 거쳐야 합니다. 불필요한 SQL 컴파일은 DB의 부하를 가중시키켜 성능 저하로 이어지게 됩니다.
이 문제를 피하려면 파라미터화된 쿼리(Parameterized Query)를 사용하는 것이 좋습니다. 보다시피 따옴표가 사라지고 ? 또는 @로 시작하는 파라미터가 추가되었습니다.

 

strQuery = "SELECT * FROM Orders WHERE OrderDate >= ? AND OrderDate < ?"
또는
strQuery = "SELECT * FROM Orders WHERE OrderDate >= @startDate AND OrderDate < _
@endDate"

 

매개변수를 가진 쿼리를 사용하는 경우에는 매개변수를 넣어주기 위해 반드시 Command 객체를 사용해야 합니다. (아래 예제 참조)

 

…
Set adoCmd = CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = strQuery
adoCmd.CommandType = adCmdText           ‘또는 1
adoCmd.Parameters.Append _
adoCmd.CreateParameter(“@startDate”, adDate, adParamInput, , startDate)
adoCmd.Parameters.Append _
adoCmd.CreateParameter(“@endDate”, adDate, adParamInput, , endDate)
…
rs.Open adoCmd           ‘Query 대신 Command 객체를 지정

 

이렇게 매개변수를 가진 쿼리를 실행하면, SQL Profiler에서 SQL배치가 아닌 RPC가 수행되어 더욱 속도가 빨라집니다. 또한 다음과 같이 매개변수화 되므로 startDate, endDate의 값이 변경되더라도 SQL 컴파일은 한번만 일어나게 됩니다.

 

exec sp_executesql N’SELECT * FROM Orders WHERE OrderDate >= @P1
AND OrderDate < @P2 ‘, N’@P1 datetime,@P2 datetime’, ‘01 1 1995
12:00AM’, ‘01 1 1996 12:00AM’

 

매개변수를 가진 쿼리는 코드 가독성을 높여주고, 성능 면에서는 좋긴 하지만 코딩량이 다소 늘어난다는 단점이 있어서 귀차니즘(?) 때문에 안 쓰는 사람들이 많습니다. 모든 쿼리를 매개변수를 가진 쿼리로 작성할 필요는 없지만, 최소한 자주 수행되는 쿼리의 경우 이렇게 작성하면 DB의 부하를 줄이고 성능을 향상시킬 수 있습니다.

 

DBHelper에서는 이러한 코딩을 간편하게 할 수 있도록 지원합니다.

 

6) ADO 관련 상수

 

먼저 다음 코드를 봅시다.

Rs.Open SQL, Dbcon, 0, 1

ASP 코딩에 능숙한 사람은 알겠지만 0은 커서를 adForwardOnly, 1은 락을 ReadOnly로 지정하는 것입니다. 하지만, 위처럼 상수값을 숫자로만 지정하면 코드의 가독성이 떨어집니다. 그러므로 실제로는 다음과 같이 해주는 것이 이상적입니다.

Rs.Open SQL, Dbcon, adOpenForwardOnly , adLockReadOnly      

ADO에서는 위와 같은 상수를 사전에 정의한 파일을 제공합니다. 이 파일을 사용하기 위해서는 C:\Program Files\Common Files\System\ado에 있는 Adovbs.inc 파일을 include 해줘야 합니다.

<!--#include file="C:\Program Files\Common Files\System\ado\adovbs.inc" -->

그러나 include를 사용하면 그만큼 페이지 컴파일에 대한 부하가 발생해서 성능이 저하게 됩니다. 이를 피하기 위해서는 type library를 사용하면 되는데 각 페이지에 다음과 같이 선언하면 됩니다.

<!--METADATA TYPE= "typelib"  NAME= "ADODB Type Library"
      FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll"  -->

이 코드를 아예 Global.asa에 추가하면 각 페이지에 추가할 필요없이 ADO 관련 상수를 마음대로 사용할 수 있습니다.

 

5. 튜닝 결과

4번 항목에서 제시된 기법들을 사용해서 DBHelper를 작성한 후, 이를 적용하도록 각 페이지를 수정하는 작업이 이루어졌습니다. 일부 쿼리는 SP 또는 파라미터화된 쿼리로 재작성되기도 했습니다. DB 튜닝과 함께 애플리케이션 튜닝을 병행한 결과는 어떻게 되었을까요?

 

예상했던 결과가 이루어졌습니다.

 

평균 CPU 사용량이 65%대이던 DB 서버는 31% 정도로 크게 감소했으며, 웹 서버는 20% 정도에서 30%로 사용량이 늘어나게 되었습니다. 이용자 수도 거의 비슷했으며 적용전, 적용후 각각 1달 가까이를 모니터링한 결과입니다.

이에 따른 예상 수치로 볼 때 S사는 현재 사용자가 2배로 늘어나도 감당할 수 있을 것이라는 새로운 Capacity를 가지게 되었습니다. 그에 따라 굳이 플랫폼을 변경하거나 하드웨어를 교체할 필요가 없게 됨으로써 시간과 비용을 절약할 수 있게 된 셈입니다. 따져보면 별다르게 한 것도 없는데 말이죠. ^^;;

 

여기까지가 장황한 튜닝기입니다. 허접한 녀석이지만 행여나 DBHelper를 써보시고 싶으신 분들은 이 글의 첫 머리에 있는 Taeyo 사이트에 가셔서 다운받으시면 됩니다.

 

그런데 DBHelper 썼는데 우리는 성능이 왜 개선 안되냐? 라고 돌 던지신다면 대략 낭패입니다. 예를 들어 DB 서버는 팽팽 놀고 웹 서버 부하는 큰데 DBHelper를 그대로 적용한다면 오히려 성능이 더 저하될 수도 있다는 것을 감안하셔야 할 것입니다. 이 경우는 오히려 DB 서버의 자원을 적극 활용하도록 DBHelper를 변경해서 써야 할 수도 있겠죠? 또한 이 코드를 보다 세련되게 잘 다듬어서 주시는 분들이 계시면 더욱 더 감사할 듯 합니다. ^^

 

p.s. DBHelper의 코드 내용에 대한 문의는 사절합니다. 솔직히.. 몇년전에 짠거라서 기억도 잘 안납니다. 참 무책임하지요? ㅎㅎㅎ

지난 번에 DBHelper를 공개했는데, 의외로 이 허접한(?) 녀석을 사용하시는 분들이 많은가봅니다. 원래 이런 것에 대한 A/S는 잘 안 하는 성격이지만, 괜히 공개해서 많은 분들을 고생시키게 한 도의적인 책임을 지고자 이번 글을 쓰게 되었습니다.

 

참고로 DBHelper에 관한 지난 관련 URL들은 다음과 같습니다.

태오 사이트 – ASP용 데이터베이스 도우미 : DBHelper 클래스

ASP DB 액세스 튜닝 사례 및 DBHelper 소개

DBHelper 버그 수정

 

저한테 메일로 질문하셨거나, 태오 사이트에 질문하신 분들 중에 보면 DBHelper를 사용할 때다음과 같은 오류가 난다고 하시는 분들이 계셨습니다.

 

ADODB.Recordset error '800a0e7b'

명령 개체를 원본으로 가진 레코드 집합 개체의 활성 연결 속성을 변경할 수 없습니다.

 

그런데, 이 오류를 재현해보려고 해도 제 쪽에서는 도저히 재현이 안되더군요. -_-;

그런데, 오늘 문득 오류를 질문하신 분들의 소스 코드 및 SP를 늘어놓고 뚫어지게 쳐다본 결과.. 드디어 공통점을 찾아내는데 성공했습니다! 그리고 제 쪽에서 실험해본 결과, 문제를 재현하는데도 성공했습니다.

 

문제의 원인을 결론적으로 말하자면… 사용법이 잘못된 것입니다.

DBHelper에서는 SP나 SQL문을 실행하기 위해 다음과 같은 4개의 메서드를 제공합니다.

ExecSPReturnRS	SP를 실행하고, RecordSet을 반환한다.
ExecSQLReturnRS	SQL 쿼리를 실행하고, RecordSet을 반환한다.
ExecSP	SP를 실행한다. (RecordSet 반환 없음)
ExecSQL	SQL 쿼리를 실행한다. (RecordSet 반환 없음)

 

원래 ReturnRS로 끝나는 메서드들의 경우, 반드시 RecordSet을 반환할 때 사용하라고 만들어 둔 것입니다. 그런데, 문제가 발생한다고 말씀하시는 분들의 경우를 분석해본 결과RecordSet을 반환하고 있지 않으면서 이 메서드들을 사용하고 있는 경우였습니다.

 

예를 들어 다음과 같이 SP1이라는 SP가 있다고 가정해 봅시다.

 

ALTER PROCEDURE [dbo].[SP1]

  (

    @pageIndex int = 1,

    @pageSize int = 10,

    @totalCount int output

  )

AS

  SELECT @totalCount = COUNT(*) FROM Products

  RETURN

 

@pageIndex 및 @pageSize는 매개변수는 무시하고, 이 SP에서는 output 변수로@totalCount를 반환하고 있기는 하지만, RecordSet에 바인딩할 Result Set을 반환하고 있지 않습니다.

그런데, 이 SP를 호출하기 위해 ExecSPReturnRS를 사용하게 되면 DBHelper의ExecSPReturnRS이 내용 중 다음 줄에서 위의 오류(명령 개체를 원본으로 가진 레코드 집합 개체의 활성 연결 속성을 변경할 수 없습니다)가 발생합니다.

 

Set rs.ActiveConnection = Nothing

 

만약 SP가 다음과 같이 정상적으로 Result Set을 반환하고 있다면(노란색으로 칠한 줄 참조)이상 없이 동작합니다. 이 SP를 SP2라고 합시다.

 

ALTER PROCEDURE [dbo].[SP2]

  (

    @pageIndex int = 1,

    @pageSize int = 10,

    @totalCount int output

  )

AS

SELECT * FROM Products

SELECT @totalCount = COUNT(*) FROM Products

  RETURN

 

이 경우 사실은 ExecSP를 사용하는 것이 바람직합니다. Result Set을 반환하지 않으므로RecordSet을 만들 필요 자체가 없는 것이죠. ExecSP를 사용하도록 변경하면 오류가 없이실행되는 것을 볼 수 있습니다.

 

그런데, 일단 이렇게 하면 되긴 하지만.. 왜 이런 오류가 발생하는지는 좀 더 정확하게 알아봐야겠죠? 그리고 가능하다면 Result Set을 반환하지 않더라도 ExecSPReturnRS나ExecSQLReturnRS가 제대로 동작하게 만들어 봐야겠다는 생각이 들었습니다.

 

그래서 오랜만에 ADO API Reference를 뒤져봤습니다.

뒤져본 결과, ActiveConnection 속성에 대한 정보에서 다음과 같은 문장을 발견했습니다.

 

For open Recordset objects or for Recordset objects whose Source property is set to a validCommand object, the ActiveConnection property is read-only. Otherwise, it is read/write.

…

If you use the ActiveConnection argument of the Open method to open a Recordset object, the ActiveConnection property will inherit the value of the argument.

If you set the Source property of the Recordset object to a valid Command object variable, the ActiveConnection property of the Recordset inherits the setting of the Commandobject's ActiveConnection property.

 

RecordSet을 Open할 때 Command 개체를 사용하면, RecordSet의 Source 속성이Command 개체로 지정되고 RecordSet의 ActiveConnection 속성은 해당CommandObject의 ActiveConnection을 상속한다는 의미입니다. 또한 RecordSet의Source가 Command 개체인 경우인 경우에는 RecordSet의 ActiveConnection이 읽기 전용이 됩니다.

 

DBHelper에서는 RecordSet을 Open할 때 Command 개체를 사용합니다.

rs.Open cmd, ,adOpenStatic, adLockReadOnly

어라? 그러면 굵게 표시된 다음 구문 자체가 잘못된 것이 아닐까요? 읽기 전용이라면서?

Set cmd.ActiveConnection = Nothing

Set cmd = Nothing

Set rs.ActiveConnection = Nothing

 

그렇다면 어차피 오류가 생기는 게 맨 아래 줄 때문이고, rs.ActiveConnection은cmd.ActiveConnection을 상속한 것이니 Set cmd.ActiveConnection = Nothing으로 해주면 자동적으로 rs.ActiveConnection도 Nothing이 되지 않을까요? 그래서 맨 아래 줄을제거(주석 처리)하고 테스트해봤습니다.

 

Set cmd.ActiveConnection = Nothing

Set cmd = Nothing

‘Set rs.ActiveConnection = Nothing

 

그렇게 하니 SP1이나 SP2나 둘 다 일단 오류는 발생하지 않습니다. 그런데 어쩐지 찜찜합니다. 진짜 rs.ActiveConnection도 Nothing으로 처리가 된 걸까요? 그래서 그 뒤에 테스트 코드를 집어 넣어봤습니다.

If Not rs.ActiveConnection is Nothing Then

      Response.Write(rs.ActiveConnection.State)

End If

 

이런.. 1이라는 값이 출력됩니다. 1이라는 값이 출력된다는 것은 ActiveConnection이Nothing이 아니라는 의미입니다. Rs.ActiveConnection.State는 ObjectStateEnum 형이며, 1이라는 값은 adStateOpen에 해당됩니다. 따라서, Connection이 Open된 상태인Connected RecordSet이 존재하게 되어 버립니다. 원래 DBHelper의 디자인 중 하나인Disconnected Recordset이 아니게 되어 버리는 셈이죠.

 

따라서 Set rs.ActiveConnection = Nothing이라는 문장은 반드시 불러줘야 합니다. 그런데, 이상한 것은 SP2의 경우에는 어떻게 저 문장이 정상적으로 작동했을까요? Source가Command 개체인 경우에는 읽기 전용이라면서?

 

시간을 투자해본 결과, 정상적으로 Result Set이 반환되는 상황에는 Source가 Command개체라고 하더라도 rs.ActiveConnection을 Nothing으로 지정할 수 있습니다. 단, rs.ActiveConnetion에는 Nothing이나 연결 문자열만을 할당할 수 있으며, Connection 개체 자체를 할당할 수는 없습니다. (내용이 문서에 정확하게 나와 있지 않더군요.)

 

이제 SP1인 경우에 왜 에러가 발생하고, SP2인 경우에는 왜 되는지 원인은 파악되었습니다.그러면 우리는 SP1인 경우에도 에러가 발생하지 않도록 만들어야겠지요?

이를 위해서는 SP나 쿼리에서 Result Set을 반환하는지 안 하는지를 ADO에서는 무엇을 기준으로 판단하는가를 알아야 합니다. 디버깅을 해본 결과, Result Set을 반환하지 않는 경우에는 RecordSet의 State가 adStateClosed라는 것을 발견했습니다. 반환하는 경우에는adStateOpen이 됩니다.

 

그러면 이렇게 고치면 될까요?

If rs.State = adStateOpen Then

      Set rs.ActiveConnection = Nothing

End If

Result Set을 반환한 경우(State가 adStateOpen인 경우)에만 Source가 Command 개체인 RecordSet의 ActiveConnection을 Nothing으로 만들어 줄 수 있다고 하니깐, 말 대로는위 코드가 맞기는 합니다. 그런데 그럼 Result Set을 반환하지 않는 경우(adStateClosed)에는 ActiveConnection을 그대로 내버려 둘 건가요? 안되겠죠?

Source가 Command 개체이고, State가 adStateClosed인 경우에는 ActiveConnection이읽기 전용이라면.. State는 어차피 내 맘대로 할 수 있는 게 아니고.. Source는 변경이 가능하겠죠? 따라서 코드를 다음과 같이 수정했습니다.

If rs.State = adStateClosed Then

      Set rs.Source = Nothing

End If

 

Set rs.ActiveConnection = Nothing

 

이렇게 하면 어떤 경우가 되든 ActiveConnection을 Nothing으로 처리해서 Disconnected Recordset이 됩니다. SP1, SP2를 실행해 본 결과 모두 이상 없이 잘 처리가 됩니다.

결과적으로 기존 DBHelper 코드의 ExecSPReturnSQL 및 ExecSQLReturnSQL에 다음 굵은글씨로 표시된 된 코드만을 추가하면 이상없이 동작하게 됩니다.

Public Function ExecSPReturnRS(spName, params, connectionString)

 

  ...

 

  Set cmd.ActiveConnection = Nothing

  Set cmd = Nothing

 

  If rs.State = adStateClosed Then

    Set rs.Source = Nothing

  End If

 

  Set rs.ActiveConnection = Nothing

 

  Set ExecSPReturnRS = rs

End Function

 

 

Public Function ExecSQLReturnRS(strSQL, params, connectionString)

 

  ...

 

  Set cmd.ActiveConnection = Nothing

  Set cmd = Nothing

 

  If rs.State = adStateClosed Then

    Set rs.Source = Nothing

  End If

 

  Set rs.ActiveConnection = Nothing

 

  Set ExecSQLReturnRS = rs

End Function

 

 

변경된 DBHelper는 이 포스트의 첨부 파일을 보시면 됩니다.

 

이렇게 해서 해결이 되긴 했지만, Result set을 반환하지 않는 SP라면 ExecSP나ExecSQL을 사용하는 것을 추천합니다. 굳이 반환하지도 않는데 불필요하게 RecordSet 개체를 만들 필요가 없다는 것이죠.

 

이 에러 문제 말고 기능 추가 및 개선이나 예제 요청 등을 하신 분들도 계십니다만, 거기까지대응해드리기는 힘들 것 같습니다. 어차피 DBHelper는 소스가 다 제공되고 있으니, 기능 추가/개선은 직접 수행하시면 되고, 예제는 원래 파일에 첨부된 문서로도 충분하다고 봅니다.

수 차례 밝혔듯이 저는 ASP 프로그래머도 아니고, VB나 VB 스크립트에 대해서도 잘 모릅니다. -_-; 아마 현재 계획 상으로는 제가 DBHelper를 다시 들여다 보고 고치는 일은 이번이마지막이지 않을까 싶네요. 나머지는 여러분들의 몫으로! ^^