Now hold on a sec... I think my test, while not scientific, reflects reality. Most people, when they run a SQL, return a result set. This thing about creating an external text file, well, is not what most folks do. And, SQL Server does not do that itself. Rather, it would rely on a client who gets the result set to do this....

As for using a network, no, I did it on my little hold P-400 Notebook running Windows 2000 with 384 mb of RAM...<bg>....And no, I did not reboot my machine between each run... Who cares, I did the same thing FOR BOTH SCENARIOS.

As for not using the SQL Insert, the point was to use only X-Base constructs. If I used a SQL construct in the X-base stuff, would that not pollute the test environment??????

The fact is, your theory got blown out of the water, quite handily I might add.....<bg>.

Look, many times the X-base constructs are better than VFP SQL. Tell you what, how about this, doing this in SQL....

Scenario 1: Running the following SQL Select in Query Analyzer -

SELECT [order details].orderid,shipname,productname,firstname,lastname
FROM Orders
INNER JOIN [order details] ON orders.orderid=[order details].orderid
INNER JOIN Products ON [order details].productid = products.Productid
INNER JOIN Employees ON orders.employeeid=employees.employeeid
Where [order details].orderid = 11077

This produces the exact result set as the VFP stuff:

Time to excute: 0:00:00, it did'nt even register on the clock.


Sceanario 2: Render the SQL to SQL-Server via SPT:


nSec=SECONDS()
lnHandle = sqlconnect("northwind")
lcSQL = "SELECT [order details].orderid,shipname,productname,firstname,lastname "
lcSQL = lcSQL + "FROM Orders "
lcSQL = lcSQL + "INNER JOIN [order details] ON orders.orderid=[order details].orderid "
lcSQL = lcSQL + "INNER JOIN Products ON [order details].productid = products.Productid "
lcSQL = lcSQL + "INNER JOIN Employees ON orders.employeeid=employees.employeeid "
lcSQL = lcSQL +" Where [order details].orderid = 11077"
Sqlexec(lnhandle,lcSQL)
?STR(SECONDS()-nSec,6,3)

Average time to execute: .038 seconds


Sceanario 3: Create a SQL Server Stored Proc:


CREATE PROCEDURE SQL_DOES_NOT_SUCK @orderid As int
AS
SELECT [order details].orderid,shipname,productname,firstname,lastname
FROM Orders
INNER JOIN [order details] ON orders.orderid=[order details].orderid
INNER JOIN Products ON [order details].productid = products.Productid
INNER JOIN Employees ON orders.employeeid=employees.employeeid
Where [order details].orderid = @orderid


Here is the VFP code:

nSec=SECONDS()
lnHandle = sqlconnect("northwind")
Sqlexec(lnhandle,"exec sql_does_not_suck 11077")
?STR(SECONDS()-nSec,6,3)

Average time: .035 seconds...


Gee Walter, keeping all things constant, SQL Server on my machine is outperforming VFP Data... A couple of things;

1. These ARE NOT scientific benchmarks.... This and a quater might get you accross town...
2. The data set here is VERY SMALL
3. But in some respects, it is a valid test because I am using the same machine, the data size is the same, and I am always returning the same result set.

Now, if you actually owned SQL-Server, you could test this for yourself...


Do you want to play again????? This is fun!!!


Tell you what, when I get more time, I will try this on my network.... I'll let you know what I find.. I don't think however, you are going to like the results....

Cmon Walter, admit it.... the Leaning Tower of Pisa has more stability than your overall theory against SQL ....

















>John,
>
>In this example, you're trying to emulate the SQL-select within a xBase variant including storing the result
>
>This is just the point, I don't want/need to store the results. I jut want to use the data directly from it's datasource, thus not copying it in another cursor/table
>
>That's why I copied the resultset to an extrernal file for both approaches in the former example.
>
>When VFP will be able group fields from different tables into one alias/workarea (see the wishlist for native VFP views), You'll not need to copy it into a cursor/table.
>
>BTW, if you'll use SQL-INSERT in your example you'll speed up creating the cursor.
>
>Second: Did you test this on a network ? Tip: Reboot your computer between each test because SQL-select tends to chache resultset components so speeding up the query next time you'll execute it.
>
>Walter,