CTE와 임시 테이블 중 어느 쪽이 더 퍼포먼스입니까?

CTE와 임시 테이블 중 어느 쪽이 더 퍼포먼스입니까?

CTE ★★★★★★★★★★★★★★★★★」Temporary Tables

사정에 따라 다르겠지.

일단은

일반적인 테이블 표현이란 무엇입니까?

(재귀적이지 않은) CTE는 SQL Server에서 인라인 테이블 표현으로도 사용할 수 있는 다른 구성 요소와 매우 유사하게 취급됩니다.파생 테이블, 뷰 및 인라인 테이블 값 함수.BOL에서는 CTE는 "일시적인 결과 집합으로 간주할 수 있다"고 하지만 이는 순전히 논리적인 설명이라는 점에 유의하십시오.대부분의 경우 그것은 그 자체로 실현되지 않는다.

임시 테이블이 뭐죠?

이것은 tempdb의 데이터 페이지에 저장된 행 모음입니다.데이터 페이지는 부분적으로 또는 전체적으로 메모리에 상주할 수 있습니다.또한 임시 테이블을 인덱싱하고 열 통계를 가질 수 있습니다.

테스트 데이터

CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO T(B)
SELECT TOP (1000000)  0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
     master..spt_values v2;

예 1

WITH CTE1 AS
(
SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780

위의 계획에는 CTE1에 대한 언급이 없습니다.기본 테이블에 직접 액세스할 뿐이며 다음과 같이 처리됩니다.

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM   T
WHERE  A = 780 

여기서 CTE를 중간 임시 테이블로 재작성하면 생산성이 크게 저하됩니다.

CTE의 정의를 구체화

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T

약 8GB의 데이터를 임시 테이블에 복사해야 하는데, 이 테이블에서 선택해야 하는 오버헤드가 남아 있습니다.

예 2

WITH CTE2
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY A) AS RN
         FROM   T
         WHERE  B % 100000 = 0)
SELECT *
FROM   CTE2 T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   CTE2 T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

위의 예시는 제 기계에서 약 4분 정도 걸립니다.

랜덤으로 생성된 값 100만 개 중 15개 행만이 술어와 일치하지만 비싼 테이블스캔은 이들을 찾기 위해 16회 실행됩니다.

이것은 중간 결과를 실현하기 위한 좋은 후보가 될 것이다.동등한 임시 테이블의 개서에는 25초가 걸렸습니다.

INSERT INTO #T
SELECT *,
       ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM   T
WHERE  B % 100000 = 0

SELECT *
FROM   #T T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   #T T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

쿼리 일부를 임시 테이블로 중간 구체화하는 것은 한 번만 평가하더라도 때로는 유용할 수 있다. 구체화된 결과에 대한 통계를 활용하여 나머지 쿼리를 재컴파일할 수 있는 경우이다.이 방법의 예는 SQL Cat 문서 When To Break Down Complex Queries에 있습니다.

경우에 따라서는 SQL Server가 CTE 등의 중간 결과를 캐시하기 위해 스풀을 사용하여 해당 서브트리를 재평가할 필요가 없습니다.이는 (이행된) 연결 항목 "CTE 또는 파생된 테이블의 중간 구체화를 강제하기 위한 힌트 제공"에서 설명합니다.그러나 이에 대한 통계는 생성되지 않으며 스풀된 행의 수가 예상과 크게 다르더라도 진행 중인 실행 계획이 응답에 동적으로 적응할 수 없습니다(최소한 현재 버전에서는).향후 적응형 쿼리 계획이 가능해질 수 있습니다.)

컨셉은 다르지만 '찰크와 치즈'라고 하기에는 너무 다르지 않습니다.

• 임시 테이블은 데이터 세트를 재사용하거나 여러 처리 패스를 수행하는 데 적합합니다.

• CTE는 반복 또는 단순히 가독성 향상을 위해 사용할 수 있습니다.
  또는 값할 수 .

• 임시 테이블은 범위에 대한 몇 가지 규칙이 있는 다른 테이블입니다.

둘 다 사용할 수 있는 프로세서(및 테이블 변수)를 저장했습니다.

CTE는 CTE의 데이터가 작고 재귀 테이블의 경우와 같이 가독성이 강한 경우에 사용됩니다.그러나 그 성능은 확실히 테이블 변수와 다를 바 없으며 매우 큰 테이블을 다룰 경우 임시 테이블이 CTE를 크게 능가합니다.이는 CTE 상에서 인덱스를 정의할 수 없기 때문에 다른 테이블과의 결합이 필요한 대량의 데이터가 있는 경우(CTE는 단순히 매크로와 같습니다).수백만 행의 레코드가 포함된 여러 테이블을 결합할 경우 CTE는 임시 테이블보다 성능이 크게 저하됩니다.

임시 테이블은 항상 디스크에 저장되어 있기 때문에 CTE를 메모리에 유지할 수 있는 한 (테이블 변수 등) 속도가 빠를 수 있습니다.

그러나 CTE(또는 임시 테이블 변수)의 데이터 로드가 너무 커지면 디스크에도 저장되므로 큰 이점이 없습니다.

일반적으로 CTE는 사용 후 사라지기 때문에 임시 테이블보다 선호합니다.명시적으로 떨어뜨릴 생각은 하지 않아도 돼요

그래서 결국 명확한 답은 없지만 개인적으로 임시 테이블보다는 CTE를 선호합니다.

최적화하도록 할당된 쿼리는 SQL Server에 있는2개의 CTE로 작성되었습니다.28초 걸렸어요

임시 테이블로 변환하는 데 2분이 걸렸고 쿼리는 3초가 걸렸습니다.

참가하고 있는 필드의 임시 테이블에 인덱스를 추가하여 2초로 줄였습니다.

CTE를 제거함으로써 3분간의 작업이 12배 빨라졌습니다.CTE도 디버깅이 어려워지기 때문에 개인적으로 사용하지 않습니다.

놀라운 것은 CTE는 단 한 번만 사용되었지만 여전히 인덱스를 붙이는 것이 50% 더 빠르다는 것이 입증되었다는 것입니다.

저는 두 가지 방법을 모두 사용해봤지만, 매우 복잡한 절차에서는 항상 임시 테이블이 작업하기에 더 좋고 더 체계적이라는 것을 알게 되었습니다.CTE에는 용도가 있지만 일반적으로 작은 데이터를 사용합니다.

예를 들어, 15초 만에 큰 계산 결과를 얻을 수 있는 sproc를 만들고 이 코드를 CTE에서 실행하도록 변환한 후 동일한 결과를 얻기 위해 8분 이상 실행되는 것을 확인했습니다.

CTE는 물리적인 공간을 차지하지 않습니다.join을 사용할 수 있는 결과 세트입니다.

임시 테이블은 임시 테이블입니다.인덱스를 생성하여 모든 변수를 정의해야 하는 일반 테이블과 같이 구속할 수 있습니다.

임시 테이블의 범위(세션 내에만 해당).두 개의 SQL 쿼리 창을 엽니다.

create table #temp(empid int,empname varchar)
insert into #temp 
select 101,'xxx'

select * from #temp

첫 번째 창에서 이 쿼리를 실행하고 두 번째 창에서 아래 쿼리를 실행하면 차이를 찾을 수 있습니다.

select * from #temp

파티에 늦었지만...

제가 일하는 환경은 매우 제약이 있어 일부 벤더의 제품을 지원하고 보고서 작성과 같은 "부가가치" 서비스를 제공합니다.정책 및 계약 제한으로 인해 별도의 테이블/데이터 공간을 사용하거나 영구 코드를 생성할 수 있는 기능은 일반적으로 허용되지 않습니다(어플리케이션에 따라 약간 향상됩니다).

IOW는 보통 저장 프로시저나 UDF, 임시 테이블 등을 개발할 수 없습니다.MY 어플리케이션인터페이스(Crystal Reports - add/link tables, w/in CR의 절 설정 등)를 통해 거의 모든 작업을 수행해야 합니다.작은 장점 중 하나는 Crystal이 COMMANDS(SQL Expression)를 사용할 수 있게 해 준다는 것입니다.일반적인 추가/링크 테이블 기능을 통해 효율적이지 않은 일부 작업은 SQL 명령을 정의하여 수행할 수 있습니다.나는 그것을 통해 CTE를 사용하고, 「리모트」로 매우 좋은 결과를 얻었다.또한 CTE는 유지보수를 보고하기 위해 코드를 개발하여 DBA에게 넘겨 컴파일, 암호화, 전송, 설치 및 다단계 테스트를 수행할 필요가 없습니다.로컬 인터페이스를 통해 CTE를 실행할 수 있습니다.

CR에서 CTE를 사용하는 단점은 각 보고서가 분리된다는 것입니다.보고서별로 각 CTE를 유지해야 합니다.SP 및 UDF를 수행할 수 있는 경우 여러 보고서에서 사용할 수 있는 기능을 개발할 수 있습니다. SP에 링크하고 일반 테이블에서 작업하는 것처럼 매개 변수를 전달하기만 하면 됩니다.CR은 SQL 명령어에 대한 매개 변수를 잘 처리하지 못하기 때문에 CR/CTE 측면이 부족할 수 있습니다.이 경우, 저는 보통 충분한 데이터(모든 데이터는 아님)를 반환하도록 CTE를 정의하고 CR의 레코드 선택 기능을 사용하여 그것을 슬라이스 및 주사위 모양으로 만듭니다.

그래서... 제 투표는 CTE를 위한 것입니다(데이터 공간을 확보할 때까지).

CTE가 뛰어난 퍼포먼스를 발휘한다는 것을 알게 된 것은 각각 몇 백만 개의 행이 있는 몇 개의 테이블에 비교적 복잡한 쿼리를 참여시킬 필요가 있었던 것입니다.

먼저 CTE를 사용하여 인덱스된 컬럼에 따라 서브셋을 선택하고 이들 테이블을 각각 수천 개의 관련 행으로 줄인 후 CTE를 메인 쿼리에 참여시켰습니다.이로 인해 쿼리 실행 시간이 기하급수적으로 단축되었습니다.

CTE의 결과는 캐시되지 않고 테이블 변수가 더 나은 선택이었을 수도 있지만, 저는 그것들을 꼭 시험해 보고 싶었고 위의 시나리오에 적합하다는 것을 알았습니다.

방금 테스트했습니다.CTE와 비CTE(모든 유니언 인스턴스에 대해 쿼리를 입력) 모두 최대 31초가 걸렸습니다.그러나 CTE는 코드를 훨씬 더 읽기 쉽게 만들었습니다. - 241줄에서 130줄로 줄여서 매우 좋습니다.반면 임시 테이블은 132행으로 줄여서 실행하는 데 5초가 걸렸습니다.장난 아니야.이 모든 테스트가 캐시되었습니다. 쿼리 모두 이전에 여러 번 실행되었습니다.

이것은 매우 개방적인 질문으로, 모두 사용 방법과 임시 테이블 유형(표 변수 또는 기존 테이블)에 따라 달라집니다.

기존 임시 테이블은 데이터를 임시 DB에 저장하므로 임시 테이블 속도가 느려지지만 테이블 변수는 느려지지 않습니다.

SQL Server에서의 경험을 바탕으로 CTE가 Temp 테이블을 능가하는 시나리오 중 하나를 발견했습니다.

저장 프로시저에서 복잡한 쿼리의 DataSet(~100000)을 한 번만 사용해야 했습니다.

• 임시 테이블로 인해 내 프로시저가 느리게 실행되고 있는 SQL의 오버헤드가 발생했습니다(Temp Tables는 현재 프로시저의 수명 동안 tempdb 및 persist에 존재하는 실제 구체화된 테이블입니다).

• 한편 CTE에서는 다음 쿼리가 실행될 때까지CTE Persist가 실행됩니다.따라서 CTE는 범위가 제한된 편리한 인메모리 구조입니다.CTE는 기본적으로 tempdb를 사용하지 않습니다.

이것은 CTE가 코드를 심플화하고 Temp Table보다 뛰어난 성능을 발휘하는 시나리오 중 하나입니다.2개의 CTE를 사용한 적이 있습니다.

WITH CTE1(ID, Name, Display) 
AS (SELECT ID,Name,Display from Table1 where <Some Condition>),
CTE2(ID,Name,<col3>) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 <Some Condition>)
SELECT CTE2.ID,CTE2.<col3>
FROM CTE2
GO

언급URL : https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables