How to avoid using ( OR && IS NULL ) on where statment when Delete Data?

Question

I work on SQL server 2012 I face issue when using ( OR && IS NULL ) on where statement to Delete data it will be very slow
so How to replace ( OR AND IS NULL ) by any thing can do same thing ?

DELETE  f
        FROM    Parts.ChemicalProfiles f
                INNER JOIN #TempPC t 
                ON t.ChemicalID = f.ChemicalID
        WHERE  t.[status]='Not Found in Chemical Master' OR t.[status] IS NULL 

I need to replace line below on where

WHERE t.[status]='Not Found in Chemical Master' OR t.[status] IS NULL

by any thing can do same job of (OR t.[status] IS NULL) because it very slow running
so I need to rewrite statement below to enhance performance and very quickly

WHERE t.[status]='Not Found in Chemical Master' OR t.[status] IS NULL

Answer 1

Hi @ahmed salah ,

In may not be used here. I tested the method to solve the performance problems caused by or. Union all is suitable for NULL:

select * from a  

select * from a  
where name ='Jane' or name is null  
  
select * from a  
where name in('Jane',null)  
  
select * from a  
where case when name ='Jane' then name else NULL end=name  
  
select * from a where name='Jane'  
union all  
select * from a where name is null  

For the delete statement, it can be divided into two queries,please check if it works：

 DELETE  f  
         FROM    Parts.ChemicalProfiles f  
                 INNER JOIN #TempPC t   
                 ON t.ChemicalID = f.ChemicalID  
         WHERE  t.[status]='Not Found in Chemical Master'   
  
 DELETE  f  
         FROM    Parts.ChemicalProfiles f  
                 INNER JOIN #TempPC t   
                 ON t.ChemicalID = f.ChemicalID  
         WHERE   t.[status] IS NULL   

But if there is only the empty string'NULL' in your data, there is no NULL, then you can refer to:

  DELETE  f  
          FROM    Parts.ChemicalProfiles f  
                  INNER JOIN #TempPC t   
                  ON t.ChemicalID = f.ChemicalID  
          WHERE  case when t.[status]='Not Found in Chemical Master' then t.[status] else 'NULL' end=t.[status]  

Or:

  DELETE  f  
          FROM    Parts.ChemicalProfiles f  
                  INNER JOIN #TempPC t   
                  ON t.ChemicalID = f.ChemicalID  
          WHERE  t.[status] in('Not Found in Chemical Master','NULL')  

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

Why do you believe this is slow? What does the query plan show? Do you have an index on status?

This might be faster this way:

     DELETE  f
             FROM    #TempPC t 
                     INNER JOIN Parts.ChemicalProfiles f 
                     ON t.ChemicalID = f.ChemicalID
             WHERE  t.[status]='Not Found in Chemical Master' OR t.[status] IS NULL 

Answer 3

Maybe try a two-step approach too:

DELETE … WHERE t.[status] IS NULL
DELETE … WHERE t.[status] = 'Not Found in Chemical Master'

Perhaps it will work faster if [status] is redesigned to be a numeric code.

Also consider something like this:

delete from Parts.ChemicalProfiles where ChemicalId in (select ChemicalId from #TempPC where [status] is null)
delete from Parts.ChemicalProfiles where ChemicalId in (select ChemicalId from #TempPC where [status] = 'Not Found in Chemical Master')

or:

delete from Parts.ChemicalProfiles as f where exists (select * from #TempPC where [status] is null and ChemichalId = f.ChemicalId)
delete from Parts.ChemicalProfiles as f where exists (select * from #TempPC where [status]='Not Found in Chemical Master' and ChemicalId = f.ChemicalId)

Answer 4

Hi @ahmed salah ,

OR may cause a full table scan or index scan, thereby reducing query efficiency.But we need the execution plan to analyze the specific problems that cause the query to slow down.
You can first try to use case when instead of or, if the problem is not resolved, you need to provide execution plan and index information, etc.

Please try:

 DELETE  f  
         FROM    Parts.ChemicalProfiles f  
                 INNER JOIN #TempPC t   
                 ON t.ChemicalID = f.ChemicalID  
         WHERE  case when t.[status]='Not Found in Chemical Master' then t.[status] else NULL end=t.[status]  

Using CASE WHEN to rewrite this SQL statement may be able to avoid the execution plan caused by OR not taking the index search (Index Seek). Of course, this does not solve all the performance problems caused by OR, and you need to refer to the execution plan to solve the problem.

In addition, OR does bring a lot of uncertainty, try to replace OR with UNION or IN. We need to follow some rules, but we cannot think of it as immutable and eternal truth. Specific analysis of specific scenarios and specific environments. For OR or UNION, there is no definite which is better, and it needs to be tested before use.

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 5

I need to use in operator so How to do that
where t.[status] in ('Not Found in Chemical Master',NULL)
so How to do that