[MSSQL]저장 프로시저 장단점, 사용 예제 정리

서론

 

회사에서 개발을 하면서 프로시저로 많은 비즈니스 로직을 구현했습니다. 그동안 프로시저를 사용해본 경험을 기반으로 장단점, 사용 예시에 대해 정리해볼까 합니다. 장단점 같은 경우는 네이버 기술 블로그 중 [백엔드 개발자를 꿈꾸는 학생개발자에게] 글에서 설명하는 내용을 참고하여 공감되는 부분 위주로 작성했습니다. (아래 링크 참고)

 

---

 

본론

 

저장 프로시저의 장점

 

1) 프로시저만 수정이 필요할 경우 애플리케이션을 배포하지 않고 프로시저만 배포하면 됩니다.

→ 즉 어플리케이션 코드 내에 SQL 로직이 포함되었을 경우 애플리케이션도 재배포 해야하지만 프로시저 내에 포함될 경우 프로시저만 수정하여 배포하면 됩니다.

 

2) 자연스럽게 프로시저에 인자를 추가하여 바인드 변수를 사용하게 되므로 SQL 하드 파싱을 걱정할 일이 없습니다.

→ 어플리케이션 코드에 SQL 작성을 아래와 같이 하는 코드들이 종종 보입니다.

 

[어플리케이션 레벨에서 작성된 안좋은 Dynamic SQL 예]

 

//strCOL1 : 입력마다 바뀌는 String 변수
String strSQL = "SELECT * FROM T1 WHERE COL1 = '" + strCOL1 + "'";

이 경우 strCOL1은 계속 변하는데 바인드 변수를 사용하지 않으므로 매번 DB에서 하드 파싱을 하게 됩니다. 라이브러리 캐시에 있는 SQL문을 제대로 활용하지 못하여 매번 하드 파싱이 일어나면 CPU 점유율이 높아져 성능이 악화되고 서비스 자체가 마비되는 경우가 생길 수도 있습니다. 그런데 아래의 SP_T 프로시저 처럼 @COL1 인자를 받아서 사용할 경우 자연스럽게 바인드 변수를 사용하여 매번 하드 파싱하지 않고 라이브러리 캐시에 있는 프로시저를 활용할 수 있게 됩니다. (소프트 파싱) 따라서 이 또한 하나의 장점이 아닐까 생각됩니다. (필자 본인 생각!)

 

[바인드 변수를 자연스럽게 사용한 저장 프로시저 예]

 

CREATE PROC [dbo].[SP_T](
             @COL1 NVARCHAR(10)
)
AS
    SELECT * FROM T1 WHERE COL1 = @COL1
GO

3) SQL문을 캡슐화하여 여러 곳에서 재사용할 수 있습니다.

→ 프로시저 내부의 SQL문을 최적화하여 여러 곳에서 재사용할 수 있다면, 코드도 줄어들테고 최적화된 SQL문을 사용하므로 성능상에도 이점이 생길 것입니다.

 

저장 프로시저의 단점

 

1) 배포 절차가 따로 없으므로 이력(버전)관리가 힘듭니다.

2) 길게 작성된 프로시저의 경우 로직 파악이 어렵습니다. (feat. 스파게티 코드일 확률이 높습니다...)

3) 프로시저 내부에 연산이 포함될 경우 CPU 점유율이 높아지고 실행시간도 길어지므로 LOCK이 걸려있을 경우 병목이 될 확률이 높아지게 됩니다.

 

예제

 

[테스트 데이터 생성]

 

CREATE TABLE T1(
	 CODE_COL1 NVARCHAR(10)
	,CODE_COL2 NVARCHAR(10)
	,NUM_SEQ INT
	,NUM_COL NUMERIC(10,2)
)

INSERT INTO T1
SELECT 'A1','A2',1,1000000
UNION ALL
SELECT 'A1-1','A2-1',1,1100000
UNION ALL
SELECT 'A1-2','A2-2',1,1200000
UNION ALL
SELECT 'A1-3','A2-3',1,1300000
UNION ALL
SELECT 'B1','B2',2,2000000
UNION ALL
SELECT 'B1-1','B2-1',2,2100000
UNION ALL
SELECT 'B1-2','B2-2',2,2200000
UNION ALL
SELECT 'C1','C2',3,3000000
UNION ALL
SELECT 'D1','D2',4,4000000
UNION ALL
SELECT 'E1','E2',5,5000000

 

[사용 예]

 

--EXEC [dbo].[USER_STORED_PROCDURE_TEST] 'A%','1100000'
CREATE PROC [dbo].[USER_STORED_PROCDURE_TEST](
             @CODE_COL1 NVARCHAR(10)
	        ,@NUM_COL NUMERIC(10,2)
)
AS

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    Set	Nocount	On

    SELECT
		 CODE_COL1
		,CODE_COL2
		,NUM_SEQ
		,NUM_COL
	FROM
		T1
	WHERE
		CODE_COL1 LIKE @CODE_COL1
		AND NUM_COL >= @NUM_COL

GO

[실행 결과]

 

[같이 사용하면 좋은 옵션들]

 

[Set Nocount On]

SQL이 실행되면서 영향 받은 행들에 대해 메세지 출력되는 부분을 안나오도록 지워줌으로서 성능 향상에 도움을 줍니다. (위 실행 결과의 메세지 탭에 나오는 출력메세지) 특히 저장 프로시저가 CUD 작업을 하여 영행 받은 행 출력 메세지가 많이 나올 경우 좋습니다.

 

[SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED]

조회 저장 프로시저의 트랜잭션 격리수준을 전부 READ UNCOMMITTED로 하는 정책이 있다면, 테이블 각각에 (NOLOCK)을 적어줘야 합니다. 그러나 격리 수준을 프로시저에 설정하면 프로시저 내부에 있는 전체 쿼리에 적용되므로 SQL 코드도 줄이고 작성 시간도 줄일 수 있습니다. 다른 격리수준이 필요할 경우 아래를 참조하면 됩니다.

 

[MSSQL 트랜잭션 격리수준 설정]

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

 

결론

 

저장 프로시저는 특별한 배포 절차 없이 사용하기에는 좋으나 프로그램 규모가 커질수록 단점이 더욱 부각된다고 생각합니다. 그러나 비즈니스, 업무 특성, 여러 이해 관계에 따라 프로시저를 사용할 수 밖에 없는 경우도 있습니다. 또한 프로시저의 장점을 잘 살리면 개발 생산성을 올릴 수 있기에 상황에 맞게 적절히 사용하면 좋을 것으로 생각됩니다.

 

Reference

 

[네이버 기술 블로그] - [백엔드 개발자를 꿈꾸는 학생개발자에게]

https://d2.naver.com/news/3435170

[Microsoft SQL docs] - [트랜잭션 격리 수준]

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/transaction-isolation-levels?view=sql-server-ver15 

트랜잭션 격리 수준 - SQL Server