Posted by Alexander Kuznetsov on 10/06/38 11:33

>tried al types of different indexes:
>* index on checksum (clustered), source_key (Non clustered, unique) and
>on expired date (non clustered) --> 99 seconds
>* Index on source_key (clustered, unique), checksum_field (non
>clustered) and on expired date (non clustered) --> 91 seconds

that is normal. accessing via a non-clustered index (thru a bookmark)
may be quite slow. Having all the columns in just one index might be
better.

>* the Source key (in the example Order_Number and Order_Detail_Number)
>was unique so i decided to build a checksum on these fields and build
>an index on the checksum and of course on the Source_key and
>expired_date. This took about 101 seconds. What?

are you speaking about one index containing all those columns?

Also note that if all the columns are not nullable, not exists()
queries may be rewritten as not in() ones.
Also try rewriting you not exists() query as an outer join.

try all 3 ways, they are equivalent if the columns you join on are not
nullable:

1.
select c.left_PK
from left_table c
where left_PK not in(select left_PK from right_table t
where c.left_PK=t.left_PK)

2.
select c.left_PK
from left_table c
where not exists(select 1 from right_table t
where c.left_PK=t.left_PK)

3.
select c.left_PK
from left_table c left outer join right_table t
on c.left_PK=t.left_PK
where t.left_PK is null

[Back to original message]