when Update data it take too long time reach to 2 hours to get result why and How to solve issue ?

Question

I work on SQL server 2012 I Face issue Update statement below take 2 hours to run

and after finish after 2 hours No result Returned .

I Guess the issue he search for NULL per every row on joins

statement update

UPDATE T
SET 
OldValueID=pck1.Value,
NewValueID=pck2.Value,
old_Value=a.Name,New_Value=av.Name,
t.DisplayonPortal= isnull('[' +  t.NewReplacementType +  ']','') + ' ' + isnull(t.ReplacementFeature,'') + ' '  +'('  + CASE WHEN t.ReplacementFeature='Temperature' and (CHARINDEX('(TA)',a.Name)-1 > 0)  THEN LEFT(a.Name,CHARINDEX('(TA)',a.Name) - 1) ELSE a.Name END + ' ' +'to' + ' ' + av.Name  + ')'
FROM #TempReplacementImporter T
INNER JOIN Z2DataCore.Parts.Nop_Part RH1 WITH(NOLOCK) on RH1.PartId=T.PartIDC  
INNER JOIN Z2DataCore.Parts.Nop_FamilyParametric fmc WITH(NOLOCK) ON fmc.PartFamilyID=rh1.PartsFamilyID
INNER JOIN Z2DataCore.Parts.Nop_FamilyParametricAttribute pck1 WITH(NOLOCK) ON pck1.FamilyParametricID = fmc.FamilyParametricID AND pck1.[Key]=T.[DiffFeaturesID]
inner JOIN [dbo].[Nop_AcceptedValuesOption] a WITH(NOLOCK) ON TRY_CAST(pck1.Value AS INT) =a.AcceptedValuesOptionID
INNER JOIN Z2DataCore.Parts.Nop_Part RH2 WITH(NOLOCK) on RH2.PartId=T.PartIDX
INNER JOIN Z2DataCore.Parts.Nop_FamilyParametric fmc2 WITH(NOLOCK) ON fmc2.PartFamilyID=rh2.PartsFamilyID
INNER JOIN Z2DataCore.Parts.Nop_FamilyParametricAttribute pck2 WITH(NOLOCK) ON pck2.FamilyParametricID = fmc2.FamilyParametricID AND pck2.[Key]=T.[DiffFeaturesID]
inner JOIN [dbo].[Nop_AcceptedValuesOption] av WITH(NOLOCK) ON TRY_CAST(pck2.Value AS INT) =av.AcceptedValuesOptionID

this is execution plan
https://www.brentozar.com/pastetheplan/?id=ByJZLztYD

Answer 1

Hi @ahmed salah ,

I checked your query and found that you joined 4 tables twice with the same conditions.

Then your value of OldValueID and NewValueID would be the same which means there is no change after executing this update statement.

Could you pelase double check and confirm whether it is necessary to have 4 tables join twice with same condition?

Besides, how many rows of all 4 tables? Do they have an order?

Only according to your execution plan provided, you could refer below suggestions and check whether any of them is helpful to you:

1. Update statistics.
2. Create clustered index or nonclustered index on the #TempReplacementImporter table if possible.
3. Decrease the number of tables to join or split it into small parts using CTE or temple tables if possible.
4. Try with Hash Join, Merge Join and Nested Loop Join and choose the fastest one.
5. Use function (isnull,left,charindex) with caution in your statement since they may cause the poor performance.

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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

I need old value and New value from same tables so i must join with same table twice

Answer 3

Hi @ahmed salah ,

As mentioned by Guoxiong, it could be better for you to provide the result of below select statement ranther than update statement.

SELECT  
pck1.Value OldValueID,  
pck2.Value NewValueID,  
a.Name old_Value,  
av.Name New_Value,  
isnull('[' +  t.NewReplacementType +  ']','') + ' ' + isnull(t.ReplacementFeature,'') + ' '    +'('  + CASE WHEN t.ReplacementFeature='Temperature' and (CHARINDEX('(TA)',a.Name)-1 > 0)  THEN LEFT(a.Name,CHARINDEX('(TA)',a.Name) - 1) ELSE a.Name END + ' ' +'to' + ' ' + av.Name  + ')' DisplayonPortal  
 FROM #TempReplacementImporter T  
 INNER JOIN Z2DataCore.Parts.Nop_Part RH1 WITH(NOLOCK) on RH1.PartId=T.PartIDC    
 INNER JOIN Z2DataCore.Parts.Nop_FamilyParametric fmc WITH(NOLOCK) ON fmc.PartFamilyID=rh1.PartsFamilyID  
 INNER JOIN Z2DataCore.Parts.Nop_FamilyParametricAttribute pck1 WITH(NOLOCK) ON pck1.FamilyParametricID = fmc.FamilyParametricID AND pck1.[Key]=T.[DiffFeaturesID]  
 inner JOIN [dbo].[Nop_AcceptedValuesOption] a WITH(NOLOCK) ON TRY_CAST(pck1.Value AS INT) =a.AcceptedValuesOptionID  
 INNER JOIN Z2DataCore.Parts.Nop_Part RH2 WITH(NOLOCK) on RH2.PartId=T.PartIDX  
 INNER JOIN Z2DataCore.Parts.Nop_FamilyParametric fmc2 WITH(NOLOCK) ON fmc2.PartFamilyID=rh2.PartsFamilyID  
 INNER JOIN Z2DataCore.Parts.Nop_FamilyParametricAttribute pck2 WITH(NOLOCK) ON pck2.FamilyParametricID = fmc2.FamilyParametricID AND pck2.[Key]=T.[DiffFeaturesID]  
 inner JOIN [dbo].[Nop_AcceptedValuesOption] av WITH(NOLOCK) ON TRY_CAST(pck2.Value AS INT) =av.AcceptedValuesOptionID  

You mentioned that you needed old value and New value from same tables so you must join with same table twice. But you used the same column from the table even though you joined same 4 tables twice. So pck1.Value would be the same as pck2.Value.

You could refer below simple example:

drop table if exists #temp  
drop table if exists temp1  
drop table if exists temp2  
  
create table #temp  
(id int,  
OldValueID int,  
NewValueID int,  
old_Value varchar(100),  
new_Value varchar(100),  
)  
  
insert into #temp  values  
(1,NULL,NULL,NULL,NULL),  
(2,NULL,NULL,NULL,NULL)  
  
create table temp1   
(value int  
)  
  
insert into temp1 values   
(1),  
(2)  
  
create table temp2   
(  
ID int,  
Name varchar(100)  
)  
  
insert into temp2 values   
(1,'A'),  
(2,'B')  
  
select b.value OldValueID,d.value NewValueID,c.Name old_Value,e.Name new_Value   
from #temp a   
inner join temp1 b on a.id=b.value  
inner join temp2 c on c.ID=b.value  
inner join temp1 d on a.id=d.value  
inner join temp2 e on e.ID=d.value  

Output:

OldValueID	NewValueID	old_Value	new_Value  
1	1	A	A  
2	2	B	B  

Above OldValueID and NewValueID are the same and old_Value and new_Value are the same too.

You could try with different columns or same column with different conditions to catch the old and new values.

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.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 4

ok i will prepare sample for that