why update statment take too much time ?

ahmed salah 3,216 Reputation points
2021-10-12T10:45:49.837+00:00

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 

CREATE TABLE [dbo].[TempROHS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ZURLSource1] [nvarchar](3000) NULL,
[OnlineSource1] [nvarchar](3000) NULL,
[Revision_ID] [bigint] NULL,
[status] [nvarchar](300) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [zsrc].[Local_URL]    Script Date: 10/13/2021 8:59:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Local_URL](
[rec_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[local_url] [nvarchar](2048) NOT NULL,

) ON [PRIMARY]

GO
/****** Object:  Table [zsrc].[Online_URL]    Script Date: 10/13/2021 8:59:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Online_URL](
[Rec_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[URL] [nvarchar](500) NOT NULL,

 CONSTRAINT [PK_Rec_ID_OnlineURL] 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]

GO
/****** Object:  Table [zsrc].[Revision]    Script Date: 10/13/2021 8:59:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Revision](
[rec_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION 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]

GO
SET IDENTITY_INSERT [dbo].[TempROHS] ON 

INSERT [dbo].[TempROHS] ([ID], [ZURLSource1], [OnlineSource1], [Revision_ID], [status]) VALUES (1, N'https://source.z2data.com/2017/3/30/11/41/56/874/706167429/Compliance.pdf', N'http://2t70un3m1d9z1kztamkdrd38.wpengine.netdna-cdn.com/wp-content/uploads/2016/03/5-wireTouch.pdf', NULL, NULL)
INSERT [dbo].[TempROHS] ([ID], [ZURLSource1], [OnlineSource1], [Revision_ID], [status]) VALUES (2, N'https://source.z2data.com/2017/11/19/11/26/36/82/1315668739/rohsCoC.pdf',N'http://2t70un3m1d9z1kztamkdrd38.wpengine.netdna-cdn.com/wp-content/uploads/2016/02/accessories-hardware-1.pdf', NULL, NULL)
INSERT [dbo].[TempROHS] ([ID], [ZURLSource1], [OnlineSource1], [Revision_ID], [status]) VALUES (3, N'https://source.z2data.com/2018/10/24/1/8/18/621/542663177/Statement_of_Compliance_PN_YACT20MJ43JNV00100_181024_160527.pdf',N'http://ac-dc.power.com/design-support/product-documents/data-sheets/linkzero-lp-datasheet', NULL, NULL)
INSERT [dbo].[TempROHS] ([ID], [ZURLSource1], [OnlineSource1], [Revision_ID], [status]) VALUES (4, N'https://source.z2data.com/2017/6/30/8/50/44/270/1593045455/RoHS.ECC36DTAH.pdf', N'http://ark.intel.com/products/78945/Intel-Pentium-Processor-3558U-2M-Cache-1_70-GHz', NULL, NULL)
INSERT [dbo].[TempROHS] ([ID], [ZURLSource1], [OnlineSource1], [Revision_ID], [status]) VALUES (5, N'https://source.z2data.com/2017/8/20/10/11/49/132/1114175001/t1116p.pdf', N'http://assmann.us/specs/A-DS25-HOOD-WP.pdf', NULL, NULL)
SET IDENTITY_INSERT [dbo].[TempROHS] OFF
SET IDENTITY_INSERT [dbo].[Local_URL] ON 

INSERT [dbo].[Local_URL] ([rec_id], [local_url]) VALUES (2, N'https://source.z2data.com/2017/3/30/11/41/56/874/706167429/Compliance.pdf')
INSERT [dbo].[Local_URL] ([rec_id], [local_url]) VALUES (3, N'https://source.z2data.com/2017/11/19/11/26/36/82/1315668739/rohsCoC.pdf')
INSERT [dbo].[Local_URL] ([rec_id], [local_url]) VALUES (4, N'https://source.z2data.com/2018/10/24/1/8/18/621/542663177/Statement_of_Compliance_PN_YACT20MJ43JNV00100_181024_160527.pdf')
INSERT [dbo].[Local_URL] ([rec_id], [local_url]) VALUES (5, N'https://source.z2data.com/2017/6/30/8/50/44/270/1593045455/RoHS.ECC36DTAH.pdf')
INSERT [dbo].[Local_URL] ([rec_id], [local_url]) VALUES (6, N'https://source.z2data.com/2017/8/20/10/11/49/132/1114175001/t1116p.pdf')
SET IDENTITY_INSERT [dbo].[Local_URL] OFF
SET IDENTITY_INSERT [dbo].[Online_URL] ON 

INSERT [dbo].[Online_URL] ([Rec_ID], [URL]) VALUES (2, N'http://2t70un3m1d9z1kztamkdrd38.wpengine.netdna-cdn.com/wp-content/uploads/2016/03/5-wireTouch.pdf')
INSERT [dbo].[Online_URL] ([Rec_ID], [URL]) VALUES (3, N'http://2t70un3m1d9z1kztamkdrd38.wpengine.netdna-cdn.com/wp-content/uploads/2016/02/accessories-hardware-1.pdf')
INSERT [dbo].[Online_URL] ([Rec_ID], [URL]) VALUES (4, N'http://ac-dc.power.com/design-support/product-documents/data-sheets/linkzero-lp-datasheet')
INSERT [dbo].[Online_URL] ([Rec_ID], [URL]) VALUES (5, N'http://ark.intel.com/products/78945/Intel-Pentium-Processor-3558U-2M-Cache-1_70-GHz')
INSERT [dbo].[Online_URL] ([Rec_ID], [URL]) VALUES (6, N'http://assmann.us/specs/A-DS25-HOOD-WP.pdf')
SET IDENTITY_INSERT [dbo].[Online_URL] OFF
SET IDENTITY_INSERT [dbo].[Revision] ON 

INSERT [dbo].[Revision] ([rec_id], [online_id], [local_id]) VALUES (1, 2, 2)
INSERT [dbo].[Revision] ([rec_id], [online_id], [local_id]) VALUES (2, 3, 3)
INSERT [dbo].[Revision] ([rec_id], [online_id], [local_id]) VALUES (3, 4, 4)
INSERT [dbo].[Revision] ([rec_id], [online_id], [local_id]) VALUES (4, 5, 5)
INSERT [dbo].[Revision] ([rec_id], [online_id], [local_id]) VALUES (5, 6, 6)
SET IDENTITY_INSERT [dbo].[Revision] OFF
/****** Object:  Index [Key2]    Script Date: 10/13/2021 8:59:24 AM ******/
ALTER TABLE [dbo].[Local_URL] ADD  CONSTRAINT [Key2] PRIMARY KEY NONCLUSTERED 
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

update t set t.Revision_ID = r.rec_id 
 FROM dbo.TempROHS t
 INNER join dbo.Local_URL l (NOLOCK) ON t.ZURLSource1  = l.local_url
 INNER join dbo.Online_URL o (NOLOCK) ON t.OnlineSource1 = o.url 
 INNER join dbo.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id
 WHERE  t.[Status] IS NULL

I updated original post with sample data

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,820 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-10-14T01:45:19.78+00:00

    Hi @ahmed salah ,

    According to your updated sample data, I analyzed your data of all tables and found that you could only update the data by TempROHS table itself like below: 

      update dbo.TempROHS set Revision_ID = id   
  WHERE  [Status] IS NULL

    OR update by joining another table Revision like below: 

      update t set t.Revision_ID = r.rec_id   
  FROM dbo.TempROHS t  
  INNER join dbo.Revision r (NOLOCK) ON t.ID=r.rec_id  
  WHERE  t.[Status] IS NULL

    If above is not working, please provide more sample data, enough to illustrate all angles of the problem.

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-10-13T01:37:42.663+00:00

    Hi @ahmed salah ,

    I checked your execution plan and found that there were two arrows are a bit thick.

    One is below:

    139958-scan.png

    You could try to change predicate to index like below: 

    CREATE NONCLUSTERED INDEX IX_status  
    ON extractreports.dbo.TempROHS (status);

    Two is below:
    140033-scan2.png
    You could create index in the same way like below: 

    CREATE NONCLUSTERED INDEX IX_local_url  
        ON [zsrc].[Local_URL] ([local_url]);

    If above actions still could not be helpful, please provide some sample data with other three tables and new execution plan.

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Olaf Helper 41,006 Reputation points
    2021-10-13T06:11:26.007+00:00

    ON isnull(t.ZURLSource1,'N/A') = l.local_url

    Better don't use functions on WHERE clause or JOIN conditions, this prevents the usage of an existing index.

    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2021-10-13T06:51:00.673+00:00

    Hi @ahmed salah ,

    You could make improvements to your data to allow you to remove the ISNULL(A, B) = something. Using ISNULL in join condition voids index seeks as well as prevents the optimizer from getting good estimates about rowcounts and thus can lead to horribly inefficient query plans.

    Besides, if ZURLSource1 is null and set it to ‘N/A’,and then join it with local_url whose value is 'N/A', it would create much meaningless joins. 

     INNER join zsrc.Local_URL l (NOLOCK) ON t.ZURLSource1 = l.local_url  
 INNER join Zsrc.Online_URL o (NOLOCK) ON t.OnlineSource1= o.url

    In addition, if the isnull function is necessary according to your requirement, you could consider to create computed column and join this column instead. 

     ALTER TABLE zsrc.Local_URL ADD ZURLSource1_not_null AS (ISNULL(ZURLSource1,'N/A')) ;  
 ALTER TABLE Zsrc.Online_URL ADD OnlineSource1_not_null AS (ISNULL(OnlineSource1,'N/A')) ;  
 
 CREATE NONCLUSTERED INDEX IX_ZURLSource1 ON extractreports.dbo.TempROHS (ZURLSource1_not_null);   
 CREATE NONCLUSTERED INDEX OnlineSource1 ON extractreports.dbo.TempROHS (OnlineSource1_not_null);   
 
update ...  
set...  
from ...  
INNER join zsrc.Local_URL l (NOLOCK) ON t.ZURLSource1_not_null  = l.local_url  
INNER join Zsrc.Online_URL o (NOLOCK) ON t.OnlineSource1_not_null = o.url   
...

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.