Indexes and Performance Supercharging Your Progress Applications BP0550: Paul Guggenheim

BP0550: Indexes and Performance - 2 About Paul Guggenheim & Associates Working in Progress since 1984 and training Progress programmers since 1986 Working in Progress since 1984 and training Progress programmers since 1986 Designed six comprehensive Progress courses covering all levels of expertise Designed six comprehensive Progress courses covering all levels of expertise Developers of the Sharp Menu System and the S.M.A.R.T.S. sales force automation package Developers of the Sharp Menu System and the S.M.A.R.T.S. sales force automation package Major consulting clients include Bank One, Textron Fastening Systems, and American Academy of Orthopaedic Surgeons Major consulting clients include Bank One, Textron Fastening Systems, and American Academy of Orthopaedic Surgeons DWP North American Distributor DWP North American Distributor

BP0550: Indexes and Performance - 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.

BP0550: Indexes and Performance - 4 Index Performance Impacts

BP0550: Indexes and Performance - 5 Sample Table and Indexes

BP0550: Indexes and Performance - 6 Index Benefits Rapid record retrieval Specified record order Enforced uniqueness

BP0550: Indexes and Performance - 7 Index Costs Index maintenance overhead occurs during an add or delete of a record or modification of an index component Additional disk space

BP0550: Indexes and Performance - 8 General Indexing Properties Bracketing affects rapid record retrieval Sorting affects specified record order

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

BP0550: Indexes and Performance - 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.

BP0550: Indexes and Performance - 11 When to Create an Index Unique index Foreign keys Reducing record reads A. A. Date fields B. B. Logical fields Composite names Description fields

BP0550: Indexes and Performance - 12 Unique Index Relational database design requires that there should be at least one index per table that can uniquely identify each record in that table. Progress doesnt require an index to be created, in which case it automatically creates a default index in dbkey order. – –This index may or may not be chronological, since dbkeys can be re-used.

BP0550: Indexes and Performance - 13 Foreign Keys Create indexes for all foreign keys of a table. A foreign key is a key in a table that is a unique key in another table. This key represents a parent record to an existing record. Indexing foreign keys gives parent records fast access to all child records.

BP0550: Indexes and Performance - 14 Foreign Keys /* Example 1 */ for each student, each stuchrg of student: each stuchrg of student: display stuchrg. display stuchrg.end students 1000 students 40 charges per student 40 charges per student 40,000 student/charge records 40,000 student/charge records Using index Student-ID-Charge-Date: 41,000 record reads Using index Student-ID-Charge-Date: 41,000 record reads Not using index Student-ID-Charge-Date: 40,001,000 record reads Not using index Student-ID-Charge-Date: 40,001,000 record reads

BP0550: Indexes and Performance - 15 Reducing Record Reads Create indexes on fields used for record selection. Since the smaller the bracket the more efficient the index, you should apply bracketing concepts.

BP0550: Indexes and Performance - 16 Bracketing with Date Fields Index date fields Users like to view records for certain time periods. – –Without an index on charge-date, the search would have to read every record in the table.

BP0550: Indexes and Performance - 17 Bracketing with Date Fields /* Example 2 */ for each stuchrg where charge-date ge 9/1/96 and charge-date le 9/2/96: and charge-date le 9/2/96: display stuchrg. display stuchrg.end.

BP0550: Indexes and Performance - 18 Bracketing with Logical Fields Indexing can be effective with logical fields if the split between the yes and no values will not be 50-50, often creating a smaller bracket. Avoid using NOT in front of a logical field, as in Example 4, since Progress wont bracket. Use field = NO instead.

BP0550: Indexes and Performance - 19 Bracketing with Logical Fields /* Example 3 */ for each student where graduated = no: display student. display student.end. /* Example 4 */ for each student where not graduated: display student. display student.end.

BP0550: Indexes and Performance - 20 Composite Names Consider building composite indexes for name fields (e.g. last-name + first-name), since users like to see records in alphabetical order.

BP0550: Indexes and Performance - 21 Description Fields Description fields should be word indexed, so that records can be accessed using specific keywords. Examples: – –Part-description in an item table – –Order-comments in an order table

BP0550: Indexes and Performance - 22 Bracketing with Word Indexes /* word.p */ def var wordvar as char format "x(30)" label "Words". repeat: update wordvar. update wordvar. for each stuchrg for each stuchrg where student-charge-description contains wordvar: where student-charge-description contains wordvar: display stuchrg. display stuchrg. end. /* for each */ end. /* for each */ end. /* repeat */

BP0550: Indexes and Performance - 23 No Bracketing with Matches /* matches.p */ def var wordvar as char format "x(30)" label "Word Match". label "Word Match".repeat: update wordvar. update wordvar. for each stuchrg for each stuchrg where student-charge-description matches wordvar: where student-charge-description matches wordvar: display stuchrg. display stuchrg. end. /* for each */ end. /* for each */ end. /* repeat */

BP0550: Indexes and Performance - 24 Index Rules Overview

BP0550: Indexes and Performance - 25 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.

BP0550: Indexes and Performance - 26 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

BP0550: Indexes and Performance - 27 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.

BP0550: Indexes and Performance - 28 Equality Matches

BP0550: Indexes and Performance - 29 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 (

BP0550: Indexes and Performance - 30 Range Matches BEGINS counts as two range matches. Why? – –Because BEGINS is essentially a GE value AND LT next-value. For example, BEGINS B is treated as GE B AND LT C.

BP0550: Indexes and Performance - 31 Range Matches Not equals (, NE) does not count as a range match. Even though x 4 is logically the same as 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.

BP0550: Indexes and Performance - 32 Range Matches A CONTAINS is neither an equality nor a range match. It is a word index operator.

BP0550: Indexes and Performance - 33 Range Matches

BP0550: Indexes and Performance - 34 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. AEM is an acronym for Active Equality Match. ARM is an acronym for Active Range Match. ARM is an acronym for Active Range Match.

BP0550: Indexes and Performance - 35 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: A non-leading component Any field in the same record buffer The NOT operator or not equal (, NE)

BP0550: Indexes and Performance - 36 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.

BP0550: Indexes and Performance - 37 Bracketing Rules

BP0550: Indexes and Performance - 38 Bracketing on cust-order Index

BP0550: Indexes and Performance - 39 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.

BP0550: Indexes and Performance - 40 Sort Matches

BP0550: Indexes and Performance - 41 Multiple Index Rules

BP0550: Indexes and Performance - 42 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.

BP0550: Indexes and Performance - 43 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.

BP0550: Indexes and Performance - 44 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.

BP0550: Indexes and Performance - 45 Multiple Index Rules – AND When multiple indexes are used with an AND, the final results list is an intersection of the results list for each index.

BP0550: Indexes and Performance - 46 Multiple Index Rules – AND

BP0550: Indexes and Performance - 47 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.

BP0550: Indexes and Performance - 48 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.

BP0550: Indexes and Performance - 49 Multiple Index Rules – OR When multiple indexes are used with an OR, the final results list is a union of the results lists of the indexes, with duplicates discarded.

BP0550: Indexes and Performance - 50 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.

BP0550: Indexes and Performance - 51 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

BP0550: Indexes and Performance - 52 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 theres a direct hit on a single record.

BP0550: Indexes and Performance - 53 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)

BP0550: Indexes and Performance - 54 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.

BP0550: Indexes and Performance - 55 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.

BP0550: Indexes and Performance - 56 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.

BP0550: Indexes and Performance - 57 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.

BP0550: Indexes and Performance - 58 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

BP0550: Indexes and Performance - 59 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.

BP0550: Indexes and Performance - 60 Counting Records A quick and easy way to count records is to use the SQL SELECT statement with the COUNT(*) function.

BP0550: Indexes and Performance - 61 Counting Records /*cntsql.p */ form with frame a. select count(*) column-label Last Name!Begins C from student where slast-name begins C from student where slast-name begins C with frame a. with frame a. select count(*) label State = CA from student where st-code = CA from student where st-code = CA with frame a. with frame a.

BP0550: Indexes and Performance - 62 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.

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

BP0550: Indexes and Performance - 64 Compiling with XREF /* mltidx.p */ for each student where slast-name = "Kramer" or country-code = "USA": or country-code = "USA": display student-id slast-name sfirst-name. display student-id slast-name sfirst-name.end. XREF reference...SEARCH school.student name...SEARCH school.student country-code

BP0550: Indexes and Performance - 65 Compiling with XREF /* whlidx.p */ for each student where slast-name = "Kramer" or city = "Chicago": or city = "Chicago": display student-id slast-name sfirst-name. display student-id slast-name sfirst-name.end. XREF reference...SEARCH school.student student-id WHOLE-INDEX

BP0550: Indexes and Performance - 66 Applying Indexing Rules /* idx2. p */ for each student where postal-code = "67890" and slast = "Smith": and slast = "Smith": display postal-code student-id sfirst-name display postal-code student-id sfirst-name slast-name. slast-name.end.

BP0550: Indexes and Performance - 67 Applying Indexing Rules

BP0550: Indexes and Performance - 68 Applying Indexing Rules …SEARCH school.student postal-code Only the postal-code index is used since all its components are utilized, but only the first component of the index name is used.

BP0550: Indexes and Performance - 69 Applying Indexing Rules /* idx7. p */ for each student where phone = " " and student-id gt 7 and student-id gt 7 and student-id le 12 and student-id le 12: display postal-code student-id sfirst-name display postal-code student-id sfirst-name slast-name. slast-name.end.

BP0550: Indexes and Performance - 70 Applying Indexing Rules

BP0550: Indexes and Performance - 71 Applying Indexing Rules …SEARCH school.student phone Phone is the only index used since student-id is involved in a range match, not an equality match. Phone has more AEMs than student- id.

BP0550: Indexes and Performance - 72 Applying Indexing Rules /* idx9. p */ for each student where graduated = yes or slast-name gt "W" or slast-name gt "W": display graduated sfirst-name slast-name. display graduated sfirst-name slast-name.end.

BP0550: Indexes and Performance - 73 Applying Indexing Rules

BP0550: Indexes and Performance - 74 Applying Indexing Rules …SEARCH school.student graduate-alpha-list …SEARCH school.student name Graduate-alpha-list and name are both used since their leading components are used in the WHERE clause and are connected by the OR operator.

BP0550: Indexes and Performance - 75 Applying Indexing Rules /* idx11. p */ for each student where student-id = 7 or student-id = 846 or student-id = 846: display student-id sfirst-name slast-name. display student-id sfirst-name slast-name.end.

BP0550: Indexes and Performance - 76 Applying Indexing Rules

BP0550: Indexes and Performance - 77 Applying Indexing Rules …SEARCH school.student student-id The student-id index is used twice and will be efficient in the way it is used. Note that this example uses two brackets on the same index.

BP0550: Indexes and Performance - 78 Applying Indexing Rules /* idx12. p */ for each student where student-id = 7 or student-id = 846 or student-id = 846 or sfirst-name = "Bob" or sfirst-name = "Bob": display student-id sfirst-name slast-name. display student-id sfirst-name slast-name.end.

BP0550: Indexes and Performance - 79 Applying Indexing Rules

BP0550: Indexes and Performance - 80 Applying Indexing Rules …SEARCH school.student student-id WHOLE-INDEX The student-id index is used but will not be efficient since Progress must search the entire table to see if there is a student whose first name is Bob.

BP0550: Indexes and Performance - 81 Applying Indexing Rules /* idx30. p */ for each stuchrg where charge-date ne ? for each stuchrg where charge-date ne ?: display student-id charge-date charge-code display student-id charge-date charge-code charge-amt. charge-amt.end.

BP0550: Indexes and Performance - 82 Applying Indexing Rules

BP0550: Indexes and Performance - 83 Applying Indexing Rules …SEARCH school.stuchrg charge-no WHOLE-INDEX Because Progress treats NE as an inactive range match, this is the inefficient way to retrieve records whose dates are not unknown.

BP0550: Indexes and Performance - 84 Applying Indexing Rules /* idx31. p */ for each stuchrg where charge-date lt ? for each stuchrg where charge-date lt ?: display student-id charge-date charge-code display student-id charge-date charge-code charge-amt. charge-amt.end.

BP0550: Indexes and Performance - 85 Applying Indexing Rules

BP0550: Indexes and Performance - 86 Applying Indexing Rules …SEARCH school.stuchrg charge-date-charge-code Since the unknown value for date fields sorts high, when reading records Progress interprets LT ? as dates that are not unknown. The moral of the story: use LT ? instead of NE ? for reading all records whose dates are not unknown.

BP0550: Indexes and Performance - 87 Joining Records Join from smallest bracket to largest. The rule is: Join from smallest bracket to largest. Should records always be joined from parent record to child? Most of the time, yes, but not always. It depends on the brackets.

BP0550: Indexes and Performance - 88 Joining Records With no WHERE conditions, there are fewer parent records than child records, so start with the parent record first. In join1.p, a student registers for more than one course, so read the student records first, before the registration records.

BP0550: Indexes and Performance - 89 Joining Records /* join1. p - from parent to child */ for each student, each registration of student, each registration of student, offering of registration, offering of registration, course of offering, course of offering, each grade of registration: each grade of registration: display student.student-id display student.student-id sfirst-name + " " + slast-name label "Name sfirst-name + " " + slast-name label "Name format "x(30)" format "x(30)" course-name format "x(20)" label "Course " course-name format "x(20)" label "Course " grade-name. grade-name.end.

BP0550: Indexes and Performance - 90 Joining Records What if the question was: Give me students from zip codes to who received a grade of A- or better from Bowling Made Easy.

BP0550: Indexes and Performance - 91 Joining Records The following are two ways we could join the records, join2. p and join3.p. There about 400 records within that zip code range, and about a dozen records for students with an A- or better in bowling. Join2. p starts with the student table. Join3. p starts with course, offering, and registration to narrow grades down to a specific course.

BP0550: Indexes and Performance - 92 Joining Records /* join2. p - from student to registration */ for each student where postal-code ge "40000" and postal-code le "79999", and postal-code le "79999", each registration of student where each registration of student where registration.grade-point ge 3.67, registration.grade-point ge 3.67, offering of registration, offering of registration, each course of offering where each course of offering where course-name = "Bowling made easy", course-name = "Bowling made easy", each grade of registration: each grade of registration: display student.student-id display student.student-id sfirst-name + " " + slast-name label "Name" sfirst-name + " " + slast-name label "Name" format "x(30)" format "x(30)" course-name format "x(20)" label "Course" course-name format "x(20)" label "Course" grade-name. grade-name.end.

BP0550: Indexes and Performance - 93 Joining Records /* join3. p - from registration to student */ for each course where course-name = "Bowling made easy", each offering of course, each offering of course, each registration of offering each registration of offering where registration.grade-point ge 3.67, where registration.grade-point ge 3.67, each grade of registration, each grade of registration, each student of registration where postal-code ge "40000" each student of registration where postal-code ge "40000" and postal-code le "79999": and postal-code le "79999": display student.student-id format "9999" label "Stu#" display student.student-id format "9999" label "Stu#" postal-code label "Zip" format "x(5)" postal-code label "Zip" format "x(5)" sfirst-name + " " + slast-name label "Name sfirst-name + " " + slast-name label "Name format "x(20)" format "x(20)" course-name format "x(20)" label "Course course-name format "x(20)" label "Course grade-name label "Grade". grade-name label "Grade".end.

BP0550: Indexes and Performance - 94 Dynamic Queries The index rules mentioned previously also apply to dynamic queries. The Index-Information method returns the same index data that the xref option does on the compile statement. This method may be executed before the dynamic query is opened, thereby preventing the execution of an inefficient query.

BP0550: Indexes and Performance - 95 Dynamic Queries The query-prepare method sets the conditions before the index-information method is executed. qh:query-prepare("for each student qh:query-prepare("for each student where slast-name begins s by postal-code). where slast-name begins s by postal-code).

BP0550: Indexes and Performance - 96 Dynamic Queries The index-information method returns a comma-delimited list of indexes selected for the n th record buffer specified. idxseled:list-items = qh:index-information(1). idxseled:list-items = qh:index-information(1). The above statement loads the indexes for the first record buffer for query qh into the idxseled selection-list. The above statement loads the indexes for the first record buffer for query qh into the idxseled selection-list.

BP0550: Indexes and Performance - 97 Dynamic Queries The WHOLE-INDEX keyword is returned if a particular record buffer is inefficient.

BP0550: Indexes and Performance - 98 Index Xref Tool The following tool is provided by Johan Forssblad from G4 IT AB. It loads the xref information into a database to allow for easy analysis. The tools shows for each program, each line of the program that has a record reading statement.

BP0550: Indexes and Performance - 99 Index Xref Tool On each line, it show the records read and the corresponding indexes selected. Within the indexes selected, it shows the components used and whether they are bracketed or not.

BP0550: Indexes and Performance Summary Now that you understand how Progress uses indexes, you can analyze the index usage of your applications for optimal performance. You will also be better equipped to design indexes for new applications. If you are not sure what index is selected, check the XREF listing.