구글와이드(336x280)_상단 2개


Stored Procedure Issues - 1부 DB

목록으로
2005-04-26 오후 3:49:56
Stored Procedure Issues - 1부 [SQL Column ] 어린왕자
스크랩 : 0 조회수 : 94
Stored Procedure Issues - 1부
손호성
그리운 우리 님의 맑은, 맑은 노래는
언제나 제 가슴에 젖어, 젖어 있어요
그리운 우리 님의 고운, 고운 노래는
해지고 저물도록 귀에, 귀에 들려요
긴날을 문밖에서 서서, 서서 들어도
밤늦고 잠들도록 귀에, 귀에 들려요
오르골~고르륵.... 줄리아 하트의 오르골이라는 노래의 한 구절이다. 책에서도 BGM이 나오면 정말 좋을 텐데, 아직 그런 시절은 안 오나 보다. 뭐.. 기다리다 보면 조만간 오겠지. 그 때는 내가 느끼는 감정을 좀더 독자들에게도 전할 수 있지 않을까.
저장 프로시저는 프로그래밍인가?
사설이 너무 길다는 여러 의견을 접수해서, 이제는 조금 짧게 가기로 했다. 바로 본론으로 들어가자. 이번 주의 주제는 저장 프로시저 프로그래밍이다. 필자의 책에서 쿼리 작성은 프로그래밍이 아니라고 주장했었는데, 저장 프로시저를 작성하는 것은 프로그래밍이다. 왜냐면, 저장 프로시저라는 것은 입력 값과 출력 값이 있고, 문장을 제어하는 흐름(Flow)를 가지고 있기 때문이다. 이에 반해 SQL은 하나의 명령문이다. 원하는 결과를 반환하도록 Path(길)을 지정할 뿐이므로, 어떠한 제어도 하지 않는다.
왜 프로시저를 저장할까?
참 단순한 질문이지만, 지금까지는 별달리 궁금하지 않았다. 저장 프로시저의 원어 Stored Procedure는 저장된 프로시저라는 의미를 가지고 있다. 프로서저는 서브루틴처럼 목적을 가지는 일련의 명령어들의 묶음이다. 목적과 상황을 위해서 이러한 명령어들은 제어될 수도 있다.
SQL 서버 데이터베이스뿐만이 아니라 거의 모든 RDBMS에서 사용자가 접속한 세션 레벨에서 배치 문장을 작성할 수 있다. 작성된 배치문장은 몇 줄 안되는 쿼리 문일 수도 있고, 수백줄 이상의 긴 문장 묶음일 수도 있다. 이러한 T-SQL 명령들이 나만 사용하는 것이고, 그것이 그다지 자주 필요하지 않다면, 문장 별로 파일로 저장해서 관리하면 그만인 것이다. 카테고리 별 폴더를 두고 파일들을 저장하며, 나중에 쿼리 분석기에서 이 파일들을 불러서 실행하면 그만이다.
하지만, 이러한 명령들을 나 이외의 공유 목적으로 이용해야 된다면, 이것은 모든 이용자들이 접근 가능한 장소에 저장되어야 하는 것이다. 바로 그러한 곳이 데이터베이스의 메타 테이블이 되는 것이다. 하나의 데이터베이스는 여러 개의 Northwind 등과 같은 카탈로그들을 가지고 있다. 각각의 카탈로그들은 테이블이나 인덱스와 같은 실제 데이터뿐만이 아니라, 저장 프로시저와 같은 실제 코드 개체도 가지고 있게 된다. 하나의 사용자가 카탈로그의 테이블을 이용하는 것과 마찬가지로, 그 사용자는 카탈로그의 저장 프로시저를 이용할 수 있다.
왜 저장 프로시저를 이용해야 하나?
많은 사람들이 T-SQL을 저장 프로시저로 변환해야만 한다고 이야기한다. 그 말이 맞을까? SQL 서버를 제외한 다른 데이터베이스의 경우에도 모두 저장 프로시저를 이용해야만 할까? 저장 프로시저에 반대되는 개념은 Ad-hoc 쿼리, 흔히 임시 쿼리라고 부르는 것들이다. 쿼리 분석기 열고 SELECT 어쩌고 쿼리를 날리게 되면 이것은 Ad-hoc 쿼리가 된다.
Ad-hoc 쿼리에 비해서 저장 프로시저는 항상 더 sk은 솔루션(Solution)일까? 대부분의 상용 RDBMS에서는 저장 프로시저를 지원하고 있고, 자주 사용되는 문들은 저장 프로시저로 작성하는 것을 권하고 있다. 물론, 그것은 저장 프로시저로 코드화 할 수 있는 경우를 이야기하는 것이다.
예를 들어 SQL 서버의 경우에는 저장 프로시저에서 여러 로우를 포함하는 Rowset을 반환할 수 있지만, 오라클에서는 참조 커서를 써야만 한다. SQL 서버에서 ADO를 통하는 경우 대부분의 Ad-hoc 쿼리들은 Auto-parameterization을 통해서 파라메터 바인딩을 지원한다. 물론 그렇다고 SQL 서버에서 Ad-hoc 쿼리가 많아지는 것이 바람직한 것은 아니다. 반면에 오라클에서는 바인드 옵션을 Force로 하지 않는 이상, Ad-hoc 쿼리들은 파라메터 바인딩 처리는 자동으로 되지 않는다. 오라클의 경우는 바인딩을 쓰지 않는 Ad-hoc 쿼리의 경우 보다 문제가 심각해질 수 있다.
여러 특성에도 불구하고 저장 프로시저는 일반적으로 Ad-hoc 쿼리보다 더 좋은 대안이 된다. 그 이유를 하나씩 살펴보자.
코드의 길이가 짧다.
가장 단순 명료한 이유이다. 간단히 몇 바이트의 명령으로 동일한 결과를 볼 수 있다면, 네트워크나 SQL 서버의 메모리 효율을 더 높여줄 수 있을 것이다. [리스트1]의 두 가지 코드는 동일한 역할을 수행하는 Northwind 카탈로그에 존재하는 CustOrderHist 라는 저장 프로시저와 저장 프로시저의 실제 코드를 비교한 것이다.
모두, 해당 고객 아이디를 인자로 받아서, 해당 고객의 주문 내역을 추적하는 역할을 수행한다. 하지만, 첫 번째 저장 프로시저는 서버로 24 바이트의 명령을 전송하면 되고, 두번째 Ad-hoc 쿼리는 그 보다 10배 많은 238 바이트의 명령을 전달해야만 한다.
<리스트 1> 저장 프로시저와 임시 쿼리 비교
-- 1. Stored Procedure, 24 Bytes
dbo.CustOrderHist "ANTON"

-- 2. Ad-hoc Query, 238 Bytes
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = "ANTON"
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
라운드 트립(Round-trip이 적다)
라운드 트립이라는 말은 네트워크 단에서 자주 사용되는 것이기는 하지만, 일반적으로 서버와의 통신이 자주 일어나는 것을 의미한다. 즉, 클라이언트가 요청하고 서버가 응답한 후에 클라이언트가 다시 서버에 요청하고 받은 응답에 따라서 다시 요청을 하는 것이 마치 패킷들이 여행하는 듯 하다 하여 사용된다.

<그림 1> 라운드 트립


데이터베이스에도 이러한 라운드 트립이 발생 할 수 있는데, 이는 전적으로 클라이언트 코드가 잘못 작성된 경우가 그러하다. 원하는 답을 얻기 위해서 한번의 쿼리로 해결하는 것이 아니라, 어떤 경우에는 여러 번의 질문을 던져야만 원하는 결과를 찾을 수도 있다. 그렇다면, 여러 번의 데이터 왕복이 서버와 클라이언트 간에 발생하게 되며, 이것은 서버의 부하를 더 가중 시키는 역할을 수행하게 된다. 그다지 좋은 예는 아니지만, 다음의 쿼리를 보면 대충은 이해가 갈 것이다.
다음의 예는 "Antonio Moreno"라는 고객의 최근 주문된 상품 정보를 알아내기 위해서 총 세 번의 질문을 던져서 결과를 받아내는 과정을 시뮬레이션 한 것이다. 물론, 하나의 쿼리로 변경도 가능하지만, 예를 위해서 일부러 풀어놓았다. 각각의 질문들이 클라이언트가 서버로 요청한 것이며, 서버는 적절한 리턴 값을 던지게 된다. 주문 테이블이 CustomerID 기반으로 되어 있으므로, 이 정보를 알기 위해서 첫 번째 질문을 던졌고, Order Details 테이블이 주문 번호인 OrderID 기준으로 되어 있으므로, 두 번째 질문으로 최근 주문 번호를 알아내었다. 이제 최종 질의를 던져서 문제를 해결하였다.
<리스트 2> SQL에서 발생 가능한 라운드 트립의 예
declare @CustomerID varchar(10)
declare @OrderID int

-- 첫 번째 질문
select @CustomerID = CustomerID from customers where ContactName = "Antonio Moreno"
-- 두 번째 질문
select @OrderID = max(orderid) from Orders where customerid = @CustomerID
-- 세 번째 질문
select * from [Order Details] where OrderID = @OrderID
<그림 2> 라운드 트립 예제 01.


총 여섯 번의 네트워크 트래픽이 발생했다. 데이터베이스에서의 라운드 트립의 경우는 이 보다 더 심한 경우를 심심찮게 본다. 예를 들어서, 루프(Loop)를 돌면서 쿼리를 날리는 경우가 그러하다. 이에 반해 스토어드 프로시저는 단 한번의 네트워크 요청과 응답만 있으면 된다. 서버 상에서 프로세싱이 가능하므로, 클라이언트에 응답을 내리는 것은 최종적인 한번만 있으면 된다. 모든 처리는 서버 상에서 요청 및 처리된다. 우선 저장 프로시저를 만들어 보자.
<리스트 3> ExCustOrderDetails 작성
create proc ExCustOrderDetails
@ContactName varchar(30)
as
declare @CustomerID varchar(10)
declare @OrderID int

select @CustomerID = CustomerID from customers where ContactName = @ContactName
select @OrderID = max(orderid) from Orders where customerid = @CustomerID
select * from [Order Details] where OrderID = @OrderID
go
이제 다음 명령만 내리면 된다. 그럼 서로간에 자주 왕.복.해야 할 일은 없을 것이다.
<리스트 4> ExCustOrderDetails 실행
dbo.ExCustOrderDetails "Antonio Moreno"
저장 프로시저는 컴파일 된다.
위의 두 가지 장점들이 네트워크에 관련된 장점들이었다면, 저장 프로시저가 컴파일 된다는 것은 SQL 서버 내부에서 발생되는 장점이 된다. 다른 프로그래밍 언어들과 마찬가지로, 저장 프로시저도 SQL 서버에 저장될 때 컴파일 되게 된다. C#과 같은 프로그래밍 언어가 컴파일 된다는 것은 바이너리나 P-Code로 변환된다는 것을 의미하지만, SQL 서버에서 저장 프로시저가 컴파일 된다는 것은 실행 계획을 저장한다는 것을 의미한다. 하지만, 컴파일링이라는 본연의 의미가 대상이 되는 플랫폼에서 해석 가능한 형태로의 변환을 의미한다면, 두 경우는 동일한 것이다.
저장 프로시저가 컴파일 된다는 것은 필연적으로 상당히 여러 가지 이슈들을 다루게 된다. 예를 들어서, 일반 배치 명령이나 저장 프로시저, 트리거들이 어떻게 컴파일 되고 캐시에 저장되느냐? 어떻게 실행되느냐? 최적화에 관련된 문제들, 등등이 이 범주에 속하게 된다.
계속해서 정의를 내리고, 각 단계들을 분석해 보자. SQL 서버가 어떤 대상을 컴파일(Compile) 한다는 것은 컴파일 된 플랜(Compiled plan)을 생성한다는 것이다. 여기에는 Ad-hoc 배치 쿼리나, 저장 프로시저, 그리고 내부적으로는 프로시저와 동일한 트리거가 포함된다.
컴파일링의 최종 목적은 쿼리 플랜(Query plan) 혹은 실행 계획(Execution plan)을 생성하는 것이다. 이를 위해서 SQL-Manager는 프로시저 소스의 문법적 오류를 파악하고, 코드의 정규화 과정을 거치게 된다. (이 정규화는 DB 모델링의 정규화가 아니라, 정규화된 트리(Normalized Tree) 형태로 코드를 계층화 시키는 것을 의미한다. 즉, 쿼리 실행 계획에서 볼 수 있는 트리 형태를 연상하면 된다.) 다음에 SQL-Manager는 옵티마이저를 동원해서 절차적인 코드가 아닌 쿼리들에 대해서 최적화된 플랜을 탐색한다. 최종적으로 실행 계획이 완성되면, 이는 캐시에 저장되어 다시 이용될 수 있다.
이 과정에서 몇 가지 고민해야 할 사항들이 있다. 컴파일링의 어느 단계에서도 코드에서 사용되는 어떤 개체들이 실제 존재하는가에 대한 검사 작업은 이루어지지 않는다. 이러한 작업은 실행 시에 이루어지도록 디자인되어 있다. 저장 프로시저는 뷰 등에서 지원되는 스키마 바인딩 옵션이 없다. 뷰는 어떤 특정한 개체들(테이블들)에 의존적으로 설계되어 있고, 부모가 되는 개체와 의존성을 설정하도록 강제하기 위해서 스키마 바인딩을 지원하고 있다.
하지만, 프로시저는 그 내부에서 새로운 개체를 만들어 낼 수도 있고, 참조되는 개체가 매우 많거나 이 개체의 참조가 동적으로 이루어지므로, 컴파일링 단계에서 이에 대한 체크를 한다는 것 자체가 매우 무의미하다.
다른 하나는 프로시저의 실행 계획이 매우 고정적이라는 것이다. 이후의 프로시저 재컴파일링 이슈에 대해서다루면서 보다 자세히 설명하겠지만, 프로시저의 입력 파라메터나 기타 인덱스나 분포 통계 상의 변화와는 무관하게, 처음 생성된 실행 계획이 고정되어 이용하게 되어 있다. 이것은 어떤 경우에는 매우 큰 이점으로 다루어지지만, 어떤 경우에는 Ad-hoc 프로시저를 실행하는 것 보다 못할 수도 있다.
어쨌거나 저장 프로시저가 컴파일 되어서 실행 계획이 캐싱 된다는 것은 저장 프로시저를 이용하게 되는 가장 첫 번째 이유가 될 것이다.
기타 이유들
저장 프로시저를 이용하는 가장 큰 세가지 이유들을 이미 설명했다. 이외에도 저장 프로시저를 이용할 때 발생하는 여러 가지 장점들이 있다. 예를 들어 저장 프로시저를 이용하면 보안에 도움을 줄 수 있다. 왜냐하면, 저장 프로시저는 테이블로의 엑세스(Access)를 우회할 수 있기 때문이다. 테이블로의 직접적인 엑세스인 INSERT,UPDATE,DELETE를 제한하고 저장 프로시저를 통해서만 접근하도록 할 수 있다.
다른 이유는 저장 프로시저로 비지니스 로직을 반영할 수 있다는 것이다. 또한, 하나의 모듈 단위로 작성된 저장 프로시저의 경우, 다른 프로시저들에서 재사용이 가능한 부분도 있다. 이러한 장점들이 나타나는 이유는 저장 프로시저가 일종의 프로그래밍으로 그 코드 내에서 흐름과 제어가 가능하다는 데에 이유가 있으며, 이것이 일반적으로 프로그램 코드 상에서 구현되었던 로직을 저장 프로시저에서도 구현하는 것이 가능하다는 데에 있다.
그리고, 저장 프로시저 또한 일종의 서브루틴이나 함수와 같은 모듈이므로, 각 기능 단위 별로 최소한으로 분리시키는 것이 코드의 재사용성을 높일 수 있게 된다.
출처명 : WINDOWS & .NET[2004년9월호]

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters