Blend Bytes 1: speed up SQL queries in BigQuery

Blend Bytes are short, practical tips for people who work with data day to day.

Is a slow query holding you back?

When you’re starting out with BigQuery, it’s easy to trip up. You might write a query that uses more computing power than it needs, or takes far longer than it should. That’s frustrating, and in BigQuery it can also cost you, because you pay for the data each query reads.

The sooner you pick up a few good habits, the faster your analysis goes. Here are three easy ones.

1. Avoid SELECT *

Instead of selecting every column, ask only for the columns you need. BigQuery stores data by column, so the fewer columns you read, the less data it has to fetch and process, and the less the query costs.

2. Filter with a WHERE clause

Always narrow your data down with a WHERE clause. Limiting the rows the query works with keeps it quick. If your table is partitioned by date, filtering on that date column means BigQuery can skip whole sections of the table.

3. Sort your results with ORDER BY

We’re constantly surprised by how many result tables come back in no particular order. Sorting them makes the output far easier to read and check, and saves time when you’re looking for something specific. Sort the final result rather than every step along the way, as sorting large amounts of data adds work.

Want more tips like these? Follow Blend on LinkedIn.