Cristian Arboleda February 2016

Query Tuning Bigquery

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:

  1. All fields are repeat
  2. It has 800 million records
  3. 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?

Answers


mimming February 2016

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.

Post Status

Asked in February 2016
Viewed 1,701 times
Voted 14
Answered 1 times

Search




Leave an answer