DataBaser.Net: Hard Parsing과Soft Parsing

1 기본적인 사항
2 예제
3 Stored Procedure Recompile

1 기본적인 사항 #

만약 다음과 같은 쿼리를 실행했다면 옵티마이저는 어떤 실행계획을 세울까?

SELECT e.empno, e.ename, d.deptno
FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno
AND e.sal > 3000

Nested Loop을 할 것인가 Sort Merge Join을 할 것인가 아니면 Hash Join을 할 것인가? Nested Loop 조인이면 emp테이블부터 읽을 것인가 아니면 dept테이블부터 읽을 것인가? 풀스캔으로 읽을 것인가? 인덱스가 있는가? 없는가? 인덱스가 있다면 사용할 것인가? 사용하지 않을 것인가? Sal컬럼에 인덱스가 있는지 없는지 인덱스가 있다면 사용할 것인지 사용하지 않을 것인지..

수많은 경우의 수가 등장을 하게 된다. 5개의 테이블만을 조인한다고 해도 위와 같은 팩터만 고려한다면 상당히 많은 경우의 수가 등장을 한다. 그러나 이러한 모든 경우의 수를 따져서 실행계획을 세우는 것은 아니다. 왜냐하면 모든 실행계획을 검토하다 보면 너무나도 많은 시간과 비용이 소요되기 때문이다. 그러므로 일정한 수만큼[1]의 경우만 모든 조인 순서를 고려하고, 일정한 수만큼만 실행계획에 대한 비용을 계산한다. 그러므로 매개변수 쿼리라든지 바인드 변수를 사용한다든지 하는 것은 실행계획을 재사용(소프트파싱)이 왜 성능에 중요한 역할을 하는지 알 수 있을 것이다. 그러므로 동적쿼리는 피하는 것이 좋다. 동적쿼리를 하는 것은 대부분 하드파싱을 하는 것이기 때문이다. 다음 두 쿼리는 CategoryID를 제외하면 실행계획이 같은 쿼리로 볼 수 있다. 필자가 알기로는 다음 두 문장은 자동 매개변수화 되어 두 쿼리는 실행계획을 재사용하기 때문이다.

SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 4

물론 프로시저를 만들어 사용하면 대부분 매개변수화된 쿼리가 된다. 물론 위와 같은 간단한 쿼리는 실행계획을 재사용(소프트파싱)을 하게 된다. 이와 같은 경우는 프로시저가 재 컴파일 되지 않는다는 전제 하에서다. MSSQL Server는 웬만하면 실행계획을 재사용하려고 한다. 그러나 복잡한 쿼리일 수록 소프트 파싱의 확률은 줄어든다. 그러므로 아래와 같이 sp_executesql 을 사용하여 매개변수를 사용한다면 복잡한 쿼리라도 소프트파싱을 유도할 수가 있다. 이것은 오라클에서 바인드 변수를 사용하는 것과 유사하다. 동적으로 쿼리를 만들때도 마찬가지이다. sp_executesql을 사용하면 일단 실행계획은 메모리로 올려져 재사용 할 기본자세를 갖춘다. 즉, 매개변수를 사용하여 SQL 문에서 상수를 분리하면 관계형 엔진이 중복된 계획을 인식하는 데 도움이 된다.

[edit]

2 예제 #

아래는 실행계획의 재사용의 예이다. 프로필러를 실행하고 다음을 실행하자. 이벤트에서 저장 프로시저의 cache에 관한 이벤트를 모두 선택한다.

Use NorthWind
GO
 
IF object_id('proc_parameter_query') IS NOT NULL
        DROP PROC proc_parameter_query
GO
 
CREATE PROCEDURE proc_parameter_query @CategoryID int, @ProductID int
AS
BEGIN
        DECLARE @sql nvarchar(100)
        SET @sql = N'SELECT * FROM Northwind.dbo.Products '
        SET @sql = @sql + N'WHERE CategoryID = @Param1 '
        SET @sql = @sql + N'AND ProductID = @Param2'
        EXEC sp_executesql
                @sql,
                N'@Param1 int, @Param2 int',        
-- 이부분을 조심해야 한다. 
-- 하나의 작은따옴표 안에 매개변수를 나열해야 햔다.
                @Param1 = @CategoryID,
                @Param2 = @ProductID
END
GO

프로필러를 실행하고, Cache Insert가 있는지 살펴보자. 프로필러의 사용법을 모른다면 도움말이나 다른 서적을 참고한다. 프로필러를 다음과 같이 설정 후 실행한다.

EXEC proc_parameter_query 1, 1
GO

--이번에는 값을 바꾸어서 proc_parameter_query 프로시저를 실행해 보자
--그리고  ExecContextHit가 있는지 살펴보자..
EXEC proc_parameter_query 1, 2
GO

-- 다음 프로시저를 만든후 수행하고...프로필러를 보자.
IF object_id('proc_parameter_query2') IS NOT NULL
        DROP PROC proc_parameter_query2
GO
 
CREATE PROCEDURE proc_parameter_query2 @a int, @b int
AS
BEGIN
        DECLARE @sql nvarchar(100)
        SET @sql = N'SELECT * FROM Northwind.dbo.Products '
        SET @sql = @sql + N'WHERE CategoryID = '+ cast(@a as varchar) + ' '
        SET @sql = @sql  + N'AND ProductID = ' + cast(@b as varchar)
        EXEC(@sql)
END
GO 
 
--다음을 실행하고 프로필러 관찰
EXEC proc_parameter_query2 1, 3
GO

EXEC proc_parameter_query2 1, 4
GO

SELECT * FROM Northwind.dbo.Products 
WHERE CategoryID = 1 AND ProductID = 5
GO

SELECT * FROM Northwind.dbo.Products 
WHERE CategoryID = ‘1’ AND ProductID = ‘4‘
GO

실제로 exec() 나 sp_execsql이나 그냥 SQL이나 ExecContextHit 한 것을 볼 수 있었다. 그 이유는 자동 매개변수화에 있다. MSSQL Server는 웬만한 것은 자동 매개변수화 하여 실행계획을 재사용하려고 하지만 끝에 봤다시피 작은 따옴표 한 개를 더 붙임으로 실행계획을 재사용하지 못했다. 위의 예제는 간단한 것이므로 실행계획을 재사용 할 확률이 높았지만 복잡한 쿼리일수록 실행계획을 재사용 할 확률은 작아진다. 그러므로 동적인 쿼리를 작성하는 것은 피하는 것이 좋다. 위와 같은 방식으로 프로시저를 작성하지 말고, 아래와 같이 매개변수를 직접 넣기 바란다. 자동매개변수화에 의존하는 것은 한계가 있다.

CREATE PROCEDURE proc_parameter_query3 @a int, @b int
AS
BEGIN
        SELECT * FROM Northwind.dbo.Products 
        WHERE CategoryID = @a AND ProductID = @b
END
GO

저장 프로시저의 장점은 많이 알려져 있다. 그 장점 중 한가지 매력적인 것은 매개변수와의 데이터 타입을 맞춰줄 수 있어, 내부적인 형 변환을 막을 수 있다는 것이다.

[edit]

3 Stored Procedure Recompile #

저장 프로시저의 재컴파일은 필요하기도 하고, 필요하지 않을 수도 있다. 재컴파일이 필요할 때는 미리 컴파일된 것을 사용하는데 분포도가 정해지지 않을 때이다. 만약 어떤 웹사이트가 여성을 위한 웹사이트라고 하면, 대부분의 회원은 여성일 것이고, 일부가 남성일 것이다. 그러므로 여성을 검색할 때는 풀스캔을 하고, 남성을 찾을 때는 인덱스를 사용하게끔 해야 한다. 그러므로 실행계획을 분리하지 않았다면, 해당 프로시저는 매번 재컴파일을 해야 할 것이다. 만약 컴파일될 당시 남성을 찾았다면 인덱스를 사용할 것이고, 바로 다음에 여성을 찾는다면 미리 컴파일된 프로시저를 사용할 것이므로 다시 인덱스를 사용할 것이다. 그러므로 이런 경우에는 재컴파일을 해야 올바른 쿼리플랜을 세워 불필요한 일을 만들지 않을 것이다. 물론 이러한 경우는 실행계획을 분리하면 당연히 재컴파일은 필요하지 않다. 재컴파일을 하는 방법은 2가지다.

-- 프로시저 내에서 
CREATE PROC Proc_Name
WITH RECOMPILE
AS
BEGIN
  …
END
 
--프로시저를 실행할 때 지정
EXEC Proc_Name WITH RECOMPILE

Contents

1 기본적인 사항 #

2 예제 #

3 Stored Procedure Recompile #