>Hey all,
>
>>The chap in your office may be right. On a local machine, VFP will hold up against SQL in moderately sized tables, however on a network, with larger tables, VFP is playing catchup. On a network, all the data specified in the query by VFP from VFP tables is transmitted over the network whereas SQL executes the query at the server and only returns the result. Also, SQL usually operates with more server resources so it does things that VFP has to do double duty on. There are ways to keep VFP competitive to SQL in terms of speeds but usually it is a catch up game at best. Let's face it, SQL loads an additional cost of between $100 to $150 per workstation to support, whereas VFP has no such cost. It stands to reason that it has to have some performance benefit to the user.
>
>GOod argument on the cost basis...that really is an advantage in VFP!
>
>Just wanted to clarify something...you state that "all the data specified in the query by VFP from VFP tables is transmitted over the network"... I was wondering if you meant that all tables have to be brought local to execute the query? As far as I know, that is not the case. I have done queries against 4 or 5 25+ MB tables on a network before, meaning that if all data were brought local, more than 100 MB of data would have to be pulled down. I don't think that is the case, as these queries could run in a few seconds even on a mediocre (10 megabit, shared) network.
>
>The key is that Fox pulls down only the parts of the CDX files that it needs, figures out what records it needs from each table (according to optimization, hopefully) and then gathers those records and assembles the result set. Yes, the resulting data definitely needs to be pulled down, but that is true for SQL Server as well. If optimization fails, VFP resorts to a table scan of the networked table, which can be very slow.
>
>Don't get me wrong...in a networked environment I seriously doubt that VFP could compete with SQL Server speedwise because even optimized queries cause several MB of CDX files to be pulled down at times. Queries can also be very flaky when "CDX skew" comes into play and a seemingly simple query suddenly takes longer because VFP decides to pull down a huger chunk of CDX because of the way a table is indexed. All of these problems essentially go away with a completely server-based solution.
>
>JoeK

Hi all,

This is all correct, but there are also non-traditional solutions out there.

In many cases, if you want to stay with FoxPro solution, you can speed up things dramatically with an idea of FoxPro Server (see Les Pinter article at http://www.lespinter.com/Articles/English/215.htm)

I am working on the enhancements for a huge FoxPro DOS 2.5 financial system now, and this approach allows the newly added ROR (Rate of Return) calculation module to:

1. Find the request file ( a DBF of 1 record with parameters in it) and get the parameters.
1. Retrieve the 300-500 records out of 5,000,000
2. Analyze the result cursor.
3. Add zero value ROR records (which are not stored to reduce the overall database size) into the gaps in the monthly ROR history.
4. Calculate 1, 2, 6, 9, 12 month, 2, 3, 5 years, YTD, and "Since Issue" Rate of Return for each of average 4-5 funds in the plan (plus summary for the plan as a whole).
5. Write a result DBF.
6. All this in 0.1 sec in average.(depending on what else the server is doing it is anywhere from 0.02 to 0.3 sec).

The amount of stored ROR records is about 10 GB, so the data are split into a number of tables, which also increases the performance - as each request goes not against the whole database, but only against a table where the data for the particular plan are stored. Here I used the techique from the Channel Tunnel application (see http://advisor.com/Articles.nsf/aidp/MATIV02)
All processing is done on the server, and the workstation which requested ROR retrieves only the result DBF with 5-6 records in it over the network.
Now show me a kind of SQL server which can provide the comparable results... :)
(don't get me wrong, I am not against SQL Server at all...)