(─━┘_└━─)/ 머리만1톤(Head1Ton)

주제 : 멀티선택 매개변수를 정적 프로시저로 구현하기

작성일 : 2009.02.04

작성자 : 유일환(ryu1hwan@empal.com / http://blog.naver.com/ryu1hwan )

안녕하세요. 유일환입니다.

오늘은 프로시저에 관련된 주제를 다루어볼까 합니다.

시스템을 개발할 때 프로시저를 사용하게 되면, 동적프로시저 또는 정적프로시저를 사용하게 됩니다.

이 중에서, 정적프로시저를 사용해서 개발을 하다 보면, 막히는 부분들이 적지 않게 있습니다.
그럴때면 동적프로시저를 사용하거나, 프로그램 소스 안에 SQL을 박아 넣는 방법을 고려하게 됩니다.
그렇지만 계속해서 다양한 생각과 시도를 하다 보면 정적 프로시저로는 불가능 할 것이라 생각되었던 부분도 처리 할 방법이 떠오르게 됩니다.
물론, 함수나 테이블의 신규생성이나 구조 변경이 발생될지도 모르지만 정적프로시저로 SQL을 구현한다는 것은
관리/운영에 아주 큰 장점을 가지고 있습니다.

오늘은 이러한 동적프로시저나 프로그램소스로 처리해야만 가능하다고 생각되었던 부분중에 하나를
정적 프로시저로 바꾸는 방법을 연구해 보고자 합니다.

개발을 하다 보면, 테이블의 한 속성에 대해 여러개의 값을 넣고 조회하는 경우가 있습니다.
예를 들어 다음과 같은 경우입니다.

<SQL-1>

SELECT *
FROM Sale T1
  INNER JOIN Shop T2
    ON T1.ShopID = T2.ShopID
WHERE T2.ShopGrade IN ('A', 'B')

<SQL-1>과 같은 SQL을 프로시저로 작성을 하는데, ShopGrade에 대한 조건을 매개변수로 받아야 합니다.
화면에서 ShopGrade를 사용자가 선택을 하는데 A, B, C중에 원하는 등급들을 멀티로 선택이 가능합니다.
즉, 조건으로는,
A, B, C
A, B
A
B, C
B
C
이렇게 6가지 유형이 조건으로 들어올수 있습니다.
이러한 유형이 발생되면 정적프로시저보다는 동적프로시저가 작성하기 편리합니다.

예를 들어 다음과 같습니다.
<SQL-2>
CREATE PROC dbo.usp_DProc_Sale
(
  @ShopGradeList nvarchar(100)
) AS

  DECLARE @SQL nvarchar(MAX)
  SET  @SQL = '
     SELECT *
     FROM Sale T1
       INNER JOIN Shop T2
         ON T1.ShopID = T2.ShopID
     WHERE T2.ShopGrade IN (' + @ShopGradeList + ')'
  EXEC(@SQL)
go

EXEC usp_DProc_Sale @ShopGradeList = '''A'',''B'''

go

EXEC usp_DProc_Sale @ShopGradeList = '''A'''
go

usp_DProc_Sale은 동적프로시저로서 ShopGradeList를 문자열로 받아 SQL을 수행하게 됩니다.
이처럼 동적프로시저로 작성하게 되면 단점은 무엇일까요?

많은 분들이 SQL에 대한 계속된 컴파일로 성능저하를 가져온다는 것을 알고 있습니다.

당연하고 맞는 이야기입니다.
하지만, 제 관점에서 이와같은 동적프로시저의 가장 큰 단점은 유지보수가 어렵다는 것입니다.
위와 같이 간단한 SQL의 경우는 문제가 없겠지만, 실제 개발을 해보면, 위의 예제보다, 10배, 20배는 되는 긴 SQL들이 많이 있습니다.
동적프로시저를 구현하기 위해 프로시저내에 수많은 IF문과 WHILE문이 나오는 경우도 허다합니다.
이런 동적프로시저가 점점 많아지면, 나중에 성능측정에서도 애를 먹게 되고,
한 번 수정을 할 때마다 여러개의 문자열로 나누어진 SQL문들을 합쳐서 수정후 다시 여러개의 문자열로 나누어야 합니다.
(말로 설명이 좀 어렵네요. 해보신 분들은 아시리라 생각됩니다.^^ 저의 표현력 부족을..용서하시길.)
물론, 동적프로시저가 좋은점도 많이 있습니다.
하나의 프로시저로 여러개의 화면을 커버하도록 구현을 함으로서 프로시저 수를 줄여서 관리항목수를 줄이거나,

결과 컬럼들이 가변적인 경우 유용하게 처리할 수 있습니다.

아무튼 여기서 동적프로시저냐 정적프로시저냐에 대해서 따지다 보면, 수많은 논쟁거리만 생기므로..
모두 생략하고.. 정적프로시저로 어떻게 구현 할 것인가에 대해서만 진행하도록 하겠습니다.

<SQL-2>를 정적프로시저로 어떻게 구현을 해야 할까요?
먼저 좀 억지스러운 방법으로 한 번 구현을 해보도록 하겠습니다.

<SQL-3>
CREATE PROC dbo.usp_SProc_Sale_1
(
  @ShopGradeA_YN nchar(1)
  ,@ShopGradeB_YN nchar(1)
  ,@ShopGradeC_YN nchar(1)
) AS

  SELECT *
  FROM Sale T1
    INNER JOIN Shop T2
      ON T1.ShopID = T2.ShopID
  WHERE T2.ShopGrade IN (
      CASE WHEN @ShopGradeA_YN = 'Y' THEN 'A' ELSE '' END
      ,CASE WHEN @ShopGradeB_YN = 'Y' THEN 'B' ELSE '' END
      ,CASE WHEN @ShopGradeC_YN = 'Y' THEN 'C' ELSE '' END
      )
go

EXEC usp_SProc_Sale_1 @ShopGradeA_YN = 'Y', @ShopGradeB_YN = 'N', @ShopGradeC_YN = 'Y'

go

EXEC usp_SProc_Sale_1 @ShopGradeA_YN = 'Y', @ShopGradeB_YN = 'N', @ShopGradeC_YN = 'N'
go

아마도 <SQL-3>의 정적프로시저를 보고 이런 억지가 어디있나라고 생각하시는 분들이 많으실것이라 생각합니다.
왜냐하면, ShopGrade가 지금은 A,B,C인데 나중에 D, E가 늘어나면 매개변수가 더 늘어나야 한다는 것이죠.
제가 생각해도.. 정말 쓰고 싶지 않은 방법입니다.
하지만, 실제 업무와 DB설계를 하다면 변경되지 않는 속성들이 있습니다.
그런 경우는 이런 방법을 써보는 것도 고려해 볼만합니다.
또는, 이미 정적프로시저로 구현된 프로시저를 동적프로시저로 변경해야 한다면,
동적으로 재구현하는 작업의 낭비를 줄이기 위해 사용할수도 있을 것입니다.
좋은 방법은 아니지만, 경우에 따라서는 사용할 수 있는 방법인 것이죠.

자, 그럼 또 다른 방법을 보도록 하겠습니다.
이번에 사용할 방법은, 사용자정의함수를 이용하는 방법입니다.
즉, 매개변수 'A, B'를 받으면, 테이블 2줄로, A와 B를 넘겨주도록 하는 방법입니다.
먼저 <SQL-4>를 통해 사용자정의 함수를 생성합니다.

<SQL-4>
------------------------------------------------------------------------
-- CREATOR   :
-- ALTER DATE   :
-- Descr   : 문자열값을 테이블로 만들기
------------------------------------------------------------------------
CREATE FUNCTION dbo.UFN_STR_TO_TBL
(  @String  nvarchar(2000),
  @DIV  nchar(1)
)
RETURNS @Array TABLE
(
  Code  nvarchar(50) COLLATE Korean_Wansung_CI_AS
)
AS
BEGIN
  DECLARE @Str nvarchar(500)
  IF (CHARINDEX(@DIV, @String) > 0)
  BEGIN
    SET @String = @String + @DIV
    WHILE (LEN(@String) > 0)
    BEGIN
      IF (CHARINDEX(@DIV, @String) > 0)
      BEGIN
        SET @Str = SUBSTRING(@String, 1, CHARINDEX(@DIV, @String) - 1)
        SET @String = SUBSTRING(@String, LEN(@Str) + 2, LEN(@String) - LEN(@Str) - 1)
       
        INSERT INTO @Array VALUES(LTRIM(RTRIM(@Str)))
      END
      ELSE
      BEGIN
        SET @Str = RTRIM(@String)
        SET @String = ''
        
        INSERT INTO @Array VALUES(LTRIM(RTRIM(@Str)))
      END
    END
  END
  ELSE
    INSERT INTO @Array VALUES(RTRIM(LTRIM(@String)))
    
  
  RETURN
END 

설명에 앞서 미리 말씀드리지만, 이 함수는 제가 고안한 것은 아닙니다.
저희팀에 개발자 한분이 사용한 방법이고, 아마도 인터넷의 어디선가 이 함수를 보고 생성하신거 같습니다.
처음 만드신 분이 누군지 알수가 없어서..^^;; 이렇게 밝히지도 못하고 사용한다는게 죄송스럽네요.
<SQL-4>는 문자열을 테이블형태로 변환하는 함수입니다. <SQL-5>를 실행해 보시죠..

<SQL-5>
SELECT T1.Code
FROM dbo.UFN_STR_TO_TBL('A,B', ',') T1

다음과 같이 문자열 A,B를 테이블로서 변환하는 것을 볼 수 있습니다.

Code
--------------------------------------------------
A
B

자, 그럼 이 함수를 이용한 정적 프로시저를 새로 개발해 보도록 하겠습니다.
<SQL-6>
ALTER PROC dbo.usp_SProc_Sale_2
(
  @ShopGradeList nvarchar(500)
) AS

  SELECT *
  FROM Sale T1
    INNER JOIN Shop T2
      ON T1.ShopID = T2.ShopID
  WHERE T2.ShopGrade IN (
        SELECT A.Code FROM dbo.UFN_STR_TO_TBL(@ShopGradeList, ',') A
        )
go

EXEC usp_SProc_Sale_2 @ShopGradeList = 'A,B'

go

EXEC usp_SProc_Sale_2 @ShopGradeList = 'A'
go

어떤가요? 이전의 정적프로시저보다 좀 더 유연해지지 않았습니까? ShopGrade에 D,E가 추가되어도,

프로시저를 추가적으로 변경할 일은 없습니다.

이 방법을 이용하면, 아마도 많은 동적프로시저를 정적프로시저로 변경을 할 수 있을 겁니다.
자,, 그럼 오늘은 여기서 끝이라고... 한다면..너무.. 적고도...같잖은 내용을 적는거겠죠..

실제로 사용자정의함수를 이용해서 멀티조건을 사용하는 방법을 사용을 해봤는데,
복잡한SQL이나, 대단위 데이터 처리를 하는SQL에 있었서는 성능상 심각한 문제를 일으키기도 합니다.

(대부분의 간단한 SQL에서는 아무런 문제를 일으키지 않았습니다.)
<SQL-6>을 실행하면 ShopGrade가 A, B, C 3개만 있다고 할 경우 UFN_STR_TO_TBL에서 최대한 나올수 있는 데이터 건수는 3건입니다.

그렇지만, SQL Server가 경우에 따라 UFN_STR_TO_TBL의 결과를 증가 시켜서 조인을 수행하는 경우가 있습니다.
최대한 3건인 데이터가 실행계획을 따라가 보면, 10만건의 데이터를 상위 계획으로 넘기는 경우가 발생되는 것입니다. 조인을 좀더 효율적으로 처리하기 위해 이와같은 연산이 발생되고는 합니다.

하지만, 실제로는 더 나쁜 성능을 발휘하기도 하지요.
그래서 말씀드리는 것은 이와 같은 방법을 사용할 때, 대단위 데이터 처리, 또는 복잡한 SQL의 경우는 꼭 성능 측정을 해야 한다는 것입니다.

성능상 치명적으로 나쁘다면 동적SQL로 처리하시거나 다른 방법을 고안해야 할 것입니다.

마지막으로 한 가지 방법을 더 보여드리고자 합니다.

이 방법이 성능상 좋다는 말씀을 드리지는 못합니다. 왜냐면, 성능이란건, 그때 그때 다르기 때문이죠..

다만, 이런 방법을 사용할 수도 있겠구나라는 것을 아시고, 좀 더 다양한 방법을 직접 구현해 보시기를 바랍니다.
위와 같은 단순한 경우가 아닌, 시스템에 사용자정의리포트라는 기능을 구현한다고 생각을 해보도록 하겠습니다.
즉, Sale테이블을 조회하는데, 사용자가 원하는 날짜들만, 원하는 상점만, 원하는 상정등급등을 골라내서 볼 수 있도록 하는 것입니다.
즉 다음과 같은 SQL이 실행이 되는 것이죠.

<SQL-7>
SELECT *
FROM Sale T1
  INNER JOIN Shop T2
    ON T1.ShopID = T2.ShopiD
WHERE T1.SaleYMD IN ('20090103', '20090105')
AND  T1.ModelID IN ('NB-A01', 'NB-A03')
AND  T2.ShopGrade IN ('A', 'C')

이와 같은 <SQL-7>을 프로시저로 구현을 하는 것입니다.
조회조건이 SaleYMD, ModelID, ShopGrade이고, 사용자가 조회조건을 한 컬럼에 대해 멀티로 선택이 가능한 경우입니다.
위에서 사용한 사용자 정의함수를 사용해서 구현을 할수도 있습니다. 하지만 다른 방법을 사용해 보도록 하겠습니다.
여기서는 추가적인 테이블을 통해 문제를 해결하도록 하겠습니다.

다음과 같은 2개의 테이블을 추가로 생성합니다.

<SQL-8>
CREATE TABLE dbo.UserDefineParamMaster
(  ParamMasterSeq numeric(18,0) identity(1,1)
  ,RegiID nvarchar(30) NULL
  ,RegiDT datetime NULL
)

ALTER TABLE UserDefineParamMaster
  ADD CONSTRAINT PK_UserDefineParamMaster PRIMARY KEY( ParamMasterSeq)

go

CREATE TABLE dbo.UserDefineParamDetail
(  ParamMasterSeq numeric(18,0) NOT NULL
  ,ParamDiv nvarchar(30) COLLATE Korean_wansung_CI_AS NOT NULL
  ,ParamVal nvarchar(50) COLLATE Korean_wansung_CI_AS NOT NULL
)

go

ALTER TABLE UserDefineParamDetail
  ADD CONSTRAINT PK_UserDefineParamDetail PRIMARY KEY(ParamMasterSeq, ParamDiv, ParamVal)
  
ALTER TABLE UserDefineParamDetail
  ADD CONSTRAINT FK_UserDefineParamDetail_1 FOREIGN KEY(ParamMasterSeq)
    REFERENCES UserDefineParamMaster(ParamMasterSeq)
go

이 테이블들은 사용자가 선택한 매개변수들을 저장하는 역할을 합니다.
관련 프로시저는 다음과 같습니다.

<SQL-9>
CREATE PROC dbo.usp_UserDefineParamMaster
(  @RegiID nvarchar(30)
  ,@R_NewParamMasterSeq numeric(18,0) OUTPUT
) AS
  INSERT INTO UserDefineParamMaster
    (RegiID, RegiDT)
  SELECT @RegiID, GETDATE()
  
  SET  @R_NewParamMasterSeq = @@IDENTITY
go

CREATE PROC dbo.usp_UserDefineParamDetail
(  @ParamMasterSeq numeric(18,0)
  ,@ParamDiv nvarchar(30)
  ,@ParamValList nvarchar(500)
) AS
  INSERT INTO UserDefineParamDetail
    (
    ParamMasterSeq
    ,ParamDiv
    ,ParamVal
    )
  SELECT @ParamMasterSeq
    ,@ParamDiv
    ,RTRIM(Code)
  FROM dbo.UFN_STR_TO_TBL(@ParamValList, ',')
go

위와 같은 프로시저 2개를 통해, 사용자가 화면에서 선택한 매개변수들을 먼저 별도의 테이블에 저장을 하게 됩니다. 즉, 실제 Sale테이블을 조회하는 프로시저를 호출하기 전에 위 2개 프로시저를 통해 매개변수들을

매개변수 테이블에 저장하고, 실제Sale조회시에는 매개변수 테이블과 조인을 통해 문제를 해결 하는 것입니다.
실제 Sale테이블을 조회하는 프로시저를 만들어 보도록 하겠습니다.
<SQL-10>
CREATE PROC dbo.usp_SProc_Sale_3
(  @ParamMasterSeq numeric(18,0)
) AS
  SELECT T1.*, T2.*
  FROM Sale T1
    INNER JOIN Shop T2
      ON T1.ShopID = T2.ShopiD
    INNER JOIN UserDefineParamDetail T3
      ON T3.ParamMasterSeq = @ParamMasterSeq
      AND T3.ParamDiv = 'SaleYMD'
      AND T3.ParamVal = T1.SaleYMD
    INNER JOIN UserDefineParamDetail T4
      ON T4.ParamMasterSeq = @ParamMasterSeq
      AND T4.ParamDiv = 'ModelID'
      AND T4.ParamVal = T1.ModelID
    INNER JOIN UserDefineParamDetail T5
      ON T5.ParamMasterSeq = @ParamMasterSeq
      AND T5.ParamDiv = 'ShopGrade'
      AND T5.ParamVal = T2.ShopGrade

<SQL-10>은 Sale테이블을 조회하는 프로시저입니다.

<SQL-11>을 통해서, 매개변수설정부터 Sale테이블을 조회하는 과정을 테스트 해보도록 하겠습니다.

<SQL-11>
DECLARE @NewParamMasterSeq numeric(18,0)

EXEC usp_UserDefineParamMaster @RegiID = 'DBA', @R_NewParamMasterSeq = @NewParamMasterSeq OUTPUT

EXEC usp_UserDefineParamDetail @ParamMasterSeq = @NewParamMasterSeq, @ParamDiv = 'SaleYMD', @ParamValList = '20090103, 20090105'
EXEC usp_UserDefineParamDetail @ParamMasterSeq = @NewParamMasterSeq, @ParamDiv = 'ModelID', @ParamValList = 'NB-A01, NB-A03'
EXEC usp_UserDefineParamDetail @ParamMasterSeq = @NewParamMasterSeq, @ParamDiv = 'ShopGrade', @ParamValList = 'A, C'

EXEC usp_SProc_Sale_3 @ParamMasterSeq = @NewParamMasterSeq

뭔가 쉬운 방법은 아닌것처럼 보이실 겁니다. 성능이 좋다고도 확답은 드릴 수 없는 방법이고요.

하지만, 이처럼 다양한 방법이 있고 시도해 본다는 것은 매우 중요합니다.

성능에 아무런 도움이 안될거 같으니, 스킵해야겠어라고 생각하시는 분들께 한말씀 드리자면,,

실제 업무에서 이와같이 멀티로 선택하는 요소가 성능을 향상시키는 팩터로 사용되는 경우는 별로 없습니다.

대부분의 크기가 큰 테이블들은 시간이 지나면서 데이터가 쌓여가는 판매, 재고와 같은 테이블들인데,

해당 테이블들의 성능을 좌우하는 주요 팩터는 일자입니다.

그러므로 이런부분의 접근의 개선이 성능에 중요한 역할을 하게 되고,

멀티로 선택되는 이런 조건은 성능과는 거리가 좀 있는 요소라고 말씀드릴 수 있습니다.

(그런데 위에서는 일자를 멀티로 선택하는 예제를.... 단지 예를 들기 위해 그렇게 했습니다.^^;;;죄송..)

좀.. 쉽고 간단하게 쓰려고 했는데, 글로 표현한다는 것은 정말 쉽지 않은 일이네요..

그리고, 실제 처음 썻던 글은 이것의 3배쯤 되었습니다.

위에서 말씀드렸듯이 논란이 될수 있는 동적프로시저냐 정적프로시저에 대한 부분을 모두

배제하니 글이 많이 줄더군요..

읽어주신 분들께 감사드리고, 요즘 IT업계도 초비상상태인듯 하던구요..

아무래도 저는 중국에 있다보니, 잘은 모르겠지만... 저도 위태위태한 상황이고요..

어찌되었든, 우리 모두 이 힘든 1년 2년을 잘 견뎌내었으면 합니다.

(길게는 4년??)

어떻게든, 1년, 2년 버텨낸면 다시 따스한 날이 올꺼라 기대해 봅니다.