Does anyone know of a comprehensive or exhaustive description of what goes on inside the VFP SQL engine? I recall reading something some years back that gave more clues about what goes on inside the engine that proved to be helpful in deciphering how to optimize the construction of SQL statements.

Second -- We have seen that the following situation is fully optimizable (based on SYS(3054,11) ) ...

select * from customer where upper(name) == "ACME"

if the index tag is set to upper(name) (i.e. the expression used to index the tag is identicle to the expression in the where filter clause). Thus ...

select * from customer where name == "ACME"

... will prove to be non-optimized using Rushmore.

What we are wondering is this:

select * from customer, invoice where customer.iInvNo == invoice.iInvNo

SQL statements like the one above do not show that Rushmore optimization is being used (i.e. SYS(3054,11) which is to display Rushmore Join Optimizations).

Here is the question:

Do any of you know the proper construction of SQL whereby Rushmore can be applied to the join conditions?

By default our Query Builder system places the join conditions in the WHERE clause. We tried moving the joins into the FROM clause in the form of:

database!table as alias inner join database!table on [joincondition]

This method had the net affect of making the SQL processing thermometer jump from o% to 99% almost immediately. However, it did not cause the Join Optimization reported by SYS(3054,11) to change from None. Also, it did not improve the overall processing speed. Furthermore, it appears only to enhanced the intermediate joins, but falls down (in terms of Rushmore) at the final joins of two intermediate tables.

Thanks in advance.

Larry Rix