Hi David.
Cons doesn't have indexes on Addrnum, PAddrnum. But it's showing as fully optimizable in sys(3054). My query result is a single row BTW. The sys(3054) is misleading in this case, but that's understandable.

I'm with you on the "OR" slowing it down. Surprising how much!


>David,
>
>You don't say if cons has indexes on Addrnum and PAddrnum. Also anytime you add an OR to a where clause it slows it down terribly. And you can get better performance from two individual selects.
>
>>Address is a table with 400,000 records indexed on Addrnum
>>Cons is a table of 120,000 contacts indexed on CCust
>>Both have tags on DELETED()
>>
>>The query...
>>

SELECT A.* FROM Address A, Cons C ;
>>  WHERE C.CCust = "GREATLA2" ;
>>  AND C.PAddrnum = A.Addrnum ;
>>UNION SELECT A.* FROM Address A, Cons C ;
>>  WHERE C.CCust = "GREATLA2" ;
>>  AND C.Addrnum = A.Addrnum
>>

>>runs about 50 times faster than...
>>

SELECT A.* FROM Address A, Cons C ;
>>   WHERE C.CCust = "GREATLA2" ;
>>   AND (C.Addrnum = A.Addrnum OR C.PAddrnum = A.Addrnum)
>>

>>Both queries show as fully optimized with SYS(3054,1), both return the same result set.
>>Strange uh!