Snowflake SQL finding the rows diffs between 2 large tables - Stack Overflow

I have 2 tables, around 300M rows each

CREATE TABLE AllIDs
(
ID int NULL,
isUsed BOOLEAN NULL
)
CREATE TABLE UsedIDs
(
ID int NULL
)

I want to find all the IDs that are not used, that is, all the ID rows in AllIDs that do not appear in UsedIDs

I have tried the below and a few similar variations but the query runs indefinitely (16+ hours) even with a Medium warehouse.

    UPDATE AllIDs t 
SET t.IsUsed = 0  
from AllIDs  
where not exists (select 1 FROM UsedIDs f 
WHERE t.ID = f.ID )

Any ideas how this can be done efficiently?

I have 2 tables, around 300M rows each

CREATE TABLE AllIDs
(
ID int NULL,
isUsed BOOLEAN NULL
)
CREATE TABLE UsedIDs
(
ID int NULL
)

I want to find all the IDs that are not used, that is, all the ID rows in AllIDs that do not appear in UsedIDs

I have tried the below and a few similar variations but the query runs indefinitely (16+ hours) even with a Medium warehouse.

    UPDATE AllIDs t 
SET t.IsUsed = 0  
from AllIDs  
where not exists (select 1 FROM UsedIDs f 
WHERE t.ID = f.ID )

Any ideas how this can be done efficiently?

• sql
• snowflake-cloud-data-platform

Share Improve this question Follow asked Jan 29 at 15:50 JamestonJameston 311 silver badge22 bronze badges 3

• No luck with Join either, running for hours still when the commands to populate these tables took < 10 mins – Jameston Commented Jan 29 at 17:58
• update t set t.isUsed=0 from AllIds as T join (SELECT A.id from AllIds as A except Select U.id from UsedIds as U). May be this one? Or, at least you can try if SELECT A.id from AllIds as A EXCEPT Select U.Id FROM UsedIds As U is more perfromant – Sergey Commented Jan 29 at 19:10
• show us the execution plan – eshirvana Commented Jan 29 at 21:33

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I believe the from clause in the update statement is unnecessary and results in a a more complex query plan, including an unnecessary Cartesian join.

The following may be more performant:

UPDATE allids t
SET t.isused = 0
WHERE NOT EXISTS (
    SELECT 1
    FROM usedids f
    WHERE t.id = f.id
)