Posted by Dave on 01/04/07 19:00

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


Tracy McKibben wrote:
> Dave wrote:
> > Guys I am really stuck on this one. Any help or suggestions would be
> > appreciated.
> >
> >
> > We have a large table which seemed to just hit some kind of threshold.
> > They query is somewhat responsive when there are NO indexes on the
> > table. However, when we index email the query takes forever.
> >
> > FACTS
> > - The problem is very "data specific". I can not recreate the
> > problem using different data.
> > - There is only a problem when I index email on the base table.
> > - The problem goes away when I add "AND b.email IS NOT NULL" to the
> > inner join condition. It does not help when I add the logic to the
> > "WHERE" clause.
> >
> >
> > DDL
> > CREATE TABLE base (bk char(25), email varchar(100))
> > create clustered index icx on base(bk)
> > create index ix_email on base(email)
> >
> >
> > CREATE TABLE filter (bk char(25), email varchar(100))
> > create clustered index icx on filter (bk)
> > create index ix_email on filter (email)
> >
> >
> >
> > Query
> > SELECT b.bk, b.email
> > FROM base b WITH(NOLOCK)
> > INNER JOIN filter f ON f.email = b.email
> > --and f.email is not null
> >
> >
> >
> >
> >
> > Data Profile
> > --35120500, 35120491, 14221553
> > SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
> > FROM base
> >
> >
> > --16796199, 16796192, 14221553
> > SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
> > FROM base
> > WHERE email IS NOT NULL
> >
> >
> >
> > --250552, 250552, 250205
> > SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
> > FROM filter
> >
> >
> > --250208, 250208, 250205
> > SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
> > FROM filter
> > WHERE email IS NOT NULL
> >
>
> What does the estimated execution plan look like? Try adding the column
> BK as a second key in your email index.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

[Back to original message]