时间戳

首页 > 解决方案 > 在整个索引上使用括号有什么区别？

openedge - 在整个索引上使用括号有什么区别？

问题描述

/* Method 1 */
FOR EACH customer NO-LOCK WHERE customer.name EQ 'John':
   DISPLAY customer.name.
END.

/* Method 2*/
for each customer where (customer.name EQ 'John'):
    DISPLAY customer.name.
end.

您能否通过括号解释编译器将如何工作？

标签： openedgeprogress-4gl

解决方案

Your second example is NOT faster. It is actually slower because you failed to specify NO-LOCK. By default you will therefore get the record(s) with a SHARE-LOCK which will then need to be unlocked as each record goes out of scope. This extra work takes time and results in a slower query.

If you are connecting client/server it can be orders of magnitude slower because:

a) each record will require 3 network messages. 1 to ask for it, another to return it and a 3rd to unlock it.

b) NO-LOCK queries can pack multiple records into a response message. this is much more efficient than requesting and sending them one at a time. and since there is no lock nothing needs to be unlocked.

(In your sample you are only getting one record so the difference is pretty small.)

"Index Brackets" are an entirely different concept from grouping sub-expressions with "(" and ")". An index bracket is a set of records specified by elements of the WHERE clause. In both of your examples above you have an equality match on a field which is the leading component of a unique index. So the "bracket" is exactly one record. (Assuming that this is the standard sports database!)

If you specify something more complicated like:

for each order no-lock where order.custid = 1 and order.ship-date >= 7/1/2019 and order.ship-date <= 7/31/2019:

Progress uses a static, rule based query optimizer. The indexes are chosen at compile time. The rules are complicated but the most important, by far, is that equality matches on leading components keep your index in play. Range matches are next most useful. But once you get a range match no further fields will be considered for index selection.

There can be cases where using parenthesis to group elements of the WHERE clause has an impact on index selection. (The case where there is only one field being selected on is not one of them.) Generally this is going to be a situation where you have a complex query with a mix of AND and OR elements. If nothing else the use of parenthesis in these situations makes operator precedence much less error prone and easier for a human to read.

There is a lot of material on the index selection process available. I suggest that you start here: https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/wp-abl-triggers/general-rules-for-choosing-a-single-index.html

There are also excellent presentations at every PUG Challenge on the topic. If you cannot attend in person (you should), many of them are available for download: http://pugchallenge.org/downloads.html

推荐阅读