Consultor Eletrônico

Status: Verified

GOAL:

How to check if a specific 4GL query uses indexes efficiently

GOAL:

How to determine if a query is inefficient

FACT(s) (Environment):

Progress 9.0x
OpenEdge 10.x
All Supported Operating Systems

FIX:

From a 4GL point of view, a query does not make efficient use of indexes if no appropriate index brackets can be used. This results in a WHOLE-INDEX table scan, meaning that the entire index will be looped through when records are retrieved.

It is possible to discover if such a whole-index scan occurs for a query in your application using the following techniques:

Queries that are opened with the static OPEN QUERY statement can be investigated by using COMPILE XREF for the procedure file. The XREF output will contain details on index use, and WHOLE-INDEX scans will be reported as such.

Also queries that are opened dynamically (using the QUERY-PREPARE() and QUERY-OPEN() methods) can be investigated by using the INDEX-INFORMATION attribute of the query object handle.
An example of how to do this using the sports database:

/* r-iinfo.p */

def query q for customer,order,order-line scrolling.
def var x as handle.
def var i as integer.
def var j as integer.

x = query q:handle.

x:query-prepare("for each customer where cust-num < 3,
each order of customer, each order-line").
x:query-open.

message "prepare string is" x:prepare-string.

repeat i = 1 to x:num-buffers:

j = lookup("WHOLE-INDEX",x:index-information(i)).
if (j > 0)
then message "inefficient index"
entry(j + 1,x:index-information(i)).
else message "bracketed index use of" x:index-information(i).
end.
Note that the above code will work both on statically defined and dynamically created queries, but the query must be prepared first.

The code will also work on a statically opened query (using the OPEN QUERY statement), provided that the static query was defined with the RCODE-INFORMATION option.