Snapshot | MV with join optimization

typeform_campaigns / Snapshot | MV with join optimization

Improving the performance of a JOIN thanks to subqueries. This was the query in a pipe that created a MV, so every time there was an ingestion in the submission finished datasource, the query was executed. Reducing the query time and processed bytes meant a lot in terms of performance.
Note: the shown Typeform's form may not be active anymore

Last updated 1 years ago

submission_finish_1

Ingestion is just 1 event. We use this node to simulate the block that will be processed in the Materialized View

SELECT * FROM submission_finish LIMIT 1

148.00B processed, 1 rows x 8 columns. This query took 6.91ms

· View chart

id String	occurred_on Int64	payload_form_id String	payload_landed_at Int64	payload_response_id String	payload_submitted_at Int64	type String	version Int16
01FYXZMB0FJ0G8ZW59PHR5DBVQ	1648126209	D9astEvg	1648126203	06vs49knso8afel112c0y06vs49ktnxm	1648126208	submission.completed	0

heavy_join

Here we do the JOIN wih the whole submission_started datasource. It processes ~220MB and takes ~5s

SELECT
    sf.payload_form_id AS form_id,
    ss.payload_visit_response_id AS response_id,
    toDate(sf.occurred_on) AS day,
    sf.payload_submitted_at AS finished_at
FROM submission_finish_1 AS sf
INNER JOIN submission_started AS ss ON ss.payload_response_id = sf.payload_response_id

219.73MB processed, 3769637 rows x 4 columns. This query took 3493.95ms

· View chart

form_id String	response_id String	day Date	finished_at Int64
D9astEvg		2022-03-24	1648126208

light_join

Using subqueries to retrieve only what we really need, we reduce processing to ~1MB and time to just some ms

SELECT
    sf.payload_form_id AS form_id,
    ss.payload_visit_response_id AS response_id,
    toDate(sf.occurred_on) AS day,
    sf.payload_submitted_at AS finished_at
FROM submission_finish_1 AS sf
INNER JOIN (
    SELECT payload_response_id, payload_visit_response_id
    FROM submission_started
    WHERE payload_response_id IN (SELECT payload_response_id FROM submission_finish_1)
) AS ss ON ss.payload_response_id = sf.payload_response_id

984.81KB processed, 16386 rows x 4 columns. This query took 4.5ms

· View chart

form_id String	response_id String	day Date	finished_at Int64
D9astEvg		2022-03-24	1648126208