1 / 87

Indexes and Performance

Indexes and Performance. Supercharging Your Progress Applications. Paul Guggenheim. About Paul Guggenheim & Associates. Working in Progress since 1984 and training Progress programmers since 1986 Designed seven comprehensive Progress courses covering all levels of expertise

nuri
Download Presentation

Indexes and Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Indexes and Performance Supercharging Your Progress Applications Paul Guggenheim

  2. About Paul Guggenheim & Associates • Working in Progress since 1984 and training Progress programmers since 1986 • Designed seven comprehensive Progress courses covering all levels of expertise • The Keys to OpenEdge Training Course released in 2007. • White Star Software Principal • TailorPro Consultant and Reseller • Tools4Progress Partner • Major consulting clients include Acument Global Technologies, American Academy of Orthopaedic Surgeons, Berlin Industries, Foxwoods Casino, Sterling Machine and US Tsubaki. http://www.pgasmarts.com

  3. Goals of this Presentation • Educate the audience on how Progress treats indexes • Suggest ways to take advantage of the index rules and ways to avoid the pitfalls.

  4. Index Performance Impacts

  5. Sample Table and Indexes

  6. Index Benefits 1.Rapid record retrieval 2. Specified record order 3.Enforced uniqueness

  7. Index Costs 1.Index maintenance overhead occurs during an add or delete of a record or modification of an index component. 2. Additional disk space

  8. General Indexing Properties • Bracketing affects rapid record retrieval • Sorting affects specified record order

  9. Bracketing • Using an index to read a subset of records from a table based upon conditions specified in WHERE,OF and USING clauses.

  10. Sorting • Reading records in a specified order may be accomplished by using an index. However, if it cannot be accomplished by using an index, then a sort table is built.

  11. Index Rules Overview

  12. Manual Index Selection • If USE-INDEX is used in the Record Phrase, the requested index is always used, regardless of its efficiency. • If a RECID or ROWID is used in the WHERE clause, the record is always retrieved using the RECID or ROWID, unless USE-INDEX is used.

  13. Index Rules Overview • Factors Progress considers when deciding which index(es) to use are: • Unique indexes • Equality matches • Range matches • Sort matches • Primary indexes • Word indexes

  14. Equality Matches • An equality match occurs whenever a field is equal to (=, EQ) an expression in an OF, WHERE, or USING clause. • If the expression includes a reference to any fields in the same record buffer, then the equality match is not active.

  15. Equality Matches

  16. Range Matches • A range match occurs when a field is compared with an expression in a WHERE clause using either: • Greater than (>, GT) • Greater than or equal to (>=, GE) • Less than (<, LT) • Less than or equal to (<=, LE) • Begins with (BEGINS)

  17. Range Matches • BEGINS counts as two range matches. • Why? • Because BEGINS is essentially a GE valueANDLT next-value. For example, BEGINS “B” is treated as GE “B” AND LT “C”.

  18. Range Matches • Not equals (<>, NE) does not count as a range match. Even though x<> 4 is logically the same as x<4 OR x>4, Progress treats it differently. • If the expression includes a reference to any fields in the same buffer, then the range match is not active.

  19. Range Matches • A CONTAINS is neither an equality nor a range match. It is a word index operator.

  20. Range Matches

  21. Active Equality & Range Matches • An active match is one that can be used by Progress to select an index, and select a bracket on that index. • AEM is an acronym for Active Equality Match. • ARM is an acronym for Active Range Match.

  22. Active Equality & Range Matches • Beginning with Version 7, an equality or range match is considered active if no conditions exist that either standalone or connected with the OR operator use: 1.A non-leading component 2.Any field in the same record buffer 3.The NOT operator or not equal (<>, NE)

  23. Bracketing Rules • If a leading component of an index has an active equality match, then it can be bracketed and the next component of that index is examined for bracketing. • If a leading component of an index has an active range match, then it can be bracketed but remaining components of that index cannot be bracketed.

  24. Bracketing Rules

  25. Bracketing on cust-order Index

  26. Sort Matches • A sort match is present whenever a field and not an expression appears in a BY phrase. • You can have multiple BY phrases; each counts as one sort match as long as they are sorted in the same order as they are in the index.

  27. Sort Matches

  28. Multiple Index Rules

  29. Multiple Index Rules • Since a word index is such a powerful search mechanism, it is always used if a CONTAINS is found in the WHERE clause. • Next, Progress determines whether the AND or OR connectors are used and applies the appropriate set of rules, if any.

  30. Multiple Index Rules – Word Index • When at least one criteria utilizes a word index, at least the word index will be used, and other indexes are used if Rule 2 and Rule 3 are met.

  31. Multiple Index Rules – AND • More than one index can be used with AND when: • All components of each index are involved in an active equality match. • None of the indexes involved in the active equality match are unique.

  32. Multiple Index Rules – AND • When multiple indexes are used with anAND, the final results list is an intersection of the results list for each index.

  33. Multiple Index Rules – AND

  34. Multiple Indexes – AND • Why such strict AND rules? • The shaded area in the diagram is relatively small, normally reflecting a relatively small number of records returned. Therefore, it is usually more efficient to use a single index, rather than pull records efficiently from multiple indexes only to discard them.

  35. Multiple Index Rules – OR • More than one index can be used with OR when both criteria utilize at least the leading component of an index. Unlike AND: • Only the leading components of any index must be used. • Range matches as well as equality matches may be used.

  36. Multiple Index Rules – OR • When multiple indexes are used with anOR, the final results list is a union of the results lists of the indexes, with duplicates discarded.

  37. Multiple Index Rules – OR • Why are OR rules not as strict as AND? • Because OR is a union and not an intersection, fewer records will tend to be discarded. Therefore, the efficiency of reading records based on leading components has a much greater positive affect on performance.

  38. Single Index Rules 1. Unique index with fewest components where all components are used in active equality matches 2. More active equality matches 3. More active range matches 4. All components used in an active equality match 5. More sort matches 6. The primary index 7. The first index alphabetically

  39. Single Index Rules – Rule 1 • A unique index with all of its components used in active equality matches (AEMs) will be chosen, even if another index has more active equality matches, since using all components of a unique index in AEMs means there’s a direct hit on a single record.

  40. Single Index Rules – Rules 2 to 7 • Rules 1 to 4 are designed to choose the smallest bracket of records. • After bracketing, Progress looks for the minimal amount of sorting needed, or arbitrarily selects the primary index, or first index alphabetically. (Single Index Rules 5, 6 and 7)

  41. Counting AEMs and ARMs • When counting AEMs and ARMs (active range matches), remember to apply bracketing rules for counting. • If the comparison is an AEM, then the next component comparison may be counted for either an AEM or an ARM.

  42. Counting AEMs and ARMs • When counting AEMs and ARMs (active range matches), remember to apply bracketing rules for counting. • If the comparison is an ARM, then no subsequent component comparison may be counted as either an AEM or ARM.

  43. Single Index Rules – Rule 4 • Rule 4 tells Progress that if two possible indexes have the same number of AEMs, then look to see if all components are used in AEMs for any of the indexes.

  44. Single Index Rules – Rule 4 • Progress assumes that an index with all components chosen will be a smaller bracket than an index where not all the components are chosen.

  45. Single Index Rules – Rules 5 to 7 • Rule 5 – Sort Matches • Progress tries to minimize sorting • Rule 6 – Primary Index • If nothing is better and it is still available, Progress will use the primary index

  46. Single Index Rules – Rules 5 to 7 • Rule 7 – First Index Alphabetically • If the primary index has been disqualified when compared to other indexes, Progress chooses the first index alphabetically of the indexes remaining.

  47. Counting Records • A quick and easy way to count records is to use the SQLSELECT statement with the COUNT(*) function.

  48. Counting Records /*cntsql.p */ form with frame a. select count(*) column-label ‘Last Name!Begins C’ from student where slastname begins ‘C’ with frame a. select count(*) label ‘State = CA’ from student where stcode = ‘CA’ with frame a.

  49. Compiling with XREF • You can use the XREF option of the COMPILE statement to check which indexes you are using. • Look for lines in the cross-reference file containing the word “SEARCH”.

  50. Compiling with XREF /* oneidx.p */ for each student where slast-name = "Kramer": display student-id slast-name sfirst-name. end. XREF reference ...SEARCH school.student name

More Related