Strategie di ottimizzazione delle query

Introduzione a BigQuery

Matt Forrest

Field CTO

Tre regole pratiche

Ci sono tre regole principali:

Riduci i dati da elaborare
Ottimizza le operazioni della query
Riduci la dimensione dell'output

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

Ridurre la quantità di dati

Evita SELECT *: seleziona solo le colonne necessarie
Filtra presto e spesso i dati nelle CTE
Filtra i dati con WHERE il prima possibile e spesso

Ottimizzare le join

Riduci i dati necessari usando le CTE.
Esegui le join usando un tipo 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)

Ottimizzare la clausola WHERE

In BigQuery, usa
- BOOL
- INT
- FLOAT
- DATE
I tipi dati con WHERE, STRING o BYTE non sono ottimali.

Non ottimale

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

Ottimale

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

Ottimizzazioni di ORDER BY

ORDER BY va sempre alla fine (esterno) della query
Unica eccezione: ORDER BY dentro una window function

ORDER BY senza ottimizzazione

Non ottimale

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 con ottimizzazione

Ottimale

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;

Usare EXISTS vs. COUNT

Se ti serve solo sapere se un record è in tabella, usa EXISTS
Evita COUNT per questo caso d'uso

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'
);

Altri metodi di ottimizzazione

Usa funzioni di aggregazione approssimate come APPROX_TOP_SUM o APPROX_COUNT_DISTINCT.
Molte tabelle BigQuery sono partizionate per data: includi date nella clausola WHERE.

Esercitiamoci!

Introduzione a BigQuery