[MSSQL] 저장 프로시저의 개요

스토어드 프로시저(Stored Procedure) 란?

일련의 쿼리를 마치 하나의 함수 처럼 실행하기 위한 쿼리의 집합이다.

DB에 대한 작업을 정리한 절차를 RDBMS(관계형 데이터 베이스 관리 시스템)에 저장한 쿼리의 집합이다. 영구저장모듈이라고도 불린다. 여러 쿼리를 하나의 함수로 묶은 것이다.

• SQL Server에서 제공되는 프로그래밍 기능. 쿼리문의 집합
• 어떠한 동작을 일괄 처리하기 위한 용도로 사용.
• 자주 사용되는 일반적인 쿼리를 모듈화시켜서 필요할 때마다 호출
• 테이블처럼 각 데이터베이스 내부에 저장

일반 쿼리문, 저장프로시저의 작동방식 비교

일반 쿼리문 작동방식

위의 그림은 일반적인 쿼리문을 최초 실행시에 동작하는 프로세스이다.
예를들어 아래와 같은 쿼리를 작성하여 실행한다고 가정하자.

SELECT name FROM user;

구문분석
입력된 sql 쿼리문을 분석하여 오류가 없는지 검사한다.
만약 오타가 존재할시, 해당 단계에서 에러메세지가 출력된다.

개체 이름 확인
user라는 테이블이 현재 데이터베이스에 있는지 확인을 한다.
존재한다면, 해당 테이블에 SELECT 한 칼럼이 있는지 확인할 것이다.

사용권한 확인
현재 쿼리를 수행하는 주체인 사용자가 해당 테이블에 접근할 권한이 있는지 확인한다.

최적화
해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 인덱스 사용여부에 따라 경로가 결정된다고 보면된다.

컴파일 및 실행계획 등록
다음은 최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 해당 실행계획 결과를 메모리(캐시)에 등록한다. 그리고 컴파일된 결과를 실행한다.

단 한줄의 쿼리를 실행을 할 때에도 이렇게나 많은 절차를 가지게 된다.
만약 최초로 해당 쿼리를 실행한 후, 재실행을 하게되면 아래 프로세스처럼 동작하게 된다.
여러 과정이 생략되다 보니 시간이 단축되게 될 것이다.

여기서 중요한점은 만약에 메모리(캐시)에 쿼리 전체가 한글자도 틀리지 않고 같아야 한다는 것이다.

실제로 업무 중에, 데이터를 추출하는 작업을 할 때에 수백만 개의 로우 데이터를 조회하는 질의를 작성 후 실행하였을 때 5분이라는 시간이 걸렸지만, 추출이 완료되고 다시 실행하였을 때 몇 초도 안 걸렸던 경험이 생각이 난다. 또한, 칼럼 명의 별칭만 다르게 했을 뿐인데 다시 몇 분이 소요되기도 하였다.

저장프로시저

저장프로시저의 정의

먼저 저장 프로시저를 정의 했을때 작동방식을 알아보자.

1. 일단 먼저 해당 저장 프로시저에서 구문 오류가 있는 지를 파악하는 과정을 거친다.

2. 다음은 지연된 이름 확인(deferred name resolution) 과정을 거치게 되는데 이는 저장 프로시저의 특징중 하나이니 잘 기억해두자. 추가로, 저장 프로시저를 정하는 시점에서 해당 개체(ex. 테이블)가 존재하지 않아도 상관없다. 프로시저 실행 당시에 테이블 존재 여부를 확인한다. 그런데 테이블의 열이름이 틀리면 오류가 발생된다.

3. 다음은 생성권한은 확인하는 단계인데 사용자가 저장 프로시저를 생성할 권한이 있는지를 확인하는 과정이다.

4. 마지막으로 시스템 테이블 등록을 진행한다. 저장 프로시저의 이름과 코드가 관련 시스템 테이블에 등록되는 과정이다.

저장프로시저의 첫 실행

구문분석 단계가 빠지는 것만 빼면 일반적인 쿼리문 수행단계와 동일하다. 일단 정의 단계에서 구문분석은 끝났기때문에 따로 구문분석을 하지 않는다.앞서 정의시에 지연된 이름 확인이란게 있었는데, 실제로 해당 개체가 유효한지를 개체이름 확인 단계에서 진행하게 된다. 다시 말해서 저장 프로시저의 실행 시에만 해당 개체가 존재하면 실행이 된다. 추가로, 저장프로시저의 정의할때 생성권한을 확인했다면 실행시에는 사용권한을 확인하는 권한의 분리가 이루어진다.

이후의 저장프로시저의 재실행

이후에 두번째 실행 부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 많이 단축한다.

주의사항
그렇다면 저장 프로시저는 과연 만능의 성능을 자랑할까? 대부분의 경우에는 성능이 향상되나 항상 그렇지는 않다.
저장 프로시저의 첫 실행시 최적화 단계를 수행할때 인덱스를 사용할지 안할지를 결정하게 되는데, 인덱스를 사용한다고 항상 수행결과가 빨라지지 않는다.

만약에 가져올 데이터가 다량인데 인덱스를 사용하면 오히려 성능이 바빠지게 될 것이다.
저장 프로시저는 첫번째 수행 시에 최적화가 이루어져서 인덱스 사용여부가 결정되어 버린다.
만약에 첫번째 수행때 데이터를 몇건만 가져오도록 파라미터가 설정되어 있다면, 인덱스를 사용하도록 최적화되어 컴파일 됐을 것이다.

그런데 두번째 수행에서 많은 건수의 데이터를 가져오도록 파라미터가 들어가면..?
일반 쿼리문이었다면 파라미터가 달라졌으니 다시 최적화되어 컴파일 되겠지만 저장 프로시저는 그냥 인덱스를 사용하는 프로시저를 실행시켜 버릴 것이다.

저장프로시저의 장단점

장점

서버/클라이언트 네트워크 트래픽 감소

프로시저의 명령은 단일 일괄 처리 코드로 실행된다. 따라서 프로시저를 실행할 호출만 네트워크에서 전송되기 때문에 서버와 클라이언트 간의 네트워크 트래픽이 크게 줄어들 수 있다.
즉, 저장 프로시저를 쓰게 된다면 단 한 번의 요청으로 여러 SQL문을 실행할 수 있으므로, 네트워크에 대한 부하를 줄일 수 있다.

보안 강화

여러 사용자 및 클라이언트 프로그램이 기본 데이터베이스 개체에 대한 직접적인 사용 권한이 없는 경우에도 프로시저를 통해 이러한 기본 개체에 대해 작업을 수행할 수 있다. 프로시저는 수행되는 프로세스 및 작업을 제어하고 기본 데이터베이스 개체를 보호한다. 따라서 개별 개체 수준에서 사용 권한을 부여할 필요가 없으며 보안 계층이 간소화된다.

즉,사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있다. 실제 테이블에 접근하여 다양한 조작을 하는 것은 위험하기 때문에 실무에서는 실제로 개발자에게는 sp권한만 주는 방식을 많이 사용한다고 한다.

성능 향상

기본적으로 프로시저는 처음 실행될 때 컴파일되며 이후 실행에 다시 사용되는 실행 계획을 만든다. 쿼리 프로세서는 새 계획을 만들 필요가 없으므로 일반적으로 프로시저 처리 시간이 줄어든다.

프로시저에서 참조하는 테이블이나 데이터가 크게 변경된 경우에는 미리 컴파일된 계획으로 인해 실제로 프로시저 실행 속도가 느려질 수 있다. 이 경우 프로시저를 다시 컴파일하고 새 실행 계획을 강제 적용하면 성능을 향상시킬 수 있다.

유지보수 및 재활용 측면에서 좋다.

C#, Java등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수정요건이 발생할때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해진다.

또한 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.

단점

낮은 처리 성능

• 문자, 숫자열 연산에 SP를 사용하면 오히려 c, java보다 느린 성능을 보일 수 있다.

유지보수

• 개발된 프로시저가 여러 곳에서 사용 될 경우 수정했을 때 영향의 분석이 어렵다(별도의 Description 사용).
• 배포, 버전 관리 등에 대한 이력 관리가 힘들다.
• APP에서 SP를 호출하여 사용하는 경우 문제가 생겨도 해당 이슈에 대한 추적이 힘들다(별도의 에러 테이블 사용).