BigQuery query extremely slow when adding JOIN

This is my first post in here, so please let me know if I've done anything wrong when posting my question.

I started learning SQL from scratch about three weeks ago, and so I'm fairly new to the whole concept and community and therefore I've probably made a lot of mistakes in my code, but here goes.

I'm struggling with a query, that I'm writing in BigQuery. BigQuery's "validator" has validated the code, so on 'paper' it's seems good, but it takes forever to run. It runs to a point where I stop it, because it has passed an hour. I've been looking in to streamlining my sql-coding so that the proces could run smoother and therefore run faster, but I've hit a wall, where I think I'm out of questions, that could provide me with a useful answer.

So I really hope that somebody here can help me out. Any advice is appreciated.

My query is the following:

WITH memberships AS (
        SELECT im.chargebee_plan_id, im.membership_id
        FROM postgres.internal_reporting_memberships AS im 
        WHERE (im.chargebee_plan_id LIKE 'yearly' OR im.chargebee_plan_id LIKE 'monthly') 
          AND  im.started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 365 day)
                    ),

     readers AS     (       
        SELECT  ip.reader_id, DATE_TRUNC(CAST(ip.read_started_at AS DATE), DAY) read_start
        FROM postgres.internal_reporting_read_progresses AS ip 
        WHERE ip.reader_id LIKE '%|%' AND ip.read_started_at >= (TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 365 day)
                    ))

SELECT  reader_id, read_start, m.chargebee_plan_id
FROM    readers AS r
  JOIN memberships AS m
  ON r.reader_id LIKE m.membership_id

cheers



Read more here: https://stackoverflow.com/questions/64894993/bigquery-query-extremely-slow-when-adding-join

Content Attribution

This content was originally published by r.c at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: