Need help on improving merge statement

Question

Hello TSQL Gurus,

Need help with below merge query,

CREATE TABLE dbo.Table1(
[Col1] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Col2] varchar NULL,
[Col3] [datetime] NULL,
[Col4] [datetime] NULL,
[Col5] [datetime] NULL,
[Createddate] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD DEFAULT (getdate()) FOR [Createddate]
GO

CREATE TABLE dbo.Table2(
[Col1] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Col2] varchar NULL,
[Col3] [datetime] NULL,
[Col4] [datetime] NULL,
[Col5] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NULL) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

declare @apl varchar(100) = '2022-07-01'

MERGE dbo.Table2 AS T
USING dbo.Table1 AS S
ON (T.Col2 = S.Col2 and T.Col3 = S.Col3)
WHEN MATCHED AND (ISNULL(S.Col4, 0) <> ISNULL(T.Col4, 0)
OR ISNULL(S.Col5, 0) <> ISNULL(T.Col5, 0))
THEN UPDATE set T.Col4 = S.Col4,T.Col5 = S.Col5 , T.ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET
THEN
INSERT (Col2, Col3, Col4 ,Col5, CreatedDate)
VALUES (S.Col2, S.Col3 , S.Col4 ,S.Col5, GetDate())
WHEN NOT MATCHED BY SOURCE AND T.Col3 = @apl THEN DELETE;

On database server, when i execute this query, it takes close to 9 seconds execution time, 37 seconds CPU and 2711326 Reads (20 GB).

I need your advise/help in optimizing this merge statement if possible. Changing this to individual update, Insert or improving the existing merge statement.

Looking forward for youe help. Thanks in advance.

Thanks;
Yesu

Answer 1

Hi @Yesuraj
To improve the performance of the MERGE statement, try the following index guidelines:

1. Create an index on the join columns in the source table that is unique and covering.
2. Create a unique clustered index on the join columns in the target table.

Refer to this document for more details.

Best regards,
LiHong