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

Question

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 ?

Answer 1

Hi @ahmed salah ,

Please provide tables and data related to the update statement so that we can perform some tests.
In addition, when publishing optimized query posts, please publish the execution plan and index information together.

Whether there is an index on the m. ZLC column, if not, you can try to build an index on the column first.
When in is inefficient, you can also replace in with exists, But the use of in or exists depends on the size of the inner and outer tables.

Regards
Echo

---

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.

Answer 2

The best of asking performance question is by providing the execution plan of your query. No one would be able to help without understanding your execution plan in better way. Can you paste execution plan for people to help you better.
Also provide the indexes on your tables.