Kbase P5496: 4GL. Which is the fastest way to count records in a Table?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/14/2010 |
|
Status: Verified
GOAL:
4GL. Which is the fastest way to count records in a Table?
GOAL:
How to count records the fastest way?
FIX:
You can use two approaches for counting records in a Table.
Use the COUNT function available in the Progress 4GL or you can implement a Query to do the same .
In terms of performance it depends on the Amount of Records within the Table, the Operating System, and also the indexes available to resolve the query. However Queries in average give a better result.
FOR EACH ... NO-LOCK with FIELDS option: The FIELDS option minimizes network traffic. When the connection is remote this operates approximately 40% faster than the nearest alternative method.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
ETIME(TRUE).
FOR EACH orderline FIELDS(ordernum) NO-LOCK:
i = i + 1.
END.
MESSAGE ETIME i
VIEW-AS ALERT-BOX INFO BUTTONS OK.
The COUNT function Approach. For the best results it is advisable to use a primary index field to ACCUMULATE on:
ETIME(YES).
FOR EACH CUSTOMER NO-LOCK:
ACCUMULATE custNum (COUNT).
END.
MESSAGE "TOTAL OF RECORDS = " (ACCUM COUNT BALANCE) SKIP(1)
"TIME FOR PROCESSING = " ETIME VIEW-AS ALERT-BOX.
The Query approach:
DEFINE QUERY MYQUERY FOR CUSTOMER SCROLLING.
ETIME(YES).
OPEN QUERY MYQUERY FOR EACH CUSTOMER NO-LOCK.
REPEAT WHILE NOT QUERY-OFF-END("MYQUERY"):
GET NEXT MYQUERY.
END.
MESSAGE "TOTAL OF RECORDS = " NUM-RESULTS("MYQUERY") SKIP(1)
"TIME FOR PROCESSING = " ETIME VIEW-AS ALERT-BOX.