Progress
Database Design
Guide
Bracketing
Having selected one or more indexes to satisfy a query, Progress tries immediately to isolate the smallest necessary index subset, so as to return as few records as possible. This is called bracketing. Careful query design can increase the opportunities for bracketing, thereby preventing Progress from scanning entire indexes and examining all records. The rules for bracketing are simple:
The following table provides some bracketing examples:
Sample WHERE Clause Indexes Used Brackets Cust-Num None Cust-Num None Name Name Sales-Rep Sales-Rep Country-Post Country-Post Comments
Country-Post Country
Postal-Code
The following recommendations are intended to help you maximize query performance. They are only recommendations, and you may choose to ignore one or more of them in specific circumstances.
- Avoid joining range matches with AND.
- Avoid ORs if any expression on either side of the OR does not use an index (or all its components), be aware that Progress must scan all records using the primary index.
- With word indexes, avoid using AND with two wildcard strings, either in the same word index (WHERE comments CONTAINS “fast* & grow*”) or in separate word indexes (WHERE comments CONTAINS “fast*” AND report CONTAINS “ris*”).
- Avoid WHERE clauses that OR a word index reference and a non-indexed criterion (WHERE comments CONTAINS “computer” OR address2 = “Bedford”).
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |