OFFSET ... FETCH
SQL Server 2012에 도입 된 새로운 모델은 간단하고 빠른 페이징을 제공합니다. 두 형식이 의미 상 동일하고 매우 일반적이라는 점을 고려하면 어떤 차이가 있습니까?
옵티마이 저가 두 가지를 모두 인식하고 (사소한) 최대한으로 최적화한다고 가정합니다.
다음은 OFFSET ... FETCH
비용 추정치에 따라 ~ 2 배 빠른 매우 간단한 경우 입니다.
SELECT * INTO #objects FROM sys.objects
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
ORDER BY object_id
SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
CI를 만들 object_id
거나 필터를 추가 하여이 테스트 사례를 변경할 수 있지만 모든 계획 차이를 제거 할 수는 없습니다. OFFSET ... FETCH
실행 시간이 적기 때문에 항상 빠릅니다.
답변
문제의 예제는 동일한 결과를 얻지 못합니다 (이 OFFSET
예제에는 오류가 있습니다). 아래의 업데이트 된 양식은 해당 문제를 해결하고 ROW_NUMBER
사례에 대한 추가 정렬을 제거하고 변수를 사용하여 솔루션을보다 일반적으로 만듭니다.
DECLARE
@PageSize bigint = 10,
@PageNumber integer = 3;
WITH Numbered AS
(
SELECT TOP ((@PageNumber + 1) * @PageSize)
o.*,
rn = ROW_NUMBER() OVER (
ORDER BY o.[object_id])
FROM #objects AS o
ORDER BY
o.[object_id]
)
SELECT
x.name,
x.[object_id],
x.principal_id,
x.[schema_id],
x.parent_object_id,
x.[type],
x.type_desc,
x.create_date,
x.modify_date,
x.is_ms_shipped,
x.is_published,
x.is_schema_published
FROM Numbered AS x
WHERE
x.rn >= @PageNumber * @PageSize
AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
x.[object_id];
SELECT
o.name,
o.[object_id],
o.principal_id,
o.[schema_id],
o.parent_object_id,
o.[type],
o.type_desc,
o.create_date,
o.modify_date,
o.is_ms_shipped,
o.is_published,
o.is_schema_published
FROM #objects AS o
ORDER BY
o.[object_id]
OFFSET @PageNumber * @PageSize - 1 ROWS
FETCH NEXT @PageSize ROWS ONLY;
ROW_NUMBER
계획의 예상 비용이 0.0197935를 :
OFFSET
계획의 예상 비용이 0.0196955를 :
즉, 0.000098 예상 비용 단위를 절약 OFFSET
할 수 있습니다 (각 행에 대해 행 번호를 리턴하려는 경우 계획에 추가 연산자가 필요함). OFFSET
계획은 여전히, 일반적으로 약간 저렴하지만, 예상 비용은 정확히 것을 기억하고 – 실제 시험이 여전히 필요합니다. 두 계획에서 비용의 대부분은 모든 종류의 입력 집합 비용이므로 유용한 인덱스는 두 솔루션 모두에 도움이됩니다.
상수 리터럴 값이 사용되는 경우 (예 : OFFSET 30
원래 예에서) 옵티마이 저는 전체 정렬 대신 TopN 정렬을 사용하고 그 뒤에 Top을 사용할 수 있습니다. TopN 정렬에서 필요한 행이 상수 리터럴이고 <= 100 ( OFFSET
및 의 합 FETCH
) 인 경우 실행 엔진은 일반화 된 TopN 정렬보다 빠르게 수행 할 수 있는 다른 정렬 알고리즘 을 사용할 수 있습니다. 세 가지 경우 모두 전체적으로 성능 특성이 다릅니다.
옵티마이 ROW_NUMBER
저가 구문 패턴을 자동으로 변환하지 않는 OFFSET
이유는 여러 가지 이유가 있습니다.
- 기존의 모든 용도와 일치하는 변환을 작성하는 것은 거의 불가능합니다.
- 일부 페이징 쿼리가 자동으로 변환되고 다른 것은 아닌 것이 혼동 될 수 있습니다.
OFFSET
계획은 모든 경우에 더 나은 보장 할 수 없습니다
페이징 세트가 상당히 넓은 위의 세 번째 포인트에 대한 한 가지 예가 발생합니다. 비 클러스터형 인덱스를 사용하여 필요한 키 를 찾고OFFSET
또는로 인덱스를 스캔하는 것과 비교하여 클러스터형 인덱스를 수동으로 조회하는 것이 훨씬 더 효율적일 수 있습니다 ROW_NUMBER
. 있습니다 고려해야 할 추가 문제 페이징 응용 프로그램 전체에서 얼마나 많은 행 또는 페이지 알아야 할 필요가있는 경우는. ‘key seek’및 ‘offset’방법의 상대적인 장점에 대한 또 다른 좋은 논의가 있습니다 .
전반적으로 사람들은 OFFSET
적절한 테스트를 거친 후 적절한 경우 사용하도록 페이징 쿼리를 변경하기위한 정보에 근거한 결정을 내리는 것이 좋습니다 .
답변
약간의 쿼리를 통해 동일한 비용 견적 (50/50)과 동일한 IO 통계를 얻습니다 .
; WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #objects
)
SELECT *
FROM cte
WHERE r >= 30 AND r < 40
ORDER BY r
SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
이렇게하면에 r
대신 정렬하여 버전에 나타나는 추가 정렬을 피할 수 object_id
있습니다.
답변
이 기능을 추가하기 위해 쿼리 최적화 프로그램을 수정했습니다. 특히 offset … fetch 명령을 지원하는 메커니즘을 구현했습니다. 즉, 최상위 쿼리에 대해 SQL Server는 더 많은 작업을 수행해야합니다. 따라서 쿼리 계획의 차이입니다.