안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 8-1. 저장 프로시저(Stored Procedure) - 소개를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 저장 프로시저(Stored Procedure) 소개입니다.

TL;DR

저장 프로시저의 개념과 장점으로, 성능 향상, 보안 강화, 개발과 데이터베이스 분리, 인덱스 최적화 쿼리, 네트워크 부하 감소 등의 이점을 다룹니다. 또한, 저장 프로시저의 단점에 대해서도 언급하며, 개발자들이 저장 프로시저를 어떤 상황에서 활용할 수 있는지에 대해 설명합니다.

지난 강좌에서 뷰와 저장 프로시저에 대해서 간략하게 소개했습니다. 이번 시간에는 저장 프로시저에 대해 좀 더 상세히 살펴보겠습니다.

저장프로시저란 무엇인가

다음 강좌로 진행되는 저장 프로시저는 프로그래밍의 함수처럼 동작하는 완전히 캡슐화된 모듈입니다. 저장 프로시저 이름과 파라미터를 제공하면, 결과셋이 출력되는 완전한 모듈화와 캡슐화를 제공합니다. 어느 정도 규모 있는 현업 개발팀에서 가장 많이 사용되고, 잘 구성된 DBMS는 100% 저장 프로시저로만 데이터를 제공합니다. 

☑️ 챗GPT 활용: 캡슐화(Encapsulation)와 모듈화(Modularization)에 대해서 알려줘

저장 프로시저 장점

성능 향상

일반 SQL구문(Ad-Hoc 쿼리라고 보통 부릅니다.) 보다 빠르게 실행되고 캐시 되어 CPU 부하가 적습니다. 

☑️ 챗GPT 활용: Ad-Hoc 쿼리에 대해서 알려줘

일반 SQL 구문 처음 수행 시
1. 구문 분석단계 - SQL구문의 문법을 검사
2. 표준화 - DB의 개체들(테이블 등)에 대해서 검사
3. 보안 점검 - 해당하는 DB개체들에 대한 사용자의 권한을 검사
4. 최적화 - 최적의 성능을 내기 위한 사항을 적용
5. 컴파일

일반 SQL 구문 처음 반복 수행 시
1. 처음 수행한 일반 SQL구문의 실행 계획이 캐싱되어 있는지 확인 후 수행
2. 캐싱되어 있지 않을 경우 처음 수행 5단계를 다시 수행

저장 프로시저를 생성할 경우
1. 구문 분석 단계 - 프로시저 생성 구문 검사
2. 표준화 - DB의 개체(테이블 등)에 대해서 검사
3. 보안 점검 - 프로시저 생성이 가능한지 검사
4. 해당 프로시저의 구문과 생성 정보를 저장

저장 프로시저 첫 실행 시
1. 보안 점검 - 해당하는 DB개체들에 대한 사용자의 권한을 검사
2. 최적화 - 최적의 성능을 내기 위한 사항을 적용
3. 컴파일 후 실행 계획을 생성하고 캐시에 저장 후 실행

저장 프로시저 반복 수행 시
1. 캐시에 실행 계획이 있는지 확인 후 실행
2. 캐시에 실행 계획이 없을 경우 - 저장 프로시저 첫 실행 과정을 반복

일반 SQL 구문과 저장 프로시저 실행 방식에 큰 차이는 없어 보입니다. 하지만 일반 SQL 구문 실행은 구문 분석, 표준화, 보안 점검 등 CPU를 어느 정도 사용해야 하는 부하가 비교적 있는 작업을 지속적으로 실행합니다. 아울러 수백 가지 종류의 다른 쿼리 패턴을 수행한다고 생각해 보세요. 저장 프로시저 사용으로 애플리케이션을 개발하면 이런 과정 없이 파라미터화된 상태로 캐시 되어 빠르게 실행됩니다.

향상된 보안

저장 프로시저 단위로 사용자나 역할 대상 권한을 부여해 프로시저를 실행하고 결과셋을 제공합니다. 프로시저 권한이기 때문에 뷰나 테이블 직접 쿼리보다 높은 보안성을 제공할 수 있습니다.

개발과 데이터베이스 분리 및 인덱스 최적화 쿼리

개발단과 데이터베이스단을 완전히 분리할 수 있습니다. DBMS의 스키마 구조나 테이블과 같은 Raw 데이터 제공 없이 보안성과 모듈화, 캡슐화 가능한 장치가 저장 프로시저입니다. 저장 프로시저 코드는 데이터베이스 개발자가 직접 작성하기 때문에, 인덱스나 잠금과 같은 데이터베이스 성능 팩터를 고려해 개발됩니다. 즉, 저장 프로시저로 최적의 성능을 제공할 수 있습니다. 또한 스키마나 테이블 구조가 변경되어도, 저장 프로시저 내부 구문을 수정해 개발에 영향 없이 조치를 취할 수 있습니다.

네트워크 부하 감소

긴 SQL 구문이 네트워크를 통해 매번 SQL Server로 전송되지 않고, 저장 프로시저 이름과 파라미터만 전달되기 때문에, 네트워크 부하를 감소할 수 있습니다.

저장 프로시저 단점

모든 면에서 좋아 보이는 저장 프로시저도 단점이 있습니다. 

저장 프로시저 작성 시간 소요

일반 SQL 구문과 크게 차이가 없지만, 파라미터화와 기본적인 프로시저 구문을 작성해야 하기 때문에 약간의 시간이 더 소요될 수 있습니다. 

저장 프로시저 캐시 기능이 성능 문제를 발생시킬 수 있음.

저장 프로시저는 최초 실행 시 캐시 됩니다. 캐시 기능이 CPU 부하를 낮추고 성능을 높이지만, 캐싱되면서 적용된 인덱스 사용 루틴이 잘못 캐시 되어 낮은 성능이 나올 수 있습니다. 예를 들어, 비클러스터형(Non-clustered) 인덱스는 전체 테이블에서 약 3~5% 내외의 데이터를 가져올 경우 인덱스 검색(Index Seek)을 수행합니다. 하지만, 가변 파라미터로 인해 최초 실행 시 인덱스 스캔(Index Scan)을 하게 되면, 지속 스캔을 하게 되어 성능에 영향이 있을 수 있습니다. 이렇게 가변 파라미터로 인해 인덱스 선택도를 취하기 어렵거나 성능 이슈가 예상되는 경우 RECOMPILE 옵션을 줄 수 있습니다. 

갑자기 인덱스가 나와서 혼란스럽습니다. 이후 강좌에서 배우게 되니 이런 게 있고, 프로시저 옵션으로 제어 가능하다는 정도만 기억해 두세요.

테이블명을 저장 프로시저 파라미터로 처리하기 위해 동적 SQL 사용

예를 들어,

-- 실행 안되는 예시입니다.
CREATE PROC pCountTable
@TableName as NVARCHAR(10)
AS
SELECT COUNT(*) FROM @TableName;
GO

이렇게 테이블명을 저장 프로시저 파라미터로 받아 처리할 경우 저장 프로시저에서 일반적으로 실행할 수 없고, 아래 프로시저 생성 구문처럼 동적 SQL(Dynamic SQL)로 실행하고 사용자에게 해당 테이블이나 뷰에 조회나 적절한 권한을 부여해야 합니다.

☑️ 챗GPT 활용: 동적 SQL(Dynamic SQL)에 대해서 알려줘
 

CREATE PROC pCountTable
@TableName AS NVARCHAR(32)
AS
DECLARE @SQL AS NVARCHAR(255)
SET @SQL = 'SELECT COUNT(*) FROM ' + @TableName
EXEC(@SQL);
GO

-- 실행
EXEC pCountTable 'Production.Product';
GO

이렇게 테이블명을 변수로 넘기는 작업을 수행하지 않도록 데이터베이스 스키마를 디자인하는 게 중요하며, 만약 같은 구조의 여러 테이블을 사용하는 프로시저를 생성해야 한다면, 여러 테이블로 구성하기보다는 하나의 테이블에 컬럼을 추가해 이 컬럼을 식별 용도로 사용하는 것이 데이터베이스 스키마 디자인에 더 적합한 방법입니다.

몇몇 단점에도 불구하고 저장 프로시저는 최선의 선택

데이터베이스와 개발 사이에는 넘을 수 없는 혼돈의 벽(Wall of Confusion)이 있습니다.

작은 개발팀에서 근무하며 애플리케이션을 개발합니다.
ORM을 사용하는데 SQL과 저장 프로시저를 배워야 하나요?

5명 이하의 소규모 조직 개발팀이라면, 빠른 MVP 제작을 위해 아마도 ORM(Object Relational Mapper)을 이용해 속도감 있게 개발을 진행하고 있을 겁니다. 네, 개발자가 새롭게 SQL 쿼리를 공부하고 배우는 시간보다, 프로그래밍 언어에서 객체를 다루듯, 데이터베이스를 다루는 ORM 패턴은 생산성에 훨씬 유리합니다.

시간이 지나 성공적으로 MVP 개발을 완료하면서, 투자를 받고, 사업이 확장됩니다. 실 서비스 단계에 들어가게 되면서 사용자가 늘고, 하루가 다르게 성장합니다. 이때, 문제가 발생합니다. ORM으로 개발 완료하고 운영하면서 시간이 지나자, 빠르게 데이터가 쌓입니다. 곧이어, 데이터베이스 성능 이슈가 발생합니다.

개발 조직에서 확인한 결과, ORM이 매핑 과정에서 자동 생성한 SQL 구문에 성능 이슈가 발생하고 있으며, ORM에서 여러 작업을 수행해도 자동 생성되는 SQL 구문 제어가 어려워집니다.

이 단계에서 SQL 쿼리나 저장 프로시저로 변경하는 것을 고려할 수 있습니다.

SQL 쿼리나 저장 프로시저를 이용하면, DBMS에 최적화된 쿼리가 가능하고, 완전한 제어가 가능합니다. 특히, 저장 프로시저를 이용하면 규모가 커진 개발팀에서도, 저장 프로시저 이름과 파라미터만 넘기면 되니 프로그래밍 언어의 함수처럼 사용이 쉬워지고 개발과 DBMS가 분리되어 개발에 속도가 붙습니다.

무엇보다, 개발팀에서 DBMS 쪽 전문가가 없어 문제가 발생하면 난감한 상황이었는데, 업무가 완벽하게 분장되고 데이터베이스 개발자가 생성한 저장 프로시저만 실행하면 되니 완전한 모듈화가 가능해집니다. 개발 조직은 개발에만 더욱 집중할 수 있습니다.

애플리케이션 개발 조직과 데이터베이스 개발 조직 간 업무 분장과 모듈화가 가능한 단 하나의 이유만으로도 저장 프로시저를 사용할 이유는 충분합니다. 이번 저장 프로시저 강좌가 개발 업무를 수행하는 SQLER 분들께 많은 도움 되길 바랍니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022