Share via

Optimization of Tables and Indexes

  • Article

You can speed access to data in tables by using indexes and by using buffering efficiently. In addition, you can use Rushmore technology to optimize your queries.

Using Indexes

To speed access to data in a table, use indexes. Adding an index to a table speeds searches, especially if you're able to use Rushmore technology to optimize your search. Indexing also allows you to work with data in a particular order, such as viewing a customer table in order by last name.

If the records in a table have unique keys, create a primary or candidate index on the field. These types of indexes allow Visual FoxPro to validate the key at a low level, resulting in best performance.

In addition to indexing fields used for searching and sorting, you should also index any fields involved in a join. If you join two tables on fields that are not indexed, the join operation can take as much as hundreds of times longer.

An important feature of Visual FoxPro is that you can create an index on any expression. (In some database products, you can index only on fields.) This capability allows you to use indexes to optimize searching, sorting, or joining on combinations of fields, or on expressions derived from fields. For example, you can index a name field based on an expression that uses the SOUNDEX( ) function. That way, your application can provide extremely quick access to names that sound alike.

When adding indexes to your tables, you must balance the benefit you get in retrieval times against a performance loss when updating the table. As you add more indexes to your table, updates and inserts to the table are slower because Visual FoxPro needs to update each index.

Finally, avoid using indexes on fields that contain only a few discrete values, such as a logical field. In these cases, the index contains only a small number of entries, and the overhead of maintaining the index probably outweighs the benefit it provides when searching.

For details about how to index effectively when using Rushmore technology, see Using Rushmore to Speed Data Access.

Optimizing Joins

When you create joins using SELECT - SQL, the following situations can degrade performance and produce unexpected results:

  • Joining tables on data that is not a primary or unique key in one of the tables.
  • Joining tables containing empty fields.

To avoid these situations, create joins based on the relationship between primary keys in one table and foreign keys in the other. If you create a join based on data that is not unique, the end result can be the product of two tables. For example, the following SELECT - SQL statement creates a join, which can produce a very large result:

SELECT *;
 FROM  tastrade!customer INNER JOIN tastrade!orders ;
 ON  Customer.postal_code = Orders.postal_code

In the example, postal code uniquely identifies a location within a city, but has little value if your intent is to match customer rows and their order rows. The postal code doesn't necessarily uniquely identify a customer or an order. Instead, create a join using a statement such as the following:

SELECT *;
 FROM  tastrade!customer INNER JOIN tastrade!orders ;
 ON  Customer.customer_id = Orders.customer_id

In this example, the field customer_id uniquely identifies a specific customer and the orders belonging to that customer, and therefore creates a result set that combines the customer row with each order row.

In addition, use caution when joining tables with empty fields because Visual FoxPro will match empty fields. However, Visual FoxPro doesn't match fields containing null. When creating a join, qualify the field expressions in the join condition by testing for an empty string.

For example, if you think that the customer id field in the Orders table might be empty, use a statement such as the following to filter out order records with no customer number:

SELECT *;
 FROM  tastrade!customer INNER JOIN tastrade!orders ;
 ON  Customer.customer_id = Orders.customer_id; 
 WHERE tastrade!orders <> ""

Tip   You can also test for an empty string using the EMPTY( ) function, but including a function call within the filter expression is not as fast as comparing to a constant value.

Using the Project Manager

When you use the Project Manager, you can combine an unlimited number of programs and procedures into a single .app or .exe file. This can greatly increase program execution speed for a couple of reasons.

First, Visual FoxPro opens a program file and leaves it open. Later, when you issue a DO command on a program contained in the file, Visual FoxPro doesn't need to open an additional file.

Second, an application of only one or two files reduces the number of files necessary in the working directory. The speed of all file operations increases as the operating system has fewer directory entries to examine when opening, renaming, or deleting files.

For information on using the Project Manager to create applications, see Compiling an Application.

General Table and Index Optimization Hints

To create the fastest possible tables and indexes, follow the recommendations listed below.

  • If record or table buffering is not enabled, use INSERT - SQL instead of APPEND BLANK followed by REPLACE, particularly with an indexed table in a multiuser environment, because indexes only need to be updated once.
  • If you need to append a large number of records to an indexed table, it might be faster to remove the index, append the records, and then re-create the index.
  • In SQL statements, avoid function calls if possible, especially in statements that will return more than one record, because the statement must be reevaluated (and the functions called again) for each record. If you are creating a SQL statement with variable data, use name expressions or macro substitution in favor of the EVALUATE( ) function. A better strategy yet is to construct the entire statement dynamically, not just individual clauses. For more information, see Using Macros and Creating Name Expressions.
  • If you usually use a certain index order, you can improve performance by periodically sorting the table in this order.
  • Use .cdx instead of .idx files in multiuser environments because you can update one .cdx file faster than you can update multiple .idx files.

See Also

Optimizing Applications | Optimizing Your System | Using Rushmore™ to Speed Data Access | Optimizing Forms and Controls | Optimization of Programs