question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft answered

why update statment take too much time ?

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can any one help me
i attached tables structure and sample data

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

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.


scan.png (71.1 KiB)
scan2.png (30.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I make indexes and issue still exist

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

it voids index seeks as

Index seeks are good, index scans are bad.





0 Votes 0 ·

i face issue here i depend on text URL when join
on below

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

can i use id instead of using text when make join

0 Votes 0 ·

I updated original post with sample data

0 Votes 0 ·
Show more comments