Hi Ken,

Interesting analysis article. Nice work.

Thanks.

Naras.

Narasimhan Jayachandran

I ran some of the queries on SQL 2000 and SQL 2005 [on the servers]. I ran it for 50 times only.
I got follow results:

SQL 2000 SP3:

time: 1
NOT IN :1376 ms
AND <> :1796 ms
derived UNION table LEFT OUTER JOIN : 453 ms

time: 50
NOT IN :58110 ms
AND <> :58800 ms
derived UNION table LEFT OUTER JOIN : 43703 ms

SQL 2005 SP1:

time: 1
NOT IN :1220 ms
AND <> :1173 ms
derived UNION table LEFT OUTER JOIN : 420 ms

time: 50
NOT IN :50236 ms
AND <> :53313 ms
derived UNION table LEFT OUTER JOIN : 21466 ms

My experiment also shows that "derived UNION table LEFT OUTER JOIN" is better than 'NOT IN" or "AND <>".....

Thanks,

Miriamka

Guys,

By replacing the union query with a temporary table you can get a further performance enhancement, this implies that a Constant scan is slower than a Table scan.

My results...

Beginning test run...1 NOT IN
Elapsed Time: 121543 ms

Beginning test run...2 <>
Elapsed Time: 132310 ms

Beginning test run...3 LEFT OUTER JOIN
Elapsed Time: 105760 ms

Beginning test run...4 NOT EXISTS (UNION)
Elapsed Time: 96250 ms

Beginning test run...5 NOT EXISTS (temp table)
Elapsed Time: 66633 ms

ta.

Nick

set statistics io on
set statistics time on
CREATE TABLE tbl_IN_VS_AND (filterCriterion_sv int PRIMARY KEY CLUSTERED NOT NULL)

Insert tbl_IN_VS_AND 
SELECT 100000 * U.I + 10000 * V.I + 1000 * W.I + 100 * X.I + 10 * Y.I + Z.I filterCriterion_sv FROM 
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) U,
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) V,
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) W,
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) X,
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) Y,
(SELECT 0 I UNION SELECT 1  UNION SELECT 2  UNION SELECT 3  UNION SELECT 4  UNION SELECT 5  UNION SELECT 6  UNION SELECT 7 UNION SELECT 8  UNION SELECT 9) Z

set statistics io off
set statistics time off