달력

32024  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
출처 : http://msdn2.microsoft.com/ko-kr/library/3517w44b(VS.80).aspx


.NET Framework 개발자 가이드 
SQL Server에 BLOB 값을 쓸 때 리소스 절약 

데이터베이스의 필드 형식에 따라 필드에 문자열 값이나 바이트 배열을 삽입하거나 업데이트하여 데이터베이스에 BLOB(Binary Large Object)를 쓸 수 있습니다(데이터 소스에 BLOB 값 쓰기 참조). 그러나 BLOB가 너무 크면 단일 값으로 쓸 때 시스템 메모리를 너무 많이 차지하여 응용 프로그램 성능이 낮아질 수 있습니다.

BLOB 값을 쓸 때 사용되는 메모리 양을 줄이는 일반적인 방법은 데이터베이스에 BLOB를 "청크"로 쓰는 것입니다. 이 방식으로 데이터베이스에 BLOB를 쓰는 과정은 데이터베이스의 성능에 따라 달라집니다.

다음 샘플에서는 SQL Server에 BLOB를 청크로 쓰는 방법을 보여 줍니다. 이 샘플에서는 Northwind 데이터베이스의 Employees 테이블에 BLOB인 직원 이미지를 비롯한 새 레코드를 추가합니다. 즉, SQL Server의 UPDATETEXT 함수를 사용하여 Photo 필드에 새로 추가한 직원의 이미지를 지정된 크기의 청크 단위로 씁니다.

UPDATETEXT 함수를 사용하려면 업데이트되는 BLOB 필드에 대한 포인터가 있어야 합니다. 이 샘플에서는 새 직원 레코드를 추가할 때 SQL Server TEXTPTR 함수가 호출되어 새 레코드의 Photo 필드에 대한 포인터가 반환됩니다. 반환된 포인터 값은 출력 매개 변수로 다시 전달됩니다. 이 샘플 코드에서는 이 포인터가 유지되며 데이터의 청크를 추가할 때 UPDATETEXT로 전달됩니다.

다음은 새 직원 레코드를 삽입하고 Photo 필드에 대한 포인터를 유지하는 데 사용되는 Transact-SQL의 예제입니다. 여기서 @Identity@PointerSqlCommand의 출력 매개 변수로 인식됩니다.

INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) 
  Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity

0x0의 초기 값(null)이 Photo 필드에 삽입되기 때문에 새로 삽입하는 레코드의 Photo 필드에 대한 포인터 값을 검색할 수 있습니다. 그러나 null 값은 추가된 데이터 청크에는 영향을 주지 않습니다.

새로 삽입한 레코드에 Photo 필드에 대한 포인터가 유지되면 이 샘플을 통해 SQL Server의 UPDATETEXT 함수를 사용하여 BLOB 필드에 데이터 청크를 추가할 수 있습니다. UPDATETEXT 함수는 Employees.Photo와 같은 필드 식별자, BLOB 필드에 대한 포인터, 현재 청크가 쓰여질 BLOB의 위치를 나타내는 오프셋 값 및 추가할 데이터 청크를 입력으로 사용합니다. 다음 코드 예제에서는 UPDATETEXT 함수에 대한 구문을 보여 줍니다. 여기서 @Pointer, @Offset,@BytesSqlCommand의 입력 매개 변수로 식별됩니다.

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

오프셋 값은 응용 프로그램의 필요에 따라 사용자가 정하는 메모리 버퍼 크기에 의해 결정됩니다. 버퍼 크기가 크면 BLOB 쓰기 속도는 빠르지만 시스템 메모리가 더 많이 사용됩니다. 이 샘플에서는 비교적 적은 128바이트의 버퍼 크기를 사용합니다. 오프셋 값은 데이터의 첫 번째 청크에 대해 0부터 시작하여 각 연속 청크에 대해 버퍼 크기만큼 늘어납니다.

이 예제의 경우 제시한 파일 경로에서 직원 사진을 청크 단위로 검색합니다. 각 청크를 지정된 버퍼 크기당 바이트 배열로 읽어옵니다. 그러면 바이트 배열은 SqlCommand@Bytes 입력 매개 변수 값으로 설정됩니다. 그런 다음 @Offset 매개 변수 값이 업데이트되고 SqlCommand가 실행되어 직원 레코드의 Photo 필드에 현재 청크 바이트가 추가됩니다.

Visual Basic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class EmployeeData

    Public Shared Sub Main()
      Dim hireDate As DateTime = DateTime.Parse("4/27/98")
      Dim newID As Integer = _
          AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp")
      Console.WriteLine("New Employee added. EmployeeID = " & newID)
    End Sub

    Public Shared Function AddEmployee(lastName As String, firstName As String, title As String, hireDate As DateTime, _
       reportsTo As Integer, photoFilePath As String) As Integer

    Dim connection As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;")

    Dim addEmp As SqlCommand = New SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
      "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" & _
        "SELECT @Identity = SCOPE_IDENTITY();" & _
        "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", connection) 

    addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName
    addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName
    addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title
    addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value     = hireDate
    addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value          = reportsTo

    Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity", SqlDbType.Int)
    idParm.Direction = ParameterDirection.Output
    Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
    ptrParm.Direction = ParameterDirection.Output

    connection.Open()

    addEmp.ExecuteNonQuery()

    Dim newEmpID As Integer = CType(idParm.Value, Integer)

    StorePhoto(photoFilePath, ptrParm.Value, connection)

    connection.Close()

    Return newEmpID
  End Function

  Public Shared Sub StorePhoto(fileName As String, pointer As Byte(), connection As SqlConnection)

    Dim bufferLen As Integer = 128   ' The size of the "chunks" of the image.

    Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", connection)

    Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
    ptrParm.Value = pointer
    Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)
    Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int)
    offsetParm.Value = 0

    ''''''''''''''''''''''''''''''''''''
    '' Read the image in and write it to the database 128 (bufferLen) bytes at a time.
    '' Tune bufferLen for best performance. Larger values write faster, but
    '' use more system resources.


    Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)
    Dim br As BinaryReader = New BinaryReader(fs)

    Dim buffer() As Byte = br.ReadBytes(bufferLen)
    Dim offset_ctr As Integer = 0

    Do While buffer.Length > 0
      photoParm.Value = buffer
      appendToPhoto.ExecuteNonQuery()
      offset_ctr += bufferLen
      offsetParm.Value = offset_ctr
      buffer = br.ReadBytes(bufferLen)
    Loop

    br.Close()
    fs.Close()
  End Sub

End Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
  public static void Main()
  {
    DateTime hireDate = DateTime.Parse("4/27/98");
    int newID  = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp");
    Console.WriteLine("New Employee added. EmployeeID = " + newID);
  }

  public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
  {
    SqlConnection connection = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");

    SqlCommand addEmp  = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
      "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
      "SELECT @Identity = SCOPE_IDENTITY();" +
      "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", connection);

    addEmp.Parameters.Add("@LastName",  SqlDbType.NVarChar, 20).Value = lastName;
    addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
    addEmp.Parameters.Add("@Title",     SqlDbType.NVarChar, 30).Value = title;
    addEmp.Parameters.Add("@HireDate",  SqlDbType.DateTime).Value = hireDate;
    addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo;

    SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
    idParm.Direction = ParameterDirection.Output;
    SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Direction = ParameterDirection.Output;

    connection.Open();

    addEmp.ExecuteNonQuery();

    int newEmpID = (int)idParm.Value;

    StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection);

    connection.Close();

    return newEmpID;
  }

  public static void StorePhoto(string fileName, byte[] pointer,  SqlConnection connection)
  {
    int bufferLen = 128;  // The size of the "chunks" of the image.

    SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", connection);

    SqlParameter ptrParm  = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Value = pointer;
    SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen);
    SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int);
    offsetParm.Value = 0;

    //''''''''''''''''''''''''''''''''''
    // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
    // Tune bufferLen for best performance. Larger values write faster, but
    // use more system resources.

    FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    BinaryReader br = new BinaryReader(fs);

    byte[] buffer = br.ReadBytes(bufferLen);
    int offset_ctr = 0;

    while (buffer.Length > 0)
    {
      photoParm.Value = buffer;
      appendToPhoto.ExecuteNonQuery();
      offset_ctr += bufferLen;
      offsetParm.Value = offset_ctr;
      buffer = br.ReadBytes(bufferLen);
    }

    br.Close();
    fs.Close();
  }
}

참고 항목

Posted by tornado
|

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

  • 김정선
  • 삼성 SDS 멀티캠퍼스
  • 전임 강사
  • Microsoft SQL Server MVP



요약

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


목차

  1. 동적 쿼리
  2. 동적 쿼리 대 정적 쿼리(비 동적 쿼리)
  3. 탈출을 위한 시작, 사례별 예제
  4. 마치면서...

동적 쿼리

일반적으로 언급하는 동적 쿼리란, 코드의 실행 시점에 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 'Bern', 1 UNION SELECT 'Geneve', 1
UNION SELECT 'Koln', 1 UNION SELECT ' Albuquerque ', 5
UNION SELECT 'Seattle', 5 UNION SELECT ' Redmond ', 5
UNION SELECT 'Kirkland', 5 UNION SELECT ' London ', 4
UNION SELECT 'Cowes', 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 END AS [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 @ City = '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 City
      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 @ City = '' 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 @ City = '' 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 City
       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


마치면서 … 단순한 사례들만을 늘어 놓았다. 최적 사례에 대한 소개가 생략된 것이다. 다음에 또 기회가 된다면, 그 때는 몇 가지 최적 사례에 대하여 논의해 볼 것이다.

예제는 예제일 뿐이다. 이 글을 읽는 분 들이 예제를 여과 없이 수용하고 개발에 적용한다면 분명 필자가 욕을 먹을 것이다. 위 예제를 예제로서 보지 말고 성능 상의 문제로까지 연결해서 분석해야 한다. 예제 중의 일부는 여전히 성능 문제를 해결하지 못하고 있으며, 여러분의 노력에 따라 더 나은 성능을 제공하는 쿼리로 변경하거나 재 개발할 수 있을 것이다. 이는 필자가 직접적으로 그 내용을 포함하지 않은 이유이기도 하다. 무엇이 문제인지를 모르고 해답만을 논할 수는 없지 않은가? 그리고 사실 이것이 더 재미있다! 그러니 오해가 없기를 바란다. 다시 한 번 당부하는 것은, 반드시 스스로 테스트하고 또 성능 문제로까지 확장해서 분석해 보아야 한다. 아마 짧지 않은 시간이 필요할 것이다!

객관식에 몇 가지 보기를 추가했을 뿐이며, 아직 많은 보기가 남아 있다. 또한, 이미 언급한대로 좋은 보기만을 제시한 것도 아니다. 다양한 사례를 통해 경험을 축적하고 이를 지식 데이터베이스화해야 한다. 최종적으로 여러분 스스로 보기를 만들어내고 가장 정답에 가까운 해답을 찾아낼 수 있는 능력을 길러야 한다.

동적 쿼리의 사용이 무조건 나쁘다는 것이 아니다. 비즈니스 모델의 특수성, 데이터베이스 모델의 변화, 단축된 개발 주기, 개발 생산성에 대한 이슈 등의 동적 쿼리를 필요로 하는 상황이 많이 발생한다. 문제는 무분별하게 과용되고 있다는 것이다. 동적 쿼리가 요구될 때, 기본적인 검토를 할 필요가 있다. 동적 쿼리를 사용하는 것이, 성능 대비 개발 생산성, 관리 용이성 등의 상대적 이득이 충분하다고 판단되어야 할 것이다. 지금 이 글을 읽고 난 뒤에, 여러분이 현재 개발하거나 운영 중인 데이터베이스의 얼마나 많은 동적 쿼리가 사용 중인지를 살펴 보자. 그리고 얼마나 무분별하게 과용되고 있는지도 평가해 보아야 할 것이다.




Posted by tornado
|