I working on SQL server 2012 I face issue update statement take too much time
and it is very slow
my query is
update t set t.Revision_ID = r.rec_id
FROM extractreports.dbo.TempROHS t
INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON isnull(t.ZURLSource1,'N/A') = l.local_url
INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON ISNULL(t.OnlineSource1,'N/A') = o.url
INNER join Z2urlSystem.zsrc.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id
WHERE t.[Status] IS NULL
and execution plan as below :
https://www.brentozar.com/pastetheplan/?id=rJfvx1mSK
sample data
sample data extractreports.dbo.TempROHS
ID ZURLSource1 OnlineSource1 Revision_ID STATUS
14254 https:
14255 https:
14256 https:
14257 https:
14258 https:
14259 https:
14260 https:
14261 https:
14262 https:
14263 https:
What I have tried:
Table [zsrc].[Local_URL]
CREATE TABLE [zsrc].[Local_URL](
[rec_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[local_url] [nvarchar](2048) NOT NULL,
CONSTRAINT [Key_Rec_ID] PRIMARY KEY NONCLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table [zsrc].[Online_URL]
CREATE TABLE [zsrc].[Online_URL](
[Rec_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[URL] [nvarchar](500) NOT NULL,
CONSTRAINT [PK_Rec_ID_oL] PRIMARY KEY CLUSTERED
(
[Rec_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table Revision
CREATE TABLE [zsrc].[Revision](
[rec_id] [bigint] NOT NULL,
[online_id] [bigint] NOT NULL,
[local_id] [bigint] NOT NULL,
CONSTRAINT [PK_Rec_ID] PRIMARY KEY CLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table extractreports.dbo.TempROHS
CREATE TABLE extractreports.dbo.TempROHS
(
ID INT IDENTITY ,
ZURLSource1 NVARCHAR(3000) ,
OnlineSource1 NVARCHAR(3000) ,
Revision_ID BIGINT ,
[status] NVARCHAR(300)
)