[DB] 저장 프로시저 Stored Procedure, SP

1. 저장 프로시저란?

= 영구저장모듈

DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합

저장 프로시저와 함수의 차이점

• 저장 프로시저: 일련의 작업을 처리한 절차. 리턴값이 없거나 많을 수도 있음. 서버에서 실행되기 때문에 속도가 빠름
• 함수: 여러 작업을 위한 기능. 리턴값이 필수. 클라이언트에서 실행되기 때문에 프로시저보다 느림
  (데이터베이스 내의 함수. 예 - COUNT)

 

---

 

2. 일반 쿼리문 vs 저장프로시저

 

일반 쿼리문 작동 방식

일반적으로 쿼리문 한 줄을 실행하더라도 파싱 -> 최적화 -> 컴파일 및 실행계획 등록(실행계획 결과를 메모리에 등록) -> 실행하는 과정의 많은 절차를 거침

최적화 단계에서 해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 결정함. 인덱스 사용여부에 따라 경로가 결정됨

🌟최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 해당 실행계획 결과를 메모리(캐시)에 등록함

그리고 컴파일된 결과를 실행함

 

---

 

저장 프로시저 작동

 

1) 저장 프로시저 정의 단계

• 구문분석
  : 구문의 오류 파악(오타가 있다면 오류가 발생되어 에러메시지 띄움)

 

• 🌟지연된 이름 확인 🌟
  : 저장 프로시저를 정하는 시점에서 해당 개체(ex. 테이블)가 존재하지 않아도 상관없음. 프로시저 실행 당시에 테이블 존재 여부 확인함(개체 이름 확인)
  👉 테이블의 열, 이름이 틀리면 오류 발생

 

• 생성권한 확인
  : 현재 접근중인 사용자가 저장 프로시저를 생성할 권한이 있는지 확인

 

• 시스템 테이블 등록
  : 저장 프로시저의 이름 및 코드가 시스템 테이블에 등록

 

2) 처음으로 저장 프로시저를 실행

구문분석 단계가 빠지는 것만 빼면 일반적인 쿼리문 수행 단계와 동일함

저장프로시저 정의 단계의 이름 확인에서 미루어두었던 해당 개체 존재 유무를 개체 이름 확인을 통해 수행함

 

3) 이후의 저장 프로시저 실행

이후에 두 번째 실행부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간을 많이 단축함

 

---

 

3. 저장프로시저의 장단점

 

장점

 

1) SQL Server의 성능을 향상시킬 수 있음

• 여러 개의 쿼리를 한 번에 실행 가능
• 캐시에 있는 것을 가져와 사용하므로 속도 빨라짐
• 쿼리를 쓸 때마다 옵티마이저가 구문을 분석하고 실행 가능한 코드로 바꿀려면 비용 多. 이 비용 없앨 수 있음

옵티마이저란?

가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진

 

2) 유지보수 및 재활용 측면에서 좋음

• 개발 언어에 비의존적
• C#, Java 등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많음. 코드 내 SQL문을 건드리는 게 아니라 SP 파일만 수정하면 됨 👉 유지보수 good
• 한번 저장 프로시저를 생성해 놓으면 언제든 실행이 가능 👉 재활용 측면에서 매우 좋음

 

3) 보안을 강화할 수 있음

• 사용자별로 테이블에 권한을 주는 게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있음
• 실제 테이블에 접근하여 다양한 조작을 하는 것은 위험함 👉 실무에서는 개발자에게 SP 권한만 주는 방식을 많이 사용함

 

4) 네트워크의 부하를 줄일 수 있음

클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 됨.

BUT 저장 프로시저를 이용하면 저장프로시저의 이름, 매개변수 등 몇 글자만 전송하면 됨 👉 부하를 크게 줄일 수 있음

 

---

 

단점

 

1) 프로시저 사용시 DB 확장 어려움

• 서버의 수를 늘려야 할 때, DB의 수를 늘리는 것이 더 어려움 👉 코드 자산으로서의 재사용성이 나쁨
• DB 교체는 거의 불가능함
• 업무의 사양 변경 시 외부 응용 프로그램과 함께 프로시저의 정의를 변경할 필요가 있음

 

2) 데이터 분석의 어려움

• 개발된 프로시저가 여러 곳에서 사용될 경우 수정했을 때 영향의 분석이 어려움(별도의 Description 사용)
• 배포, 버전 관리 등에 대한 이력 관리가 힘듦
• APP에서 SP를 호출하여 사용하는 경우 문제가 생겨도 해당 이슈에 대한 추적이 힘듦(별도의 에러 테이블 사용)

 

3) 낮은 처리 성능

• 문자, 숫자열 연산에 SP를 사용하면 오히려 C, java보다 느린 성능을 보일 수 있음

 

---

 

출처

https://github.com/devSquad-study/2023-CS-Study/blob/main/DB/db_stored_procedure.md

2023-CS-Study/DB/db_stored_procedure.md at main · devSquad-study/2023-CS-Study

https://devkingdom.tistory.com/323

[MSSQL] 저장 프로시저 (Stored Procedure) 란?

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%8A%A4%ED%86%A0%EC%96%B4%EB%93%9C-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-%F0%9F%94%B8-%EC%8A%A4%ED%86%A0%EC%96%B4%EB%93%9C-%ED%95%A8%EC%88%98