Using INDEXED-REPOSITION to improve query performance

If you anticipate jumping around in the result set using statements such as GET LAST, you should add another option to the end of your OPEN QUERY statement: the INDEXED-REPOSITION keyword. If you do this, your DEFINE QUERY statement must also specify the SCROLLING keyword.

If you do not open the query with INDEXED-REPOSITION, then the AVM retrieves all records in sequence in order to satisfy a request such as GET LAST. This can be very costly. If you do use INDEXED-REPOSITION, the AVM uses indexes, if possible, to jump directly to a requested row, greatly improving performance in some cases. There are side effects to doing this, however, in terms of the integrity of the results list.

INDEXED-REPOSITION and field lists

When you define a query with the FIELDS phrase, make sure that you include (in the list of fields) the index that the query will use when you open the query with the INDEXED-REPOSITION keyword. Otherwise, you will get an error when the query is re-opened and it attempts to reposition on a field that it cannot find.

Factors that invalidate CURRENT-RESULT-ROW and NUM-RESULTS

Under some circumstances, when you open your query with the INDEXED-REPOSITION keyword, the value of CURRENT-RESULT-ROW or NUM-RESULTS becomes invalid. As explained in Determine the current number of rows in a query, the results list holds the row identifiers for those rows that satisfy the query and that have already been retrieved.

Thirteen rows satisfy the query for Customers in Louisiana, so the value of these two functions goes as high as 13 for that query. When you do a PRESELECT or a nonindexed sort, all the rows have already been retrieved before any data is presented to you, so NUM-RESULTS is 13 at the beginning of the DISPLAY loop. Normally, the AVM adds the identifiers for all the rows it retrieves to the results list, but there are circumstances where this is not the case. If you execute a GET LAST statement on a query, and your OPEN QUERY statement does not use a PRESELECT or a sort that forces records to be pre-retrieved, the AVM jumps directly to the last record using a database index, without cycling through all the records in between. In this case, it has no way of knowing how many records would have been retrieved between the first one and the last one, and it cannot maintain a contiguous results list of all rows that satisfy the query. For this reason, the AVM flushes and reinitializes the results list when you jump forward or backward in the query. So after a GET LAST statement, NUM-RESULTS returns 1 (because the GET LAST statement has retrieved one row) and CURRENT-RESULT-ROW is unknown (because there is no way to know where that row would fit into the full results list).