Hey all,

I am having trouble with LEFT OUTER JOIN queries in VFP 6.0 (SP3) (and it does the same thing in VFP 5.0). Here is the scenario.

Let's say you have a PARENT table of 100,000 records or so, and a CHILD with 100,000 records. Let's say you do a simple inner join like this:

SELECT PARENT.* ;
FROM PARENT INNER JOIN CHILD ;
ON PARENT.joinID == CHILD.joinID ;
WHERE CHILD.criteria =

And let's say CHILD.criteria is properly indexed, and the joinID field in both tables is properly indexed. This will be a fully optimized query (providing SET DELETED is OFF), and will likely be very fast. SYS(3054) [showplan] will show that the query is fully optimized. But change the query to a LEFT OUTER JOIN, thusly:

SELECT PARENT.* ;
FROM PARENT LEFT OUTER JOIN CHILD ;
ON PARENT.joinID == CHILD.joinID ;
WHERE CHILD.criteria =

And the query will be an order of magnitude slower. Showplan still shows full optimization, but the query will be slow (I have been testing all morning with similarly sized tables, and the first query takes a few seconds, while the second query takes about 45-50 seconds).

As close as I can figure, when VFP tries a LEFT OUTER JOIN where the filter conditions are all related to the child (right) table, optimization falters. VFP _should_ realize that all conditions relate to the child, query the child, and then join that reduced recordset to the parent. It does not seem to be doing this (or at least if it is, then what the heck is taking so long?). I have done tests where the child filter condition reduces the number of child records to 8, and the query still takes 45 seconds. Why so? The only thing I can figure is that VFP is still doing table scans on the parent to make sure the LEFT OUTER conditions are met or something.

If I change the above LEFT OUTER JOIN to have a filter condition on the PARENT, thusly:

SELECT PARENT.* ;
FROM PARENT LEFT OUTER JOIN CHILD ;
ON PARENT.joinID == CHILD.joinID ;
WHERE PARENT.criteria =

This will again be a very fast query. It is only when the child table of the outer join is used as a filter that things slow down. I should amend that: It is only when the filter condition is based _solely_ on the child that the query slows. If the WHERE claues is a mix of PARENT and CHILD filtering, the query will be fast, probably because the PARENT filters reduce the recordset right away.

Additional points: It does not seem to matter whether the child filter is placed in a WHERE clause or is used in the JOIN criteria. Still slow. Also, I can hear the responses now, asking why would I want to do a LEFT OUTER JOIN when the child table is running the show? I mean, if criteria is based on the child, you will obviously only get back records where there is a child, right? This is true, but we have several large queries that are parameterized -- some parameters relating to the parent, and some to the child. We always want the parent data even if there is no child, so we have to use a LEFT OUTER JOIN, even though that is overkill when the user only searches by child criteria. But what a pain it would be to have to decide before running the query (we use local views for this) what type of join to use?? Or worse, have to make two views and call them under different circumstances? Ugh.

First off, has anyone else seen this type of behavior? Is this considered a bug in VFP's optimizer?

At this point I think I am going to build my views with a macro-substituted string where the join type would be, and adjust it to "INNER" or "LEFT OUTER" depending on the criteria the user enters to search by. This is a pain, but at least I can still use the existing views I have constructed with minimal changes.

I would appreciate any comments/workarounds folks have for this issue.

Thanks,
Joe Kaufman
jkaufman@encompas.com