(T-SQL) 서버 측 동적 쿼리에서의 탈출

(T-SQL) 서버 측 동적 쿼리에서의 탈출

 

요약.

수년 전부터 MS 플랫폼에서 SQL Server를 연동한 프로젝트 수행 시 개발 방법론의 중요한 한 축으로 저장 프로시저의 적극적인 활용이 일반화되고 있다. 그로 인한 부작용 중에 하나는 이전에 응용 프로그램 아키텍처 상의 프레젠테이션 계층 혹은 비즈니스 서비스 계층에서 문자열 데이터로 동적으로 구성하던 쿼리가 저장 프로시저를 통해서 서버 측에서 구성이 된다는 것이다. “서버 측 동적 쿼리”(이하 동적 쿼리로 표기)라고 표현하는 이러한 동적 T-SQL  또는 일괄처리는 성능상의 문제를 내포함과 동시에, 개발 생산성과 관리 및 유지 보수의 용이성 측면에서 많은 고려 사항을 포함하고 있다. 그럼에도 아직 많은 개발자들이 막연히 문제 해결 중심적인 사고로 동적 쿼리를 선호하는 경향이 있다. 이 글에서는 동적 쿼리를 비 동적 쿼리(이하. 정적 쿼리로 표기)로 변환하거나 대체할 수 있는 기초적인 사례들을 살펴볼 것이다. 불필요한 동적 쿼리 사용으로 인한 성능상의 문제 해결과 관리 및 유지 보수 용이성을 얻는데 미약하나마 이 글이 도움이 되기를 바란다.

   

목차

-         동적 쿼리

-         동적 쿼리 대 정적 쿼리(비 동적 쿼리)

-         탈출을 위한 시작, 사례별 예제

-         마치면서...

   

동적 쿼리

일반적으로 언급하는 동적 쿼리란, 코드의 실행 시점에 SQL 문이 동적으로 구성되고 실행되는 쿼리를 말한다. ODBC를 사용하는 C 언어계열에서, SQL 문을 문자 배열에서 동적으로 구성한 뒤 이를 ODBC API, SQLPrepare 혹은 SQLExecDirect 함수로 전달하거나 ADO를 사용하는 Visual Basic, ASP 등에서 SQL 문을 문자열로 동적으로 구성한 뒤 이를 Connection 오브젝트나 Recordset 오브젝트를 통해서 호출하거나, 또는 다양한 .NET Data Provider를 제공하는 .NET 호환 언어에서 역시 문자열로 동적으로 구성한 SQL 문을 DataAdapter, DataReader 등의 오브젝트를 통해서 호출하는 경우가 이에 해당한다.

                                                

참고. SQL Server .NET Data Provider 의 경우엔 동적 쿼리를 호출하더라도 순수한 쿼리 형태로 호출되지 않고, SQL Server의확장 저장 프로시저, sp_executesql 을 통해서 자동 변환이 된다. 이는 Ad hoc 쿼리와 Precompiled 쿼리(저장 프로시저, 트리거등)의 중간 형태인, Parameterized 쿼리의 생성 방법 중의 하나로써 .NET 기반 데이터베이스 응용프로그램 개발자들이 기본적으로 숙지해야 할 시스템 프로시저 중의 하나입니다.

 

동적 쿼리를 나타내는 용어도 다양하다, 원시적으로는 Embedded SQL for C(ESQL/C) 에서 사용된 용어를 시작으로 플랫폼이나 개발 언어에 따라 Dynamic Query, Hard Coded Query, Ad hoc Query 등으로 불려지고 있다.

 

서버 측 동적 T-SQL 혹은 동적 일괄 처리(Batch)

이전의 C, C++, Visual Basic, ASP 등에서 동적 쿼리의 사용은 일반적이었다. 물론 여전히 많은 응용 프로그램과 프로젝트에서 동적 쿼리형식이 사용되고 있다. 그러나 현재 기본적으로 권장하는 SQL Server 데이터베이스 응용 프로그램의 개발 방법론에서는 서버 측의 T-SQL쿼리 오브젝트(저장 프로시저, 사용자-정의 함수, 트리거, 등)를 사용하는 것이다. 이는 수년 전 IIS 기반의 ASP를 사용한 웹 응용 프로그램의 개발이 일반화되면서 ASP 개발자들이 이를 수용, 프로젝트에 적용하기 시작했다 - 사실, 저장 프로시저의 적극적인 사용을 권장한 것은꽤 오래된 이야기이지만 국내에서 활발하게 적용되기 시작한 것은 얼마 되지 않는다.

 

이러한 방법론을 통해 얻을 수 있는 이득이 많은 반면, 피하기 어려운 문제점 또한 가지고 있었다. 그 중에 하나가 바로 서버 측에서의 동적쿼리의 필요성이었다. 런타임 시 쿼리를 문자열 상수와 변수로 구성하고, 이를 통해 완성된 최종 쿼리를 호출하던 이전의 방식에서는 런타임 시 조건에 따라 다양한 쿼리를 구성할 수 있는 편리함이 제공된  반면 저장 프로시저, 트리거 등에서 런타임 시 쿼리를 동적으로 변경하는 것은 상대적으로 쉬운 문제가 아니었다. 가장 흔히 접하게 되는 몇 가지 예제를 보자:

 

-         SELECT @컬럼명 FROM table

-         SELECT * FROM table WHERE @컬럼명 LIKE @표현식

-         SELECT * FROM table WHERE column IN (@표현식)

 

어떻게 해결 할까? SQL Server 2000 버전에서 저장 프로시저, 트리거, 혹은 스크립트 내에서 런타임 시에 쿼리를 동적으로 구성하기 위한방법으로 다음 두 가지가 주로 사용된다.

 

-         sp_executesql 확장 프로시저를 사용해서 유니코드 문자열을 실행. 동적 쿼리 구문 내에 매개변수를 선언하고 연동하는 것이가능하며, 이를 통해 얻을 수 있는 추가 이득이 있다.

-         EXECUTE 문을 사용해서 문자열을 실행.

 

참고. 하나 이상의 T-SQL 쿼리를 하나의 일괄처리(Batch) 범위 내에서 동적으로 구성할 수 있기 때문에 동적 T-SQL 혹은 동적 일괄처리라고 부르는 것이 보다 가까운 표현이 될 것이며, 온라인 설명서에도 이와 같이 언급하고 있다.

 

저장 프로시저 등에서 동적 쿼리 구성은 SQL Server 관련된 온라인 커뮤니티와 뉴스그룹 등에서 가장 활발한 질문/답변 유형 중의 하나이다, 두드러진 변화는 대략 1년 전부터인 것으로 기억이 난다. 그 만큼 많은 코드에서 적용되고 있다는 것을 암시하지만, 문제는 이러한 접근방법이 또 다른 문제를 야기한다는 것이며, 그 사실을 잘 알지 못한 채 불필요한 상황에서까지 과도하게 사용하고 있다는 것이 더욱 큰 문제에 해당한다. 저장 프로시저 등을 사용해서 얻을 수 있는 성능 상의 이득을 반감시키고, 보안 위험, 관리 상의 어려움 등 여러 가지 문제가 유발된다.

 

동적 쿼리 대 정적 쿼리(비 동적 쿼리)

응용 프로그램 아키텍처 상에서 적용되는 계층에 관계없이, 동적 쿼리의 과용은 여러 가지 문제점들을 가지고 있다. 기본적으로 실행 시마다 컴파일을 반복하게 됨으로써 PreCompiled/Procedure Cache 재사용을 저해함으로써 발생하는 성능 상의 문제(여기서, SQL Server의Auto Parameterization/Prepared Cache 재사용성에 대한 특징은 따로 언급을 하지 않을 것이다.)를 포함해서, Ownership Chain 권한 처리 문제와 SQL Injection (문자열 쿼리 내에 또 다른 쿼리를 삽입해서 의도하지 않는 동작을 유도하는 방식)등에 관련된 보안 상의 문제, 공통 모듈에 대한 관리 및 배포에 대한 어려움 등을 예로 들 수 있다. 이는 이미 오래 전부터 많은 전문가들에 의해 잘 알려진 사실 들이다.

 

참고. 이에 관련된 좋은 문서가 하나 있다, Microsoft SQL Server MVP 이기도 한 Erland sommarskog 가 쓴 글, “The curse and blessings of dynamic SQL” 을 읽어보기 바란다. 그리고 이 글은 한글 버전도 제공된다. 또한 그의 다른 좋은 글 들도 도움이 될 것이다.

 

동적 쿼리가 가지고 있는 여러 가지 문제점은, 저장 프로시저를 사용하도록 권장하는 이유들과 자연스럽게 연결된다. 이제는 일상적인 논쟁거리로, SQL Server 개발자들의 술자리 메뉴 중의 하나가 되었지만, “동적 쿼리에서의 탈출”이라는 사뭇 거창한 주제를 강조하기 위해서라도 다시 한 번 저장 프로시저를 사용하는 이유를 간단하게 정리해 보자:

 

-         실행 계획 Caching 을 통한, 성능 이득

-         Network Traffic 최소화

-         출력 Parameter, Return 값 사용

-         Ownership Chain 을 통한 권한 처리, SQL Injection 차단 등의 보안 기능

-         업무 논리의 캡슐화, 모듈화

-         SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능

-         이 외에도 적용 방법과 구성에 따라 추가 이득을 얻을 수 있다.

 

저장 프로시저가 정답은 아니다. 또한 동적 쿼리를 무조건 사용하지 말라는 것도 아니다. 이들은 모두 우리가 얻을 수 있는 해답의 한 가지일 뿐이며, 무엇이 가장 정답에 가까운 해답인지에 따라서 그 이득의 유무와 정도가 달라는 것이다. 어느 것이 정답에 가까운지를 판단하기에 앞서 어떤 해답이 있는지를 알고 있는 것이 기본일 것이다 ? 여러분은 주관식과 객관식 중 어느 것을 좋아하는가?

 

동적 쿼리가 가지고 있는 문제점을 저장 프로시저 안에 재현하는 것은, 결국 위에서 언급한 저장 프로시저 사용에 대한 이득을 저해하는 요소가 되는 것이다. 현재 저장 프로시저의 적지 않은 코드가 동적 쿼리 형태로 작성되고 있으며, 그들 중의 상당 부분은 정적 쿼리로 재 작성할 수가 있다. 즉, 동적 쿼리를 불필요하게 남발하고 있는 코드가 많다는 것이다. 이는 객관식 보기가 충분하지 않다는 것이기도 하다. 다음에서 이러한 사례들을 살펴볼 것이며, 여러분이 가지고 있는 객관식 보기 유형에 추가하길 바란다. 이를 통해 SQL Server 저장 프로시저가제공하는 본래의 이득에 충실한 코드를 개발하는데 도움이 될 것이다.

 

탈출을 위한 시작, 사례별 예제

서버 측 동적 T-SQL을 정적 쿼리로 변환하는 방법에 대한 다양한 사례가 SQL Server 전문 웹사이트, 뉴스그룹 등을 통해서 알려져 왔으면지금도 다양한 사례가 논의되고 개발되고 있다. 여기서 기초적인 몇 가지 사례를 살펴보자. 여기에 기록한 사례들은 주로, Robert Marda가 쓴 글, “How Dynamic SQL Can Be Static SQL”, 그리고 Erland sommarskog 의 다른 글 및 뉴스그룹 등의 자료 들을 정리한 것이다.

 

예제에 대한 자세한 설명은 제외했다. 이면의 부족함도 있지만, 대신 동적 쿼리 형식과 이를 대체한 정적 쿼리를 비교해서 기록했으므로,코드를 보는 것으로도 충분히 설명을 대체할 수 있을 것이라 본다. 그리고, 예제는 모두 SQL Server의 Northwind 데이터베이스를 사용한다.

 

 

  - 예제 1. 동적 WHERE 조건자 컬럼

동적 쿼리 형식

SELECT * FROM table WHERE @컬럼명 = @표현식

정적 쿼리

DECLARE @column varchar(10), @value varchar(20)   SET @Column = 'Last' SET @value = 'Full%'                                              SELECT * FROM dbo.employees WHERE (CASE @column WHEN 'Last' THEN LastName                            WHEN 'First' THEN FirstName                            WHEN 'Title' THEN Title                            ELSE @value                            END) LIKE @value

   

 

  - 예제 2. 동적 SELECT 절

동적 쿼리 형식

SELECT { @컬럼1, @컬럼2, @컬럼3 | @컬럼4, @컬럼1, @컬럼5 | , ... } FROM table

정적 쿼리

DECLARE @column varchar(10)   SET @Column = 'title'   SELECT EmployeeID, CASE @column             WHEN 'Name' THEN LastName             WHEN 'Title' THEN Title             ELSE LastName             END AS Column1, CASE @column             WHEN 'Name' THEN FirstName             WHEN 'Title' THEN LastName             ELSE CAST(BirthDate as varchar(20))             END AS Column2, CASE @column             WHEN 'Title' THEN CAST(HireDate as varchar(20))             ELSE ''             END AS Column3 FROM dbo.employees WHERE EmployeeID < 4

 

 

 

 

  - 예제 3. 동적 Order By 절 (컬럼명 만 유동적인 경우)

동적 쿼리 형식

SELECT * FROM table ORDER BY @컬럼명

정적 쿼리

DECLARE @OrderBy varchar(10) SET @OrderBy = 'LastName'   SELECT * FROM dbo.employees WHERE EmployeeID < 4 ORDER BY (CASE @OrderBy      WHEN 'LastName' THEN LastName      WHEN 'Title' THEN Title END) ASC

 

 

 

 

  - 예제 4. 동적 정렬 기준 ( 정렬 기준 & 컬럼명 모두 유동적인 경우 ) 

동적 쿼리 형식

SELECT * FROM table ORDER BY @컬럼명 @정렬기준

정적 쿼리

DECLARE @OrderBy varchar(10), @Sequence varchar(4) SET @OrderBy = 'LastName' SET @Sequence = 'DESC'   SELECT * FROM dbo.employees WHERE EmployeeID < 4 ORDER BY (CASE @Sequence WHEN 'ASC' THEN CASE @OrderBy                          WHEN 'LastName' THEN LastName                          WHEN 'Title' THEN Title END END) ASC, (CASE @Sequence WHEN 'DESC' THEN CASE @OrderBy                            WHEN 'LastName' THEN LastName                            WHEN 'Title' THEN Title END END) DESC

 

 

 

 

 

 

 

  - 예제 5. 동적 GROUP BY, HAVING

동적 쿼리 형식

SELECT @컬럼명 FROM table GROUP BY @컬럼명 HAVING @HAVING-조건식

정적 쿼리

DECLARE @column varchar(10), @ActivateCount bit, @Count int   SET @Column = 'name'   -- Group By 기준 컬럼 SET @ActivateCount = 1-- Having 절에 적용될 컬럼 SET @Count = 4         -- Having 절에 비교될 값   SELECT CASE @column             WHEN 'Name' THEN 'Number of Employees'             WHEN 'Title' THEN 'Number of Titles'             END AS Type, CASE @column             WHEN 'Name' THEN COUNT(employeeID)             WHEN 'Title' THEN COUNT(Title)             END AS QTY, CASE @column             WHEN 'Name' THEN Country             WHEN 'Title' THEN Title             END AS GroupBy FROM dbo.employees GROUP BY (CASE @column             WHEN 'Name' THEN Country             WHEN 'Title' THEN Title             END) HAVING (CASE @column + LTRIM(STR(@ActivateCount))             WHEN 'Name1' THEN COUNT(employeeID)             WHEN 'Title1' THEN COUNT(Title)             ELSE @Count + 1             END) > @Count

 

 

 

 

 

  - 예제 6. 동적 조인 컬럼 ( 조인 컬럼을 동적으로 가져가는 경우)

동적 쿼리 형식

SELECT * FROM table1 INNER JOIN table2 ON @테이블.컬럼명 = table2.Column INNER JOIN table3 ON ...

정적 쿼리

-- 테스트를 위한 임시 테이블 CREATE TABLE #RegionSupervisors (City varchar(20), employeeID int)   INSERT INTO #RegionSupervisors (City, EmployeeID)   SELECT '<st1:city w:st="on">Bern</st1:city>', 1               UNION SELECT 'Geneve', 1    UNION SELECT 'Koln', 1         UNION SELECT '<st1:city w:st="on"> Albuquerque </st1:city>', 5 UNION SELECT '<st1:city w:st="on">Seattle</st1:city>', 5     UNION SELECT '<st1:city w:st="on"> Redmond </st1:city>', 5       UNION SELECT '<st1:city w:st="on">Kirkland</st1:city>', 5    UNION SELECT '<st1:city w:st="on"> London </st1:city>', 4        UNION SELECT '<st1:city w:st="on">Cowes</st1:city>', 4       UNION SELECT 'Colchester', 4   -- @supervisors에 따라서, 조인 컬럼을 변경하는 경우 DECLARE @supervisors varchar(10) SET @Supervisors = 'employee'   SELECT CustomerID, OrderDate, ShipCity, e.LastName AS [Assigned To] ,  e.City AS [Employee Home Office] , CASE @Supervisors WHEN 'employee' THEN 'employee supervisor: ' + se.LastName WHEN 'order' THEN 'order supervisor: ' + se.LastName <st1:city w:st="on">END</st1:city> <st1:state w:st="on">AS</st1:state> [Region Supervisor]   FROM dbo.Orders o INNER JOIN dbo.Employees e ON o.EmployeeID = e.EmployeeID INNER JOIN dbo.#RegionSupervisors s ON (CASE @Supervisors                      WHEN 'employee' THEN e.City                      WHEN 'order' THEN o.ShipCity END) = s.City INNER JOIN dbo.Employees se ON se.EmployeeID = s.EmployeeID

 

 

 

 

 

 

  - 예제 7. 동적 비교 연산자 ( 대/소 비교를 동적으로 가져가는 경우 )

동적 쿼리 형식

SELECT * FROM table WHERE column @비교연산자 @표현식

정적 쿼리

DECLARE @Sign char(1), @date datetime   SET @Sign = '>' SET @date = '19630703'   -- 1-1. SELECT * FROM dbo.employees WHERE (CASE @Sign WHEN '>' THEN BirthDate WHEN '<' THEN @date END) > (CASE @Sign WHEN '>' THEN @date WHEN '<' THEN BirthDate END)

 

 

 

 

  - 예제 8. 동적 조건자 ( 조건자 자체를 동적으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table WHERE @조건자1 AND @조건자2 , ...

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30)   SET @LastName = 'd' SET @FirstName = 'a'   SELECT * FROM dbo.employees WHERE EmployeeID < 4 WHERE (CASE WHEN @LastName <> '' THEN LastName           WHEN @LastName = '' THEN 'Eliminate' END) Like    (CASE WHEN @LastName <> '' THEN @LastName + '%'           WHEN @LastName = '' THEN 'Eliminate' END) AND    (CASE WHEN @FirstName <> '' THEN FirstName           WHEN @FirstName = '' THEN 'Eliminate' END) Like    (CASE WHEN @FirstName <> '' THEN @FirstName + '%'           WHEN @FirstName = '' THEN 'Eliminate' END)

 

 

 

 

 

  - 예제 9. 동적 계단식 조건자 ( 다양한 조건식을 계단식으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table WHERE @조건자1 [ AND @조건자2 [ AND @조건자3 [, ... ] ] ]

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30), @Country varchar(5) , @City varchar(20), @Title varchar(30)   SET @Country = 'usa ' SET @ <st1:placetype w:st="on">City</st1:placetype> = 'k' SET @LastName = '' SET @FirstName = '' SET @Title = 's'      -- 최종 결과 확인용 쿼리. SELECT * FROM dbo.employees WHERE Title LIKE @Title + '%' AND Country LIKE @Country AND City LIKE '%' + @City + '%'   -- 1. Tilte 존재 시 -> Country 존재 시 -> City 존재 시 SELECT * FROM dbo.employees WHERE CASE WHEN @Title <> '' THEN Title          WHEN @Country <> '' THEN Country          WHEN @City <> '' THEN <st1:placetype w:st="on">City</st1:placetype>          WHEN @LastName <> '' THEN LastName          WHEN @FirstName <> '' THEN FirstName END LIKE      CASE WHEN @Title <> '' THEN @Title + '%'          WHEN @Country <> '' THEN @Country          WHEN @City <> '' THEN '%' + @City + '%'          WHEN @LastName <> '' THEN @LastName + '%'          WHEN @FirstName <> '' THEN @FirstName + '%' END AND    CASE WHEN @Title <> '' THEN CASE WHEN @Country = '' THEN 'Eliminate' ELSE Country END          WHEN @Country <> '' THEN CASE WHEN @ <st1:placetype w:st="on">City</st1:placetype> = '' THEN 'Eliminate' ELSE City END          WHEN @City <> '' THEN CASE WHEN @LastName = '' THEN 'Eliminate' ELSE LastName END          WHEN @LastName <> '' THEN CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE FirstName END          WHEN @LastName = '' THEN 'Eliminate'          WHEN @FirstName = '' THEN 'Eliminate' END LIKE      CASE WHEN @Title <> '' THEN CASE WHEN @Country = '' THEN 'Eliminate' ELSE @Country END          WHEN @Country <> '' THEN CASE WHEN @ <st1:placetype w:st="on">City</st1:placetype> = '' THEN 'Eliminate' ELSE @City + '%' END          WHEN @City <> '' THEN CASE WHEN @LastName = '' THEN 'Eliminate' ELSE '%' + @LastName + '%' END          WHEN @LastName <> '' THEN CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE '%' + @FirstName + '%' END          WHEN @LastName = '' THEN 'Eliminate'          WHEN @FirstName = '' THEN 'Eliminate' END AND    CASE WHEN @Title <> '' THEN <st1:placetype w:st="on">City</st1:placetype>          WHEN @Country <> '' THEN LastName          WHEN @City <> '' THEN FirstName          WHEN @LastName = '' THEN 'Eliminate'          WHEN @LastName <> '' THEN 'Eliminate'          WHEN @FirstName <> '' THEN 'Eliminate' END LIKE      CASE WHEN @Title <> '' THEN '%' + @City + '%'          WHEN @Country <> '' THEN @LastName + '%'          WHEN @City <> '' THEN '%' + @FirstName + '%'          WHEN @LastName = '' THEN 'Eliminate'          WHEN @LastName <> '' THEN 'Eliminate'          WHEN @FirstName <> '' THEN 'Eliminate' END

 

 

 

 

 

  - 예제 10. 동적 UPDATE

 

동적 쿼리 형식

UPDATE table SET @컬럼명 = @표현식

정적 쿼리

DECLARE @colname varchar(128) SET @colname = 'orderdate'   UPDATE dbo.orders SET orderdate = CASE @colname WHEN 'orderdate' THEN getdate() ELSE orderdate END, requireddate = CASE @colname WHEN 'requireddate' THEN getdate() ELSE requireddate END   WHERE orderid = 10248 go