Walter,

I'm sorry, but your comparison test is totally worthless.

Item 1) A high fraction of the time spend is CPU time actually spent executing the ? command. Not on the code you are trying to really benchmark.

Item 2) Your SELECT * is creating a result table that is 49 columns wide instead of the 5 fields your scan loop processes. So yes you are asking the SQL engine to do one hell of a lot more work than you are asking the Xbase DML to do.

Item 3) Rushmore can't fully optimize the query because of missing indexes.

If you run this code instead:

set path to "C:\Program Files\VS98\MSDN98\98VSa\1033\SAMPLES\VFP98\data"

CLEAR ALL
close data all

create cursor junk1( order_id c(6), to_name c(40), prod_name c(40), first_name c(40), last_name c(40) )

USE ORDITEMS  in 0
USE ORDERS IN 0 ORDER TAG Order_id
USE Employee IN 0 ORDER TAG emp_id
USE Products IN 0 ORDER TAG Product_id

nSec=SECONDS()

select orditems
SET RELATION TO Order_id INTO ORDERS
SET RELATION TO Product_id INTO PRODUCTS ADDITIVE
SELECT Orders
SET RELATION TO Emp_id INTO Employee ADDITIVE
SELECT OrdItems

SCAN FOR Orders.shipped_on # {} AND Products.Discontinu 
	insert into junk1 values ( order_id, orders.to_name, products.prod_name, employee.first_name, employee.last_name )
ENDSCAN

? SECONDS() - nSec


close data all
USE ORDITEMS  in 0
USE ORDERS IN 0
USE Employee IN 0
USE Products IN 0

nSec=SECONDS()

SELECT orders.order_id, orders.to_name, products.prod_name, employee.first_name, employee.last_name;
   FROM Orders ;
   INNER JOIN Orditems ;
      ON orders.order_id = orditems.order_id ;
   INNER JOIN Products ;
      ON orditems.product_id = products.Product_id ;
   INNER JOIN Employee ;
      ON orders.emp_id = employee.emp_id ;
	WHERE shipped_on # {} AND products.Discontinu ;
	INTO CURSOR junk2

? SECONDS() - nSec

You'll find that the SQL is 3.63 times faster than the xbase code! On my P2-300 notebook the average of 3 runs were 0.145 seconds for the xbase and 0.040 seconds for the SQL.

I added indexes on:
select products
index on discontinu tag discontinu
select orders
index on shipped_on tag shipped_on
and deleted() on all tables to get full optimization. Even without fully optimizeable queries the SQL is going to be faster.