저장프로시저

저장 프로시저는 DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다. 저장프로시저를 사용하면, 두번째 실행부터는 캐시(메모리)에 있는 것을 가져와서 사용하므로 속도가 빨라지며, 여러개의 쿼리를 한번에 실행할 수 있다는 장점이 있습니다. 또한, 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하면, 수정이 필요할 때 SP파일만 변경하면 되기 때문에 유지보수가 유리합니다.
반면 단점으로는 DB확장이 어렵고, 프로시저가 여러 곳에서 사용될 경우 수정했을 때 영향이 어렵다는 점이 있습니다.

개념 정리

구문분석
- 구문 자체에 오류가 없는지 분석함
- 구문 분석 트리 생성
- 오류가 있으면 구문 오류 반환
개체 이름 확인
- 해당 테이블이 현재 db에 있는지 확인, 만약 있다면 그 안에 해당 열이 있는지 확인
사용 권한 확인
- 해당 테이블에 현재 사용자가 접근 권한이 있는지 확인
최적화
- 쿼리 옵티마이저가 최적의 실행 계획을 생성함 → 비용기반 혹은 규칙기반으로 작동
- 인덱스 사용 여부, 조인 순서 (Join Order), 필터 조건 최적화를 고려하여 가장 효율적인 방법 선택
- ex) 전체 데이터를 가져오는 쿼리의 경우 테이블 스캔이나 클러스터 인덱스 스캔을 사용
컴파일 및 실행 계획 등록: 해당 실행 결과를 메모리에 등록
실행

지연된 이름 확인: 저장 프로시저를 정하는 시점에서 해당 개체(ex. 테이블)가 존재하지 않아도 상관없음
- 프로시저 실행 당시에 테이블이 존재하는지를 확인함
- 없는 테이블이 프로시저 정의에 사용되지 않았는지 실수를 주의해야함
생성권한 확인: 현재 사용자가 저장 프로시저를 생성할 권한이 있는지 확인
시스템 테이블 등록: 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록

→ 동일한 저장 프로시저가 다시 실행될 경우, 새로 최적화할 필요 없이 기존 계획을 재사용

MySQL: stored procedure의 코드를 compile하여 메모리 영역에 캐시로 저장한 후 stored procedure를 실행함
- 동일한 session에서 같은 stored procedure를 재호출 하면 MySQL은 기존에 있는 캐시로 실행
MSSQL: 플랜 캐시를 먼저 확인:
- 동일한 입력 파라미터일 경우: 기존의 실행 계획을 바로 사용
- 다른 입력 파라미터인 경우: 파라미터화된 계획 (Parameterized Plan)이 존재하면 그대로 사용
- 새로운 계획 생성이 필요할 경우 새로 컴파일됨(캐시미스)

SQL Server의 성능 향상
- 여러개의 쿼리를 한번에 실행
- 캐시에 있는 것을 가져와 사용하므로 속도가 빨라짐
- 쿼리를 쓸 때마다 옵티마이저가 구문을 분석하고 실행 가능한 코드로 바꿀려면 많은 비용이 드는데, 이 비용을 없앨 수 있음
유지보수 및 재활용 측면
- 개발 언어에 비의존적: 저장 프로시저는 DBMS 내에서 직접 실행됨
- 응용프로그램에서 직접 SQL문을 호출하지 않고 SP를 호줄하도록 설정하여 사용하는 경우가 많음 → SP 파일만 수정하면 되기 때문에 유지보수에 유리함
보안 강화
- 사용자 별로 테이블에 권한을 주지 않고, SP에만 접근 권한을 주는 방식
네트워크 부하 줄일 수 있음
- 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생
- 저장 프로시저를 사용하면 저장프로시저의 이름, 매개변수 등 몇글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있음

DB확장 어려움
- 서버의 수를 늘려야할 때, DB의 수를 늘리는 것이 어려움
- DB 교체는 거의 불가능함
데이터 분석의 어려움
- 개발된 프로시저가 여러 곳에서 사용 될 경우 수정했을 때 영향의 분석이 어려움(별도의 Description 사용)
- 배포, 버전 관리 등에 대한 이력 관리가 힘듦
- APP에서 SP를 호출하여 사용하는 경우 문제가 생겨도 해당 이슈에 대한 추적이 힘듦(별도의 에러 테이블 사용)
낮은 처리 성능
- 문자, 숫자열 연산에 SP를 사용하면 오히려 c, java보다 느린 성능을 보일 수 있음

출처

https://eunsun-zizone-zzang.tistory.com/52

Last updated 9 months ago