Use-index prevent multiple brackets: documented? - Forum - OpenEdge Development - Progress Community

Use-index prevent multiple brackets: documented?


Use-index prevent multiple brackets: documented?

This question is not answered

I'm using both 10.2B and 11.3.

I have a question about the effect of USE-INDEX on index bracketing (and I'd rather not get mired in a discussion of the general evils of USE-INDEX... :) ).  I've been through the documentation and so far I haven't found a reference to the behaviour I have seen.  First, some back story.  I came across a query like this:

for each table no-lock where
  code = 'a' and
  (status = 'w' or
   status = 'x' or
   status = 'y' or
   status = 'z') and
   log = false:

The order in which records are returned by the query is not important.  For the purpose of this discussion, let's say there are two indexes: code-num (components "code" and "num") which is primary and non-unique, and code-stat (components "code" and "status") which is non-unique.  When the query is written as above, the compiler selects index code-num and brackets only on "code".  This is inefficient as the bracket ends up being almost all of the records in the table.  A little less than half of the records in the table satisfy the WHERE clause.

First, the developer changed the query by adding "use-index code-stat", thinking this would improve things.  Using -zqil and QryInfo I demonstrated that this query still only bracketed on "code" and read the same number of records and index keys as the first query.  I suggested rewriting the query like this:

for each table no-lock where
  (code    = 'a' and
   status  = 'w' and
   log     = false)  or
  (code    = 'a' and
   status  = 'x' and
   log     = false)  or
  (code    = 'a' and
   status  = 'y' and
   log     = false)  or
  (code    = 'a' and
   status  = 'z' and
   log     = false):

Written this way, -zqil and INDEX-INFORMATION both showed that the compiler selected index code-stat and used four brackets, each an equality match on both components.  QryInfo showed that this query was much more efficient than the first two; blocks accessed dropped in half and records read were just slightly more than the number of records that satisfy the query.  Good stuff.  I understand that using multiple brackets means record order is not guaranteed, but as I said in this particular case it doesn't matter.

Just for kicks I tried adding "use-index code-stat" to the end of the query.  Can't hurt, right?  It's going to use that index anyway.  Wrong.  This is the least efficient of the queries: it results in a table scan.  Re-reading the docs (FOR statement, Triggers and Indexes) and various KB articles (Index Brackets Explained, Single Index Selection Explained, Multiple Index Selection Explained, Using USE-INDEX Explained, etc.), I couldn't understand this at first.  Then I found this info in the article "4GL Query Concepts" (S000012195):

"In version 6, FOR EACH queries use a single index cursor and a single index bracket, and their performance is similar to that of FIND queries. In version 7 and later, they use server queries, and utilize multiple index brackets and multiple indexes when possible.

There are two ways to force FOR EACH queries in version 7 to behave like they did in version 6: by using the -v6q startup parameter, or by using the USE-INDEX clause."

So that explains the runtime behaviour.  But I may still file a documentation bug.  Apart from the KB article mentioned above, does anyone know if this side-effect of USE-INDEX is explicitly mentioned anywhere in the OE docs?  Any help is appreciated.

All Replies
  • @ske

    I think you are correct about the workaround of running appserver locally (in shared memory). There is a KB which is intended to discuss the topic of client/server database performance :   ... but that KB concludes by explaining the "APPSERVER ADVANTAGE", just as you did.  It almost sounds like a capitulation, and encourages people to avoid client/server in the first place.

    However, these days people want their ABL logic running on another, lower-cost, tier instead of putting it all on the same hardware that is shared by the OE database.  Moving this stuff to another tier also adds fault-tolerance and load-balancing.  I think the new PASOE will probably run with client/server connections more frequently than "classic" appserver ever did.  Anyone trying to deploy PASOE on DOCKER, for example, will probably avoid shared memory connections.  Hopefully the client/server configuration will start becoming a high priority for Progress.