CTE, Sub-Query, Temporary Table 또는 Table Variable에 성능 차이가 있습니까?

CTE, Sub-Query, Temporary Table 또는 Table Variable에 성능 차이가 있습니까?

이 훌륭한 SO 질문에서,CTE ★★★★★★★★★★★★★★★★★」sub-queries논의되었습니다.

구체적으로 묻고 싶은 것은 다음과 같습니다.

다음 중 어느 상황에서 더 효율적이고 빠릅니까?

• CTE
• 서브쿼리
• 임시 테이블
• 테이블 변수

전통적으로, 저는 이 제품을 많이 사용했어요.temp tables 개발함에 있어서stored procedures 많은 - - - - - - - - - - -.

Non-recursive CTE인 해결책을 항상 입니까?아니면 가장 효율적인 솔루션을 찾기 위해 항상 다양한 옵션을 만지작거려야 하는 경우입니까?

---

편집

효율성 측면에서 임시 테이블에는 히스토그램(통계량 등)이 관련되어 있으므로 가장 먼저 선택하는 것이 좋다는 말을 최근에 들었습니다.

SQL은 절차 언어가 아닌 선언형 언어입니다.즉, 원하는 결과를 설명하는 SQL 문을 구성합니다.SQL 엔진에 작업 방법을 지시하지 않습니다.

일반적으로 SQL 엔진과 SQL 옵티마이저가 최적의 쿼리 계획을 찾도록 하는 것이 좋습니다.SQL 엔진 개발에는 많은 인력이 투입되기 때문에 엔지니어가 할 수 있는 일을 하도록 하십시오.

물론 쿼리 계획이 최적이 아닌 경우도 있습니다.그런 다음 쿼리 힌트 사용, 쿼리 재구성, 통계 업데이트, 임시 테이블 사용, 인덱스 추가 등을 통해 성능을 향상시킬 수 있습니다.

당신의 질문에 대해서요.이론적으로 CTE와 서브쿼리의 퍼포먼스는 같아야 합니다.이는 양쪽이 쿼리 옵티마이저에 동일한 정보를 제공하기 때문입니다.한 가지 차이점은 여러 번 사용된 CTE를 쉽게 식별하고 계산할 수 있다는 것입니다.그런 다음 결과를 저장하고 여러 번 읽을 수 있습니다.유감스럽게도 SQL Server는 이 기본적인 최적화 방법을 이용하지 않는 것 같습니다(일반적인 서브쿼리 제거라고 할 수 있습니다.

임시 테이블은 쿼리 실행 방법에 대한 추가 지침을 제공하기 때문에 다른 문제입니다.한 가지 큰 차이점은 옵티마이저가 임시 테이블의 통계정보를 사용하여 쿼리 계획을 확립할 수 있다는 것입니다.이로 인해 퍼포먼스가 향상될 수 있습니다.또한 여러 번 사용하는 복잡한 CTE(서브쿼리)가 있는 경우 임시 테이블에 저장하면 성능이 향상되는 경우가 많습니다.쿼리는 한 번만 실행됩니다.

질문에 대한 답변은 특히 정기적으로 실행되는 복잡한 쿼리의 경우 기대하는 성능을 얻기 위해 신중하게 작업해야 한다는 것입니다.이상적인 환경에서는 쿼리 옵티마이저가 완벽한 실행 경로를 찾을 수 있습니다.비록 종종 그렇긴 하지만, 당신은 더 나은 성과를 얻을 수 있는 방법을 찾을 수 있을 것이다.

규칙이 없어요.CTE는 읽기 쉽고 퍼포먼스에 문제가 없는 한 사용합니다.이 경우 CTE가 문제일 것으로 추측하고 다른 접근방식을 사용하여 다시 쓰기를 시도하기보다는 실제 문제를 조사합니다.보통 이 문제에는 내가 쿼리에서 나의 의도를 선언적으로 말하는 방법보다 더 많은 것이 있다.

CTE를 풀거나 서브쿼리를 삭제하고 #temp 테이블로 대체하여 기간을 단축할 수 있는 경우가 있습니다.이는 오래된 통계, 정확한 통계조차 얻을 수 없는(테이블 값 함수에 가입하는 등), 병렬 처리 또는 쿼리의 복잡성으로 인해 최적의 계획을 생성할 수 없는(이 경우 최적화를 해제하면 최적화가 어려워질 수 있음) 등의 다양한 이유로 인해 발생할 수 있습니다.그러나 #temp 테이블 작성에 관련된 I/O가 CTE를 사용하여 특정 계획 형태를 매력적으로 만드는 다른 성능 측면보다 더 큰 경우도 있습니다.

솔직히 말해서, 당신의 질문에 "올바른" 대답을 하기에는 변수가 너무 많습니다.쿼리가 어떤 접근법에 유리하게 기울 수 있는지 예측할 수 있는 방법은 없습니다.이론적으로는 CTE 또는 단일 서브쿼리에 대해 동일한 의미론을 실행해야 합니다.이것이 사실이 아닌 몇 가지 사례를 제시한다면 당신의 질문은 더 가치가 있을 것입니다.그것은 당신이 옵티마이저의 한계를 발견했거나(또는 알려진 것을 발견했거나), 당신의 쿼리가 의미적으로 동등하지 않거나 최적화를 방해하는 요소를 포함하고 있는 것일 수 있습니다.

따라서 가장 자연스러운 방법으로 쿼리를 작성하는 것이 좋습니다.또, 옵티마이저가 실제로 퍼포먼스에 문제가 있는 것을 발견했을 때에 한정해 주세요.개인적으로 나는 #temp table을 최후의 수단으로 하여 CTE, 서브쿼리 순으로 순위를 매긴다.

#temp는 materialized이지만 CTE는 materialized하지 않습니다.

CTE는 구문일 뿐이므로 이론적으로는 서브쿼리일 뿐입니다.실행됩니다.#실현되었습니다.따라서 #temp에서 여러 번 실행되는 가입에서 고가의 CTE가 더 나을 수 있습니다.한편, 간단한 평가가 실행되지 않지만 여러 번 실행되는 경우 #temp의 오버헤드가 발생하지 않습니다.

SO에서는 테이블 변수를 좋아하지 않는 사람도 있지만 #temp보다 구체화되고 작성 속도가 빠르기 때문에 좋아합니다.쿼리 옵티마이저가 테이블 변수에 비해 #temp를 사용하는 것이 더 나을 수 있습니다.

#temp 변수 또는 테이블 변수에 PK를 작성하는 기능을 통해 쿼리 옵티마이저는 CTE보다 많은 정보를 얻을 수 있습니다(CTE에서는 PK를 선언할 수 없습니다).

CTE보다 항상 # Temp Table을 사용하는 것이 바람직하다고 생각하는 것은 다음 2가지입니다.

1. CTE에 프라이머리 키를 배치할 수 없기 때문에 CTE에 의해 액세스되는 데이터는 임시 테이블의 PK 또는 인덱스에 액세스하는 것이 아니라 CTE 테이블의 각 인덱스를 통과해야 합니다.

2. CTE에는 제약조건, 인덱스 및 프라이머리 키를 추가할 수 없기 때문에 버그가 슬금슬금 발생하고 데이터가 불량해질 가능성이 높아집니다.

---

-어제 어느 날

다음은 #table 제약으로 CTE에서는 해당되지 않는 불량 데이터를 방지할 수 있는 예를 제시하겠습니다.

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );

IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;

언급URL : https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-temporary-table-or-ta