I decided to take a positive approach, to get brush off all the negative frustrations of the last couple of weeks and hope to contribute something to the community.

Over the years, we have dealt with a lot of performance questions over the last 2 decades, and I like to share my experience.
By far the number #1 cause of performance issues is data related. So it makes sense to investigate that first.

When you are developing a piece of software, the chances are that your development environment is not representative for the production environment at a client. Your application runs fine for you in the development environment, but clients are complaining it is dreadfully slow. For the question why you did not catch the problem in development, the following answers could apply.

1. The volume of data. A client is using your application every day, and possibly a large number of users. There is no way you can create that data on your own. Even with data generators, its very difficult to simulate the performance problems in your development database. So if you have the chance to use a large dataset of a client, possibly de-personalized, the better. You are in a better position to simulate and identify potential performance problems.

2. Network characteristics. This is even more important if you are accessing VFP tables over the network in a multi users environment. It is very hard to tune a VFP database over a LAN (don't even try a WAN) connection for multiple users. Indexes might help, but could hurt as well if they have low selectivity. File shared databases, like VFP could use opportunistic locking (See https://docs.microsoft.com/en-us/windows/win32/fileio/opportunistic-locks) , allowing to cache data on the local workstation, as long no other workstation is writing to that file (index/table/memo file). As soon as one other workstation (user) is going to write to the file, the file server is sending a message to all workstation to drop all local buffers and read directly from the network. This has a dramatic effect on performance.

This explains why it is working fine and fast, when you are working on the application al alone, and its dreadfully slow when 20 users are pounding away.
You can actually see the low lever calls, the op-locking mechanism at work when using a tool like process monitor. (https://docs.microsoft.com/en-us/sysinternals/downloads/procmon). You can see the amount of data from DBF, CDX and FTP retrieved from the network and how much time that takes.

For database servers, the effect is much less and could even perform very well over WAN connections as the network intensity is much and much less for the same amount of data to be retrieved.

3. Missing indexes. Again there is a huge difference between a VFP database and using a database server (e.g., MS-SQL / Oracle / MySQL). VFPs rushmore optimizer is a rule based optimizer, which means it will use any index that could potentially contribute to optimizing the query. It has to drag that down to the workstation and is processed locally. That sounds good, but isn't always. If you are searching or retrieving for patients your than 100 years, and they all are, you are better of not using the date_of_birth index as this index has low selectivity and only adds to network traffic and time needed to process the query. This even applies to a simple LOCATE FOR statement.

Database servers, usually have a cost-base optimizer. It will determine through the values in the where clause and the available statistics of the indexes whether it is useful to use such index. In the example above, it will just perform a simple table scan as this is the fasted way to retrieve the result.

4 Dragging too much data over the network
Again for VFP the situation is different and more difficult to tune. Though you might want to limit the fields you want to retrieve, in many cases it still will drag down all the field of the entire row. Excluding memo field might get you some performance boost as then the FPT file is not accessed, but in general there isn't much you can do to control what is dragged down to the workstation

For database servers, it is a little more straightforward. You should only retrieve or write data that you need. You can easily check the volume of data in SSMS (SQL server management studio) send from the database server to your application by entering and running queries there.

5. Opening tables/views. This often takes a lot of time over networks. The less tables/views you have to open, the quicker your forms will load.
For VFP, it sometimes pays of to open the tables you frequently need at the beginning of your application and leave them open, even if you have to open them again in another datasession. Opening and closing tables multiple times is slower because it is closing the handle on the network level. If you keep it open in a datasession you don't use for anything else, the opening and closing of the tables in forms will go quicker because the file handle on the network stays open.


All in all, most performance problems we have encountered are related to data. Sure having lots of code in refresh events, or calling refreshes too many times unnecessarily could also have negative effects on performance.

So what to do? if you can't really figure out whether the performance is slow. What if this problem is occurring at a client and you can't simply debug it because the VFP IDE is not installed there?

For this problem I usualy use

SET COVERAGE TO Log.csv

You might have a command window utility in your application where you can execute it, or have this build into your application and can activate it under a menu items or toolbar button, etc.

After executing the command you can run the slow part of your application and either issue a

SET COVERAGE TO

or close you applicaion. You can load the CSV file directly into excel and filter upon lines that take lets say more than a 1 second. From there on you can determine what is taking up all time. Is it a query or is it a tight loop?


That is it for now.


Walter,