SQL Server 쿼리: 리터럴에서는 빠르지만 변수에서는 느림

SQL Server 쿼리: 리터럴에서는 빠르지만 변수에서는 느림

나는 CTE를 사용하여 테이블에서 2 int를 반환하는 뷰를 가지고 있습니다.이렇게 보기를 쿼리하면 1초 이내에 실행됩니다.

SELECT * FROM view1 WHERE ID = 1

하지만 이렇게 조회하면 4초가 걸립니다.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id

두 개의 쿼리 계획을 확인했는데 첫 번째 쿼리는 메인 테이블에서 클러스터된 인덱스 검색을 수행하여 레코드 1개를 반환하고 나머지 보기 쿼리를 해당 결과 집합에 적용하는 것입니다.두 번째 쿼리는 인덱스 스캔을 수행하고 있습니다. 인덱스 스캔은 내가 관심 있는 레코드가 아닌 약 3000개의 레코드를 반환하고 나중에 결과 세트를 필터링합니다.

두 번째 쿼리에서 인덱스 검색이 아닌 인덱스 검색을 사용하도록 시도할 때 누락된 항목이 있습니까?SQL 2008을 사용하고 있지만 모든 작업을 SQL 2005에서도 실행해야 합니다.처음에는 일종의 파라미터 스니핑 문제인 줄 알았는데 캐시를 클리어해도 같은 결과가 나옵니다.

아마도 모수의 경우 최적화 도구는 값이 null이 아님을 알 수 없으므로, 값이 null일 때도 올바른 결과를 반환하는 계획을 만들어야 하기 때문일 것입니다. SQL Server 2008 SP1을 해 볼 수 .OPTION(RECOMPILE)질문에 대해서는

쿼리에 OPTIMATE FOR 힌트를 추가할 수 있습니다. 예가 있습니다.

DECLARE @id INT = 1
SELECT * FROM View1 WHERE ID = @id OPTION (OPTIMIZE FOR (@ID = 1))

되었으며, 된 것은 "DB" "VarChar" "VarChar" 입니다.CONVERT_IMPLICIT비교하기 전에 데이터 유형을 일치시키는 실제 실행 계획에서, 그것은 2초 대 11초의 암퇘지 성능의 원인이었습니다.매개 변수 유형을 수정하기만 해도 매개 변수화된 쿼리가 매개 변수화되지 않은 버전만큼 빠릅니다.

그렇게 하는 한 가지 가능한 방법은CAST다음과 같은 매개변수:

SELECT ...
FROM ...
WHERE name = CAST(:name AS varchar)

이것이 비슷한 문제를 가진 누군가에게 도움이 되기를 바랍니다.

직접 할당(WHERE UtilActId=12345)을 사용하여 10ms 미만으로 실행되었지만 변수 할당(WHERE UtilActId = @UtilActId)보다 100배 이상 오래 걸리는 보기를 사용하여 직접 이 문제에 부딪혔습니다.
후자에 대한 실행 계획은 전체 테이블에서 보기를 실행한 경우와 다르지 않았습니다.

이 솔루션에는 수많은 인덱스, 최적화 도구 힌트 또는 긴 통계 업데이트가 필요하지 않았습니다.

대신 보기를 사용자 테이블 함수로 변환했습니다. 여기서 매개 변수는 WHERE 절에 필요한 값입니다.실제로 이 WHERE 절은 3개의 쿼리에 깊이 중첩되어 있으며 여전히 작동하여 10ms 미만의 속도로 되돌아갔습니다.

결국 파라미터를 UtilActIds(int) 테이블인 TYPE으로 변경했습니다.그러면 WHERE 절을 테이블의 목록으로 제한할 수 있습니다.WHERE UtilActId = [parameter-List].UtilAccctId.이것은 훨씬 더 잘 작동합니다.사용자 테이블 기능은 미리 컴파일된 것 같습니다.

SQL이 변수를 사용하여 쿼리에 대한 쿼리 계획을 최적화하기 시작하면 사용 가능한 인덱스와 열이 일치합니다.이 경우 인덱스가 있으므로 SQL은 인덱스를 스캔하여 값을 찾습니다.SQL이 열과 리터럴 값을 사용하여 쿼리를 계획할 때 통계와 값을 확인하여 인덱스를 검색할지 또는 검색이 정확한지 여부를 결정할 수 있습니다.

Optimize 힌트와 값을 사용하면 SQL에 "이 값이 대부분 사용될 값이므로 이 값에 대해 최적화하십시오."라는 메시지가 표시되고 계획이 이 리터럴 값을 사용한 것처럼 저장됩니다.최적화 힌트와 UNKNOWN의 하위 힌트를 사용하면 SQL이 값을 알 수 없음을 알 수 있으므로 SQL은 열에 대한 통계를 살펴보고 검색 또는 검색 중 가장 적합한 항목을 결정하고 그에 따라 계획을 수립합니다.

답변한 지 오래된 문제라는 것을 알지만, 저는 이와 같은 문제를 접했고 힌트, 통계 업데이트, 추가 인덱스, 강제 계획 등이 필요 없는 매우 간단한 해결책을 가지고 있습니다.

"최적화기는 값이 null이 아니라는 것을 알 수 없습니다."라는 위의 의견을 바탕으로 변수에서 테이블로 값을 이동하기로 결정했습니다.

원래 코드:

declare @StartTime datetime2(0) = '10/23/2020 00:00:00'
declare @EndTime datetime2(0) = '10/23/2020 01:00:00'
    
SELECT * FROM ...
WHERE 
C.CreateDtTm >= @StartTime
AND  C.CreateDtTm < @EndTime

새 코드:

declare @StartTime datetime2(0) = '10/23/2020 00:00:00'
declare @EndTime datetime2(0) = '10/23/2020 01:00:00'

CREATE TABLE #Times (StartTime datetime2(0) NOT NULL, EndTime datetime2(0) NOT NULL)
INSERT INTO #Times(StartTime, EndTime) VALUES(@StartTime, @EndTime)

SELECT * FROM ...
WHERE 
C.CreateDtTm >= (SELECT MAX(StartTime) FROM #Times)
AND  C.CreateDtTm < (SELECT MAX(EndTime) FROM #Times)

이 작업은 원래 코드에 대해 몇 분이 아닌 즉시 수행되었습니다(분명히 결과는 다를 수 있습니다).

메인 테이블의 데이터 유형을 NULL이 아닌 것으로 변경하면 이 유형도 작동할 것으로 예상되지만 시스템 제약으로 인해 현재 테스트할 수 없습니다.

제가 직접 이 같은 문제를 발견했는데 하위 쿼리 결과에 대한 (왼쪽) 조인과 관련된 누락된 인덱스인 것으로 나타났습니다.

select *
from foo A
left outer join (
  select x, count(*)
  from bar
  group by x
) B on A.x = B.x

bar.x에 대해 bar_x라는 인덱스가 추가되었습니다.

DECLARE @id INT = 1

SELECT * FROM View1 WHERE ID = @id

수행

DECLARE @sql varchar(max)

SET @sql = 'SELECT * FROM View1 WHERE ID =' + CAST(@id as varchar)

EXEC (@sql)

문제 해결

언급URL : https://stackoverflow.com/questions/4459425/sql-server-query-fast-with-literal-but-slow-with-variable