[sql-server] 더 성능이 뛰어난 CTE 또는 임시 테이블은 무엇입니까?

때에 따라 다르지.

가장 먼저

공통 테이블식이 란 무엇입니까?

(재귀 적이 지 않은) CTE는 SQL Server에서 인라인 테이블 식으로 사용할 수있는 다른 구문과 매우 유사하게 처리됩니다. 파생 테이블, 뷰 및 인라인 테이블 값 함수 BOL은 CTE가 “임시 결과 집합으로 생각할 수있다”고 말하지만 이것은 순수한 논리적 설명입니다. 종종 그 자체로는 materlialized되지 않습니다.

임시 테이블이란 무엇입니까?

이것은 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 분이 걸립니다.

1,000,000 개의 임의로 생성 된 값 중 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 기사 복잡한 쿼리를 분석 할 때를 참조하십시오 .

경우에 따라 SQL Server는 스풀을 사용하여 CTE와 같은 중간 결과를 캐시하고 해당 하위 트리를 다시 평가하지 않아도됩니다. 이것은 (이주 된) 연결 항목에서 설명됩니다 . CTE 또는 파생 테이블의 중간 구체화를 강제하는 힌트를 제공하십시오 . 그러나 이에 대한 통계는 작성되지 않으며 스풀 행 수가 예상과 크게 다를지라도 진행중인 실행 계획이 응답에 동적으로 적응할 수는 없습니다 (적어도 현재 버전에서는 적응 쿼리 계획이 가능할 수 있음). 미래).

답변

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

select * from #temp

select * from #temp