레드 게이트 Hub

T-SQL 창 기능을 쓰고 많은 질문을 쉽게,그리고 그들은 종종 더 나은 성능을 제공뿐만 아니라 이전 기술입니다. 예를 들어 지연 함수를 사용하는 것이 자체 조인을 수행하는 것보다 훨씬 낫습니다. 더 나은 성능을 얻으려면 전반적으로,그러나 당신이 필요하의 개념을 이해하고 프레임을 어떻게 창 함수에 의존하는 분류를 제공합니다.

참고:2019 의 최적화 프로그램 개선이 성능에 미치는 영향을 알아 보려면 새 기사를 참조하십시오!,

OVER 절 및 정렬

over 절에는 정렬을 유발할 수있는 두 가지 옵션이 있습니다:PARTITION BY 및 ORDER BY. PARTITION BY 는 모든 창 기능에서 지원되지만 선택 사항입니다. ORDER BY 는 대부분의 기능에 필요합니다. 달성하려는 작업에 따라 오버 절을 기반으로 데이터가 정렬되며 쿼리의 성능 병목 현상 일 수 있습니다.

올바른 순서로 함수를 적용하기 위해 말하자면 데이터베이스 엔진이 행을 줄 수 있도록 OVER 절의 ORDER BY 옵션이 필요합니다., 예를 들어 SalesOrderID 순서대로 row_number 함수를 적용하기를 원한다고 가정 해보십시오. 함수가 내림차순으로 TotalDue 의 순서로 적용되기를 원하는 경우와 결과가 다르게 보일 것입니다., 여기를 들어:

이 첫 번째 쿼리가 사용하여 클러스터의 키으로 순서에 의해 선택권,정렬이 필요합니다.

두 번째 쿼리에는 비싼 정렬 작업입니다.,

ORDER BY 에서 이상 절에 연결되지 않은 주문 절의 추가 전체 쿼리를 수 있는 매우 다릅니다., Here is an example showing what happens if the two are different:

인덱스 키 SalesOrderID 지만,행해 처음으로 분류됩 TotalDue 을 내림차순으로 다시 SalesOrderID. 을 살펴 실행 계획:

파티션으로 절을 지원하지만 옵션에 대한 모든 T-SQL 창능한 원인 분류를 제공합니다. 집계 쿼리에 대한 GROUP BY 절과 비슷하지만 정확히 같지는 않습니다., This example starts the row numbers over for each customer.

실행 계획을 보여줍니다 하나의 정렬 작업의 조합 CustomerID 및 SalesOrderID.

을 극복하는 유일한 방법은 성능에 미치는 영향을 분류하는 인덱스를 생성하기 위해 특별히 이 절입니다. 창 기능을 사용하는 그의 책 Microsoft Sql Server2012 고성능 T-SQL 에서 Itzik Ben-Gan 은 POC 색인을 권장합니다. POC 는(P)ARTITION BY,(O)RDER BY 및(c)overing 의 약자입니다., 그는 키의 파티션 기준 및 순서 기준 열 앞에 필터링에 사용되는 모든 열을 추가하는 것이 좋습니다. 그런 다음 포함 된 열로 덮음 인덱스를 만드는 데 필요한 추가 열을 추가하십시오. 다른 것과 마찬가지로 이러한 인덱스가 쿼리 및 전체 작업 부하에 어떤 영향을 미치는지 테스트해야합니다. 물론,할 수 없습니다 추가 인덱스에 대한 모든 쿼리는 당신이 쓰지만,경우의 성능 특정 사용하는 쿼리 창 기능은 중요하다,당신을 시도 할 수 있습니 조언입니다.,

Here is an index that will improve the previous query:

경우 쿼리를 다시 실행,정렬 작업은 지금 사라에서 실행 계획:

프레임

In my opinion,프레임은 가장 어려운 개념을 이해하는 경우에 대해 학습 T-SQL 창능합니다. 구문에 대한 자세한 내용은 t-SQL 창 함수 소개를 참조하십시오., 프레임은 필요한 경우는 다음과 같습니다:

• 창의 집계를 가진 순서에 의해 사용되는 실행하기 위한 합계 또는 이동평균,예를 들어
• FIRST_VALUE
• LAST_VALUE

다행히도,프레임은 필요하지 않습 시간의 대부분은,그러나 불행하게도,그것은 새로운 구조와 기본값을 사용합니다. 기본 프레임은 항상 제한되지 않은 선행 행과 현재 행 사이의 범위입니다. 주문 기준 옵션이 고유 한 열 또는 열 집합으로 구성된 한 올바른 결과를 얻는 동안 성능 히트가 표시됩니다.,id=”4b64f77d3b”>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

크; GO 선택 CustomerID SalesOrderID, TotalDue, SUM(TotalDue)이상(파티션에 의해 CustomerID ORDER BY SalesOrderID) 로 RunningTotal 에서 판매합니다.,SalesOrderHeader; 선택 CustomerID SalesOrderID, TotalDue, SUM(TotalDue)이상(파티션에 의해 CustomerID ORDER BY SalesOrderID 사이 행 무제한 이전 및 현재의 행) 로 RunningTotal 에서 판매합니다.SalesOrderHeader;

결과는 같지만,성능이 매우 다르다. 불행히도,실행 계획은이 경우에 당신에게 진실을 말하지 않습니다., 그것은 보고서에는 각 쿼리했의 50%자료:

을 검토하는 경우 통계 IO 값을,당신은 그 차이를 볼 수 있습니다:

를 사용하여 올바른 프레임은 더욱 중요한 경우,주문 옵션에 의해 고유하지 않거나 사용하는 경우 LAST_VALUE. 이 예에서 ORDER BY 열은 OrderDate 이지만 일부 고객은 주어진 날짜에 둘 이상의 주문을했습니다. 프레임을 지정하지 않거나 범위를 사용하는 경우 함수는 일치하는 날짜를 동일한 창의 일부로 처리합니다.,”>

는 이유에 대한 불일치가 있는 범위를 보고 데이터를 논리적으로는 행은 그것을 보는 위치 적으. 이 문제에 대한 두 가지 해결책이 있습니다. 하나는 주문 기준 옵션이 고유한지 확인하는 것입니다. 다른 더 중요한 옵션은 항상 지원되는 프레임을 지정하는 것입니다.

프레이밍이 논리적 인 문제를 일으키는 다른 장소는 LAST_VALUE 입니다., LAST_VALUE 는 프레임의 마지막 행에서 식을 반환합니다. 때문에 기본 프레임(범위 사이에 무제한 이전 및 현재의 행)를 현재 행의 마지막 행이 프레임은 행 계산이 수행됩니다.,

창 집계

중 하나의 기능을 활 T-SQL 의 윈도우 기능을 추가 할 수있는 기능 집계식이 아닌 집계 쿼리가 있습니다. 불행히도,이것은 종종 제대로 수행되지 않을 수 있습니다. 문제를 확인하려면,당신은 당신이 논리적 읽기의 큰 숫자를 볼 수 있습니다 통계 IO 결과를 볼 필요가있다., 나의 통보가 필요할 때 반환하는 값에서 다른 세부적에 대한 동일한 쿼리를 행이 많은 중 하나를 사용하여 이전과 같은 일반적인 표현(CTE),temp table,또는 변수입니다. 창 집계를 사용하기 전에 미리 집계 할 수 있다면 다른 옵션입니다.,ows 사이의 차이를 창을 집계하고 또 다른 기술:

첫 번째 쿼리만을 검사하는 테이블에 한 번 있지만,그것은 28,823 논리에 읽어들이는 작업대. 두 번째 방법은 테이블을 두 번 스캔하지만 작업 테이블은 필요하지 않습니다.

다음 예제에서 사용하는 집계를 적용하여 집계 식

때 윈도우를 사용하여 기능 집계 쿼리에서 식 따라야 합니다으로 동일한 규칙을 선택하고 주문 절입니다., 이 경우 창 함수는 SUM(TotalDue)에 적용됩니다. 중첩 된 집계처럼 보이지만 실제로는 집계 식에 적용된 창 함수입니다.

이후 데이터 집계하기 전에 윈도우 기능 적용,성능이 좋다:

가 하나 더 많은 흥미로운 것에 대해 알고 윈도우를 사용하여 집계가 있습니다. 절 정의를 통해 일치를 사용하는 여러 표현식을 사용하는 경우 성능이 추가로 저하되는 것을 볼 수 없습니다.내 조언은이 기능을주의해서 사용하는 것입니다., 그것은 매우 편리하지만 그렇게 잘 확장되지 않습니다.

성능 비교

예는 지금까지 사용되는 작은 판매합니다.AdventureWorks 의 SalesOrderHeader 테이블과 실행 계획 및 논리적 읽기를 검토했습니다. 실제 생활에서 당신의 고객에 대해 걱정하지 않습니다 실행 계획 또는 논리를 읽고,그들이 돌보는 방법에 대해 빠르는 쿼리를 실행합니다. 실행 시간의 차이를 더 잘보기 위해 Adam Machanic 의 Thinking Big(Adventure)스크립트를 트위스트와 함께 사용했습니다.

스크립트는 3 천만 개 이상의 행을 포함하는 bigTransactionHistory 라는 테이블을 만듭니다., Adam 의 스크립트를 실행 한 후 각각 15 백만 행과 750 만 행을 가진 그의 테이블 사본을 두 개 더 만들었습니다. 또한 돌에서 결과 삭제한 후 실행 숙박 시설에는 쿼리 편집기를 그래서 채워 그리드에 영향을 미치지 않았다는 실행 시간. 각 테스트를 세 번 실행하고 각 실행 전에 버퍼 캐시를 지 웠습니다.,

I can’t say enough about how important it is to use the frame when it’s supported., 의 차이를 볼 수 있게 테스트하여 실행 계산 합계는 네 가지 방법으:

• 커서 솔루션
• 상관 sub-query
• 윈도우 기능으로 기본 프레임
• 윈도우 기능으로 행

가 테스트에서 새로운 세 가지 테이블이 있습니다. 결과는 다음과 같습니다 차트에서 서식:

실행하는 경우의 행이 프레임,7.5million 행 테이블했 두 번째 보다는 더 적은에서 실행할 시스템을 사용했을 때 수행하는 테스트입니다. 3 천만 행 테이블을 실행하는 데 약 1 분이 걸렸습니다., id=”4b64f77d3b”>

1 2 3 4 5

SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM bigTransactionHistory;

I also performed a test to see how window aggregates performed compared to traditional techniques., 이 경우에,나는 사용 30 만 행 테이블,그러나 수행 중 하나는,두 개 또는 세 개의 계산에 사용하는 동일한 세분화하고,따라서,같은 이상 절이 있습니다. 창 집계 성능을 CTE 및 상관 된 하위 쿼리와 비교했습니다.

창 집계는 각 경우에 약 1.75 분 동안 최악을 수행했습니다. Cte 는 테이블이 세 개 모두에 대해 한 번만 터치 되었기 때문에 계산 수를 늘릴 때 가장 잘 수행되었습니다., 상관된 하위 쿼리 수행되는 더 나쁠 경우의 수를 증가 계산 이후는 각각의 계산으로 실행했는 별도로,그리고 감동 테이블의 총합니다.,v>

결론

T-SQL 윈도우 기능으로 추진하는 훌륭한 성능을 향상시킬 수 있습니다. 내 의견으로는,그들은 쓰기 쿼리를 쉽게,하지만 당신은 좋은 성능을 얻기 위해 그들을 잘 이해해야합니다. 인덱싱은 차이를 만들 수 있지만 작성하는 모든 쿼리에 대해 인덱스를 만들 수는 없습니다., 프레이밍은 이해하기 쉽지 않을 수도 있지만 큰 테이블까지 확장해야하는 경우 매우 중요합니다.피>

---