R
Rob Fitzpatrick
Guest
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.
Continue reading...
). 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.Continue reading...