왜 저장 프로시저를 사용하는가?

동적 SQL이 실제로 무엇인가를 살펴보기 전에, 왜 저장 프로시저를 사용해야 하는지를 먼저 알아볼 필요가 있다. 저장 프로시저를 사용하지 않고 클라이언트(※ 역주: 여기서의 클라이언트는 SQL 서버에 대한 클라이언트를 의미합니다. 프로그램 개발의 경우 웹서버가 여기에 해당하며, 클라이언트 코드는 ASP와 같은 Server Side Script를 의미합니다.) 혹은 (COM+와 같은) 중간층(middle layer)에서 직접 SQL 문으로 이루어진 명령문을 내보내는 복잡한 응용프로그램을 작성할 수도 있다. 세련되어 보이기 때문에 저장 프로시저를 사용하는 것은 아니며, 이에는 분명 장점이 존재한다.

1. 권한 체계 (The Permission System)

저장 프로시저는 사용자들에게 데이타에 대한 제한적인 접근을 허용케하는 전통적인 수단이다. 쿼리 분석기와 같은 도구를 이용할 경우 원하는 어떠한 작업도 수행가능하므로, 사용자들은 SELECT, INSERT, UPDATE 혹은 DELETE 같은 문장을 직접 실행할 수 있는 권한을 가져서는 안된다. 예를 들어, 권한을 가진 사용자가 직원 데이타베이스에 접근하게 될 경우, 쉽게 봉급을 인상시킬 수 있다. 저장 프로시저를 사용하면, 해당 프로시저의 실행은 프로시저 소유자의 권한을 이용하게 되므로, 사용자들은 테이블에 대한 직접적인 권한을 필요로 하지 않게 된다.

요즘은 이 상황에 대해 몇가지 선택 가능한 사항이 더 존재한다. 사용자 계정에 직접 권한을 부여하기 보다는, 비밀번호가 필요한 고정 서버 역할(application role)에 접근권한을 부여할 수 있으며, 그런 비밀번호를 응용프로그램 안에 숨겨둘 수 있다. SQL 서버에서 고정 서버 역할은 7.0 버전부터 지원되었으며, COM+와 같은 중간층을 사용하면 사용자가 SQL 서버에 직접 접근할 필요가 없다는 점에서 더 안전하다.
하지만 고정 서버 역할이나 COM+ 등을 사용하지 않는다면, SQL 서버 보안 측면에서 저장 프로시저는 여전히 중요한 의미를 가진다.

2. 실행계획 캐쉬 (Caching Query Plans)

저장 프로시저를 사용하는 다른 중요한 이유는 성능을 향상시키기 위해서이다. 저장 프로시저가 최초로 실행되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성시키며, 이 실행계획이 캐쉬에 저장된다. 해당 저장프로시저가 재실행 요청을 받으면, SQL 서버는 저장된 실행계획을 재사용한다. 실행계획이 만료되거나 혹은 SQL 서버가 새로운 실행계획을 생성시켜야 할 이유가 생길 때까지 해당 실행계획은 캐쉬에 유지된다. (이 과정은 프로시저가 실행되는 동안에 일어나는데, 여기에 대한 논의는 이 기사의 범위를 범어난다.)
SQL 서버는 저장 프로시저 외부에서 실행되는 SQL 문장들에 대한 실행계획도 캐쉬한다. 자동 매개 변수화 과정(auto-parameterization) 또한 수행되어, 만약 다음과 같은 문을 실행시킬 경우, :
SELECT * FROM pubs..authors WHERE state = 'CA' go SELECT * FROM pubs..authors WHERE state = 'WI'
SQL 서버가 쿼리를 다음과 같이 캐쉬하므로, 2번째 Select 문장은 첫번째 문장의 실행계획을 재사용한다.
SELECT * FROM pubs..authors WHERE state = @1
SQL 문장이 좀더 복잡해 질 경우에는, SQL 서버가 자동 매개 변수화에 실패할 수도 있다. 심지어 유사한 쿼리문에서 공백 문자(white space)의 차이로 인해 SQL 서버가 캐쉬에서 해당 문장을 찾는데 실패하는 경우를 본 적도 있다. 결론은 SQL 서버가 순수 SQL 문장을 캐쉬하는데에도 좋은 성능을 발휘하지만, 저장 프로시저를 사용하는 경우 쿼리 실행계획의 재사용 확률이 더 높다는 것이다.
작고 짧은 실행시간을 가지며 자주 실행되는 SQL문 혹은 저장 프로시저의 경우에 캐쉬는 더욱 중요한 의미를 가진다. 실행계획을 만드는데 500ms 가 소요되면 상당한 과부하가 될 수도 있기 때문이다. 반면에, 20분동안 실행되는 프로시저의 실행계획을 세우는데 3초가 소요되는 것은 대단한 문제가 아니다. 만약, 아직도 SQL 6.5를 사용하고 있다면, 해당 버전의 SQL 서버는 순수한 SQL 문에 대한 캐쉬를 하지 않으며, 저장 프로시저에 대한 실행계획만을 캐쉬에 저장한다는 사실을 알아야 한다.

3. 네트워크 소통량 최소화 (Minimizing Network Traffic)

이것 역시 성능문제이다. 50줄 이상의 복잡한 Select 문장이 있으며, 각 실행시마다 Where 절 조건문이 조금씩 변한다고 가정해보자. 이 문장을 저장 프로시저에 넣을 경우, 네트워크를 통해 전달되는 데이타 소통량이 상당히 감소하게 되며, 해당 프로시저가 자주 실행될 수록 성능향상 효과가 증대된다.
서로 연관성을 가진 여러개의 SELECT / INSERT / UPDATE 문장으로 구성된 업무규칙이 존재할 경우 이러한 효과는 더욱 커진다. 저장 프로시저를 사용하면, 서버내부에서 이동하는 모든 데이타를 임시 테이블 혹은 변수에 저장할 수 있게 된다. 만약 순수 SQL 문장을 이용한다면, 서버와 클라이언트 혹은 중간층 사이에 데이타를 이동시켜야 한다. (사실, 이것이 전적으로 맞는 말은 아니다. SQL 문만을 사용해서도 임시 테이블을 생성시키고 거기에 데이타를 저장할 수 있다. 하지만 Connection pooling과 disconnected record sets에 대한 주의를 기울여야 한다.)

4. 출력매개변수 사용 (Using Output Parameters)

단일 행을 반환하는 SQL 문장을 실행시키고 싶을 경우에, SQL 문만 사용하면 결과 집합(record set)을 레코드셋으로 반환받아야 한다. 하지만, 저장 프로시저를 사용할 경우에는 성능이 월등한 출력매개변수의 사용이 가능하다. 단일 쿼리에 대한 실행시간의 차이는 무시할 수 있겠지만, 만약 다음과 같은 작업을 SQL 서버에 수천번 해야 한다면, 결과 집합으로 값을 받는 것에 비해 @key를 출력매개변수로 반환받는 경우의 이점은 엄청나게 커지게 된다.:
INSERT tbl (...) VALUES (...) SET @key = @@identity

5. 업무규칙 모듈화 (Encapsulating Logic)

이것은 보안 혹은 성능향상과 관련된 것은 아니지만, 코드를 모듈화하는 방법의 하나이다. 저장 프로시저를 사용하면, SQL 문을 만들어내기 위해 클라이언트 코드와 씨름할 필요가 없다. 하지만, 이러한 이유때문에 저장 프로시저를 사용해야 한다고 말할 수는 없다. (SQL 코드가 클라이언트측 주요 언어의 문법에 묻혀 버리기는 하겠지만) 여전히 여러 매개변수들로부터 SQL 문을 조합하는 것이 가능하다.
좀 특별한 경우를 예로 들어 보겠다: 만약 쿼리 분석기 외의 다른 응용프로그램이 없다면, 관리자들을 위한 저장 프로시저를 작성하게 된다는 말이며, 이런 경우에는 저장 프로시저가 업무규칙 모듈화를 위한 유일한 수단이 된다.

6. 의존성 파악 (Keeping Track of what Is Used)

수백개의 테이블이 존재하는 복잡한 시스템에서, 간혹 어디에서 어떤 테이블 혹은 칼럼이 참조되었는지 알고 싶을 때가 있다. 이를테면, 만약 칼럼을 변경할 경우에 어떤 일이 일어날지 알고 싶은 경우가 있을 수 있다. 만약 모든 코드가 저장 프로시저에 보관되어 있다면, 참조된 개체를 찾기 위해 저장 프로시저의 코드만 살펴보면 된다. 또는 간단히 변경하고자 하는 칼럼 혹은 테이블을 누락시킨 데이타베이스를 생성시켜 어떤 일이 일어나는지 관찰할 수도 있다. 시스템 테이블sysdepends와 시스템 저장 프로시저 sp_depends를 이런 목적에 사용할 수도 있지만, sysdepends내에 보관된 정보를 온전히 정확하게 유지시키기는 어렵다.
응용프로그램에서도 순수 SQL 문을 사용가능하도록 허용 한다면, 문제는 더욱 심각해진다. 훨씬 많은 양의 코드를 살펴봐야 하며, status와 같은 일반적인 이름을 가진 칼럼들은 놓치기도 쉽다. 그리고 sysdepends는 완전히 무의미하게 된다.

출처 - http://egloos.zum.com/bowwowmew/v/593664