Strategier för frågeoptimering

Introduktion till BigQuery

Matt Forrest

Field CTO

Tre tumregler

Det finns tre huvudsakliga optimeringsregler:

Minska mängden data som behöver bearbetas
Optimera frågeoperationerna
Minska utdatastorleken för din fråga

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

Minska datamängden

Undvik SELECT * – välj bara de kolumner du behöver
Filtrera data i dina CTE:er tidigt och ofta
Filtrera data med WHERE-satsen tidigt och ofta

Optimera joins

Minska datamängden med CTE:er.
Joina med datatypen 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)

Optimera WHERE-satsen

Använd i BigQuery
- BOOL
- INT
- FLOAT
- DATE
Datatyperna WHERE, STRING och BYTE är inte optimala.

Inte optimalt

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

Optimalt

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

Optimeringar med ORDER BY

ORDER BY ska alltid placeras ytterst (i slutet) av frågan
Det enda undantaget är ORDER BY i en fönsterfunktionssats

ORDER BY utan optimering

Inte optimalt

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 med optimering

Optimalt

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;

EXISTS jämfört med COUNT

Om du bara behöver kontrollera om en post finns i tabellen, använd EXISTS
Undvik att använda COUNT för detta ändamål

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

Andra optimeringsmetoder

Använd ungefärliga aggregatfunktioner som APPROX_TOP_SUM eller APPROX_COUNT_DISTINCT.
Många BigQuery-tabeller är partitionerade efter datum – inkludera datum i WHERE-satsen.

Nu kör vi en övning!

Introduktion till BigQuery