Hello, All.

We have got a query against 2 VFP6 tables, A_SYS.DBF and XX.DBF. A_SYS.DBF has got 1.3 Million records. XX.DBF has 1 record. The result set has 5 records which match. A_SYS is in a .DBC, XX is a free table.

First we run:

SELECT a_sys.* ;
FROM a_sys, xx;
WHERE a_sys.prodrid = xx.prodrid ;
AND a_sys.affid IN (SELECT affid FROM xx) ;
INTO CURSOR ctemp

The query take 0.04 seconds to run.

However, when we run:

SELECT a_sys.* ;
FROM a_sys, xx;
WHERE a_sys.prodrid = xx.prodrid ;
AND a_sys.affid = xx.affid ;
INTO CURSOR ctemp

the query takes approximately 80 seconds to run.

Both files are indexed on affid and prodrid. Both of these fields are of type "integer."

Why does it take so long to join on 2 fields as compared to running the first command? When we test using SYS(3054), it reports that it is not optimizing the 2nd query, and that it is partially optimizing the first.

- George [This makes no sense to me]