[sql] CTE, 하위 쿼리, 임시 테이블 또는 테이블 변수간에 성능 차이가 있습니까?

규칙이 없습니다. CTE가 더 읽기 쉽고 성능 문제가 없는 한 사용합니다 .이 경우 CTE가 문제라고 생각하기보다는 실제 문제를 조사한 후 다른 접근 방식을 사용하여 다시 작성하려고 시도합니다. 쿼리에 의도를 선언적으로 선언하는 방식보다 일반적으로 문제가 더 많습니다.

CTE를 풀거나 하위 쿼리를 제거하고 #temp 테이블로 바꾸고 지속 시간을 줄일 수있는 경우가 있습니다. 오래된 통계, 정확한 통계를 얻을 수 없거나 (예 : 테이블 반환 함수에 참여할 수 없음), 병렬 처리 또는 쿼리의 복잡성으로 인해 최적의 계획을 생성 할 수없는 등의 다양한 요인 때문일 수 있습니다 ( 이 경우이를 해제하면 옵티 마이저에게 전투 기회가 제공 될 수 있습니다. 그러나 #temp 테이블 생성과 관련된 I / O가 CTE를 사용하여 특정 계획 형태를 덜 매력적으로 만들 수있는 다른 성능 측면을 능가하는 경우도 있습니다.

솔직히 말해서 귀하의 질문에 “올바른”답변을 제공하기에는 너무 많은 변수가 있습니다. 쿼리가 한 접근 방식 또는 다른 접근 방식을 선호하는 시점을 알 수있는 예측 가능한 방법은 없습니다. 이론적으로 CTE 또는 단일 하위 쿼리에 대해 동일한 의미론 이 정확히 동일한 방식으로 실행 되어야한다는 사실 만 알고 있으면 됩니다. 이것이 사실이 아닌 경우를 제시하면 귀하의 질문이 더 가치가 있다고 생각합니다. 최적화 도구에서 제한을 발견했거나 알려진 것을 발견했을 수도 있고 쿼리가 의미 적으로 동등하지 않을 수도 있습니다. 또는 최적화를 방해하는 요소가 포함되어 있습니다.

따라서 가장 자연스러운 방식으로 쿼리를 작성하는 것이 좋으며 최적화 프로그램에서 실제로 발생하는 성능 문제를 발견했을 때만 벗어날 수 있습니다. 개인적으로 CTE 순위를 매긴 다음 하위 쿼리를 #temp 테이블이 최후의 수단으로 사용합니다.

답변

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;