Strategi optimisasi kueri

Pengantar BigQuery

Matt Forrest

Field CTO

Tiga aturan praktis

Ada tiga aturan optimisasi utama:

Kurangi data yang perlu diproses
Optimalkan operasi kueri
Kurangi ukuran output kueri

¹ https://cloud.google.com/bigquery/docs/best-practices-performance-compute#use-bi-engine

Mengurangi jumlah data

Hindari SELECT *; pilih hanya kolom yang diperlukan
Saring data dalam CTE sedini dan sesering mungkin
Saring data dengan klausa WHERE sedini dan sesering mungkin

Mengoptimalkan join

Pastikan kita mengurangi data menggunakan CTE.
Lakukan join dengan tipe data INT64.

WITH filter_my_data AS (SELECT 
-- Filter data with 
-- WHERE in the CTE first
)
SELECT 
-- This query will run 
-- faster with less data
JOIN a USING (user_id)

Mengoptimalkan klausa WHERE

Di BigQuery, gunakan
- BOOL
- INT
- FLOAT
- DATE
Tipe data dengan WHERE, STRING, atau BYTE tidak optimal.

Tidak optimal

SELECT user_id, date_ordered
FROM dataset.table
WHERE product = 'shoes'

Optimal

SELECT user_id, date_ordered
FROM dataset.table
WHERE product_id = 1234

Optimisasi ORDER BY

ORDER BY sebaiknya selalu di bagian terluar (akhir) kueri
Satu-satunya pengecualian: ORDER BY dalam klausa window

ORDER BY tanpa optimisasi

Tidak optimal

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- Order by is not at the end of the query
ORDER BY last_purchase_date
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN order_total USING (user_id);

ORDER BY dengan optimisasi

Optimal

WITH order_total AS (SELECT
user_id,    
last_purchase_date
sum(product_price) as order_sum
GROUP BY user_id         
)
SELECT order_total.order_sum, 
users.user_name
FROM dataset.users users
JOIN a USING (user_id)
-- Order by should always be at the end
ORDER BY orders_total.last_purchase_date;

Menggunakan EXISTS vs. COUNT

Jika hanya perlu tahu apakah sebuah record ada di tabel, gunakan EXISTS
Hindari memakai COUNT untuk kasus ini

SELECT EXISTS (

  -- Write the main query as a subquery within the exists call
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

Metode optimisasi lain

Gunakan fungsi agregat perkiraan seperti APPROX_TOP_SUM atau APPROX_COUNT_DISTINCT.
Banyak tabel BigQuery dipartisi per tanggal — sertakan tanggal di klausa WHERE.

Ayo berlatih!

Pengantar BigQuery