I need help determining the memory requirements for
a system I am creating. Our development server does
not seem to have have enough (even though it is 128M)
and I need to be able to tell my client exactly what
the production server will need.

The system is a searchable database available through
the Web. The server is running standalone, with the
Web server and VFP on the same machine. Here are the
specs:

Table.dbf: 640Megs
Table.ftp: 250Megs
Table.idx: 24Megs
Table.phd: 21Megs (PhDBase index)
VFP: Version 5.0
OS: Windows NT 4.0 with SP2
Current Mem: 128Megs
System: Pentium Pro 200, two Ultra-SCSI HDs.
System and VFP stuff on one HD, swap
file and temp space on the second HD

I have finally been able to re-create my problem
consistently and here is what happens. I can run
this query twice, "SELECT Field FROM Table WHERE
BETWEEN( Income, lnLow, lnHigh) ORDER BY SortOrder
INTO CURSOR Results". These take <2 seconds. Next
I perform a few other functions of the system.

I then exit my system and from the command window I run the EXACT SAME query. This time it takes 5 minutes. During the first 20 seconds, free memory goes from 60M to 1M, file cache size goes from 15M to 106M. Also during the first 20 seconds, there are 500-800 page faults/second. Between 20 seconds and 5 minutes, page faults average 3000/second and I've seen a max of 22,000 in a second (using performance monitor)!!! After the query finishes if I close Table, file cache drops from 106M to 9M, and available physical memory goes from <1M up to 100M (measurements using task manager and confirmed using GlobalMemoryStatus).

I have called Miscrosoft, posted to NT and VFP newsgroups and have learned a little more, but haven't fixed the problem. It seems I need more memory but I don't know how much more. I am not even completely sure I need more RAM but I don't know what else to do.

What I don't understand is why the first queries take seconds and then the SAME query can then take several minutes. VFP still has 60Megs of physical memory (according to task manager and GlobalMemoryStatus) when the problem query starts. It then fills this 60Megs up immediately with file cache leaving under 1Meg for VFP. VFP then starts using swap space as RAM, causing the 3000 page faults/second. This I know, but I don't know what to do about it.

Can anyone suggest anything? Do I need more RAM? Surely I am not the first person to use a 800Meg table (640M + 250M memo)...

Any help will be greatly appreciated.

- Will Schmidt
schmidt@apex-it.com