I am still trying to find a solution to my VFP query performance problem. My post from a month ago is repeated below. I recently got the network engineering department to attach a sniffer to my port while I was running my test queries in FPD 2.6, FPW 2.6 and VFP 6.0.

The tests are using the same data and code on an NT Server, the only difference is the version of FoxPro. The queries are optimized. We discovered that FPD transfers 700 KB of data from the server to my machine during the queries. FPW transfers 6.5 MB and VFP transfers 40 MB. This explains the large time difference. The question is, why is VFP transfering so much more data?

Out of curiosity I decided to delete all index tags from all of the databases and rerun the tests. FPD then transfers 98 MB of data from the server to my machine during the queries. FPW transfers 91 MB and VFP transfers 84 MB. The query times are all about the same, 1 minute to 1.5 minutes.

Does anyone know anything about the internal workings of FPD, FPW or VFP during SQL queries? Any ideas on how to speed up VFP?



My post from a month ago.
***********************************************************
I am in the process of converting a FPD application to VFP. The conversion was looking good until a Beta version was released in late August for group testing. It was quickly discovered that queries that take 2 or 3 seconds in the FPD version, take 20 to 30 seconds in the VFP version.

In an effort to verify and improve this performance problem, I copied a program that creates a temporary database for a report and created a stand-alone program for testing. This test program performs a four step query process on the three largest databases in the application (Task.dbf, 69K recs & 85.4MB; SubTask.dbf, 155K recs & 131 MB and Time.dbf, 1M recs & 62MB) and creates a temporary database. I added code to time the query process. I then ran the program in FPD, FPW and VFP with the program and the data on different drives. The results are listed below. In all cases the number is the average number of seconds for 10 runs.

(Does anybody know how to format tables so thay are not all run together?)

Local Drive NT Server
FPD 0.04 2.37
FPW 0.25 7.81
VFP 8.29 27.09

As you can see VFP takes a lot longer to run on the NT Server. While running these tests I had the WinNT Task Manager Performance window open. When running FPD the CPU Usage is pegged at 100%. When running the Local test it stays at 100%, However, when running the Server test it goes down to 3 or 4% while waiting for results. When running FPW the CPU Usage stays below 10% even when running the tests. When running VFP the CPU Usage goes up to 90 to 95% during the entire Local test. It stays at about 5 to 10% during the Server test and goes up to about 60% only for the last second or two.

During my search for a solution to this performance problem I discovered an article in the February 1997 issue of "FoxTalk". It talks about the SYS(3050) function. The default value on my machine is about 82MB. The following table lists the results of running the test program with different values for SYS(3050,1,x). In all cases the number is the average number of seconds for 3 runs.

Memory(x) Local Drive NT Server
10 MB 1.49 23.70
20 MB 1.55 25.23
30 MB 2.52 25.08
40 MB 6.83 24.13
50 MB 8.82 24.78
60 MB 10.45 23.58
70 MB 11.19 24.65

As you can see this function improves Local performance, but has no effect on the NT Server performance.

I also decided to test the program on different servers that I have access to. I copied the program and nearly 300 MB of data to test directories and ran the test. In all cases the number is the average number of seconds for 5 runs.

Server Time
Arms (Novell) 36.67
Jupiter (NT) 23.10
Mars (NT) 28.10
Mercury (NT) 46.11
Saturn (NT) 30.35

Can anyone give me some suggestions on how to speed up the server performance? I am meeting with the Server administrators on Friday. They believe that VFP is the problem and that it should be abandonded. I think it is a Server problem, but I do not know how to track it down, let alone solve it.

Any help will be appreciated.

Thank you.
*******************************************