What's New with Data in Visual FoxPro 9? (Cont.)

View and Query Designers

Unfortunately, due to the complexity of the SQL statements you can write with these enhancements, the Query and View Designers do not support many of the sub-query changes to SQL.

Also, with the hard-coded limits of SQL IN removed, the Designers no longer convert IN to INLIST(). The INLIST() function still has a limit of 24 elements.

Enhanced UNION Support

In addition to having no hard-coded limits for the number of UNIONs, you can now use a UNION inside the result set that is used by an INSERT INTO.

You can now also ORDER BY <fieldname> when using UNION. The referenced field must be present in the SELECT list for the last SELECT in the UNION.

Performance

Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority for Visual FoxPro. Visual FoxPro 9 enhances the data engine even further.

Binary Indexes

This new index type is a specialized index, designed for one thing.

The new index type can be used with any NOT NULL logical expression. Other restrictions preclude the use of a FOR expression and ASCENDING, DESCENDING, UNIQUE, and CANDIDATE keywords.

SET ORDER TO is not supported and the INDEX ON command sets the current order to 0. Also, you cannot use a Binary index with any Seek operation.

The big advantage of a Binary index is its size. A Binary index for a table with 8,000,000 records is approximately 30 times smaller (1.1Mb versus 31.5Mb). Smaller means faster I/O and faster APPEND and REPLACE, all with the same Rushmore optimization as a non-binary index on the same expression.

There is a trade-off to consider. Rushmore optimization is faster if the amount of records returned is more than 3% of the total records (about 92% faster when all records match the condition). However, Rushmore optimization is slower if the amount of records returned is less than 3% (about two times slower when 0 records match the condition). It is likely that the 3% threshold will become smaller as the number of records in the table increases.

Turning your DELETED indexes into Binary indexes is an easy way to start taking immediate advantage of Visual FoxPro 9 performance enhancements. Just be sure that all clients accessing your data are upgraded, as this new index cannot be read by prior versions.

Rushmore Optimizations

There are a few new Rushmore optimizations that do not require changes to data and index structures.

Top N [PERCENT], an optimization made to SQL, provides improved performance. This operation returns only the top number or percent of records in a result set as controlled in the ORDER BY clause. This change in Visual FoxPro 9 eliminates records from the sort process that don't belong in TOP N as early as possible, reducing comparison operations and decreasing file I/O in low memory situations. This also has the side-effect of only returning exactly N [PERCENT] records.

In previous versions, if there was a tie for nth place, all records that matched the tie were included, resulting in getting back more than N records.

If this change in behavior is not desired, consider bracketing the SQL call with SET ENGINEBEHAVIOR 80.

The only limitation to this optimization is that TOP N PERCENT cannot be used unless the entire result set can be read into memory at once.

When appropriate, Visual FoxPro 9 uses filtered indexes to optimize MIN() and MAX() aggregate functions in FOR DELETED() and FOR NOT DELETED() only. This improves MIN()/MAX() performance, if such an index exists.

The Like "sometext%" operator is now fully optimizable when the string ends in a wildcard. (Note that this is not the case when the comparison value begins with a wildcard or when the wildcard is embedded within the string.) This optimization scenario works like WHERE field = "sometext".

More INDEX Smarts

Visual FoxPro 9 is even smarter in how it utilizes existing indexes to achieve Rushmore optimization. For example:

This index is used to optimize both NOT DELETED() and DELETED() conditions without the presence of a tag created by INDEX ON NOT DELETED().

Just like the MIN()/MAX() optimization, Visual FoxPro 9 uses a FOR NOT DELETED() filter on an index to optimize a DELETED() or NOT DELETED() query. Whenever it is possible to determine that a condition should filter on DELETED() or NOT DELETED(), a filtered index FOR DELETED() or FOR NOT DELETED() is used in the event that no non-filtered indexes exist. Take this upgrade opportunity to review the indexes you currently deploy. If you are unable to use a binary index, you may find that with these optimizations, you can at least drop a few existing indexes.

If only indexes filtered FOR NOT DELETED() were used for Rushmore optimization and SET DELETED is ON, additional NOT DELETED() optimization is unnecessary.

SYS(3055)

If you are not bound by a low memory environment, consider using SYS(3055) to set the complexity level of the FOR and WHERE clauses to its maximum value of 2040 when you set up the environment for your applications. This allows more complex SQL statements and more values with IN. See the Help files for a list of other commands and functions that are affected by this setting.