SQL Server T-SQL Tuning – NOT IN and NOT Exists

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “

Not in and Not Exists commands :

In some circumstances we will need to select/update/delete orphan data – that is data does not exists in the major table but still exists in other interrelated tables

The response to such a circumstance will often be to utilize the “Not in” and “Not Exists” commands. This can however have a major impact on performance as the Not IN command requires a check to be made on each record individually resulting in a strain on resources and can also lead to locks particularly when updating or deleting a large volume of data .

Alternatives to NOT IN and NOT Exists

SQL Server 2008 introduced the Merge command for bulk insert / update / delete operations which can be used with the Except clause to minimize its performance hit.

Examples:

First we will create two tables :

use [Workshops]
Create table Mytable1 (companyname varchar (100) )
Create table Mytable2 (companyname varchar (100) )

Select/update/delete orphaned data using Not in command :

SELECT companyname FROM Mytable1 where companyname not in (select  companyname from Mytable2)
UPDATE Mytable1 SET companyname =N'Company_Name'  where companyname not in (select  companyname from Mytable2)
DELETE  Mytable1 FROM Mytable1 where companyname not in (select  companyname from Mytable2)

Improved performance using Merge and Except :

merge Mytable1 T using (select companyname from mytable1 except select companyname from mytable2 )S on t.companyname=s.companyname
when  matched  then update SET companyname=N'Company_Name' ;
merge Mytable1 T using (select companyname from mytable1 except select companyname from mytable2 )S on t.companyname=s.companyname
when  matched  then delete ;
SELECT * FROM MYTable1 S where   not exists (select 1 from  MYTable1 inner join MYTable2 on MYTable1.companyname=MYTable2.companyname and MYTable1.companyname=s.companyname)

Note that this also applies Not Exists :

SELECT * FROM MYTable1 S where   not exists (select 1 from  MYTable1 inner join MYTable2 on MYTable1.companyname=MYTable2.companyname and MYTable1.companyname=s.companyname)

Healthy T-SQL practices to Select orphaned data using Except Command

select companyname from mytable1 except select companyname from mytable2

In the next article of this series we will look at optimizing the performance of Views. ]]>