>Have you tried switching the tables and using a RIGHT OUTER JOIN?

Interesting stuff here...to recap, here is the general query:

SELECT parent.* FROM parent child ;
ON parent.joinid == child.joinid ;
WHERE child.criteria =

The query is fully optimizable as far as join condition and WHERE filter. Here are some results of various joins run on a sample parent and child table I have. The parent table has 168,000 recs, and the child table has 190,000 recs. The child criteria is enough to limit the result set to 8 records, so data transfer time of the result set should be negligible:

INNER JOIN : less than one second
LEFT OUTER JOIN : about 60 seconds
RIGHT OUTER JOIN (tables in same orientation) : 16.4 seconds
RIGHT OUTER JOIN (tables reversed) : 43 seconds

The RIGHT OUTER JOIN with tables the same orientation should have been faster, since that is essentially the same as switching the join to an INNER join because the child controls the filtering. I am not sure why it still took 16 seconds.

Throughout the day I have been tweaking views that are now running slow because we made our tables larger (ran some conversions last evening). The one thing that is abundantly clear is that Foxpro optimizes INNER JOINS _much_ better than LEFT OUTER JOINs. Queries that took two minutes went down to 2-3 seconds with the same search criteria being used. Now, going to INNER JOINS has its problems...it means that if anywhere along the line one of the joined tables has no record to match to, the entire result record will not show up. I am finding that in a lot of cases we have used LEFT OUTER JOINs a bit over-zealously, but that was because I figured Rushmore would work just as well with them as with the INNERs. I guess I was wrong.

Anyone else have any experiences/thoughts on this matter?

Thanks,
Joe Kaufman
jkaufman@encompas.com