>any suggestions on how to optimize the query below. it runs fine when the there are 40,000 or fewer records in table test (95% of the time). however every once in a while i get 250,000 record sets. i have tried creating indexes on all eight fields.
>
>thanx.
>
>SELECT DISTINCT t1.invnum, t1.vendname, t1.invdate, t1.invamt, t1.vendnum, t1.checknum, t1.checkdat, t1.misc;
>FROM test t1, test t2 ;
>WHERE t1.invnum != t2.invnum ;
>AND t1.vendname = t2.vendname ;
>AND t1.invdate !=t2.invdate ;
>AND t1.invamt =t2.invamt ;
>INTO TABLE report6

The problem, as I see it, is the inequality, which gets many "hits" for that part of the condition. That is, record keys are obtained from the index. This is part of Rushmore Optimization.

See my FAQ #8109 for details.

You can delete the index for the inequality part (or for any other part that gets many "hits"), or disable R.O. for that part.

For instance, changing:

t1.InvDate != t2.InvDate

to:

t1.InvDate + 0 != t2.InvDate + 0

will effectively ignore any index on InvDate, since an index on InvDate + 0 will be sought, and not found.

HTH, Hilmar.