저장 프로시저

저장 프로시저

• SQL Server에서 제공되는 프로그래밍 기능이라고 생각하면 된다. 일반적인 프로그래밍과는 조금 차이가 있지만, SQL SERVER 내부에서 사용하기에는 아주 적절한 방식을 제공해준다.
• SQL SERVER도 낯선 쿼리문을 만나면 어떻게 수행해하는 것이 최선의 방법인지 고민하는 과정이 필요하다. 이 과정을 CPU가 담당하는데 계속해서 낯선 쿼리문이 수행되면 CPU의 부담이 클 수밖에 없다. CPU의 바람은 "항상 똑같은 쿼리문이 수행되면 좋겠다"인데 이 바람을 만족시켜주는 것이 저장 프로시저다.

저장 프로시저에 대한 이해

Transact-SQL 이나 Managed Code(. Net 코드 등)로 구현할 수 있는 저장 프로시저는 다른 프로그래밍 언어의 프로시저나 메서드(Methods)와 비슷하다. 입력 매개 변수, 출력 매개 변수 그리고 리턴 값을 가질 수 있으며, 데이터 변경 처리는 물론, 조회된 결과를 반환할 수도 있다.

 

저장 프로시저의 이점

1. 캡슐화의 이점 : 저장 프로시저 로직이 변경되어도 매개 변수 형태에 변화가 없다면 사용자와 응용프로그램은 영향을 받지 않을뿐더러, 변경된 로직을 즉시 적용받을 수 있다.
2. 성능의 이점 : 저장된 실행 계획을 재사용함으로써 CPU의 사용을 절약하고, 구문 분석, 이름 확인, 그리고 최적화에 걸리는 시간을 단축할 수 있다.
3. 네트워크 트래픽 최소화 : 서버로 전송하는 쿼리문의 문자열을 줄여 네트워크 트래픽을 줄일 수 있다. 상당한 길이의 쿼리문 대신 저장 프로시저 이름과 매개 변수만 사용하기 때문이다.
4. 보안 계층으로 사용 : 개체에 접근할 권한을 직접 부여하지 않고 저장 프로시저 실행 권한만 부여하여 개체에 대해 불필요한 접근을 제한할 수 있다. 예를 들어, dbo.Employee 테이블에 Salary 열이 있지만, 호출하는 저장 프로시저가 Salary 열의 값을 넘겨주지 않으면 Salary 열의 값을 볼 수 없다. 저장 프로시저가 넘겨주는 값만 볼 수 있다.

일반 sql 문 

 

일반 sql 문이 처음 실행되는 과정

쿼리문 문법을 검사한 후 불필요한 구문을 제거해서 표준화하고, 사용자가 해당 쿼리문을 실행할 권한이 있는지 확인한다. 그리고 실행 계획을 만드는 컴파일 과정을 통해 메모리(이 메모리를 플랜 캐시라고 함)에 저장한 후 구문을 실행한다.

 

해당 쿼리문이 다시 실행 될 때. 실행 계획이 메모리에 존재하면 즉시 실행되지만, 그렇지 않으면 처음 실행할 때와 같은 과정으로 실행된다. 일반 sql 문은 구문이 조금만 달라도 다른 쿼리문으로 인식되어. 실행 계획이 재사용될 가능성이 크지 않다. 결국, 빈번한 쿼리 컴파일로 인해 CPU와 메모리 사용량을 높인다.

 

저장 프로시저

저장 프로시저는 우선 저장 프로시저가 만들어지는 단계가 진행되어야 한다.

 

저장 프로시저 구문 문법을 검사하고, 사용자가 저장 프로시저를 만들 권한이 있는지 확인한다. 저장 프로시저가 참조하는 개체가 없어도 아무런 문제 없이 저장 프로시저가 만들어진다. 이런 특징은 '지연된 이름 확인(Deferred Name Resolution)'이라 한다. 완성된 저장 프로시저 정보는 시스템 테이블에 저장된다.

저장 프로시저가 만들어진 후 처음으로 실행될 때는, 저장 프로시저가 참조하는 개체가 있는지를 먼저 확인한다. 이어서 사용자가 저장 프로시저를 호출할 권한이 있는지 확인하고, 전달된 매개 변수로 저장 프로시저를 수행할 최적의 실행 계획을 만들어 컴파일 과정을 통해 메모리에 저장한 구문이 실행된다.

저장 프로시저가 다시 호출될 떄는 메모리에 실행 계획이 있으면 즉시 실행되고, 그렇지 않으면 처음 실행할 때와 같은 과정으로 실행된다. 저장 프로시저는 매개 변수가 변할 뿐 구문이 변하지 않으므로 (물론 구문이 변하는 저장 프로시저도 있다). 저장된 실행 계획을 재사용할 가능성이 크다. 이로 인해 CPU와 메모리 사용량을 줄일 수 있는 이점이 있다. 컴파일 단계가 없으니, 그만큼 실행 시간도 단축된다. 이러한 점들이 저장 프로시저를 사용하는 이유다.