[MSSQL] T-SQL(Transact SQL) VS Stored Procedure

일반 SQL VS 저장 프로시저

일반 SQL에 비해 저장 프로시저의 속도가 더 빠르다.

- 저장 프로시저는 일반 SQL에 비해 컴파일 및 최적화 하는 횟수가 적다.

(예시 1)

SELECT * FROM userTbl WHERE id = 'LSG';
SELECT * FROM userTbl WHERE id = 'KBS';
SELECT * FROM userTbl WHERE id = 'KKH';

- 위의 3개의 쿼리문은 모두 다른 쿼리문으로 인식하여 한 줄의 쿼리문을 사용할 때마다 컴파일과 최적화 과정을 거친다.

-- 저장 프로시저 생성
CREATE PROCEDURE usp_id
    @id NVARCHAR(10)
AS 
    SELECT * FROM userTbl WHERE userID = @id
 
-- 저장 프로시저 
EXECUTE usp_id 'LSG';
EXECUTE usp_id 'KBS';
EXECUTE usp_id 'KKH';

- 앞에 사용했던 3개의 쿼리문과 같은 작동을 하지만 최초로 저장 프로시저를 사용 할 때 한 번만 컴파일 및 최적화 과정이 발생한다.

이 후 2번의 저장 프로시저 사용시 컴파일 및 최적화 과정이 생략된다.

(예시 2)

SELECT * FROM userTbl WHERE id = 'LSG';
sELECT * FROM userTbl WHERE id = 'LSG';        -- SELECT 맨 앞 알파벳만 소문자이다.

- 위의 2개의 쿼리문은 논리적으로 같으며,  같은 결과를 보여준다. 하지만 맨 앞 알파벳 's'가 다르기 때문에 다른 쿼리문으로 인식하여 컴파일과 최적화 과정이 각각 발생한다.

일반 SQL의 처리 과정

(1) 최초 실행시

(2) 같은 쿼리 실행시

- 구문 분석 : SELECT, FROM 등의 단어에 오류가 없는지 분석, 예약어가 아니거나 오타일 경우에는 여기서 오류 발생하여 더 이상 진행하지 않는다.

- 개체 이름 확인 : 데이터베이스에 테이블이 존재하는지 확인, 존재 한다면 열이 있는지를 확인한다.

- 사용권한 확인 : 현재 사용자가 접근할 권한이 있는지 확인한다.

- 최적화 : 구문을 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 주로 인덱스 사용 여부를 결정한다고 생각하면 된다. (테이블 스캔 or 인덱스 찾기)

- 컴파일 및 실행계획 등록 : 최적화된 결과를 컴파일한다. 그리고 그 결과(실행 계획)를 메모리(캐시)에 등록한다.

저장 프로시저 처리 과정

(1) 최초 실행시

(2) 이미 실행한 저장 프로시저 실행시

- 개체 이름 확인 : 관련 카탈로그 뷰를 통해 저장 프로시저가 존재 하는지 확인

- 사용권한 확인 : 현재 사용자가 접근할 권한이 있는지 확인한다.

- 최적화 : 구문을 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 주로 인덱스 사용 여부를 결정한다고 생각하면 된다. (테이블 스캔 or 인덱스 찾기)

- 컴파일 및 실행계획 등록 : 최적화된 결과를 컴파일한다. 그리고 그 결과(실행 계획)를 메모리(캐시)에 등록한다.

저장 프로시저 생성 과정

- 구문 분석 : 일반 T-SQL과 마찬가지로 해당 구문의 오류 등을 파악, 오류 발생시 더 이상 진행하지 않는다.

- 지연된 이름 확인 : 저장 프로시저의 특징으로 저장프로시저를 정의하는 시점에서 해당 개체(주로 테이블)가 없어도 관계가 없다는 의미이다.

                           그렇지만 이미 테이블이 있다면, 개체를 확인하므로 사용하는 열 이름이 틀리면 안된다. (틀리면 오류 발생)

- 사용권한 확인 : 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인한다.

- 시스템 테이블 등록 : 저장 프로시저의 이름 및 코드가 관련 시스템 테이블에 등록된다.

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)