Sweating the Small Stuff

[precisonline]

A couple of days ago I received a request where someone was writing a SUBR(..) type dictionary that was taking a long time to run in the context of a UniQuery statement.  In this subroutine were the following lines:

BIN.CNT  = DCOUNT(REC<79>,@VM)
FOR BIN.LOOP = 1 TO BIN.CNT
  BIN = REC<79,BIN.LOOP>
  ...
NEXT BIN.LOOP

I suggested that the extraction from attribute 79 could benefit from some simple optimization, as follows:

BIN.LIST = REC<79>
BIN.CNT  = DCOUNT(BIN.LIST,@VM)
FOR BIN.LOOP = 1 TO BIN.CNT
  BIN = BIN.LIST<1,BIN.LOOP>
  ...
NEXT BIN.LOOP

This small change, which many people might find otherwise innocuous, reduced the processing time of this particular routine from ~30 minutes to ~2.  If the bin lists were of any size at all (they usually have less than a half dozen entries) I would have also recommended using REMOVE instead of the extraction, but the client was pleased with the progress of the simple optimization so there was no pressing need for the next level.

This just goes to show how the small stuff can make a big difference when you multiply it times a couple hundred thousand records!

[Tom Pellitieri]

In UniBasic, you can use List Processing instead of REMOVE to get the same benefits.  E.g.:

BINS = RAISE(REC<79>)
SELECT BINS TO BIN.LIST
LOOP WHILE READNEXT BIN FROM BIN.LIST DO
   ...
REPEAT

Please note that you shouldn't use this method if you need to access associated multi-values.  For example, if REC<79> has Bin Numbers, and REC<80> has the product in that bin, and REC<81> has the quantity, you would still need to use a loop index to access all three.  However, you could still improve performance using Kevin's example, as follows:

BIN.LIST = REC<79>
PROD.LIST = REC<80>
QTY.LIST = REC<81>
BIN.CNT = DCOUNT(BIN.LIST,@VM)
FOR BIN.LOOP = 1 TO BIN.CNT
   BIN = BIN.LIST<1,BIN.LOOP>
   PROD = PROD.LIST<1,BIN.LOOP>
   QTY = QTY.LIST<1,BIN.LOOP>
   ...
NEXT BIN.LOOP