Sorry for those who jump in here this a continueation of Thread #303866 Message #305417

John,

>>The differences between the two mechanisms become clear when using a hierarchy of tables linked to eachother with the SET RELATION command (thus suing the indexes efficiently) to get the data you want. On top of that you can filter tables within the hierarchy to provide a very powerfull way to reach the same goal as you try to do with JOINS in SQL. Difference here is that the Xbase solution of using a hierarchy is many times faster than using the SQL equivalent (yes i've tested this), and requires less memory (there is no query to be executed) to get the data you want, especially when the hierarchy involves lots of tables (thus many joins in SQL).

>I would like to see the notes and data of your tests....

Since I it's difficult to give you a possibility to check my original test, here is one I constructed today.

The tables used are from the VFP 5 samples\data directory:

CLEAR ALL
CLOSE ALL
SET SAFETY OFF

nSec=SECONDS()

USE ORDITEMS 
USE ORDERS IN 0 ORDER TAG Order_id
USE Employee IN 0 ORDER TAG emp_id
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 Emp_id INTO Employee ADDITIVE
SELECT OrdItems

set console off 
set print to data.txt
set print on

SCAN FOR Orders.shipped_on # {} AND Products.Discontinu 
	? order_id, orders.to_name, products.prod_name, employee.first_name, employee.last_name
ENDSCAN

set print off
set print to
set console off
wait window STR(SECONDS()-nSec,6,3)



CLEAR ALL

nSec=SECONDS()

SELECT * ;
	FROM Orders o 	INNER JOIN Orditems oi ON o.order_id=oi.order_id ;
					INNER JOIN Products P ON oi.product_id = p.Product_id ;
					INNER JOIN Employee E ON O.emp_id=e.emp_id ;
	WHERE shipped_on # {} AND P.Discontinu ;
	INTO CURSOR x


set console off 
set print to data.txt
set print on

SCAN
	? order_id_a, to_name, prod_name, first_name, last_name
ENDSCAN	

set print off
set print to
set console off
	
wait window STR(SECONDS()-nSec,6,3)

This program does show two methods of collecting data and writing it into a custom file. Tough there is much to say about the implementation, you're able to make changes yourself. The program shows two different ways to collect information about shipped orders where discontinued articles are involved.

since the SQL method collects the information at once and the RELATION method collects the data within the SCAN, i've decided to write some information to a custom file.

In my cases the xBase variant was took about 0.21 seconds and the SQL variant took about 0.96 seconds, on the workstation where the data was stored.

Over a 10 MB network (on a slower computer) the Xbase varian took about 1.3 seconds the SQL took about 10.3 seconds.

Note that there are numerous parameters involved which can determine the differences between the two:
- Performance of the network
- Internal memory
- are the tables used by another user
- etc..

The trick is that SQL makes copies of the source tables data in internal memory or in temporary files, thus consuming more memory and more CPU cycles, whereas the Xbase variant uses the source tables themselves to perform the 'query'

This is one of the reason why SQL is so inefficient in terms of performance. This is exactly why Xbase had a chance to survive, especially true in the time where the first relational databases showed terrible performance.

Walter,