조회조건이 유동적인 프로시저호출
--정적 쿼리를 사용하는 프로시저에서
1. IF, OR과 case를 피하라
-부하가 많이 걸리는 예
( case @OrderID when '%' then @OrderID else OrderID end ) = @OrderID
(OrderID = @orderid OR @orderid IS NULL)
-권장되는예(NULL이 없어야함)
od.ProductID = coalesce(@prodid, od.ProductID)
2. coalesce 사용
-NULL이 존재하는 경우의 검색방법
1)컬림이 int형일 경우
DECLARE @minint int, @maxint int
SELECT @minint = convert(int, 0x80000000),
@maxint = convert(int, 0x7FFFFFFF)
OrderID BETWEEN coalesce(@orderid, @minint) AND
coalesce(@orderid, @maxint)
*NULL이 들어갈 수없음(int구조이기때문)
2) 컬림이 char(nchar)일 경우
DECLARE @mincustid nchar(10), @maxcustid nchar(10),
IF @custid IS NULL
SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
FROM Customers
CustomerID BETWEEN coalesce(@custid, @mincustid) AND
coalesce(@custid, @maxcustid)
*NULL이 존재하는 char(nchar)형식일 경우 최소/최대값을 이용하도록 한다
3) 컬럼이 varchar(nvarchar)일 경우 (OR쓸 수 밖에..)
(Region = @region OR @region IS NULL)
4) 날짜 컬럼일 경우
AND OrderDate >= coalesce(@fromdate, '17530101')
AND OrderDate <= coalesce(@todate, '99991231')
5) LIKE검색이 필요한 컬럼
ProductName LIKE coalesce(@prodname + '%', '%')
-- coalesce경우 다른(OR, CASE)처럼 만큼은 아니지만 scan을 피할 수없다.
따라서, 위의 방법으로도 성능이 나쁘다면 다이나믹 SQL(sp_executesql)을 사용하라
*다이나믹 SQL(sp_executesql)을 사용고려
-장점
OR, CASE와는 달리 scan을 최소한으로 줄이는 최적화된 index seek쿼리가 가능하므로
수행속도가 빠르다.
-단점(알려진)
1) 항상 재컴파일된다?..plan cash를 할 수없다?
정적프로시저와 다이나믹프로시저(sp_executesql)는 plan캐쉬가 다르게 작동한다.
정적프로시저는 매개변수(input)와는 상관없이 프로시저(objectid)기준으로 plan을 만들고 캐쉬한다
따라서, 첫번째 매개변수만 사용했을 경우, 두번째 매개변수만을 사용했을 경우 plan이 만들어져 있다면 캐쉬한다.
다이나믹프로시저(sp_executesql)는 statment기준으로 캐쉬한다
첫번째 매개변수만을 넣어 생성한 statment와 두번쨰 매개변수만을 넣어 생성한 statment는 다른 plan을 세우지만 해당 statment자체를 캐쉬하므로 다음부터 동일패턴이 들어오면 캐쉬한다.
따라서, 정적프로시저보다 매개변수 패턴만큼 plan을 세우므로 정적프로시저보다 나쁘다고 말할 수는 있지만, 상황에 따라 많은 Scan(논리적읽기가 커짐)현상으로 정적프로시저의 성능이 나빠진다면, 오히려 해당 statment에 최적화된 쿼리를 생성하는 다이나믹프로시저(sp_executesql)를 사용하는 것이 정확한 인덱스를 사용하면서 캐쉬할 수있다(재사용 가능)
이것은, ad-hoc쿼리와는 다른것이다(해당 쿼리는 정확히 일치하지 않으면 다 다른 것으로 간주되지만 다이나믹프로시저(sp_executesql)는 parameter방식이기 때문에 성능이 좋다)
2) 보안설정을 할 수없다
정적프로시저는 해당 프로시저에 소유자 기반의 보안(Role)을 줄 수있지만, 다이나믹프로시저는 그럴수없다.
이것은 조회조건을 제외한 나머지 메인쿼리부분을 view나 function을 사용하여 Role을 주고
이것을 다이니믹프로시저에서 select하여 조회조건부분을 적용하면 해결할 수있다
(단지, view(function)을 하나 더 만들어야하는 단점이 있지만, 실제로 프로시저에 role을 주는 곳은
많지 않고(금융권), 모든 프로시저를 다이나믹프로시저(수행이 나쁜 것만 선별적 테스트하여 적용)로 사용하지 않기때문이다
3) 유지보수가 힘들다
그렇다고 볼 수있다. 하지만, 모든 프로시저를 다이나믹프로시저(sp_executesql)를 사용하지 않고
문제가 된는 프로시저와 비교테스트하여 사용한다면 좋은 결과를 얻을 수있는데도 불구하고, 유지보수가 힘들다라고 아예 배제하는 것은 더욱 좋지않다
4) SQL Injection의 위험이 있다
parameter방식으로 프로시저에서 사용하며, sp_executesql는 내부적으로 별도의 parameter를 더 사용하여 연결하므로 실제 테스트(SQL Injection을 유발하는 value값을 테스트)한 결과 Injection의 위험은 없었다
*일반 동적조회조건을 위한 프로시저를 생성할 시 현재까지는 coalesce방식이 작성도 쉽고, 유지보수도 쉬우므로 이 방식을 택하는 것으로한다(더 나은 방식이있다면 제시해주세요..please!!)
하지만, 이렇게 해도 수행속도가 나빠진다면 다이나믹프로시저(sp_executesql)를 비교테스트하여
적용을 고려하는 것도 좋은 방법이다.
*SQL2005에서 프로시저가 캐슁되는지 판별방법
SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Proc' AND cacheobjtype = 'Compiled Plan'
ORDER BY usecounts DESC
(정적 프로시저 , 다이나믹프로시저 모두 볼 수있다)
SELECT TOP 10 usecounts, size_in_bytes, cacheobjtype,
SUM (total_worker_time / 1000) AS total_cpu_time_in_ms,
SUM (total_physical_reads) AS total_physical_reads,
SUM (total_logical_reads) AS total_logical_reads,
SUM (total_logical_writes) AS total_logical_writes,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype in ('Proc','Prepared') AND cacheobjtype = 'Compiled Plan'
GROUP BY usecounts, size_in_bytes, cacheobjtype, [text]
ORDER BY usecounts DESC
(정적 프로시저 , 다이나믹프로시저(statment기준)별로 IO(읽기/쓰기) 및 cpu사용시간등을 볼 수있다)
DBCC FREEPROCCACHE
(캐쉬를 모두 지운다)
SET STATISTICS IO ON
SET STATISTICS TIME ON
을 사용하여 IO와 TIME을 볼 수있다
(샘플예)
1. coalesce
http://www.sommarskog.se/dynsearch/search_orders_5.sp
2. 다이나믹프로시저(sp_executesql)
http://www.sommarskog.se/dynsearch/search_orders_1.sp
참고)
1. Windows & .NET Magazine 2005년 11월
2. http://www.sommarskog.se/dyn-search.html
3. http://www.powerdb.net/?inc=read&aid=506&criteria=mssql&subcrit=&id=&limit=20&keyword=&page=1
