[SQL] CTE, 하위 쿼리, 임시 테이블 또는 테이블 변수 사이의 성능 차이가 있나요?

CTE, 하위 쿼리, 임시 테이블 또는 테이블 변수 사이의 성능 차이가 있나요?

이 우수한 SO 질문에서, CTE 및 하위 쿼리 사이의 차이가 논의되었다.

내가 특별히 부탁드립니다 :

어떤 상황에서 다음과 같은보다 효율적인 / 빠른 각각 무엇입니까?

전통적으로, 내가 저장 프로 시저 개발에 임시 테이블을 많이 사용했습니다 -들이 얽혀 하위 쿼리를 많이보다 더 읽을 것 같은.

비 재귀 CTE는 아주 잘 데이터의 집합을 캡슐화, 매우 읽을 수 있지만, 하나는 그들이 항상 더 나은 수행 말할 수있다 특정 상황은? 또는 가장 효율적인 해결책을 찾기 위해 다양한 옵션을 항상 바이올린 주위에있는의 경우입니까?

편집하다

나는 최근에 그들이 관련 히스토그램 즉 통계를 가지고 효율성의 관점에서, 임시 테이블은 좋은 첫 번째 선택 것을 들었다.

해결법

1. ==============================

   1.SQL은 선언적 언어가 아닌 절차 적 언어입니다. 즉, 당신은 당신이 원하는 결과를 설명하는 SQL 문을 구축합니다. 당신은 어떻게 일을 할 수있는 SQL 엔진을 이야기하지 않습니다.

   SQL은 선언적 언어가 아닌 절차 적 언어입니다. 즉, 당신은 당신이 원하는 결과를 설명하는 SQL 문을 구축합니다. 당신은 어떻게 일을 할 수있는 SQL 엔진을 이야기하지 않습니다.

   일반적으로, SQL 엔진과 SQL 최상의 쿼리 계획을 찾아 최적화 할 수있는 좋은 아이디어이다. 는 SQL 엔진을 개발에 들어갈 노력의 많은 인년 때문에 엔지니어들이 수행하는 방법을 알고하자가 있습니다.

   쿼리 계획이 최적이 아닌 경우 물론, 경우가 있습니다. 그런 다음, 임시 테이블을 사용하여 쿼리 힌트, 구조 조정 쿼리, 업데이트 통계를 사용하여 인덱스를 추가 등 더 나은 성능을 얻을 싶어요.

   귀하의 질문에 관해서. 모두 쿼리 최적화에 동일한 정보를 제공하기 때문에 열팽창 계수와 하위 쿼리의 성능은 이론적으로 동일해야합니다. 한 가지 차이점은 CTE가 번 쉽게 식별 할 수 있으며, 한 번 계산보다 사용한다는 것입니다. 결과는 저장되어 여러 번 읽을 수 있습니다. 불행하게도, SQL 서버는 기본적인 최적화 방법을 활용하지 않는 것 (이 일반적인 하위 쿼리 제거를 호출 할 수 있습니다).

   쿼리를 실행하는 방법에 대한 자세한 지침을 제공하기 때문에 임시 테이블은 다른 문제입니다. 하나의 주요 차이점은 최적화 프로그램이 쿼리 계획을 수립하기 위해 임시 테이블에서 통계를 사용할 수 있다는 것입니다. 성능이 향상 될 수 있습니다. 당신이 한 번 이상 사용하는 복잡한 CTE (하위 쿼리)이있는 경우 또한, 다음 임시 테이블에 저장하는 것은 종종 성능 향상을 제공 할 것입니다. 쿼리는 한 번만 실행됩니다.

   귀하의 질문에 대한 대답은 당신이 특히 정기적으로 실행되는 복잡한 쿼리에 대해 기대하는 성능을 얻을 수 놀러 필요가있다. 이상적으로, 쿼리 최적화 프로그램은 완벽한 실행 경로를 찾을 것입니다. 종종 않지만, 당신은 더 나은 성능을 얻을 수있는 방법을 찾을 수 있습니다.

2. ==============================

   2.규칙은 없습니다. 나는 CTE를 더 읽기 발견하고 그들은 내가 실제 문제를 조사하기보다는 추측 CTE는 문제가 있음과는 다른 접근 방식을 사용하여 다시 작성하려고하는 경우 일부 성능 문제를 전시하지 않는 한 그들을 사용합니다. 일반적으로 더 내가 선언적 쿼리 내 의도를 명시하기 위해 선택한 방법보다 문제에있다.

   규칙은 없습니다. 나는 CTE를 더 읽기 발견하고 그들은 내가 실제 문제를 조사하기보다는 추측 CTE는 문제가 있음과는 다른 접근 방식을 사용하여 다시 작성하려고하는 경우 일부 성능 문제를 전시하지 않는 한 그들을 사용합니다. 일반적으로 더 내가 선언적 쿼리 내 의도를 명시하기 위해 선택한 방법보다 문제에있다.

   당신은 CTE를 해명 또는 하위 쿼리를 제거하고 #temp 테이블로 교체 및 기간을 줄일 수있는 경우는 확실히 있습니다. 이는 같은 오래된 통계 등 다양한 것들에 될 수 있기 때문에 쿼리의 복잡성 최적의 계획을 생성하는 경우에도 (예 : 테이블 반환 함수에 합류) 정확한 통계를 얻을 수없는, 병렬, 또는 무능력 ( 이 경우에 그것을 깨는) 옵티 마이저에게 싸울 기회를 제공 할 수 있습니다. 그러나 I / O가 CTE가 덜 매력적 사용하여 특정 계획 모양을 만들 수있는 다른 성능 측면을 능가 할 수있는 #temp 테이블을 생성과 관련된 경우도 있습니다.

   솔직히, 너무 많은 변수가 귀하의 질문에 "올바른"답을 제공 할 수있는 방법이 있습니다. 단지 알고 이론에서는, CTE 또는 단일 하위 쿼리에 대해 동일한 의미가 동일한를 실행해야 - 쿼리가 하나의 방법 또는 다른 찬성 팁을 수있는 경우 알 수있는 예측 가능한 방법이 없습니다. 나는이 사실이 아니라는 일부의 경우를 제시하는 경우 귀하의 질문에 더 가치가있을 거라고 생각 - 당신이 최적화에 제한을 발견 (또는 알려진 하나를 발견)했다고 할 수있다, 또는 당신의 쿼리를 의미 동등하지 않은 것을 할 수있다 나 하나가 무력화 최적화하는 요소가 포함되어 있습니다.

   내가 당신에게 가장 자연스러운 보이는 방법으로 쿼리를 작성 제안하고 최적화 데 실제 성능 문제를 발견 할 경우에만 벗어나는 것입니다 그래서. 개인적으로 나는 #temp 테이블이 최후의 수단 인으로, 그 CTE, 다음 하위 쿼리를 평가한다.

3. ==============================

   3.#temp는 materalized하고 CTE는 없습니다.

   #temp는 materalized하고 CTE는 없습니다.

   CTE는 너무 이론적으로 그냥 하위 쿼리입니다 구문입니다. 그것은 실행됩니다. #temp이 구체화된다. A의 비싼 CTE 그래서 더 나은 #temp에있을 수 있습니다 여러 번 실행 조인. 다른 측면에서 그것은 실행되지 않습니다 쉽게 평가지만 #temp의 오버 헤드를 몇 번 한 후 가치가없는 경우.

   은 테이블 변수처럼하지 않는 SO에 어떤 사람들하지만 그들처럼 나는이 구체화하고 빠르게 #temp보다 더 만들 수있다. 쿼리 최적화 프로그램은 테이블 변수에 비해 #temp 더 나은 않는 경우가 있습니다.

   (당신은 CTE에 PK를 선언 할 수 없습니다와 같은) #temp 또는 테이블 변수에 PK를 만드는 기능은 CTE보다 더 많은 정보 최적화 쿼리를 제공합니다.

4. ==============================

   4.내 생각은 그냥 두 가지가 항상 바람직 다음 CTE는 오히려 # 임시 테이블을 사용할 수 있도록 :

   내 생각은 그냥 두 가지가 항상 바람직 다음 CTE는 오히려 # 임시 테이블을 사용할 수 있도록 :

   -onedaywhen 어제

   다음은 CTE의의 경우가 아니라 #table 제약이 잘못된 데이터를 방지 할 수있는 예입니다

   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;
   

from https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-temporary-table-or-ta by cc-by-sa and MIT license