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
. 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.
This new index type is a specialized index, designed for one thing.
INDEX ON DELETED() TAG DELETED BINARY
The new index type can be used with any NOT NULL
logical expression. Other restrictions preclude the use of a FOR
expression and ASCENDING
, and CANDIDATE
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
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
, 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.
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]
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
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()
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:
INDEX ON DELETED() TAG DELETED
This index is used to optimize both NOT 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
, additional NOT DELETED()
optimization is unnecessary.