# BigQuery query performance best practices

> Which BigQuery performance advice is worth following, measured. Slot-time and run time for LIKE vs regex, approximate counts, JavaScript UDFs, ORDER BY, window functions and NTILE, plus advice from Google's guide that did not hold up in our tests.

Source: https://docs.querylab.io/bigquery/performance-best-practices/

Updated: 2026-10-14

Most BigQuery advice is about bytes, because bytes are the bill: read fewer columns, prune partitions, don't `SELECT *`. That advice is in [how queries are billed](https://docs.querylab.io/bigquery/how-queries-are-billed/) and [partitioned tables](https://docs.querylab.io/bigquery/partitioning/). This page is about the other half, compute. Two queries can read the same bytes and differ by 40 times in slot-time. Under on-demand pricing that difference is not on your bill, but it is how long you wait, and under a [reservation](https://docs.querylab.io/bigquery/slots/) it is the capacity you pay for.

Google's guide lists dozens of practices. We ran the ones that can be tested on our own tables of 2 to 10 million rows, read at the job's slot-time and elapsed time, and kept the ones that moved. We repeated each query 3 to 8 times and report medians, because slot-time for the same query can vary by 30 percent or more from run to run. See [how a query runs](https://docs.querylab.io/bigquery/query-execution/) for what slot-time is and how to read it.

## What moved the numbers

| Slower form, instead of the faster one                                                                       | Slower              | Faster             | Factor     |
| ------------------------------------------------------------------------------------------------------------ | ------------------- | ------------------ | ---------- |
| `DENSE_RANK() OVER (ORDER BY ...)` over all rows, then `LIMIT 10`, instead of `LIMIT` first, then the window | 72.9 slot-s, 14.5 s | 1.8 slot-s, 0.6 s  | 42x, 24x   |
| `NTILE(10)` instead of `APPROX_QUANTILES(x, 10)`                                                             | 24.6 slot-s         | 1.9 slot-s         | 13x        |
| JavaScript UDF instead of a SQL UDF with the same expression                                                 | 15.5 slot-s         | 0.6 slot-s         | 25x        |
| `COUNT(DISTINCT x)` instead of `APPROX_COUNT_DISTINCT(x)`                                                    | 4.5 slot-s          | 0.9 slot-s         | 5x         |
| The same, 100 groups                                                                                         | 8.9 slot-s          | 2.8 slot-s         | 3.1x       |
| Join first and aggregate after, instead of aggregating one side before the join                              | 14.8 slot-s         | 5.4 slot-s         | 2.7x       |
| `REGEXP_CONTAINS(s, r'123')` instead of `s LIKE '%123%'`                                                     | 2.8 slot-s          | 1.4 slot-s         | 2.0x       |
| Filter on a `STRING` key instead of an `INT64` key, equality                                                 | 0.87 slot-s         | 0.48 slot-s        | 1.8x       |
| The same, range filter                                                                                       | 1.41 slot-s         | 0.56 slot-s        | 2.5x       |
| `ORDER BY` on a 10-million-row `CREATE TABLE AS SELECT`                                                      | 69.9 slot-s, 11.4 s | 49.7 slot-s, 3.4 s | 1.4x, 3.3x |
| A CTE read twice instead of once                                                                             | 11.0 slot-s         | 6.3 slot-s         | 1.7x       |

The factor is the slower number divided by the faster. The rows with a second time give the elapsed time, which can move more than slot-time. A final `ORDER BY` has almost no parallelism, so the clock suffers more than the slot-seconds do.

### Limit before you rank

A `DENSE_RANK()` over a whole table sorts the whole table, and a `LIMIT 10` after it doesn't help: the plan for ours had 13 stages and moved 1.8 GB between them. Take the first rows in a subquery, then rank them. The two forms give the same answer when the window orders by what the subquery orders by:

**rank only the three oldest accounts**

```sql
WITH accounts AS (
  SELECT 7 AS id, DATE '2011-03-02' AS created UNION ALL
  SELECT 3, DATE '2008-07-31' UNION ALL
  SELECT 9, DATE '2014-01-15' UNION ALL
  SELECT 1, DATE '2008-07-31' UNION ALL
  SELECT 5, DATE '2012-10-09' UNION ALL
  SELECT 2, DATE '2009-04-22'
),
oldest AS (
  SELECT id, created
  FROM accounts
  ORDER BY created, id
  LIMIT 3
)
SELECT id, created, DENSE_RANK() OVER (ORDER BY created) AS age_rank
FROM oldest
ORDER BY age_rank, id
```

Result in BigQuery (3 rows):

| id | created | age_rank |
| --- | --- | --- |
| 1 | 2008-07-31 | 1 |
| 3 | 2008-07-31 | 1 |
| 2 | 2009-04-22 | 2 |

This is Google's own example, and we saw the effect it describes, 15 seconds against 2 on their table, at larger scale on ours. It does not work when the rank must reflect rows that the limit removes. See [window functions](https://docs.querylab.io/bigquery/window-functions/).

### Approximate counts

`APPROX_COUNT_DISTINCT` gave 3 to 5 times less slot-time than `COUNT(DISTINCT)` over about a million distinct values. The answer is approximate by design, but on a column with 997 distinct values it returned the exact number:

**exact and approximate agree on small cardinalities**

```sql
SELECT
  COUNT(DISTINCT MOD(i, 997)) AS exact_count,
  APPROX_COUNT_DISTINCT(MOD(i, 997)) AS approx_count
FROM UNNEST(GENERATE_ARRAY(1, 5000)) AS i
```

Result in BigQuery (1 row):

| exact_count | approx_count |
| --- | --- |
| 997 | 997 |

When the error is acceptable, as in a dashboard that shows "about 1.2M users", use it. See [approximate aggregation](https://docs.querylab.io/bigquery/approximate-aggregation/). Querylab.io hints at `COUNT(DISTINCT x)` and has a quick fix that replaces it with `APPROX_COUNT_DISTINCT` ([code quality](https://docs.querylab.io/writing-sql/code-quality/#quick-fixes)). `APPROX_QUANTILES` replaces `NTILE` the same way. Google notes the buckets are close but not identical: the remainder rows land in different places.

### JavaScript UDFs

A JavaScript UDF starts a subprocess. For `SUM(f(x))` over 10 million rows, the JavaScript version took 15.5 slot-seconds and the SQL UDF and the inline expression both took 0.6. A SQL UDF cost nothing extra here, and Google says the optimizer can optimize SQL UDF definitions. Use JavaScript only for logic SQL can't express. See [user-defined functions](https://docs.querylab.io/bigquery/user-defined-functions/).

### LIKE and regular expressions

`LIKE` was twice as fast as `REGEXP_CONTAINS` for a substring test. The difference is real but small in absolute terms, and the two give the same answer for a plain substring:

**same matches, different cost**

```sql
WITH pages AS (
  SELECT '/shop/checkout' AS url UNION ALL
  SELECT '/shop/cart' UNION ALL
  SELECT '/checkout/done' UNION ALL
  SELECT '/blog/checkout-tips' UNION ALL
  SELECT '/Checkout'
)
SELECT
  url,
  url LIKE '%checkout%' AS like_match,
  REGEXP_CONTAINS(url, r'checkout') AS regexp_match
FROM pages
ORDER BY url
```

Result in BigQuery (5 rows):

| url | like_match | regexp_match |
| --- | --- | --- |
| /Checkout | false | false |
| /blog/checkout-tips | true | true |
| /checkout/done | true | true |
| /shop/cart | false | false |
| /shop/checkout | true | true |

Both are case-sensitive, so `/Checkout` matches neither. Use a regular expression when you need one. Google's example pattern `'.*test.*'` was in the same range as the plain one.

### Types in filters

Google says to use `BOOL`, `INT64`, `FLOAT64` or `DATE` columns in `WHERE` clauses rather than strings. It held: the same row count found with `k = 123456` took 0.48 slot-seconds and with `ks = '123456'` took 0.87, on a table where `ks` is the string form of `k`. All the bytes were read in both cases. The effect is roughly twice, which only matters on large scans. The same applies to join keys. See [optimizing joins](https://docs.querylab.io/bigquery/optimizing-joins/).

### Sorting

Google says to put `ORDER BY` in the outermost query or in a window, because one in the middle of a query greatly affects performance. If you only need the top rows, add `LIMIT`: the plan then has `SORT ... LIMIT 10` steps that keep only the top rows in each worker. A plain sort of 10 million rows was the slowest stage of its plan and raised the elapsed time from 3.4 to 11.4 seconds. Querylab.io warns about `ORDER BY` without `LIMIT` on a large table ([code quality](https://docs.querylab.io/writing-sql/code-quality/#performance)).

## What we could not confirm

**"Place the largest table first in a join."** Google's guide says to order tables from largest to smallest and that this triggers a broadcast join. We joined a 10-million-row table with a 100,000-row table in both orders as an inner join and as a left join, and once as a right join. Every version produced the same one-stage plan, with a median slot-time between 1.0 and 1.7 seconds. The optimizer chooses the sides. See [optimizing joins](https://docs.querylab.io/bigquery/optimizing-joins/).

**"Use temporary tables instead of a CTE that is referenced twice."** Google warns that a CTE used in several places may be evaluated each time. It was: reading the CTE twice cost 11.0 slot-seconds against 6.3 for reading it once. But writing it to a temporary table and reading that twice cost 16.5 slot-seconds and ran for 5.4 seconds against 1.0, because writing and reading the table cost more than the repeated aggregation did. A temp table pays off when the CTE is expensive and is read many times. It didn't for one aggregate read twice. See [temporary tables](https://docs.querylab.io/bigquery/temporary-tables/).

**"Filter before the join."** Writing the filter in `WHERE`, in the `ON` clause or in a subquery before the join gave the same plan and the same bytes. BigQuery moves the filter itself. What helps is a filter that shrinks one side, and it helps through the plan, not through where you wrote it.

## What does show up in the bytes

Primary and foreign key constraints are declared, not enforced, and the optimizer uses them. On a 10-million-row table joined to a 100,000-row one, a `LEFT JOIN` that selected only columns of the big table was dry-run at 160.8 MB when neither table had constraints, and 80 MB when the small table had `PRIMARY KEY (k) NOT ENFORCED` and the big one a matching `FOREIGN KEY`. BigQuery dropped the join. An `INNER JOIN` kept reading the foreign key column but stopped reading the small table. The cost of this is on you: if the data breaks the constraint, results can be wrong. See [primary and foreign keys](https://docs.querylab.io/bigquery/primary-and-foreign-keys/).

[Materialized views](https://docs.querylab.io/bigquery/materialized-views/) are the other big one: a rewritten aggregation read 120 KB where the table scan read 14.4 MB.

## How we measured

Each query ran with the cache off, on tables we created from `GENERATE_ARRAY`. We read `totalSlotMs` and the elapsed time from the job, and for the closest comparisons alternated the two versions so a slow minute hit both. The plan and `INFORMATION_SCHEMA.JOBS_BY_PROJECT` also show `optimization_details`, where Google records the history-based optimizations that change a query between runs. Your numbers will differ, in size and in sign for the borderline cases. The ones in the first table are large enough that they won't flip.

Check that LIKE and REGEXP_CONTAINS agree on the same rows. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Optimize query computation](https://cloud.google.com/bigquery/docs/best-practices-performance-compute)

- [Best practices for functions](https://cloud.google.com/bigquery/docs/best-practices-performance-functions)

- [Introduction to optimizing query performance](https://cloud.google.com/bigquery/docs/best-practices-performance-overview)