In recent training one of the attendee asked if I can show a simple method to convert IN clause to EXISTS clause so it impacts performance. 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 our case listed above it does for sure give better performance.
Click on below image to see the execution plan.
SQL Server Performance Tuning and health check is a very challenging subject that requires expertise in Database Administration and Database Development. Here are few pointers how one can keep their SQL Server Performance Optimization. I am often asked what can one do keep SQL Server Health Optimal and SQL Server keep on running very smooth. What is the tool that you use to check for configuration settings on your SQL Server box? In the past, I have talked about SQL Server Management Studio Standard reports and a number of reports that can help.
Reference: Pinal Dave (http://blog.SQLAuthority.com)