Professional SQL Server® 2005 Performance Tuning

9.4. Tuning T-SQL with a New Approach

In the database world, DBAs approach performance issues by naturally assuming that the query is the limiting constraint. Then you start tinkering with the SQL statement to achieve an excellent low-cost result only to only have it crash and burn under different conditions in the production environment. It is an art to know when to yield to the declarative nature of SQL and when to tinker. The optimizer can interpolate many alternative ways to execute even poorly written queries, but there are times when correcting an inefficiently written query can often produce dramatic gains in performance and time.

9.4.1. NOT IN and NOT EXISTS Rewrites are in the Past

The conventional wisdom in the past was to rewrite NOT IN statements to use NOT EXISTS statements if the subquery scanned all the rows in a subtable. Mainly this was because the subquery would perform a full scan of the clustered index. Because of the advances in the optimizer, this is no longer necessary. The optimizer allows you to be more declarative, code using either method, and still get the most efficient implementation. Looking at a proof of this is instructive. An example in the AdventureWorks database would be a query that needs to return the departments that have never had an employee. The queries using NOT IN and NOT EXISTS structures would look like this:

SELECT Name, DepartmentId FROM HumanResources.Department WHERE DepartmentId NOT IN (SELECT DepartmentId FROM HumanResources.EmployeeDepartmentHistory) ...