Enqurious logo
Back to blog
Guides & Tutorials

Optimizing Query Performance in BigQuery

Optimizing Query Performance in BigQuery blog cover image
BigQuery
Query Optimization
GCP
Ayushi GuptaSr. Data Engineer

Recently, I faced a scenario with a BigQuery table named orders. The table size was modest—9.78 MB. Here's the query I started with:

SELECT *
FROM gbmart.orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2025-01-31'

This query scanned all 9.78 MB of data, even though I was only interested in records for a specific date range. While this might seem negligible, the inefficiency compounds with larger datasets and frequent queries.

The Problem

When querying large tables, scanning unnecessary data increases query costs and slows performance. For larger datasets, this inefficiency can become a bottleneck. Clearly, an optimization strategy was needed.

blog0.png

The Solution: Partitioning

I decided to partition the table by OrderDate, as most of my query requirements were time-based. Partitioning breaks the data into manageable chunks, reducing the amount of data scanned for each query.

Here’s how I implemented it:

CREATE TABLE gbmart.orders_partitioned
PARTITION BY TIMESTAMP_TRUNC(OrderDate, MONTH)
AS
SELECT * FROM gbmart.orders;

By partitioning on OrderDate at the monthly level, I significantly optimized query performance. Running the same query on the partitioned table resulted in only 3.95 MB scanned—a 60% reduction in data processed!

blog00.png

Deciding Factors for Partitioning

Partitioning can work wonders, but how do you decide when and how to partition? Here are some key considerations:

  1. Query Patterns: Analyze how you query the data. Are your queries time-based, region-specific, or based on some other logical grouping? In my case, most queries were time-based, making OrderDate a natural choice.

  2. Granularity of Partitioning: Choose a granularity (e.g., daily, monthly, yearly) that balances query efficiency and storage costs. Monthly partitioning worked best for me, as daily partitioning would have created too many partitions for my use case.

  3. Table Size: Partitioning becomes crucial as table sizes grow. For smaller tables, the benefits might not outweigh the setup effort.

  4. Future Growth: Consider how your dataset might evolve. Designing for scalability ensures your approach remains efficient over time.

The Results

Partitioning the table brought immediate benefits:

  • Reduced data scanned: From 9.78 MB to 3.95 MB for the same query.

  • Lower costs: With fewer bytes processed, query costs decreased.

  • Improved performance: Queries ran faster with less overhead.

Partitioning is a simple but powerful strategy to optimize query performance in BigQuery. If you’re dealing with large datasets or frequent queries, it’s worth exploring.