Posted by othellomy on 01/09/07 06:25

Hi,
I have modified your query a little bit and hope this will help.

DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index ix_email on base(email)
create index icx on base(bk)

CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index ix_email on filter (email)
create index icx on filter (bk)

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
Dave wrote:
> Thanks for the explanation.
>
> I copied the tables to a test db. I created an identity column on each
> table and created a unique clustered in the identity columns. I then
> created a non-clustered on bk and a non clustered on email.
>
> This did not help the query performance.
>
> I will keep checking back here to see if anyone else has any
> suggestions on how to trouble shoot this. It is no longer a critical
> issue due to the work around. I just don't like the fact that we can
> not explain this behavior.
>
>
> dmarkle wrote:
> > Well, it comes mostly from relational and set theory. I like to
> > explain the need for PK's like this:
> >
> > If your wife makes you go to the grocery store and she asks you what
> > you bought, you'd give a set back as your answer:
> >
> > "Peas, carrots, potatoes, and Coca-Cola"
> >
> > You wouldn't say:
> >
> > "Peas, carrots, potatoes, peas, and Coca-Cola".

[Back to original message]