[SQL] CTE, 뷰 그리고 임시 테이블

대용량 데이터를 다룬 이후로 성능에 대한 관심이 커졌다. 특히 자주 사용하는 컬럼들에 대해 임시 테이블을 생성했던 것이 다른 테이블들과 조인 시 확실히 효율적이고 체감 성능도 개선되었던 좋은 경험이었다. 그래서 CTE와 뷰, 임시 테이블을 사용할 때 실제로 성능 차이가 있는지, 그리고 임시 테이블을 생성하기까지 몇 차례의 시행착오가 있었기 때문에 CTE와 임시 테이블을 언제, 어떻게 사용해야 할지 알아보았다.

CTE와 뷰의 차이

우선 CTE와 뷰에 대해서는 이전에 메모해둔 내용이 있어서 다시 찾아보았다. 

오늘 강의에서는 뷰에 대한 내용이 있었는데 뷰의 쓰임 중 하나가 복잡한 연산을 용이하게 하는 것이어서 with절과의 차이가 무엇인지 궁금해서 추가로 찾아보았다.​

​임시테이블이라는 관점에서는 유사하지만 view는 한 번 만들어 놓으면 삭제할 때까지 없어지지 않지만 with절의 경우 정의된 쿼리문 내에서만 실행된다는 차이점이 있다.

Difference between SQL View and WITH clause
SQL views and with clauses are very similar. Here are some differences.
Views create an actual object in the database, with associated metadata and security capabilities.
With statements are only part of a single query.
In many databases, views have options, for instance, to index them or to "instantiate" them.
With statements offer the opportunity to have recursive CTEs, in some databases. This is not possible for views.
For simple subqueries incorporated into queries, they are quite similar. The choice really depends on whether you want to create a reusable code (views) or are focused on a single query (with).

출처: [스타터스] TIL 9일차 - View(뷰)

뷰는 메타데이터로써 쿼리의 결과가 아닌 쿼리 정의 자체를 저장한다. 따라서 뷰를 조회할 때마다 쿼리문이 그대로 조회되는 것과 동일한 과정을 거치게 된다. 그렇다면 성능 개선의 관점에서 뷰는 pass

임시 테이블 사용 시 성능이 개선될까?

놀랍게도.. select에 대해서는 유의미한 성능차이가 없다고 한다. 아래 글에서는 임시 테이블을 생성하여 조인한 쿼리와 where 조건을 부여한 쿼리의 성능을 비교하였다.

Temporary Table
1. Drop Temporary Table
2. Create Temporary Table
3. 1부터 10,000,000 범위 숫자 20개 무작위 추출
4. 다음과 같이 Temporary Table과 Join하여 데이터 Select

>> SELECT a.i, a.c1, a.c2
FROM dbatest a INNER JOIN tmp_dbatest_12 b ON a.i = b.i

None Temporary Table
1. 1부터 10,000,000 범위 숫자 20개 무작위 추출
2. 다음과 같이 IN 구문을 사용하여 Select

>> SELECT a.i, a.c1, a.c2
FROM dbatest a
WHERE a.i in (1,2,3,4,..,17,18,19,20)

Performance Test Select Result

출처: MySQL에서 Temporary Table을 활용한 데이터 질의..그 효과는?

임시 테이블의 경우 "테이블의 생성/삭제, 데이터 Insert및 Join Select 등 불필요한 단계"가 존재하기 때문이다. 다만 여기서 한 단계 더 나아가서 생각을 해보자면

위 경우는 두 쿼리를 단순 일대일로 비교했기 때문에 절차가 더 많은 임시 테이블의 성능이 더 좋지 않았던 것이고, 임시 테이블을 2회 이상 재사용한다면 그 이후부터는 테이블의 생성/삭제 및 데이터 insert 단계가 생략되기 때문에 더 나은 성능을 보일 것이라고 예상된다.

그럼에도 임시 테이블을 사용하는 이유?

기본적으로 데이터베이스에서는 쿼리에서 요청하는 데이터를 반환하기 위해 결과를 추정할 수 있지만 중첩된 서브쿼리(CTE)를 여러 번 사용할 경우 이 추정이 틀릴 가능성이 높아진다.

If you have nested subqueries, the error will propagate and can get amplified, up to the point that – potentially – the query optimizer will try to use index “A” as it thinks at some point there will be only – for example – 10 rows involved, but there will be 10K for real, making the index usage a potentially bad choice.

→ 중첩된 서브쿼리가 있는 경우 오류가 전파되고 증폭될 수 있다

이 때 서브쿼리의 결과를 임시 테이블에 저장하면 쿼리 엔진이 임시 테이블의 데이터를 사용할 수 있으므로 잘못된 추정을 줄일 수 있다는 것이다. 또한 동일한 쿼리를 반복해서 작성해야 하는 불편함도 당연히 감소된다.

Temporary tables can help to greatly reduce or even fix the poor row estimation due to the aforementioned error amplification. How? Well, by storing the result of a subquery into a temporary table, you are resetting such error amplification as the query engine can use the data in the temporary table and thus make sure it is not guessing too much anymore.

→ 서브쿼리의 결과를 임시 테이블에 저장함으로써 결과 row에 대한 잘못된 추정을 줄이거나 수정할 수 있다

Another reason to use a temporary table is if you have a complex query that needs to be used one or more time in subsequent steps and you want to avoid spending time and resource to execute that query again and again (especially if the result set is small compared to the originating data and/or the subsequent queries will not be able to push any optimization down to the subquery as you are working on aggregated data, for example)

→ 다음 단계에서 한 번 이상 사용되는 복잡한 쿼리인 경우

출처: CTEs, Views or Temp Tables?

그동안 CTE를 사용한 경험을 되짚어보면, 하부가 상부를 참조하는 방식으로 연속적으로 작성되기 때문에 하나의 쿼리문 내에서만 유효했고 같은 결과를 재사용하기 위해서 필요한 부분을 반복해서 복사-붙여넣기 해야했다. 반면 임시 테이블은 세션이 살아있는 동안에는 테이블 명만을 기재하여 참조할 수 있다는 장점이 있다.

/* CTE */
WITH sub1 AS
  (
  -- 생략
   )
   , sub2 as
  (
  -- 생략
   )
   , sub3 as
  (
  -- 생략
   )
  
 
/* Temporary table */
CREATE TEMPORARY TABLE TMP_tbl1 AS
  -- 생략

CTE와 임시 테이블은 각각 언제, 어떻게 사용해야 할까?

종합하면 한 번 이상 재사용되는 쿼리에 대해 임시 테이블을 생성하는 것은 성능 면에서나 가독성 면에서 분명히 개선 효과가 있다. 그러나 변동성이 있는 쿼리를 조회하는 과정에서 매번 임시 테이블을 생성하는 것은 의미가 없으며 비효율적이다.

CTEs are usually better when:

• SQL Server can do a good job of estimating how many rows will come out of it, and the contents of what those rows will be, or
• When what comes out of the CTE doesn’t really influence the behavior of the rest of the query, or
• When you’re not sure what portions of the CTE’s data will actually be necessary for the rest of the query (because SQL Server can figure out what parts to execute, and what parts to simply ignore)

→ 결과 row 수를 예측할 수 있을 때, CTE의 결과에서 어떤 데이터를 실제로 사용할지 불분명할 때

Temp tables are usually better when:

• You have to refer to the output multiple times, or
• When you need to pass data between stored procedures, or
• When you need to break a query up into phases to isolate unpredictable components that dramatically affect the behavior of the rest of the query

→ 여러 번 참조해야 할 때, 쿼리를 단계별로 분할해야 할 때

출처: What’s Better, CTEs or Temp Tables?

 

---

참고

CTEs, Views or Temp Tables? - Azure SQL Devs’ Corner

MySQL에서 Temporary Table을 활용한 데이터 질의..그 효과는? – gywndi's database

What's Better, CTEs or Temp Tables? - Brent Ozar Unlimited®