Lets try something a little more real world, and we will keep this in a VFP-only environment...

Scenario 1: Using X-Base constructs to build a result set for order 1078

nSec=SECONDS()
Open Data "TASTRADE"

USE order_line_items
USE ORDERS IN 0 ORDER TAG Order_id
USE Employee IN 0 ORDER TAG employee_i
USE Products IN 0 ORDER TAG Product_id

SET RELATION TO Order_id INTO ORDERS
SET RELATION TO Product_id INTO PRODUCTS ADDITIVE
SELECT Orders
SET RELATION TO Employee_id INTO Employee ADDITIVE

Create Cursor foo ;
(order_id c(6),ship_to_name c(40),product_name c(40),first_name c(10),last_name c(10))

SELECT Order_line_items
SCAN for order_id = " 1078"
Select foo
append blank
Replace order_id With order_line_items.order_id, ;
ship_to_name with orders.ship_to_name,;
product_name with products.product_name,;
first_name with employee.first_name,;
last_name with employee.last_name
Select order_line_items
ENDSCAN

?STR(SECONDS()-nSec,6,3)


TIMES:

On average .048 seconds


Scenario 2: Using SQL constructs to build a result set for order 1078


nSec=SECONDS()
Open Data TASTRADE
SELECT order_line_items.order_id,ship_to_name,product_name,first_name,last_name ;
FROM Orders ;
INNER JOIN Order_line_items ON orders.order_id=order_line_items.order_id ;
INNER JOIN Products ON order_line_items.product_id = products.Product_id ;
INNER JOIN Employee ON orders.employee_id=employee.employee_id ;
Where orders.order_id = " 1078" ;
INTO CURSOR foo
?STR(SECONDS()-nSec,6,3)

TIMES:

On average .050 seconds

---

Now, which code block would you want to maintiain? Number 1 or 2? My guess is number 2? Which is easier to migrate to another platform? Again, number 2.

So, are the xbase constructs faster? Sure, but in this case, only .002 seconds faster. BIG DEAL...

One thing you did in your example was a SELECT *. In the xbase example, only 5 fields were used in the output. You will notice that I did not make that mistake above. It made a big difference in time....

It kind of pops a big hole in your thoery..... does it not??

And, if you really want to make the xbase code run faster, this would be a better approach:

SELECT Order_line_items
Set Order to order_id
Seek(" 1078")
SCAN While order_id = " 1078"
Select foo
append blank
Replace order_id With order_line_items.order_id, ;
ship_to_name with orders.ship_to_name,;
product_name with products.product_name,;
first_name with employee.first_name,;
last_name with employee.last_name
Select order_line_items
ENDSCAN

In this case, the average running time was only .044 seconds. But still, it would only be .006 seconds faster on average than the SQL code. Once again, BIG DEAL!!!

Are you sure you still want to go on with this debate???????????