snowflake MERGE statement on 9M+ rows table is too slow (takes >15 seconds), how to speed it up?

Viewed 384

I have 13 rows in a json.gz file. I am running this MERGE statement.

MERGE INTO order_lines
  USING (
    SELECT 
      $1:tenant_id as tenant_id, 
      $1:data:id as id 
      $1:data AS data, 
      $1:data_hash as data_hash, 
    FROM @s3_some_stage/dump/order_lines/2022-02-13_21-24-20_518.json.gz
  ) AS new_batch
  ON 
    order_lines.tenant_id = new_batch.tenant_id 
    AND order_lines.id = new_batch.id
  WHEN MATCHED AND order_lines.data_hash != new_batch.data_hash THEN 
    UPDATE SET 
      id = new_batch.id
      data = new_batch.data, 
      data_hash = new_batch.data_hash,
  WHEN NOT MATCHED THEN 
    INSERT (tenant_id, id, data, data_hash) 
    VALUES (new_batch.tenant_id, new_batch.id, new_batch.data, new_batch.data_hash);

It takes 15 seconds to run. When I initially ran, 3 rows updated and it took 15 seconds. When I ran it again, no rows changed but it still took 15 seconds on an S (small) warehouse. order_lines has 9.3M rows.

[{"number of rows inserted":0,"number of rows updated":0}]

SELECT 
      $1:tenant_id as tenant_id, 
      $1:data:id as id 
      $1:data AS data, 
      $1:data_hash as data_hash, 
    FROM @s3_some_stage/dump/order_lines/2022-02-13_21-24-20_518.json.gz

Takes 600ms to run and has 13 rows. Pretty small file.

Going to query profiler, it does show execution time at 15 seconds, but seeing the nodes, the most expensive node is 129ms. Snowflake spent 14s in processing, what does that mean?

The merge statement doesn't update any rows since the data_hash's are the same. So the MERGE statement is a no-op and I'd expect it to be very fast.

snowflake profile

If I do a join between the staged file and the actual table, the filter returns in 400ms (13 rows). So why is the MERGE so slow?

WITH tmp as (
    SELECT $1:tenant_id as tenant_id, $1:data:id::varchar AS id 
    from @s3_some_stage/dump/order_lines/2022-02-13_21-24-20_518.json.gz
)
select order_lines.id
from order_lines
right join tmp on 
    order_lines.tenant_id = tmp.tenant_id and order_lines.id = tmp.id;
1 Answers

MERGE is one of, if not the most, expensive operations in any database. Compounded in this case by reading directly from a file rather than the database.

Logically, every row must be examined and compared, although partition pruning eliminates some of this.

Also, suggest you try loading the file into Snowflake and then trying the merge; also larger warehouse.

Related