Posted by Erland Sommarskog on 01/04/07 22:37

Dave (daveg.01@gmail.com) writes:
> bk is clustered so there is no reason to add it to the other index.
>
> Estimated plan does an index scan on each table, then a merge join.
>
> When I remove the index on base.email the plan does a table scan on
> base then performs a Hash Match

And the merge-join plan takes forever? That's indeed very funny, because
that's sounds like a quite decent plan. Your tables looks funny, because
they have no keys, but that would matter.

What happens if you try:

SELECT b.hk, b.email
FROM base b
WHERE EXISTS (SELECT *
FROM filter
WHERE f.email = b.email)


Which version of SQL Server?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]