SQL Server

동적쿼리 제대로 알고 활용하자.

서른마른다섯 2012. 3. 28. 15:40

동적쿼리의 올바른 사용


매개변수로 인해 쿼리 스트링이 변경될 수 있다. 그 중에서도 컬럼명이나 테이블명, 데이터베이스 명이 바뀔 수 있을 때 SQL을 실시간으로 작성하여 DBMS가 처리하도록 한 것을 동적쿼리라 한다.


동적쿼리는 EXEC ('QueryString') 와 EXEC sp_executesql N'QueryString' 의 두가지 형태로 실행될 수 있다. 전자의 방법으로 실행할 경우 실행계획을 재사용 할 수 없기 때문에 일반적으로 후자를 사용할 것을 권고한다. OUTPUT을 사용할 수 있다는 것도 강점이다.


EXEC sp_executesql N'QueryString' 를 사용할 경우에도 실행계획 사용 불가의 함정에 빠질 수 있으므로 유의할 필요가 있다.


몇가지 예시를 보자.

 Query1- SELECT * FROM TableName WHERE Col1 = @ParameterValue

위의 쿼리는 동적쿼리로 만들 필요가 없다. value에 대한 값을 변수로 사용하기 때문이다.


 Query2- SELECT * FROM @ParameterTableName

위의 쿼리는 동적쿼리의 이미를 가질 수 있다. 테이블 명을 변수화 하여 @ParameterTableName 값에 따라

여러 테이블에 작업을 할 수 있기 때문이다.


테스트를 해보면


--/*-- 실행영역1

    -- DB 버퍼를비우자.

    DBCC DROPCLEANBUFFERS

    -- 프로시저캐시를비우자.

    DBCC FREEPROCCACHE

--*/ -- 실행영역1

 

--/*-- 실행영역2 Query1의형태

    DECLARE @iIDX BIGINT

    select  @iIDX = 1

    SELECT * FROM dbo.TableAdhoc WHERE IDX = @iIDX

--*/ -- 실행영역2

 

--/*-- 실행영역3 Query1의형태

    DECLARE @iIDX BIGINT

    select  @iIDX = 2

    SELECT * FROM dbo.TableAdhoc WHERE IDX = @iIDX

--*/ -- 실행영역3

 

--/*-- 실행영역4 Query2EXEC 사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    select  @ParameterTableName = 'TableEXEC'

    ,       @iIDX               = 1

    EXEC ('SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + @iIDX)

--*/ -- 실행영역4

 

--/*-- 실행영역5 Query2EXEC 사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    select  @ParameterTableName = 'TableEXEC'

    ,       @iIDX               = 2

    EXEC ('SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + @iIDX)

--*/ -- 실행영역5

 

--/*-- 실행영역6 Query2Executesql 사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 1

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + CAST(@iIDX AS NVARCHAR(5))

    EXEC sp_executesql @SQL

--*/ -- 실행영역6

 

--/*-- 실행영역7 Query2Executesql 사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 2

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + CAST(@iIDX AS NVARCHAR(5))

    EXEC sp_executesql @SQL

--*/ -- 실행영역7

 

--/*-- 실행영역8

    -- 시스템카탈로그를이용하여확인해보자.

SELECT sql, cacheobjtype, objtype, usecounts, pagesused

FROM SYS.SYSCACHEOBJECTS

WHERE dbid = DB_ID('TEST')

--*/ -- 실행영역8

 

 

<그림1>과 같은 결과를 볼 수있다. (순서가 섞여있으니 잘 봐야한다.)

EXEC(실행영역4, 5) 를 통해 실행할 경우 실행 결과가 파란 박스에서 보이는 것처럼 Adhoc으로 모두 실행된다.

sp_Executesql(실행영역 6, 7)을 통해 실행했을 때 빨간박스 Prepared로 보이나 사실상 usecount가 1로 Adhoc으로 동작했다는 것을 알 수 있다. @1 이란 변수를 만들어 value를 Parameter화 하여 실행을 하지만 실제 실행에서 value를 고정하기 때문에 각각은 다른 쿼리로 인식되어 실행계획을 세우는 것을 알 수 있다.

이것이 바로 EXEC sp_executesql N'QueryString' 를 사용할 경우의 실행계획 사용 불가 함정이다.

실행이 되기전 이미 쿼리스트링이 완전히 완성 되었기 때문에 동적으로 처리하는 것 처럼 보여도 옵티마이저는 다른 쿼리가 실행된 것으로 판단한다.


실행계획을 재사용 하도록 수정해보자.

--/*-- 실행영역1

    -- DB 버퍼를비우자.

    DBCC DROPCLEANBUFFERS

    -- 프로시저캐시를비우자.

    DBCC FREEPROCCACHE

--*/ -- 실행영역1

 

--/*-- 실행영역2 Executesql 실행계획미사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 1

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + CAST(@iIDX AS NVARCHAR(5))

    EXEC sp_executesql @SQL

--*/ -- 실행영역2

 

--/*-- 실행영역3 Executesql 실행계획미사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 2

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + CAST(@iIDX AS NVARCHAR(5))

    EXEC sp_executesql @SQL

--*/ -- 실행영역3

 

--/*-- 실행영역4 Executesql 실행계획재사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 3

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX = @iIDX'

    EXEC sp_executesql @SQL, N'@iIDX BIGINT', @iIDX

--*/ -- 실행영역4

 

--/*-- 실행영역5 Executesql 실행계획재사용

    DECLARE @ParameterTableName VARCHAR(MAX)

    ,       @iIDX               INT

    ,       @SQL                NVARCHAR(MAX)

    select  @ParameterTableName = 'TableExecutesql'

    ,       @iIDX               = 4

    ,       @SQL                = N'SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX = @iIDX'

    EXEC sp_executesql @SQL, N'@iIDX BIGINT', @iIDX

--*/ -- 실행영역5

 

--/*-- 실행영역6

    -- 시스템카탈로그를이용하여확인해보자.

SELECT sql, cacheobjtype, objtype, usecounts, pagesused

FROM SYS.SYSCACHEOBJECTS

WHERE dbid = DB_ID('TEST')

--*/ -- 실행영역6


 


<그림2>의 결과를 볼 수 있다.

파란 박스를 보면 확실히 실행 계획을 재사용 한 것을 알 수 있다.

별도의 변수를 만들고 다시 실행하는 과정도 없어졌다.


동적쿼리 바로 알고 바로 쓸 수 있도록 해야하겠다.


ps1.

동적쿼리를 이용할 경우 파라미터 스니핑 현상을 유의해야 한다.

파라미터 스니핑이란 동적쿼리 사용 시 파라미터의 입력에 따른 최적화되지 못한 실행계획으로 인해

성능 저하를 일으키게 되는 현상을 일컫는다.


ps2.

SQL SERVER 2008 이상에서는 옵션으로 Adhoc 쿼리도 실행계획을 재사용 할 수 있다고 한다.

http://hyoksong.tistory.com/38

 

20120328 1 동적쿼리 데이터 생성.sql

20120328 2 동적쿼리 테스트.sql

20120328 3 동적쿼리 테스트.sql

'SQL Server' 카테고리의 다른 글

unable to begin a distributed transaction  (0) 2012.07.24
Index Rebuild  (0) 2012.07.19
2008 서버에서 성능데이터 SQL Server로 수집하기  (0) 2012.05.21
MySQL DBLink  (0) 2012.04.05
COLLATION  (0) 2012.03.29