I have two tables(src, tgt) on a dedicated sql pool in Azure Synapse, which both contain approximately 200 million records. Let's say they have columns A and B. I'm trying to update the field tgt.A with the value from src.A when tgt.B matches src.B. If src.B does not exist in tgt.B, insert the new record.
I tried using the MERGE statement as below:
MERGE tgt
USING src
ON tgt.B = src.B
WHEN MATCHED AND tgt.A <> src.A
THEN UPDATE SET tgt.A = src.A
WHEN NOT MATCHED
THEN INSERT (
A,B
)
Values
(
src.A, src.B
);
I also tried the insert and update statements separately as below:
INSERT INTO tgt
SELECT A, B
FROM src
WHERE NOT EXISTS(SELECT B FROM tgt WHERE tgt.B = src.B)
UPDATE tgt
SET tgt.A = src.A
WHERE EXISTS(SELECT B FROM tgt WHERE tgt.B = src.B)
For testing purpose, I took a smaller subset of tgt table containing 6 million records and source containing only 400k records. Still, with either of the above approaches, the stored procedure keeps executing beyond 1 hour where I cancel the execution. I noticed that with even smaller samples with 1k records in each table, it executes within a minute but struggles with large number of records. Please recommend the best approach to tackle the issue, can this be optimized?
Note: The source table is populated and dropped within the stored procedure and only the target table is saved with the updated records.