Stratégies d'optimisation de requêtes

Introduction à BigQuery

Matt Forrest

Field CTO

Trois règles simples

Trois règles d'optimisation principales :

Réduire la quantité de données à traiter
Optimiser les opérations de la requête
Réduire la taille du résultat

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

Réduire la quantité de données

Évitez SELECT * ; ne sélectionnez que les colonnes nécessaires
Filtrez tôt et souvent dans vos CTE
Filtrez vos données tôt et souvent avec WHERE

Optimiser les jointures

Réduisez d'abord les données via des CTE.
Faites les jointures sur un type INT64.

WITH filter_my_data AS (SELECT 
-- Filtrer d'abord
-- avec WHERE dans la CTE
)
SELECT 
-- La requête s'exécute
-- plus vite avec moins de données
JOIN a USING (user_id)

Optimiser la clause WHERE

Dans BigQuery, privilégiez
- BOOL
- INT
- FLOAT
- DATE
Les types avec WHERE, STRING ou BYTE ne sont pas optimaux.

Pas 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

Optimisations d'ORDER BY

ORDER BY devrait toujours se trouver tout à la fin de la requête
Seule exception : ORDER BY dans une fenêtre (window clause)

ORDER BY sans optimisation

Non optimal

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- ORDER BY n'est pas à la fin de la requête
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 avec optimisation

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 devrait toujours être à la fin
ORDER BY orders_total.last_purchase_date;

EXISTS vs COUNT

Si vous devez seulement savoir si un enregistrement existe, utilisez EXISTS
Évitez COUNT pour ce cas d'usage

SELECT EXISTS (

  -- Écrire la requête principale comme sous-requête dans EXISTS
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

Autres méthodes d'optimisation

Utilisez des fonctions d'agrégation approximatives comme APPROX_TOP_SUM ou APPROX_COUNT_DISTINCT.
Beaucoup de tables BigQuery sont partitionnées par date : ajoutez des dates dans la clause WHERE.

Passons à la pratique !

Introduction à BigQuery