Kbase P77065: INDEX-REPOSITION and ROWID performances condiderations
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/12/2004 |
|
Status: Verified
GOAL:
INDEX-REPOSITION and ROWID performances condiderations
GOAL:
Performance consideration with ROWID and INDEX-REPOSITION
FACT(s) (Environment):
Progress 9.x
OpenEdge 10
FIX:
The number of records in your results list can have a serious impact on the performance of your browse. You may want to optimize your query definition to take advantage of features that work well with small and large data sets.
When a query is opened, it does not necessarily build the entire results list immediately. In Programming Handbook Chapter 9, "Database Access," there is a detailed discussion about how results lists are initialized and built. For small data sets, it may be more advantageous to force the query to build the complete results list with a PRESELECT option.
This technique has the added benefit of giving the scrollbar access to all the records in the query from the time the browse is initialized. While the results list is being built, the scrollbar represents the range of records currently in the results list.
If you have a query to a large data set, using the REPOSITION TO ROWID statement may significantly affect performance. The INDEXED-REPOSITION option of the OPEN QUERY statement attempts to optimize REPOSITION statements that use ROWID. To accomplish this, the query essentially starts over. It begins building the results list from the new position, on each REPOSITION statement. The results list will only be built as much as necessary to satisfy the REPOSITION statement. This option can dramatically speed up browse repositions. The option has side effects, however. Because of the new results list, your view of the query can change. For example, new records entered into the browse will appear in their correct sorted order when the results list is initialized.
Using the INDEX-REPOSITIONED option on a query associated with a multiple-select browse is legal, but may have too many drawbacks to be useful. When the results list is restarted, all selected rows in the browse are deselected. Also, the vertical scrollbar thumb is disabled.
Since PRESELECT forces a complete results list to be created and maintained, you cannot use the INDEXED-REPOSITION option with a PRESELECT query.