When records are marked for deletion in Visual FoxPro, they are not physically removed until the PACK command is called. Therefore, these records remain visible and accessible while performing basic table operations unless you set the SETDELETED command to ON, which hides these records from basic operations.

To help ensure that queries use Rushmore optimization, some developers create an index tag for deleted records by using the DELETED(В ) function in the index expression. The DELETED(В ) function determines whether a record is marked for deletion. For example, the following code creates an index named Deleted using an index expression that includes the DELETED(В ) function that selects records marked for deletion:

В	Copy Code
INDEX ON DELETED() TAG Deleted

However, performance issues can still exist with this method, usually when working with large data sets in a LAN/WAN networking environment where deleted records are scattered in a table and when transmitting a large index over the network.

Instead, you can create and use a binary index, which optimizes the bit that marks records for deletion, to improve performance when you set the SET DELETED command to ON. You can create a binary index by choosing the Binary index type in the Table Designer or by including the BINARY keyword in the INDEX command. For example, the following code creates a binary index named myDeleted that selects records marked for deletion:

В	Copy Code
INDEX ON DELETED() TAG DeletedTag BINARY

For more information, see Visual FoxPro Index Types, DELETED(В ) Function, and SET DELETED Command.

Rushmore Optimization for Indexes Based on Deleted Records

For indexes based on deleted records, Visual FoxPro includes the following Rushmore optimization enhancements in the SQL engine:

INDEX ON NOT(DELETED()) optimizes NOT(DELETED()) and DELETED() query conditions. In versions prior to Visual FoxPro 9.0, only DELETED() was optimized.

For example, the following queries are now optimized:

В	Copy Code
CLEAR CLOSE DATABASE ALL SYS(3054,1) CREATE TABLE myTable (f1 I) INDEX ON NOT(DELETED()) TAG NotDel SET DELETED OFF SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable INTO CURSOR tempCursor INDEX ON DELETED() TAG Del SET DELETED OFF SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable INTO CURSOR tempCursor CLOSE DATABASE ALL

Copy Code

CLEAR
CLOSE DATABASE ALL
SYS(3054,1)
CREATE TABLE myTable (f1 I)
INDEX ON NOT(DELETED()) TAG NotDel

SET DELETED OFF
SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor

SET DELETED ON
SELECT * FROM myTable INTO CURSOR tempCursor
INDEX ON DELETED() TAG Del

SET DELETED OFF
SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor

SET DELETED ON
SELECT * FROM myTable INTO CURSOR tempCursor
CLOSE DATABASE ALL

INDEX ON ... FOR DELETED(В ) or INDEX ON ... FOR NOT(DELETED()) optimizes DELETED() or NOT(DELETED()) query conditions respectively when INDEX ON DELETED() or INDEX ON NOT(DELETED()) is not present.

For example, the following queries are now optimized:

В	Copy Code
CLEAR CLOSE DATABASES ALL SYS(3054,1) CREATE TABLE myTable (f1 I) INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED()) INDEX ON f1 TAG f1_Del FOR DELETED() SET DELETED OFF SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable INTO CURSOR tempCursor INDEX ON DELETED() TAG DeletedRec SET DELETED OFF SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable INTO CURSOR tempCursor CLOSE DATABASES ALL

Copy Code

CLEAR
CLOSE DATABASES ALL
SYS(3054,1)
CREATE TABLE myTable (f1 I)
INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED())
INDEX ON f1 TAG f1_Del FOR DELETED()

SET DELETED OFF
SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor

SET DELETED ON
SELECT * FROM myTable INTO CURSOR tempCursor
INDEX ON DELETED() TAG DeletedRec

SET DELETED OFF
SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor

SET DELETED ON
SELECT * FROM myTable INTO CURSOR tempCursor
CLOSE DATABASES ALL

When Visual FoxPro can determine that a query should not return deleted or undeleted records, and if no unfiltered index is present, it uses INDEX ON <expression> ... FOR DELETED() or INDEX ON <expression> FOR NOT(DELETED()).

For example, the following queries are now optimized:

В	Copy Code
CLEAR CLOSE DATABASE ALL SYS(3054,1) CREATE TABLE myTable (f1 I,f2 I) INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED()) INDEX ON f1 TAG f1_Del FOR DELETED() SET DELETED OFF SELECT * FROM myTable WHERE ; (NOT(DELETED()) AND f1>3) OR ; && Tag f1_NotDel used for optimization. (DELETED() AND f1<3) ; && Tag f1_Del used for optimization. INTO CURSOR tempCursor SELECT * FROM myTable WHERE ; (f1>3 AND NOT(DELETED())) OR ; && Not optimized. f1>3 comes first. (f1<3 AND DELETED()) ; && Not optimized. f1<3 comes first. INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable WHERE ; (f1>3000) OR ; && Tag f1_NotDel used for optimization. (f1<1000) ; && Tag f1_NotDel used for optimization. INTO CURSOR tempCursor INDEX ON f1 TAG f1 SET DELETED OFF SELECT * FROM myTable WHERE ; (NOT(DELETED()) AND f1>3) OR ; && Tag f1 used for optimization. (DELETED() AND f1<3) ; && Tag f1 used for optimization. INTO CURSOR tempCursor SET DELETED ON SELECT * FROM myTable WHERE ; (f1>3000) OR ; && Tag f1 used for optimization. (f1<1000) ; && Tag f1 used for optimization. INTO CURSOR tempCursor CLOSE DATABASE ALL

Copy Code

CLEAR
CLOSE DATABASE ALL
SYS(3054,1)
CREATE TABLE myTable (f1 I,f2 I)
INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED())
INDEX ON f1 TAG f1_Del FOR DELETED()

SET DELETED OFF
SELECT * FROM myTable WHERE ;
  (NOT(DELETED()) AND f1>3) OR ;    && Tag f1_NotDel used for optimization.
  (DELETED() AND f1<3) ;       && Tag f1_Del used for optimization.
  INTO CURSOR tempCursor
SELECT * FROM myTable WHERE ;
  (f1>3 AND NOT(DELETED())) OR ;   && Not optimized. f1>3 comes first.
  (f1<3 AND DELETED()) ;      && Not optimized. f1<3 comes first.
  INTO CURSOR tempCursor

SET DELETED ON
SELECT * FROM myTable WHERE ;
  (f1>3000) OR ;       && Tag f1_NotDel used for optimization.
  (f1<1000) ;         && Tag f1_NotDel used for optimization.
  INTO CURSOR tempCursor
INDEX ON f1 TAG f1

SET DELETED OFF
SELECT * FROM myTable WHERE ;
  (NOT(DELETED()) AND f1>3) OR ;   && Tag f1 used for optimization.
  (DELETED() AND f1<3) ;    && Tag f1 used for optimization.
  INTO CURSOR tempCursor

SET DELETED ON 
SELECT * FROM myTable WHERE ;
  (f1>3000) OR ;       && Tag f1 used for optimization.
  (f1<1000) ;      && Tag f1 used for optimization.
  INTO CURSOR tempCursor
CLOSE DATABASE ALL

If only indexes with NOT(DELETED()) filter expressions are used for Rushmore optimization and SET DELETED is set to ON, then additional optimization using NOT(DELETED()) is not performed as it is unnecessary.

SQL Optimizations for MIN(В ) and MAX(В ) Aggregate Functions

When appropriate, Visual FoxPro uses FOR DELETED() and FOR NOT(DELETED()) filter expressions to optimize MIN(В ) and MAX(В ) aggregate functions. Visual FoxPro uses filtered indexes based on DELETED() index expressions when available.

Filtered indexes that use FOR NOT(DELETED()) can provide additional optimization with SQL queries with any SET DELETED setting. However, a filtered index tag with FOR DELETED() provides benefits only when SET DELETED is set to OFF.

Note:
These optimizations are not Rushmore-based; therefore, they do not appear in the results returned by SYS(3054) - Rushmore Query Optimization Level.

В	Copy Code
CLEAR SET SAFETY OFF CLOSE DATABASE ALL CREATE TABLE myTable (f1 I) INDEX ON f1 TAG f1 SET DELETED OFF SELECT MAX(f1) FROM myTable INTO CURSOR temp1 * tag f1 is used to optimize MAX(f1) SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1 * MAX(f1) is not optimized and slow SET DELETED ON SELECT MAX(f1) FROM myTable INTO CURSOR temp1 * MAX(f1) is not optimized and slow SELECT myTable INDEX ON f1 TAG f1Del FOR DELETED() INDEX ON f1 TAG f1NotDel FOR NOT(DELETED()) SET DELETED OFF SELECT MAX(f1) FROM myTable INTO CURSOR temp1 * tag f1 is used to optimize MAX(f1) SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1 * tag f1Del is used by MAX(f1) SET DELETED ON SELECT MAX(f1) FROM myTable INTO CURSOR temp1 * tag f1NotDel is used to optimize MAX(f1) CLOSE DATABASE ALL

Copy Code

CLEAR
SET SAFETY OFF
CLOSE DATABASE ALL

CREATE TABLE myTable (f1 I)
INDEX ON f1 TAG f1

SET DELETED OFF
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1 is used to optimize MAX(f1)

SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1
* MAX(f1) is not optimized and slow

SET DELETED ON
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* MAX(f1) is not optimized and slow

SELECT myTable
INDEX ON f1 TAG f1Del FOR DELETED()
INDEX ON f1 TAG f1NotDel FOR NOT(DELETED())

SET DELETED OFF
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1 is used to optimize MAX(f1)

SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1
* tag f1Del is used by  MAX(f1)

SET DELETED ON
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1NotDel is used to optimize MAX(f1)

CLOSE DATABASE ALL

Rushmore Optimization for Indexes Based on Deleted Records

SQL Optimizations for MIN(В ) and MAX(В ) Aggregate Functions

See Also

Other Resources