[BigQuery] 빅쿼리 슬롯 및 SQL 쿼리 최적화

 

 

슬롯 

: SQL 쿼리를 실행하기 위해 BigQuery 에 사용되는 가상 CPU

예약은 빅쿼리를 쓸 때, 이 슬롯 용량을 구매해서 쓰는 것을 말하는데, 쿼리는 해당 용량 범위 내에서 실행되며, 일반적으로 슬롯을 더 많이 구매하면 동시 쿼리를 더 많이 실행할 수 있어 복잡한 쿼리가 더욱 빠르게 실행된다.

 

쿼리는 해당 용량 범위 내에서 실행되며, 일반적으로 슬롯을 더 많이 구매하면 동시 쿼리를 더 많이 실행할 수 있으며 복잡한 쿼리가 더욱 빠르게 실행된다.

예를 들어 2,000개의 BigQuery 슬롯을 구매하면 집계 내의 쿼리가 언제든 2,000개 이하의 가상 CPU만 사용하도록 제한된다. 삭제할 때까지 이 용량이 제공되며 슬롯 2,000개에 대한 비용이 청구

 

만약 쿼리가 현재 사용가능한 슬롯보다 더 많은 슬롯을 요청하면, 큐에 저장하여 슬롯이 사용 가능해질때까지 대기하고, 쿼리 실행이 진행되며 슬롯이 확보되면 큐에 추가됐던 작업들이 자동으로 실행된다.

 

---

 

수정한 부분

쿼리는 두개의 테이블을 가져와 네이밍(AS A, B)을 하고 해당 콜롬의 값들을 쓴다.

 

 

JOIN

WHERE 절 - 두 테이블의 id값이 같은 특정 조건을 기준으로 원하는 행 확인

최대한 읽기 쉬운 방식으로 질의를 작성하는 것이 좋다.

=> 두개의 table을 WHERE 문으로 사용하는 구문을 join을 활용하여 가독성과 유지관리에 장점이 있다.

WHERE 절에서 데이터 필터링을 하는 것으로 IS NOT NULL은 그대로 적용

+ 적절한 JOIN을 사용하는 것도 중요한데,

예를들어서 INNER JOIN은 모든 행이 일치하는 경우에만 검색하고,

OUTER JOIN은 일치하지 않는 행도 검색할 수 있지만, INNER JOIN보다 성능이 떨어진다.

이전 쿼리에서 Oracle Join을 사용하여 하고 있다.

 

서브쿼리 최소화

서브쿼리는 SELECT 문 안에 포함된 또 다른 SELECT 문으로, 복잡한 쿼리를 작성할 때 유용하지만, 성능 저하의 원인이 될 수 있기에 서브쿼리를 최소화하는 것이 좋다.

서브쿼리 대신에 JOIN, UNION, EXISTS 등의 다른 방법을 사용하여 쿼리를 작성하는 것이 좋다. 

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE gender = 'F'
)

위의 코드를 아래와 같이 JOIN으로 변경하는 것이 가능하다.

SELECT orders.*
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.gender = 'F'

 

 

필요 데이터 취사선택

데이터베이스에서 데이터를 조회할 때, 불필요한 정보까지 모두 가져오는 것은 성능 저하의 주범 중하나다. 

대신 꼭 필요한 데이터만 골라내는 것이 성능 최적화에 좋은데, 

해당 작업을 수행하는데, 불필요한 값 삭제하였다.

 

분석함수 ROW_NUMBER()

그리고 분석함수를 사용하면 전통적인 집계함수와 달리 사전에 데이터를 그룹화할 필요가 없어 불필요한 자원 소모를 줄이고 쿼리 성능을 높일 수 있다.

 

ROW_NUMBER()

분석함수이자, 윈도우 함수인 ROW_NUMBER() 이 각 ROW 별로 세부적인 계산을 가능하게 하는데

ROW_NUMBER()에서 한 결과값이 DISTINCT를 꼭 사용하지 않아도 될 것 같았다.

중복값을 제거하는 연산은 최대한 사용하지 않는게 좋은데,
중복 값을 제거하는 연산은 많은 시간이 걸린다.
사용해야 하는 상황이라면 최대한 DISTINCT 연산을 대체할 수 있는 EXIST를 활용하거나, 연산 대상의 테이블 크기를 최소화하는 방법을 생각해보는게 좋다.

 

어떤 특정 기준에 따라 순위를 매기고 싶으면 ROW_NUMBER() 함수를 이용할 수 있다.
PARTITION 까지는 필요가 없는 것 같았고, 

왜 굳이 많은 RANK함수 대신 ROW_NUMBER() 함수를?

RANK()와 DENSE_RANK() 함수도 비슷한 방식으로 동작하지만, 동일한 값에 대해 동일 순위를 부여한다. DENSE_RANK()의 경우 순위 간격을 항상 1로 유지하는 특징이 있다.

PySpark 스터디에서 멤버 중 한명이 쉽게 알려주신 예시를 예를 들자면,
RANK() - 1, 1, 1, 4
DENSE_RANK() - 1, 1, 1, 2
(안적어놔서 확실하지 않다.)

 

 

 

이전 수정 전 쿼리

    SELECT * EXCEPT([값3]) FROM
    (
    SELECT DISTINCT
        A.[ID]
        , B.[값1] AS [값1]
        , B.[값3] AS [값3]
        , B.[] AS []
        , B.[] AS []
        , A.[값2] AS [값2]
        , A.[값5] AS [값5]
        , A.[값4 = 값 2]
    FROM [테이블명 1] A
        , [테이블명 2] B
    WHERE A.[ID] = B.[ID]
    AND B.[값이 존재시 Y인 열] ='Y'
    )
    WHERE [값1] IS NOT NULL
    ORDER BY ROW_NUMBER () OVER (ORDER BY
                        CASE WHEN [CASE WHEN 조건]
                        ELSE 99
                        END ASC, 
                        RIGHT([ID], 3) ASC, [값2] ASC
        )
    ;

 

 

위의 쿼리를 아래로 변경

 

+ 콤마는 실수할 가능성이 많기에, 값 앞에 두는 것을 선호

 

수정한 쿼리

WITH RankedItems AS (
  SELECT 
      A.[ID]
      , B.[값1] AS [값1]
      , A.[값2] AS [값2]
      , A.[값~]
      , ROW_NUMBER() OVER (
        ORDER BY 
          CASE WHEN [조건절] 
            ELSE 99
          END ASC, 
          RIGHT(A.[ID], 3) ASC, 
          A.[값2] ASC
      ) AS RN
  FROM 
      [테이블명 1] A, [테이블명 1] B
  WHERE 
  	  A.[ID] = B.[ID]
  AND
      B.[값이 존재시 Y인 열] = 'Y'
)

SELECT 
    [ID]
    ,[값1]
    ,[깂2]
    ,[값~]
FROM 
    RankedItems
WHERE 
    [값1] IS NOT NULL
ORDER BY 
    RN;

 

아래와 같이 슬롯이나 처리한 바이트가 줄어든 것을 확인할 수 있다.

 

 

 

 

 

 

 

 

 

 

피드백 언제나 환영입니다.

 

참고 :

- 쿼리 최적화 | BigQuery Guide Book - 빅쿼리 가이드북 (zzsza.github.io)

- 클러스터링된 테이블 소개  |  BigQuery  |  Google Cloud

- 쿼리 성능 최적화 소개  |  BigQuery  |  Google Cloud

- https://chung-develop.tistory.com/145#%EC%BF%BC%EB%A6%AC%20%EC%8B%A4%ED%96%89%20%EA%B3%84%ED%9A%8D%20%ED%99%95%EC%9D%B8%ED%95%98%EA%B8%B0-1

- https://www.itworld.co.kr/news/319554

- https://community.heartcount.io/ko/query-optimization-tips/