Browse DevX
Sign up for e-mail newsletters from DevX


What's New with Data in Visual FoxPro 9? : Page 3

The Microsoft Visual FoxPro team has a great reputation for responding to community requests and version 9 reflects many such changes, most notably changes to the data engine that enhance data access and database application development.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority.
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.

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 number of records returned is more than three percent 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 three percent (about two times slower when no records match the condition). It is likely that the three percent 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" queries.

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date