Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I working on SQL server 2012 I face issue update statement take too much time
and it is very slow

my query is
SQL
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://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N1.pdf    N/A    NULL    NULL
 14255    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N2.pdf    N/A    NULL    NULL
 14256    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N3.pdf    N/A    NULL    NULL
 14257    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N4.pdf    N/A    NULL    NULL
 14258    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N5.pdf    N/A    NULL    NULL
 14259    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N6.pdf    N/A    NULL    NULL
 14260    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NA.pdf    N/A    NULL    NULL
 14261    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NB.pdf    N/A    NULL    NULL
 14262    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NC.pdf    N/A    NULL    NULL
 14263    https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405ND.pdf    N/A    NULL    NULL


What I have tried:

SQL
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)
 )
Posted
Updated 12-Oct-21 22:32pm

Speed depends on many things and we cannot tell you exactly what the issue is. Even looking at your performance plan you can see where the slowness is but without knowing all the schema of your db we still cannot tell you.

However, some things to note:

1. You are joining on field that appears to be a url which will be a very inefficient join. You should join on ids or indexed fields when possible.
2. You are joining to two tables on url fields
3. Put an index on t.Status
4. If you have lots of rows, this will be slow because of your joins.
 
Share this answer
 
Comments
ahmed_sa 12-Oct-21 16:03pm    
so how to make update more faster
i already put on status index but nothing changed
it take 6 hour for 68 k
so if you have any suggestion to do it faster
i attach execution plan above
Member 15329613 12-Oct-21 16:08pm    
Your joins to urls are killing you.
ahmed_sa 12-Oct-21 16:15pm    
so what i do
Member 15329613 12-Oct-21 16:19pm    
I can't tell you. I have no idea what the data is or what you are doing so I can tell you general rules but you have to figure out how to apply them to your specific issue.

You can try indexing your fields that you are joining on. Can you add ids to your tables and join on them instead?

Are you updating 68k rows or does the table have 68k rows?
ahmed_sa 13-Oct-21 1:53am    
so how i do below
You can try indexing your fields that you are joining on. Can you add ids to your tables and join on them instead?
Start by getting rid of the table scan:
SQL
CREATE NONCLUSTERED INDEX IX_TempROHS_Status
ON dbo.TempROHS (Status)
INCLUDE (ZURLSource1, OnlineSource1);
Create indexes with included columns - SQL Server | Microsoft Docs[^]

Get rid of the RID Lookup on the Local_URL table:
SQL
CREATE CLUSTERED INDEX CI_LocalURL
ON [zsrc].[Local_URL] ([local_url]);

Add a covering index on the Revision table:
SQL
CREATE NONCLUSTERED INDEX IX_Revision_LocalID_OnlineID
ON [zsrc].[Revision] ([local_id], [online_id]);

Then try again and see what difference that makes.
 
Share this answer
 
Comments
ahmed_sa 13-Oct-21 13:06pm    
thank you for reply
can you write these statement below by joining using id
without join by url text
are there are any way to do that

update t set t.Revision_ID = r.rec_id
FROM extractreports.dbo.TempROHS t
INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON t.ZURLSource1 = l.local_url
INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON t.OnlineSource1 = 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
Richard Deeming 14-Oct-21 3:37am    
If you don't have the ID in the table, then you can't join on the ID.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900