Strategieën voor query-optimalisatie

Introductie tot BigQuery

Matt Forrest

Field CTO

Drie vuistregels

Er zijn drie hoofdregels voor optimalisatie:

Verminder de hoeveelheid data die moet worden verwerkt
Optimaliseer de query-bewerkingen
Verminder de output van je query

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

Minder data verwerken

Vermijd SELECT *; selecteer alleen de kolommen die we nodig hebben
Filter vroeg en vaak in onze CTE’s
Filter data vroeg en vaak met de WHERE-clause

Joins optimaliseren

Beperk data met CTE’s.
Join op een INT64-datatype.

WITH filter_my_data AS (SELECT 
-- Filter data eerst met 
-- WHERE in de CTE
)
SELECT 
-- Deze query draait 
-- sneller met minder data
JOIN a USING (user_id)

De WHERE-clause optimaliseren

Gebruik in BigQuery
- BOOL
- INT
- FLOAT
- DATE
Datatypes met WHERE, STRING of BYTE zijn niet optimaal.

Niet optimaal

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

Optimaal

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

ORDER BY-optimalisaties

Zet ORDER BY altijd helemaal aan het einde van je query
Uitzondering: ORDER BY binnen een window clause

ORDER BY zonder optimalisatie

Niet optimaal

WITH order_total AS (SELECT
user_id,           
sum(product_price) as order_sum
FROM orders
GROUP BY user_id   
-- Order by staat niet aan het einde van de 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 met optimalisatie

Optimaal

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 hoort altijd aan het einde
ORDER BY orders_total.last_purchase_date;

EXISTS vs. COUNT gebruiken

Als we alleen willen weten of een record bestaat, gebruik EXISTS
Vermijd COUNT voor deze usecase

SELECT EXISTS (

  -- Schrijf de hoofdquery als subquery binnen de exists-aanroep
  SELECT
    user_id
  FROM
    dataset.table
  WHERE
    product_category = 'home_goods'
    AND status = 'Closed Account'
);

Andere optimalisaties

Gebruik benaderende aggregaties zoals APPROX_TOP_SUM of APPROX_COUNT_DISTINCT.
Veel BigQuery-tabellen zijn op datum gepartitioneerd: neem datums op in de WHERE-clause.

Laten we oefenen!

Introductie tot BigQuery