Warning this got very long...
As a part of Roxane's thread on Optimizing SQL with ==, the thread happily diverged into talking about VFP memory usage and Mac Rubel's tests regarding tuning the buffer usage to 50%.
Apart from that reference there is an article in Foxtalk Feb. 97 issue by Flavio Almeida and Walter Loughney regarding the same kind of findings. They also go into some detail as to the causes of the different behaviour of VFP over FPW.
In summary VFP is a native 32bit app and therefore negotiates with the operating system to get as much memory as it can. Almeida and Loughney say that Windows gets that memory allocation wrong and in fact gives too much as that amount includes paged memory which can sit on the disk. This can result in swap file thrashing.
I'd go along with that so far as it goes, except to say that it is possible for an application to discover how much physical memory it has rather than how much virtual memory and blocks of memory can be locked so that they aren't swapped out (although it isn't a particularly nice thing to do, especially under 95).
They make use of SYS(3050) to show the relative sizes of the buffers in foreground and background. In crude terms the default settings for me appear to be around 50% of the total memory available (physical+swap in NT 4.0 SP3), for foreground use and a fairly constant 14-15Mb in background.
They recommend modifying both foreground and background buffer sizes on any particular machine to optimise the memory usage based upon a simple non-index non-rushmore query, (if only life were this long to be this good).
Personally, I'd prefer this approach. If users aren't complaining or queries aren't generating grinding noises from the drive, leave well alone. But if there is a problem (and you are running NT, and if you aren't and the data size is this large why aren't you), setup a Performance monitor which shows the maxima and minima of available physical memory for the whole system and your actual application. See how the usage of memory changes during your query, if you find the application has gone into overcommit (live memory is partially in physical, partially in swap file), reduce your foreground buffer size to below the size where it starts to overcommit. Repeat until user satisifaction = value of satisfaction.
If you aren't running 95 you can still get some idea by running the app on NT but increasing the available physical memory by 2mb before the app is run, or by reducing the buffer size by around about 2mb less than on NT. That is all rule of thumb and not intended to be etched in stone :-).
This all assumes that the environment in which the application runs remains fairly constant, and in some restricted areas it does. But for the most part if an application is running on a user machine a user is pretty much always the main cause of any sudden change in a system's behaviour. This isn't to say its their fault (though the steely glare with which they are asked 'So what did you do today that was different?' may give them cause to think otherwise; but its often enough the reason. Likely its just that they started Excel or Word or somemsuch which sucked available memory.
Perhaps an optimum solution (in lieu of VFP doing the job properly itself), is to have as part of your applications state machine a process that checks the available physical memory periodically and adjusts the buffer size to suit. It might also make VFP a better behaved app to change buffer sizes around particularly hungry queries.
I think I've rambled more than enough now :-)
Simon
