sq_execute & exec()

오늘 갑자기 SQL Server 에서 동적쿼리를 실행을 시킬때 사용하는 sp_executesql 와 exec()에 대해서 정리를 하고 싶었다. 머 사실 내가 팀원들에게 한달에 한번씩 어떠한 주제는 상관없이 SQL Server에 관련된 것을 하나씩 팀블로그(http://gdbt.tistory.com)에 포스팅해라!! 라고 해서.. 나도 어쩔 수 없이 포스팅을 해야 하기에 고민하던 중 문득 생각이 나서 정리 하는 것이다.

예전 부터 "Dynamic Query는 가급적이면 사용하지 말아라! 되도록이면 Stored Procedure을 사용해라"이런 문구를 많이들 보았을 것이다. 이는 Dynamic Query는 실행시킬때마다 실행플랜이 캐쉬에 올라가기 때문이다. 물론 Stored Procedure는 한번 실행할때 플랜이 캐쉬되고 그 캐쉬를 제 활용 한다. 언제까지? Stored procedure의 내용이 수정되기 전 또는 인덱스 통계가 갱신되기 전까지..

우리는 Dynamic Query를 실행시킬때 exec()또는 sp_executesql을 사용한다. 그럼 exec()하고 sp_executesql은 어떻게 다른가? 천천히 알아보자 *^^*

일반적으로 sp_executesql이 exec()보다 몇가지 장점을 제공 한다고 한다.

Query 에 매개변수(입력/출력)을 정의 할수 있다.
매개변수 사용으로 인하여 쿼리 최적화 프로그램이 컴파일된 실행플랜을 재 사용할 확률이 높아진다.
sp_executesql은 시스템저장프로시져가 아닌 확장 저장프로시져이다.

sp_executesql을 사용할때도 주의할점이 몇가지 있기는 하다. 지원이 안되는 부분이 몇가지가 존재한다.

동적 SQL문에서는 RETURN문을 사용할 수 없다.
로컬 별수를 액세스 할 수 없다.
현재 데이터베이스가 변경되지 않는다.

해당 sp_executesql에 대해서 더욱 자세한 설명을 보고 싶다면 MSDN을 참고 하기 바란다.
참고 http://msdn.microsoft.com/ko-kr/library/ms175170.aspx

그럼 먼저, sp_executesql이 지원이 안되는 부분부터 살펴보자. 해당 Transact-SQL은 MSDN에 있는것을 그대로 복사하여 사용한 것이다.

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';


/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE test;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM dbo.test
GO

첫째, sp_executesql는 로컬변수를 사용할 수 없다. 아래 스크린샷을 참고 하자

둘째, 현재 데이터베이스가 변경되지 않는다.

자, 이제 그럼 sp_executesql 장점을 살펴보지 앞서 아래와 같이 TEST용 Database와 TEST용 Table을 생성하자.

-- TEST에 사용될 database 만들기
CREATE DATABASE test

-- TEST에 사용될 계정 만들기
USE [test]
GO
CREATE LOGIN login1 WITH PASSWORD = '1111';
CREATE LOGIN login2 WITH PASSWORD = '1111';

USE [test]
GO
CREATE USER [login1] FOR LOGIN [login1]
CREATE USER [login2] FOR LOGIN [login2]
GO

-- TEST에 사용될 테이블 만들기
CREATE TABLE test (idx INT);
GO

-- TEST에 사용될 테이터 입력하기
DECLARE @i INT
SET @i = 0

WHILE @i < 100
BEGIN
	INSERT INTO test (idx) VALUES (@i);
	SET @i = @i + 1
END

TEST용 Database와 TEST용 Table을 만들었으면, 본격적으로 테스트를 해보자.
첫째, Query 에 매개변수(입력/출력)을 정의 할수 있다.

declare @stmt as nvarchar(100)
declare @params as nvarchar(100)
declare @orderret as int
 

set @stmt = 'select @ordercnt = count(*) from dbo.test'
set @params = '@ordercnt as int OUTPUT' 
exec sp_executesql @stmt, @params, @ordercnt = @orderret OUTPUT
select @orderret

해당 쿼리는 test 테이블의 count(*)을 output 하는 쿼리이다.
실행결과는 아래 스크린샷을 참고 하자.

다들 예상하셨겠지만, OUTPUT이 보란듯이 찍힌다.
자 이제 그럼 중요한 실행플랜 제 사용을 보자. 앞서 이야기 했지만, 예전에는 APP(응용프로그램)단에서 들어오는 Ad-hoc Query가 심각했다. 성능적으로도 그랬고 SQL 인젝션에도 취약했다. 성능적으로 취약한 부분은 Dynamic Query는 실행플랜을 재 사용을 하지 못하고 실행될 때마다 플랜을 캐쉬에 만들기 때문에 성능에 안좋은 영향을 준 것이다. 그럼 이제 우리가 볼 sp_executesql은 ?

--저장프로시져 테스트용
DROP PROC dbo.upOrderDetailsQuery
@orderid INT
GO
CREATE PROC dbo.upOrderDetailsQuery
@orderid AS INT 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM test.dbo.test WHERE idx = @orderid
END
GO
EXEC sp_executesql @stmt, @paramas, @orderid = 4278

--exec
DECLARE @stmt AS NVARCHAR(100)
DECLARE @orderid AS NVARCHAR(100)
SET @orderid = 2580
SET @stmt = 'select * from test.dbo.test where idx = ' + @orderid
EXEC(@stmt)

--sp_executesql
DECLARE @stmt AS NVARCHAR(100)
DECLARE @paramas AS NVARCHAR(100)

SET @stmt = 'select * from test.dbo.test where idx = @orderid'
SET @paramas = '@orderid int'
EXEC sp_executesql @stmt, @paramas, @orderid = 4278
GO 

위 Transact-SQL을 실행시킬때 우리는 성능카운터 SQLServer:Plan Cache 의 Cache Object Counts을 참고하면 좋을 듯 하다. 또한, 위 Transact-SQL을 실행시킬때 아래와 같은 구분을 추가하여 실행을 시키자.

-- 실행플랜 초기화
DBCC FREEPROCCACHE
-- 캐쉬에 올라와 있는 플랜
select * from master..syscacheobjects where dbid = db_id('test')

해당 쿼리를 실행시키면, 우리도 알고 있듯이 저장프로시져는 실행플랜을 재 활용하고, exec()는 실행플랜을 재 활용하지 못하고 계속 만든다.

그럼, sp_executesql은 ? 실행플랜을 재 활용한다.