Software Optimization

4 Programming Considerations

Software Optimization

The most significant gains in terms of speed improvement can be made by following some simple rules in designing programs using SORT operations. There are essentially three classes of rules which will be covered:

Generally true rules.
Rules which are to be used if no FINDs, QFINDS, SORTs or PRINT #s have been done on the workfile (REC=0).
Rules which are to be used if pointers have been put in the workfile (REC \\neq0 ).

General Rules

The most important rule is to keep thread length minimal. Also, if either the first or the last set in the thread is a master and the only item that will ever used out of it for FINDing, QFINDing or SORTing is the search item, it can be eliminated. This is possible since that item also exists in the associated detail thus enabling a calculated access DBGET to be used to get the additional information out of the master.

Turn off all possible IN DATA SETs (via the FREE option) before doing a FIND or QFIND. If a particular IN DATA SET is active for some data set in the thread and no values from that set are needed to evaluate the selection expression, FREEing that IN DATA SET stops FIND/QFIND from reading information from that set.

Rules When REC = 0

When there are no pointers in the workfile, SORT BY and FIND statements must do a serial read of the first set in the thread. If you need only a few entries from a large data set, use QFIND to locate requested entries before executing FIND or SORT BY.

Specifying the conditional expression in a QFIND statement reduces overhead against QFIND/FIND sequence, because records are only processed once.

QFIND works by appending, i.e. adding entries. This is particularly useful if data conditions are diferent.

If an item occurs in more than one set in the thread (normally because it is a search item), it should be selected to come from the set closest to the start of the thread. For FIND it is very important to notice all appropriate sets (ones with IN DATA SETs active) have been read. Thus, if the needed set can be restricted to those near the head of the thread, the expression can be evaluated sooner.

If the FIND condition is a series of conditions separated by ANDs, it may be beneficial to break them up into separate FINDs. In general, if some of the clauses pertain only to the first set in the thread and they will select significantly less than all the data available, then it is best to construct two FIND statements (the first one pertaining only to the set at the head of the thread). Remember when doing this to deactivate and reactivate the IN DATA SET relations (via FREE) to maximize effect.

Rules When

Here again it is a good idea to deactivate all unused IN DATA SET relations pertaining to sets in the thread. In the case of SORT BY, the fewer sets involved the better. Remember that if one of the sort items is a search item it may be possible to select it from one to several sets. Select it from the set which allows you to deactivate the most IN DATA SETs.

In the case of FIND the same things as mentioned for SORT BY also apply. However, breaking up a complex FIND separated by ANDs into several FINDs may increase speed if (and only if) some of the clauses separated by the ANDs do not involve the same sets or involve fewer sets than the other clauses. If this is the case, the clauses which have the fewest sets involved and the lowest probability of being true should be executed first. Remember, again, that the only way FIND knows which sets are involved is by which IN DATA SETs are active. Clearly, most of these rules assume the programmer has a good understanding of the form the data will take (in terms of probable events). When in doubt, perform tests.

Overview
REC =0
(no previous QFIND, FIND, SORT BY or PRINT ) (previous FIND, SORT BY or PRINT #)
FIND Keep thread lenght short.
Make sure the last set with an
IN DATA SET active on it is as close to
the start of the thread as possible
Make sure IN DATA SET are active on only those sets from which
information must be retrieved
SORT BY Keep thread lenght short. Make sure sort keys come from as few sets as possible.

Overview
	REC =0
	(no previous QFIND, FIND, SORT BY or PRINT )	(previous FIND, SORT BY or PRINT #)
FIND	Keep thread lenght short. Make sure the last set with an IN DATA SET active on it is as close to the start of the thread as possible	Make sure IN DATA SET are active on only those sets from which information must be retrieved
SORT BY	Keep thread lenght short.	Make sure sort keys come from as few sets as possible.

ALWAYS:
1) Minimize thread length.
2) Minimize complexity of the FIND selection expression.
3) Minimize total sort key.

Eloquence Sort Manual - 19 DEC 2002