동적쿼리의 올바른 사용
매개변수로 인해 쿼리 스트링이 변경될 수 있다. 그 중에서도 컬럼명이나 테이블명, 데이터베이스 명이 바뀔 수 있을 때 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 Query2의EXEC 사용
DECLARE @ParameterTableName VARCHAR(MAX)
, @iIDX INT
select @ParameterTableName = 'TableEXEC'
, @iIDX = 1
EXEC ('SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + @iIDX)
--*/ -- 실행영역4
--/*-- 실행영역5 Query2의EXEC 사용
DECLARE @ParameterTableName VARCHAR(MAX)
, @iIDX INT
select @ParameterTableName = 'TableEXEC'
, @iIDX = 2
EXEC ('SELECT * FROM dbo.'+ @ParameterTableName +' WHERE IDX =' + @iIDX)
--*/ -- 실행영역5
--/*-- 실행영역6 Query2의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
--*/ -- 실행영역6
--/*-- 실행영역7 Query2의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
--*/ -- 실행영역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
'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 |