Query Tuning Bigquery

Question

I have a query with a strange behavior because in certain occasions it takes 120 seconds and another 250 seconds. I have to minimize execution time but can not find a guide to help me to improve the runtime of my queries.

The table containing the information has the following characteristics:

All fields are repeat
It has 800 million records
Process 14.2 GB

The query is this:

SELECT
 todayInfo.client AS Client,
  todayInfo.todayInfo AS Today,
  todayInfo.dayAgoInfo AS DayAgo,
  todayInfo.threeDaysAgo AS ThreeDaysAgo,
  todayInfo.weekAgo AS weekAgo,
FROM (
  SELECT
    client,
    SUM(IF( bp_Time BETWEEN TIMESTAMP('2016/01/01')
    AND TIMESTAMP('2016/01/31'),1,0)) AS todayInfo,
    SUM(IF( bp_Time BETWEEN DATE_ADD(TIMESTAMP('2016/01/01'), - 1,"DAY")
    AND DATE_ADD(TIMESTAMP('2016/01/31'), - 1,"DAY"),1,0)) AS dayAgoInfo,
    SUM(IF( bp_Time BETWEEN DATE_ADD(TIMESTAMP('2016/01/01'), - 3,"DAY")
    AND DATE_ADD(TIMESTAMP('2016/01/31'), - 3,"DAY"),1,0)) AS threeDaysAgo,
    SUM(IF( bp_Time BETWEEN DATE_ADD(TIMESTAMP('2016/01/01'), - 8,"DAY")
    AND DATE_ADD(TIMESTAMP('2016/01/31'), - 8,"DAY"),1,0)) AS weekAgo
  FROM
    [dataset.table]
   GROUP BY
    client   
  ) AS todayInfo

ORDER BY
    Today DESC
LIMIT 10

There is a guide or tips that can help me optimize runtimes not only this query but also future queries?

Can you publish here your [query plan explanation](https://cloud.google.com/bigquery/query-plan-explanation) to get an idea of where BigQuery is spending its time? — DoiT International, Feb 08 '16 at 19:59
does it make a difference if you use GROUP BY vs GROUP EACH BY? — Felipe Hoffa, Feb 08 '16 at 23:16
The GROUP BY EACH is slower than the GROUP BY. In My Web UI i dont have the option 'Explanation'. — Cristian Arboleda, Feb 09 '16 at 16:50
I can be wrong, but looks like 'Explanation' option was removed recently - I don't have it anymore too — Mikhail Berlyant, Feb 10 '16 at 00:19
Mikhail: the disappearance of the "Explain" button was an accidental UI regression, it's now fixed. — William Vambenepe, Feb 18 '16 at 07:44

score 2 · Answer 1 · edited May 23 '17 at 12:23

Because of the way BigQuery works, it isn't as tunable as relational databases. It does not use indexes, and every query is a full scan.

That being said, you need to learn more about why your queries are slow. This depends a lot on the specifics of your data. You can use the query plan explanation to get a good idea of where BigQuery is spending its time.

But, as @Pentium10 mentioned in the comment, your probably going to have to partition your data by day to reduce the size of scans. It's explained in more detail in this answer.

score 1 · Answer 2 · answered Apr 23 '18 at 21:18

In this link you can find more information on how BigQuery works behind the scenes. Understanding slots are key to optimizing queries. Slots are units of computational capacity and the data is shuffle between them depending on the joins, order by, group by, functions, subqueries and more.

In your case as you compute "SUM", "IF", "BETWEEN" in your query you are demanding more computation. Some functions that would be useful in your case is the "DATE_SUB" and the countif().

SELECT countif((DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))=end_date) as AS dayAgoInfo from [dataset.table];

Query Tuning Bigquery

2 Answers2