I’ve been contracted to update and add new features to an existing VFP application. Several months into the process, I’ve been given a new requirement to limit the display of “client” records to the customer’s office where they were entered, but still maintain the over-all record set in a single file to facilitate reporting and statistical analysis for management. The potential size of the table is in the 80,000 to 100,000 record range.

The “clients” table is already tightly integrated with the UI and would be a major task to change, so I’m considering other possible solutions and am looking for advice on performance issues, since I have little previous experience with record sets of that size. The Stonefield Database Toolkit manages the data, so changes in data structure that don’t impact the UI are not difficult (thanks Doug!).

Solution one: add a “location” integer field to the “clients” table and set a filter on it. I’m concerned about performance issues, even with an index on the field.

Solution two: rename the “clients” table to “all-clients” (or some such) and create a parameterized view named “clients” that would then replace the current “clients” table in the DE of all relevant forms. There are a number of indexes that would have to be maintained to support incremental search and grid column header order selection. I have zero experience with views on a record set of that scale and am still concerned about performance and indexing issues.

Can anyone with experience in this area offer advice? Or another possible solution?