EXEC와 동적(adhoc) 쿼리

1. EXEC와 동적(adhoc) 쿼리

 

자세한 내용은 MSDN의 EXECUTE 페이지를 참고하자.

(내용 겁나 길다 ㅋ)

 

EXECUTE 문은 SQL 문장을 실행시키는 역할을 한다.

EXEC와 동일하기에, 보통 EXEC로 많이들 사용한다.

 

(시스템/유저정의/CLR) 저장 프로시져를 직접 호출할 수 있으며, 문자열 쿼리를 실행시킬 수 있다.

 

다음 두 개의 예제는 차례대로 저장 프로시져와 문자열 쿼리를 EXEC로 실행하는 내용을 포함하고 있다.

 

1. -- #1. 저장 프로시져 호출 예제

2. USE AdventureWorks2012

3. GO

4.  

5. -- uspGetExployeeManagers 라는 저장 프로시져에 인자 6을 넘기며 실행시킨다

6. -- usp (user stored procedure)

7. EXEC dbo.uspGetEmployeeManagers 6

8. GO

9.  

10. -- #2. 직접 문자열 쿼리 실행 예제

11. USE Son

12. GO

13.  

14. DECLARE @SQLStmt VARCHAR(80) =

15.         'SELECT * FROM UserTable WHERE ID = ''LHJ'''

16. EXEC(@SQLStmt)

 

이와 같이 EXEC 문을 사용하여, 쿼리문 내에서 다른 쿼리문을 실행시킬 수 있다.

 

이렇게 EXEC()를 이용해서 쿼리문을 실행하는 것을 동적(adhoc) 쿼리라고 한다.

 

 

2. 동적 쿼리를 가급적 쓰지 말아야 하는 이유

 

저장 프로시져는 실행 계획이 플랜 캐쉬에 캐싱된다.

즉, 한번 컴파일되면 이것이 캐쉬에 저장되고, 재사용이 되면 될수록 재컴파일이 필요없기에 효율이 올라간다.

 

그렇다면 저장 프로시져는 언제까지 실행 계획이 캐시에 유지될까?

• 저장 프로시져가 수정되거나
• 인덱스 통계가 재빌드되기 전까지

 

하지만, 동적 쿼리는 시스템에 캐싱되지 않아 호출할 때마다 컴파일을 해야하고, 

이것이 결국 서버의 성능을 떨어트리게 된다.

 

동적 쿼리라고 해서 무조건 실행 계획에 캐슁되지 않는 것은 아니지만,

매개 변수가 하나라도 바뀌면 이것은 새로 컴파일되고 새로 캐슁된다. (EXEC의 경우 그렇다)

 

즉, 조금이라도 쿼리문이 바뀌면 이것은 새로 컴파일 -> 기존 캐시 미사용 -> 성능 하락이 발생하는 것이다.

 

동적 쿼리를 쓰는 방법은 다음 두 가지가 있다.

• EXEC
• EXEC sp_executesql

 

EXEC()는 위에서 얘기한 매개변수가 하나라도 바뀌면 재컴파일됨이 확실하다.

 

하지만, EXEC sp_executesql은 EXEC()와는 다르며, 조금 더 융통성 있고 효율적이라고 할 수 있다.

(자세한 내용은 MSDN의 sp_executesql 사용 페이지를 참고하자)

 

우선 EXEC에 비해 EXEC sp_executesql이 가지는 장점은 아래와 같다.

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

 

EXEC sp_executesql은 매개변수를 정의할 수 있고 이를 파라미터화 할 수 있기 때문에

쿼리문이 고정된 채 매개변수만 바뀌는 경우라면, 첫번째 실행에서 실행 계획을 캐싱, 재사용하게 된다.

이로 인해 EXEC()에 비해 많은 성능상 이득을 취할 수 있게 된다.

 

따라서, 어쩔 수 없이 동적 쿼리를 사용해야 한다면, EXEC() 보다 EXEC sp_executesql을 사용하도록 하자.

 

 

그리고 다음은 EXEC sp_executesql의 제약 사항이다.

• 현재 데이터베이스가 변경되지 않는다.
• 로컬 변수에 접근할 수 없다.

 

위 내용에 대해 아래 예제가 이해하는 데 도움을 줄 것이다.

 

1. -- 다음의 일괄처리에서 EXEC sp_executesql은 로컬 변수에 접근하지 못한다.

2. DECLARE @CharVariable CHAR(3) = 'abc'

3. EXEC sp_executesql N'PRINT @CharVariable';

4. GO

5.  

6.  

7. USE master;

8. GO

9. -- sp_executesql이 끝나고 나면 Database context가 리셋된다

10. EXECUTE sp_executesql N'USE Son'

11. GO

12.  

13. -- 아래 구문은 실패하는데, 이미 Database context가 master로 리셋되었기 때문이다.

14. SELECT * FROM Son.UserTable;

15. GO

 

 

3. EXEC()와 EXEC sp_executesql의 성능 비교

 

(다음 예제는 "http://sungedb.tistory.com/99" 에서 퍼왔습니다)

 

위에서 EXEC()는 매개변수만 바뀌어도 새로 컴파일되서 재사용이 불가능하다고 하였고,

EXEC sp_executesql은 매개변수가 바뀌어도 구문이 변하지 않으면, 재사용이 가능하다고 하였다.

 

 

이제 아래 예제를 통해 그 차이를 확실히 느껴보자.

 

1. 우선 테스트에 사용될 DB를 생성하자.

 

1. ------------------------------------------------------------

2. -- 테스트 테이블

3. ------------------------------------------------------------

4. CREATE TABLE TAB_A

5. (

6.     PK_COL      INT NOT NULL,

7.     COL1        INT NOT NULL,

8.     COL2        INT NOT NULL

9. );

10.  

11. ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY (PK_COL);

12. GO

13.  

14. CREATE TABLE TAB_B

15. (

16.     PK_COL      INT NOT NULL,

17.     COL1        INT NOT NULL,

18.     COL2        INT NOT NULL

19. );

20.  

21. -- 조인에 사용될 제약 조건

22. ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY (PK_COL);

23. ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B FOREIGN KEY (PK_COL);

24. REFERENCES dbo.TAB_A(PK_COL);

25. GO

 

2. 이제 테스트 쿼리를 작성해 보자.

 

1. -- 측정하기 전에 플랜캐쉬를 초기화하자

2. DBCC FREEPROCCACHE;

3.  

4. DECLARE @SQLStmt NVARCHAR(MAX);

5. DECLARE @PARAM_DEF NVARCHAR(MAX);

6.  

7. SET @SQLStmt =

8.     N'SELECT A.COL1, A.COL2, B.COL1, B.COL2

9.      FROM dbo.TAB_A A

10.      INNER JOIN dbo.TAB_B B

11.      ON B.PK_COL = A.PK_COL

12.      WHERE A.COL1 = @VAL;';

13.          

14. SET @PARAM_DEF = N'@VAL INT';

15.  

16. DECLARE @I INT;

17. SET @I = 0;

18. -- 같은 쿼리가 캐싱되는지 확인하기 위해서 값만 바꿔가면서 반복 실행한다.

19. WHILE (@I < 10)

20. BEGIN

21.     -- EXEC sp_executesql

22.     EXEC sp_executesql @SQLStmt, @PARAM_DEF, @VAL = @I; -- 값만 바꿔서 넘겨준다.

23.        

24.     DECLARE @PARAM NVARCHAR(MAX);

25.     SET @PARAM = CAST(@I AS NVARCHAR(MAX));

26.        

27.     EXEC (N'SELECT A.COL1, A.COL2, B.COL1, B.COL2

28.            FROM dbo.TAB_A A

29.            INNER JOIN dbo.TAB_B B

30.            ON B.PK_COL = A.PK_COL

31.            WHERE A.COL1 = ' + @PARAM + N';'); -- 값이 바뀌면서 쿼리문 전체가 바뀐다.

32.  

33.     SET @I = @I + 1;

34. END

35.  

36. -- 아래는 실행 계획 캐쉬의 정보를 보여준다.

37. SELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text

38. FROM sys.dm_exec_cached_plans cp

39. CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

40. WHERE cp.objtype IN ('Proc', 'Prepared', 'Adhoc')

41.       AND st.text NOT LIKE '%SELECT cp.objtype%'

42.       AND st.text NOT LIKE '%@BatchID%'

43.       AND st.text NOT LIKE '%@_msparam%'

44.       AND st.text NOT LIKE '%database%'

45.       AND st.text NOT LIKE '%objtype%'

 

위 예제에서 실행 계획 캐쉬 정보는 아래와 같다.

 

 

EXEC()는 매개변수가 바뀔 때마다 새로 컴파일, 재사용이 되지 않아 use counts가 1 인 것을 확인할 수 있고,

EXEC sp_executesql은 모두 재사용되어, use_counts가 10 인 것을 확인할 수 있다.

 

 

참고 1)

 

DBCC는 DataBase Console Command의 약자로 DB를 매니지먼트하는데 사용된다.

 

자세한 내용은 MSDN의 DBCC 페이지를 살펴보자. 공부할 게 많은 페이지이다.

 

참고 2)

 

마지막 예제의 실행 계획 캐쉬의 정보를 보는 SELECT 문은 가급적 재사용 하자.