BigQuery: why querying from temporary table takes 20+ times more time than individual aggregations over entire dataset?

Question

Table dataset.transactions contains ~5.000.000 records.

1) This query takes ~ 3 seconds:

SELECT *
  FROM dataset.transactions
  WHERE customer = 'c1' AND year = 2017

2) This query takes about 10 seconds:

SELECT
  salesrep_id AS id,
  date AS lastUsedForFplDate,
  fpl AS individual_fpl,
  ANY_VALUE(salesrep_name) AS salesrep_name,
  ANY_VALUE(customer) AS customer
FROM dataset.transactions VT1
  WHERE date = (
    SELECT
      MAX(date)
    FROM dataset.transactions VT2
    WHERE
      VT1.salesrep_id = VT2.salesrep_id
  )
GROUP BY
  salesrep_id,
  date,
  fpl

3) Whereas this one takes more than 200 seconds (tried few times, cancelled it each time after 200 seconds):

WITH transactions AS (
  SELECT *
  FROM dataset.transactions
  WHERE customer = 'c1' AND year = 2017
)

SELECT
  salesrep_id AS id,
  date AS lastUsedForFplDate,
  fpl AS individual_fpl,
  ANY_VALUE(salesrep_name) AS salesrep_name,
  ANY_VALUE(customer) AS customer
FROM transactions VT1
  WHERE date = (
    SELECT
      MAX(date)
    FROM transactions VT2
    WHERE
      VT1.salesrep_id = VT2.salesrep_id
  )
GROUP BY
  salesrep_id,
  date,
  fpl

Third query consist of the two queries above, except the #2 uses output of #1 as a source.

Why does it take so much time?

I'd suggest posting a job-id so the nice folks over at Google at look into it for you. — Graham Polley, May 07 '18 at 11:40
Self joins are [an anti pattern](https://cloud.google.com/bigquery/docs/best-practices-performance-patterns#self-joins). Why not use an analytic function to get the most recent row for each category? — Elliott Brossard, May 07 '18 at 13:28
Search for "most recent" under the google-bigquery tag, e.g.: https://stackoverflow.com/q/43368239/6253347 — Elliott Brossard, May 07 '18 at 14:24

score 1 · Accepted Answer · answered May 19 '18 at 10:44

Just not to leave the question unanswered. The third query uses self-joins which is an anti pattern as commented by Elliott.

The result of using a self-join is that it potentially doubles the number of output rows. This increase in output data can cause poor performance.

Instead of using a self-join, it's recommended to use a window (analytic) function to reduce the number of additional bytes that are generated by the query.

The simplified syntax of Analytic Functions is as below:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

BigQuery: why querying from temporary table takes 20+ times more time than individual aggregations over entire dataset?

1 Answers1