How to avoid writing (in ) when update fields temp table?

Transact-SQL
,
1

I work on SQL server 2012 . I face issue when using in clause on query it make query very slow
and take too much time
so i need to rewrite statement with best practice and best performance ?

  update t set t.status='You must not have Obslelence Code Part X AND C Both Active' from 
 #TempReplacementImporter t  
 inner join parts.lifecyclemaster m on m.ZpartID=t.PartIDC
  inner join parts.lifecyclemaster m2 on m2.ZpartID=t.PartIDX
 WHERE m.ZLC IN (37002,37003,37008,490621,490620) AND m2.ZLC in (37002,37003,37008,490621,490620) AND t.Obsolescence IS Not NULL AND t.status is null

so How to rewrite query above with good best practice ?

2

hi ahmed

hope you can read and understand English ..

https://www.mssqltips.com/sqlservertip/5829/update-statement-performance-in-sql-server/#:~:text=Make%20sure%20you%20have%20the,or%20indirectly%20impact%20query%20performance.

3

Without seeing the query plan, I can't be sure if changing the JOINs will help, but it might:

--first, cluster the #TempReplacementImporter table by ( status, Obsolescence )

update t set t.status='You must not have Obslelence Code Part X AND C Both Active' 
from #TempReplacementImporter t  
inner join parts.lifecyclemaster m on m.ZpartID=t.PartIDC AND m.ZLC IN (37002,37003,37008,490621,490620)
inner join parts.lifecyclemaster m2 on m2.ZpartID=t.PartIDX AND m2.ZLC in (37002,37003,37008,490621,490620) 
where t.Obsolescence IS NOT NULL AND t.status is null
4

why are you joining to parts.zpartid twice? m.zpartid = t.partidc and m2.zpartid = t.partidx. Can't you join once and add t.partidc = t.partidx? The DDL and sample data would help

UPDATE t 
SET    t.status = 'You must not have Obslelence Code Part X AND C Both Active' 
FROM   #tempreplacementimporter t 
       INNER JOIN parts.lifecyclemaster m 
               ON m.zpartid = t.partidc 
			  AND t.partidc  = t.partidx
              AND m.zlc IN ( 37002, 37003, 37008, 490621, 490620 ) 
WHERE  t.obsolescence IS NOT NULL 
  AND t.status IS NULL
5

That code is different, in that it requires the same row to match both conditions. The original code allows different rows to match each join, which I believe will be necessary here.

6

thank you for reply
I join with table two time because I have two Parts
Partidc as original PartIDX as alternative
then when I need to get status to two parts then
it must join to table two times
sample data

PartIdc     Partidx
505           6070

on lifecyclemaster that it have status

zpartid   zlcstatus
505       1233
6070      2333

then to get status of partidc and partid x from lifecyclemaster table then it must join
two times
if i wrong please tell me

7

are this correct as explain or not