Posted by Tracy McKibben on 01/04/07 17:56

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

• Next in forum: MI5 Persecution: Communications with Security Service Tribunal in 1999
• Prev in forum: INNER JOIN/Index Threshold?
• Thread view: Re: INNER JOIN/Index Threshold?

[Reply to this message]