동적 쿼리 사용 이유, 예시, 장단점, 플랜캐시, 주의점

 

흔히 동적 쿼리, 다이나믹 쿼리라는 것이 존재한다.

 

언제 동적 쿼리를 사용하는 걸까?

 

동적 쿼리는 주로 비즈니스 로직이 복잡해져서 where 조건절에서 조건이 너무 다양해지고 많아지는 경우,

 

유용하게 사용할 수 있다. 예를 들면, 검색이나, 페이징 처리 등의 상황이 있다.

 

그렇다면 예시를 살펴보자.

아래 예시는 @MinPrice 및 @MaxPrice 매개 변수에 따라 제품 테이블에서 가격 범위 내의 제품을 검색하는 쿼리이다.

 

예시)

DECLARE @MinPrice DECIMAL(10, 2) = 100.00
DECLARE @MaxPrice DECIMAL(10, 2) = 500.00

DECLARE @DynamicSQL NVARCHAR(MAX)

SET @DynamicSQL = N'
SELECT ProductName, Price
FROM Products
WHERE 1 = 1'

IF @MinPrice IS NOT NULL
BEGIN
    SET @DynamicSQL = @DynamicSQL + N' AND Price >= @MinPrice'
END

IF @MaxPrice IS NOT NULL
BEGIN
    SET @DynamicSQL = @DynamicSQL + N' AND Price <= @MaxPrice'
END

-- 실행할 동적 쿼리 출력 (디버깅 목적)
-- PRINT @DynamicSQL

-- 동적 쿼리 실행
EXEC sp_executesql @DynamicSQL, N'@MinPrice DECIMAL(10, 2), @MaxPrice DECIMAL(10, 2)', @MinPrice, @MaxPrice

 

이 예시에서는 @MinPrice와 @MaxPrice 매개 변수를 사용하여 동적으로 WHERE 절을 조립하고 sp_executesql을 사용하여 동적 쿼리를 실행한다.

 

---

 

하지만 동적 쿼리는 장점만 존재하는 것이 아니다.

 

MS-SQL에서 저장 프로시저, Stored Procedure는 실행 계획이 플랜 캐시라는 개체에 캐싱된다.

 

(https://learn.microsoft.com/ko-kr/sql/relational-databases/performance-monitor/sql-server-plan-cache-object?view=sql-server-ver16)

 

즉, 한번 컴파일된 저장 프로시저는 실행 계획이 만료되거나, 새로운 실행 계획을 생성해야 하기 전까지(ex. 프로시저가 수정되거나) 캐시에서 재사용되기 때문에 성능이 좋아진다.

 

하지만 동적 쿼리는 플랜 캐시에 캐시되기 위한 조건이 있다.

 

1. EXEC()를 사용하는 경우

EXEC() 함수를 사용하여 동적 SQL을 실행할 경우, 동일한 동적 SQL 문자열이 실행되더라도 매개변수의 값이 달라질 때마다, 매번 새로운 실행 계획을 생성한다. 

EXEC()로 실행되는 동적 쿼리는 매번 새롭게 컴파일되므로 실행 계획이 캐시에 저장되지 않는다. 

 

때문에 성능이 안 좋아진다.

2. EXEC sp_executesql을 사용하는 경우

EXEC sp_executesql 을 사용하면 동적 SQL을 실행할 때, 매번 동일한 쿼리 문자열이라도 일정한 매개 변수화된 형식으로 실행할 수 있다. 이 경우, SQL Server는 일정한 형식의 매개 변수를 사용한 쿼리에 대한 실행 계획을 캐시에 저장한다.

즉, 매개변수의 값이 달라지더라도 실행 계획이 캐시에 저장되기 때문에 동일한 쿼리 템플릿에 대한 재사용이 가능하다.

따라서 동적 쿼리를 사용할 경우, EXEC sp_executesql를 사용해야 한다.

 

---

 

EXEC sp_executesql 사용 시 주의할 점

1. Database Context

sp_executesql을 사용하여 동적 SQL을 실행할 때, 해당 SQL 문은 현재 데이터베이스 컨텍스트에서 실행된다.

 

이것은 동적 SQL이 다른 데이터베이스로 스위치하거나 다른 데이터베이스의 객체에 액세스할 수 없다는 것을 의미한다.

예를 들어, 현재 데이터베이스가 "DB1"로 설정되어 있고 sp_executesql을 사용하여 동적 SQL을 실행하면 해당 SQL

 

은 "DB1"에서만 작동하며 다른 데이터베이스인 "DB2"의 테이블이나 뷰에 직접 액세스할 수 없다.

 

필요한 경우, 다른 데이터베이스로 컨텍스트를 변경해야 한다.

2. Access to Local Variables

sp_executesql 내에서는 동적 SQL 내부에서 사용된 로컬 변수에 직접 액세스할 수 없다.

 

동적 SQL 문에서 로컬 변수를 사용할 때는 매개 변수화된 쿼리를 통해 값을 전달해야 한다.

예를 들어, 다음과 같이 로컬 변수를 사용하는 것은 허용되지 않는다.

 

DECLARE @LocalVariable INT = 123
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'SELECT @LocalVariable'
EXEC sp_executesql @DynamicSQL -- 에러 발생: @LocalVariable에 직접 액세스할 수 없음

 

그 대신, 이러한 방식으로 접근해야 한다.

 

DECLARE @LocalVariable INT = 123
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'SELECT @Parameter'
EXEC sp_executesql @DynamicSQL, N'@Parameter INT', @LocalVariable

 

---

결론

 

일반적으로 정적 쿼리가 동적 쿼리보다 성능이 우수하므로, 동적 쿼리는 꼭 필요한 경우 사용해야 한다.