Posted by Dave on 01/04/07 23:23

Your correlated subquery works.
It added a stream aggregate step to the execution plan right before the
merge join.

This is really weird. Could it be an SQL Server Bug? Like I said
earlier, it is very data specific. I can add 3 million random records
to the filter table and it normally works fine. It is only this
specific dataset that is causing problems.


Erland Sommarskog wrote:
> 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]