CODEWITHSUNDEEP
sqlperformancegoogle-bigquery
Jethin Abraham
Jethin Abraham

Reputation: 21

Bigquery SQL Performance issue on tables with cluster and partition

I have a simple join between 2 tables with a where clause. Both the tables are partitioned on inserted_date and used cluster on numeric_id column. Even with all of the performance tuning suggested , the query takes a good 30 sec and with different cluster columns the best I can get it is to run for 22 sec. Not sure what I can do to improve the performance.

Note : the table_1 is long with around 100 columns

Table_1 Table Size - 121.18 MB Rows - 279,567

Table_2 Table Size - 148 MB Rows - 864,177

    select q.*,a.product_id
    from table_1 qav
    inner join table_2 a on a.id = qav.application_id
    where a.product_id in (1,5,7,9)

Upvotes: 1

Views: 201

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10222

Try moving a.product_id in (1,5,7,9) from where to on and start with the largest table:

select q.*,a.product_id 
from table_2 a inner join table_1 qav
on a.id = qav.application_id 
   and a.product_id in (1,5,7,9)

Upvotes: 0

Related Questions