Today, I have received to optimize a store procedure where I have made changes and gotten good performance.
I can show simple method to convert IN clause to EXISTS clause. Here is the simple example.
USE AdventureWorks
GO
— use of =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
— use of EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
It is NOT necessary that every time when IN is replaced by EXISTS it gives better performance. However, in my case listed above it does for sure give better performance.
Click on below image to see the execution plan.
