MSSQL : 저장 프로시저

---

1. 정의
데이터베이스 프로그램

2. 저장 프로시저의 이점

1) 캡슐화

저장 프로시저 로직이 변경되어도 매개 변수 형태에 변화가 없다면 사용자와 응용프로그램은 영향을 받지 않고 로직을 즉시 변경 받을 수 있음 

만약 프로시저가 실행되어 데이터를 처리하는 과정 중,  다른 이벤트가 중복으로 일어나 가비지 데이터가 생겨도 그 안에 ROLLBACK해주는 쿼리를 넣으면 데이터 검증이 가능

2) 성능 

저장된 실행계획을 사용함으로써 CPU를 절약하고 최적화에 걸리는 시간을 단축할 수 있음

3) 보안계층으로 사용 : 개체에 접근할 권한을 직접 부여하지 않고, 저장 프로시저 실행 권한만 부여하여 개체에 대한 불필요한 접근을 제한할 수 있음, 권한부여시 실행에 대한 결과도 당연히 권한을 가진 사람만 볼 수 있음

3. 일반 SQL문과 저장 프로시저의 처리과정

 

1) 일반 SQL문

① 구문분석 : SYNTAX(구문)체크, 문법이 올바른가 또는 그 테이블이나 컬럼이 있는지 먼저 확인

(쿼리문 실행시 메시지에 '구문이 잘못되었습니다' 같은게 뜬 경우 여기서 처리된 것)

② 표준화 : 각 개체들의 이름을 확인하고 불필요한 구문을 제거함 (ansi쿼리로 변환)

③ 보안점검 : 구문을 실행한 사용자의 권한을 확인함

④ 최적화 : 가장 빠른 성능을 위해 색인, 조인, 잠금 등을 결정하여 최적화된 실행계획을 만듦 (옵티마이징)
*이 때 색인 조인 등을 결정하는 요소는 조건문, 최적화 과정에서 실행계획이 별도의 메모리(캐시)에 저장됨

⑤ 컴파일 : 실행계획을 컴파일 한 후 캐시(캐시에 저장될 수 있는 실행계획은 한정되어 있어서, 최근에 사용된 실행계획이 상단으로 오게됨, 그래서 자동으로 사용되지않은 실행계획은 삭제됨)에 등록

⑥ 실행 : 컴파일 된 구문을 실행
* 일반 SQL문은 구문이 조금만 달라도 다른 쿼리문으로 인식되어, 실행 계획이 재사용 될 가능성이 높지 않음

결국 빈번한 쿼리 컴파일로 인해 CPU와 메모리 사용량을 높임

2) 저장 프로시저
① 구문분석 : 키워드를 분리하고 문법을 검사함

② 지연된 이름확인 : 저장 프로시저를 만들 때 해당개체의 이름이 존재하는지 확인하지 않음

③ 보안점검 : 저장 프로시저를 만들 수 있는 권한이 있는지 확인

④ 결과저장 : 시스템 테이블에 개체의 정보를 저장

(* 일반적인 과정일 뿐 모든 쿼리와 모든 프로시저가 이렇게 동작한다는 것은 아님 ! )

4. 재사용
매개변수(파라미터)화된 쿼리를 짠다면 사용계획을 재사용할 수가 있음

1) 상수값(VALUE값)이 바뀌는 경우 계속 새로운 실행계획을 만들어서 재사용 불가
2) 쿼리 작성법에 따라 재사용에 대한 처리를 다르게 해 줄 수 있음 (일반쿼리에서도 실행계획 재사용 가능)