Walter.....

I don't have a lot of time to really get into this in the detail I would like..

However, my comments regarding SQL where really meant in the context of using the SQL Server product for your data store as opposed to using the native VFP data storage mechansim..

If anything, you have shown that when working with VFP data, Xbase constructs can often perform better than relying soley on SQL constructs... You will get no argument from me here.... The fact is, on SQL Server, it is often better to have a stored proc that does several intermediate operations as opposed to doing things in one big SQL statement...

Once again, I think you are unilaterally re-directing the debate here...

In summary, your test addresses the wrong issue....


>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,