|
|
Posted by AlterEgo on 01/04/07 20:00
Dave,
Do you have a lot of null emails on both tables? If so, that might be an
influencing factor.
-- Bill
"Dave" <daveg.01@gmail.com> wrote in message
news:1167930181.492080.156680@s80g2000cwa.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|