Andy

Last fall we decided to convert several apps from FP/DOS and FP/Win to VFP. We started with a functional conversion of an app called TaskTrak and started testing. It was quickly discovered that queries that take 2 or 3 seconds in TaskTrak/DOS, take 20 to 30 seconds in TaskTrak/VFP.

In an effort to verify and improve this performance problem I copied a program that creates a temporary database for a Programmer Performance report and created a stand-alone program for testing. This test program performs a four step query process on the three largest databases in TaskTrak (Task.dbf, 69K records & 85.4MB; SubTask.dbf, 155K records & 131 MB and Time.dbf, 1M records & 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 5 to 10 runs.

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 over 3 times longer to run on the NT Server. While running these tests I had the Task Manager Performance window open and made the following observations. When running FPD the CPU Usage is pegged at 100 %, even when running in the background. Some users have reported that this behavior causes other applications to run very slow. 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". This article talks about a function that is new to VFP, it allows you to specify the amount of memory that VFP can use. The default value on my machine is about 82 MB. This is real memory plus virtual or disk memory. If this value is set lower it improves performance, because it forces VFP to only use real memory.

The following table lists the results of running the test program with different amounts of memory. In all cases the number is the average number of seconds for 3 runs.

Memory 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.

We also decided to run the tests with a Sniffer installed on my network node. The results of that test follow.

FoxPro DOS transferred 700 KB of data
FoxPro Windows transferred 6.5 MB of data
Visual FoxPro transferred 40 MB of data

The reason for the slow performance when running queries in VFP is quite obvious. After further research we have discovered that VFP needs to transfer the index files from the server to the client machine when performing a query. All of the index files for the test query are approximately 40 MB in size. Why VFP works this way only Microsoft knows and they are not telling.

According to the February 1997 "FoxTalk" article mentioned above, FPD & FPW manage memory for themselves. They are not good Win95 or WinNT clients, but they are great memory managers. VFP, on the other hand, lets Windows take care of its memory management. Unfortunately, this is required if an application is going to be a good Win95 or WinNT client, especially if that application is made by the same company that makes Windows. Unfortunately, Windows is NOT as good at managing memory as is FPD & FPW.

The solution to this problem was to create a DCOM Query Server. The calling program passes the name of a query/program file to the Query Server, it runs the query/program and creates a temporary result table on the server and returns the name of the temporary result table. The calling program then opens the temporary result table, uses it for the report and then deletes it.

Hope this helps.