동적 쿼리 및 정적 쿼리 (개요)

동적 쿼리 및 정적 쿼리 (개요)

2008.04.20 00:15 | 튜닝 | David

http://kr.blog.yahoo.com/fazio127/56 

성능 향상을 위해서는 SQL 서버 측의 튜닝도 필요하지만 정작 쿼리를 날리는 클라이언트 및 다른 비지니스 Layer 등에서도 쿼리를 작성 시 유의해야 할 부분이 있는데 그 중에서도 개발자들이 동적쿼리를 많이 사용하고 있어 이 부분에 대한 가이드를 소개코자 합니다. 김정선 님께서 작성한 "서버 측 동적 쿼리에서의 탈출"내용을 바탕으로 요약해서 올리며 다음 Article에서 사례를 중심으로 알아 보도록 하겠습니다. Overview 수년 전부터 MS 플랫폼에서 SQL Server를 연동한 프로젝트 수행 시 개발 방법론의 중요한 한 축으로 저장 프로시저의 적극적인 활용이 일반화되고 있다. 그로 인한 부작용 중에 하나는 이전에 응용 프로그램 아키텍처 상의 프레젠테이션 계층 혹은 비즈니스 서비스 계층에서 문자열 데이터로 동적으로 구성하던 쿼리가 저장 프로시저를 통해서 서버 측에서 구성이 된다는 것이다. "서버 측 동적 쿼리"(이하 동적 쿼리로 표기)라고 표현하는 이러한 동적 TSQL 또는 일괄처리는 성능상의 문제를 내포함과 동시에,개발 생산성과 관리 및 유지 보수의 용이성 측면에서 많은고려 사항을 포함하고 있다. 동적 쿼리 1. 정의 - 코드의 실행 시점에 SQL 문이 동적으로 구성되고 실행되는 쿼리 2. 용어 - 플랫폼이나 개발 언어에 따라 Dynamic Query, Hard Coded Query, Ad hoc Query 등으로 불려짐. [참고] SQL Server .NET Data Provider 의 경우엔 동적 쿼리를 호출하더라도 순수한 쿼리 형태로 호출되지 않고, SQL Server의 확장 저장 프로시저, sp_executesql 을 통해서 자동 변환이 된다. 이는 Ad hoc 쿼리와 Precompiled 쿼리(저장 프로시저, 트리거 등)의 중간 형태인,Parameterized 쿼리의 생성 방법 중의 하나로써 .NET 기반 데이터베이스 응용프로그램 개발자들이 기본적으로 숙지해야 할 시스템프로시저 중의 하나입니다. 서버 측 동적 T-SQL 혹은 동적 일괄 처리(Batch) 1. 서버 측의 T-SQL 쿼리 오브젝트(저장 프로시저, 사용자-정의 함수, 트리거, 등)를 사용 권장 2. 이 방법을 통해 이득이 많은 반면, 서버 측에서 동적 쿼리의 필요성이란 문제가 대두. 3. SQL Server 2000 버전에서 저장 프로시저, 트리거, 혹은 스크립트 내에서 런타임 시에 쿼리를 동적으로 구성하기 위해    sp_executesql 또는 execute 문 실행 동적 쿼리 Vs. 정적 쿼리(비 동적 쿼리) 1. 동적 쿼리의 문제점    1) 실행 시마다 컴파일을 반복하게 됨으로써 PreCompiled/Procedure Cache 재사용성을 저해    2) Ownership Chain 권한 처리 문제    3) SQL Injection 관련 보안 문제    4) 공통 모듈에 대한 관리 및 배포 어려움 2. 저장 프로시저를 사용하는 이유    1) 실행 계획 Caching을 통한 성능 이익    2) Network Traffic 최소화    3) 출력 Parameter, Return 값 사용    4) 소유권 체인을 통한 권한 처리, SQL Injection 차단 보안 기능    5) 업무 논리의 캡슐화, 모듈화    6) SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능 등.. 3. 동적 쿼리가 가지고 있는 문제점을 저장 프로시저 안에 재현하는 것은 2번에서 언급한 저장 프로시저의 장점을 제대로 살리지 못함을 의미, 현재 저장 프로시저의 적지 않은 코드가 동적 쿼리 형태로 작성되고 있으며, 상당 수 정적 쿼리로 재 작성 가능 사례 사례1, 사례2, 사례3, 사례4, 사례5, 사례6