ca.portal.admin

Performance tip: Sweep an index area, rather than a data area.

Discussion created by ca.portal.admin on Jun 11, 2010
Latest reply on Jun 11, 2010 by ca.portal.admin
Maybe this is common knowledge to others, but it was news to me. If the information you need is stored in an index, you can read only the index, without reading the indexed records themselves. If your data area is large, this can reduce the I/O and CPU used to a fraction of the cost of a regular area sweep.

You don't use the FIND or OBTAIN command to read an index - the magic verb is RETURN. Here is an example: Let's say that you have a record called PERSON. It has an index called IX-PERSON-NAME with a key consisting of three fields: LAST-NAME, FIRST-NAME, and MIDDLE-INITIAL. You need to get a list of everyone with the first name ""BOB"". Using FIND or OBTAIN, your only option would be to do an area sweep of the entire data area. But using RETURN, you can sweep only the index area. You would loop through the index using the code below:

RETURN WS-DBKEY FROM IX-PERSON-NAME NEXT.
IF FIRST-NAME = 'BOB'
MOVE LAST-NAME TO OUTPUT-LAST-NAME
MOVE FIRST-NAME TO OUTPUT-FIRST-NAME
MOVE MIDDLE-INITIAL TO OUTPUT-MIDDLE-INITIAL
END-IF.

WS-DBKEY is a field that you define in working-storage to hold the DBkey. You can use it to obtain the PERSON record, if you need more information that is not in the index. Even if you do have to read the ""BOB"" PERSON records, you will not have to read any of the other PERSON records.

Isn't that the coolest thing?

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
"Re: Performance tip: Sweep an index area, rather than a data area."
"Yes, good tips indeed. Another use of RETURN command is get the number of records meeting your sort criteria before actually retrieving the data. I used this to stop users from doing excessively long retrievals online (that is, if the number of records exceeded a threshold, direct the query to batch instead of online).

Dan Miley
Lockheed Martin

Outcomes