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

 

저장 프로시저 장단점

 

저장 프로시저의 장점

 

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

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

 

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

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

 

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

 

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

 

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

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://goldswan.tistory.com/51

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