>Woops...I just noticed your last note about non-filtered index.
>I am not familiar with this expression. So I then added 3 new indexes
>in the actual CDX: one on FA_GENRE, one on ABS(FA_Total) and one on
>ABS(FA_ZPayed). But I'm not sure if this is what you mean.
>
>Let's redo test 3 with these new indexes:
>
>SELECT * FROM Facture WHERE (FA_GENRE = "FA") AND ;
> (ABS(FA_TOTAL) > ABS (FA_ZPAYED)) INTO TABLE Tmp ORDER BY CL_Code
>
>* It now takes 28.25 seconds
>
First, I think it may be a mistake to include those parentheses in the query. I think that is tricking Rushmore into not trying to optimize. Also, you don't have indexes mentioned for either DELETED() or CL_Code (for the ORDER BY). Here is my first approach to this problem:
INDEX ON FA_GENRE TAG FA_GENRE
INDEX ON CL_CODE TAG CL_CODE
INDEX ON ABS(FA_TOTAL)-ABS(FA_ZPAYED) TAG ABS_DIFF
INDEX ON DELETED() TAG DELETED
Now change your query to exactly:
SELECT * FROM Facture WHERE FA_GENRE = "FA" AND ;
ABS(FA_TOTAL)-ABS(FA_ZPAYED)>0 INTO TABLE Tmp ORDER BY CL_Code
To further optimize, you can consider removing the ORDER BY and then indexing the result set.
-- Randy