FIX: Query Optimizer Fails to Consider Anti Semi Join for NOT IN Queries

• Microsoft SQL Server version 7.0

SYMPTOMS

For queries that contain a predicate ... IN (a, b, c, ...), the SQL Server 7.0 optimizer evaluates the possibility of converting the query to a semi join. However, it fails to consider a similar simplification that would simplify a ... NOT IN (a, b, c, ...) as an anti semi join. Thus, a NOT IN query returning a similar number of rows may take considerably longer to run than a corresponding query using IN.

WORKAROUND

To work around this problem, explicitly create and populate an intermediate table with this list of values from the NOT IN clause, and rewrite the query using a join.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: optimizer showplan performance slow

Keywords          : SSrvGen SSrvTran_SQL kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 8, 1999