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.
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;
