Skip to content

Latest commit

 

History

History
140 lines (94 loc) · 7.16 KB

저장 프로시저.md

File metadata and controls

140 lines (94 loc) · 7.16 KB

저장 프로시저(Stroed Procedure)

저장 프로시저(Stroed Procedure, SP) 는 일련의 쿼리를 마치 하나의 함수 처럼 실행하기 위한 쿼리의 집합이다.
데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한것으로, 영구저장모듈(Persistent Storage Module)이라고도 불린다.

call_sto

Oracle, MySQL, MSSQL 등 대부분의 DBMS 에서 저장 프로시저 기능을 제공하고 있다.
Oracle의 경우 아래와 같이 프로시저를 생성하고 실행할 수 있다.

CREATE PROCEDURE procedure_name(IN parameter)
  BEGIN
    SQL1;
    SQL2;
  END;
  /

EXECUTE procedure_name(parameter);
CALL procedure_name(parameter);

저장 프로시저 사용에 따른 동작 비교

📄Reference devkingdom

저장 프로시저를 사용하지 않은 경우

저장 프로시저를 사용하지 않고 SQL문을 사용하는 경우 아래와 같이 동작한다.
마이크로소프트의 T-SQL 기준

  1. 구문분석 | 구문 자체에 오류가 없는지 분석한다.
  2. 개체 이름 확인 | SQL 문 내에 사용되는 테이블과 컬럼명이 있는지 등을 확인한다.
  3. 사용 권한 확인 | 현재 접근중인 사용자가 권한이 있는지를 확인한다.
  4. 최적화 | 해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 사실상 인덱스 사용 여부에 따라 경로가 결정된다.
  5. 컴파일 및 실행계획 등록 | 최적화 결과를 바탕으로 실행 계획 결과를 메모리(캐시)에 등록한다.
  6. 실행 | 컴파일된 결과를 실행한다.

  • 이후 동일한 SQL문을 실행하면 메모리(캐시)를 확인하여 바로 실행하게 된다. 여러 과정이 생략되므로 시간이 단축된다.
  • 하지만 변수를 포함한 쿼리 전체가 완전히 동일한 경우 만 해당하며, 차이가 있을경우 처음 실행과 같이 모든 과정을 거친다.

저장 프로시저를 사용한 경우

저장 프로시저를 사용하는 경우 아래와 같이 동작한다.

먼저 저장 프로시저를 생성한다.

  1. 구문분석 | 해당 프로시저에 구문 오류가 있는지 분석한다.
  2. 지연된 이름 확인(deferred name resolution) |
    저장 프로시저가 갖는 특성으로, 사용된 테이블의 존재 여부와 상관 없이 정의가 가능하다. 테이블의 존재 여부를 프로시저의 실행 시점에 확인한다.
    (여기서 특이점은 존재하는 테이블의, 존재하지 않는 컬럼에 대한 참조는 에러를 발생시킨다.)
  3. 생성 권한 확인 | 사용자가 저장 프로시저를 생성할 권한이 있는지 확인한다.
  4. 시스템 테이블 등록 | 저장 프로시저의 이름과 코드를 관련 시스템 테이블에 등록한다.

  • 저장한 프로시저를 첫 번째로 실행했을 때에는 일반 쿼리문을 처음 실행했을 때와 비슷하게 동작한다.
  • 여기서 확인해야할 점은, 저장 프로시저의 지연된 이름 확인에 따른 실제 테이블등의 존재 확인을 이 시점에 진행한다는 것이다.

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

저장 프로시저 적용 예시

SELECT * FROM userTable WHERE name = '최다인'
SELECT * FROM userTable WHERE name = '이도하'
SELECT * FROM userTable WHERE name = '이규현'

위와 같이 파라미터만 달라도 일반 SQL문을 사용하면 다른 쿼리로 인식해 복잡한 과정을 모두 거치게된다.

CREATE PROC select_by_name
	@Name NVARCHAR(3)
AS
	SELECT * FROM userTable WHERE name =@name;
EXEC select_by_name '최다인';
EXEC select_by_name '이도하';
EXEC select_by_name '이규현';

저장 프로시저를 사용하면 첫 실행해서 최적화 및 컴파일을 수행하고, 나머지는 메모리(캐시)에 있는것을 사용하게된다. 자주 쓰는 저장 프로시저를 쓰는것이 성능적인 측면에서 효과적이다.


저장 프로시저의 장점

  1. SQL 서버 성능 향상
    변수만 다른 동일한 쿼리문의 저장 프로시저를 사용하면, 최초 실행시 최적화 및 컴파일되어 캐시(메모리)에 저장되고, 이 후에는 바로 캐시에서 가져오게 되어 빠르다.

  2. 유지보수 및 재사용
    SQL문을 캡슐화하여 코드 재사용 이 가능하고, 수정시에도 응용프로그램의 재배포 없이 저장프로시저만 수정 및 배포 하면 되기 때문에 유지보수가 편리하다. (이에 따른 단점도 있다.)

  3. 보안 향상
    저장 프로시저는 자체적인 보안 설정 기능을 가지고 있어, 사용자별이 아닌 프로그램 단위로 권한 설정이 가능하여 보안을 강화할 수 있다. SQL 인젝션과 같은 기본적인 보안사고 역시 피할 수 있다. Prepared Statement와 유사한 원리이다.

  4. 네트워크 부하 감소
    저장 프로시저를 사용하면 클라이언트가 직접 SQL문을 작성하지 않고, 프로시저 명과 매개변수만을 전달하여 서버에 저장된 여러 SQL문을 사용 할 수 있어 부하를 줄일 수 있다.


저장 프로시저의 단점

  1. 낮은 연산 처리 성능
    스토어드 프로그램은 SQL 엔진에서 해석되고 실행된다. 하지만 본래 절차적 코드 처리를 목적으로 하지 않기 때문에 프로그램 처리 성능이 떨어진다. 따라서 문자열 및 숫자 연산이 포함되었을 경우 적합하지 않다.

  2. 코드의 파편화
    기능과 관련한 코드가 응용프로그램과 저장 프로시저 등에 분산되어, 응용프로그램의 설치나 배포가 더 복잡해지고 유지보수 또한 어려워질 수 있다.


Reference

저장 프로시저와 일반 SQL의 동작 과정 비교
✨https://devkingdom.tistory.com/323

📄https://12bme.tistory.com/54
📄https://ko.wikipedia.org/wiki/저장_프로시저
📄https://siahn95.tistory.com/entry/DBMSSQL-저장-프로시저Stored-Procedure란 📄https://goldswan.tistory.com/51