The OpenEdge Hive

When adding records to a temp-table having at least a unique index, it can be faster to trap the ABL error generated on a collision instead of using a CAN-FIND prior to creating each record.

The performance of the three methods is relative to the percentage of collisions. For usual business cases with relatively low collision rates (< 20%), method 3 is the fastest.

Note also that in the sample, using a NO-UNDO temp-table and deleting the problematic record was faster than using an UNDO temp-table and undoing the record addition on a fail.

&SCOPED-DEFINE NumLoops 10000
&SCOPED-DEFINE NumDuplicatesLoops 8000

DEFINE TEMP-TABLE ttMetricValue NO-UNDO
 FIELD cMetricLiteralValue  AS CHARACTER
 FIELD deMetricDecimalValue AS DECIMAL
 INDEX ByMetricDecimalValue IS PRIMARY UNIQUE deMetricDecimalValue.
 
DEFINE TEMP-TABLE ttMetricValue2 NO-UNDO LIKE ttMetricValue.

DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE iEmptyLoopTime AS INTEGER NO-UNDO.
DEFINE VARIABLE iEmptyDuplicateLoopTime AS INTEGER NO-UNDO.
DEFINE VARIABLE iMethodTime AS INTEGER NO-UNDO.
DEFINE VARIABLE iMethod1Time AS INTEGER NO-UNDO.
DEFINE VARIABLE iMethod2Time AS INTEGER NO-UNDO.
DEFINE VARIABLE iMethod3Time AS INTEGER NO-UNDO.
DEFINE VARIABLE cResults AS CHARACTER NO-UNDO INITIAL "--Results ({&NumLoops} entries, {&NumDuplicatesLoops} duplicates)--~n".

ETIME(TRUE).
DO i = 1 TO {&NumLoops}:
END.
iEmptyLoopTime = ETIME.

ETIME(TRUE).

DO i = 1 TO {&NumLoops}:
  IF NOT CAN-FIND(FIRST ttMetricValue NO-LOCK WHERE ttMetricValue.deMetricDecimalValue = i)
  THEN DO:
    CREATE ttMetricValue.
    ASSIGN
     ttMetricValue.deMetricDecimalValue = i
     ttMetricValue.cMetricLiteralValue  = STRING(i).
  END.
END.

iMethod1Time = ETIME - iEmptyLoopTime.
cResults     = cResults + "~nMethod 1: " + STRING(iMethod1Time).
EMPTY TEMP-TABLE ttMetricValue.
ETIME(TRUE).

DO i = 1 TO {&NumLoops}:
  CREATE ttMetricValue.
  ASSIGN
   ttMetricValue.deMetricDecimalValue = i
   ttMetricValue.cMetricLiteralValue  = STRING(i)
  NO-ERROR.

  IF ERROR-STATUS:NUM-MESSAGES > 0 /* we'll let Progress validate the uniqueness */
  THEN DELETE ttMetricValue.
END.

iMethod2Time = ETIME - iEmptyLoopTime.
cResults     = cResults + "~nMethod 2: " + STRING(iMethod2Time).
EMPTY TEMP-TABLE ttMetricValue.
ETIME(TRUE).

DO i = 1 TO {&NumLoops} ON ERROR UNDO, NEXT:
  CREATE ttMetricValue.
  ASSIGN
   ttMetricValue.deMetricDecimalValue = i
   ttMetricValue.cMetricLiteralValue  = STRING(i)
  .

  CATCH e AS Progress.Lang.Error :
    DELETE ttMetricValue.
  END CATCH.
END.

iMethod3Time = ETIME - iEmptyLoopTime.
cResults     = cResults + "~nMethod 3: " + STRING(iMethod3Time).

/* add duplicates */
ETIME(TRUE).
DO i = 1 TO {&NumDuplicatesLoops}:
END.
iEmptyDuplicateLoopTime = ETIME.

BUFFER ttMetricValue2:COPY-TEMP-TABLE (BUFFER ttMetricValue:TABLE-HANDLE).
ETIME(TRUE).

DO i = 1 TO {&NumDuplicatesLoops}: /*with 3000 duplicates (30%), both methods are about equal in speed.  With less than that, the suggested method is faster.*/
  IF NOT CAN-FIND(FIRST ttMetricValue2 NO-LOCK WHERE ttMetricValue2.deMetricDecimalValue = i)
  THEN DO:
    CREATE ttMetricValue2.
    ASSIGN
     ttMetricValue2.deMetricDecimalValue = i
     ttMetricValue2.cMetricLiteralValue  = STRING(i).
  END.
END.
iMethodTime = ETIME.
cResults    = cResults + SUBSTITUTE("~nMethod 1 with duplicates: &1 + &2 = &3", iMethod1Time, iMethodTime - iEmptyDuplicateLoopTime, iMethod1Time + iMethodTime - iEmptyDuplicateLoopTime).
EMPTY TEMP-TABLE ttMetricValue2.

BUFFER ttMetricValue2:COPY-TEMP-TABLE (BUFFER ttMetricValue:TABLE-HANDLE).
ETIME(TRUE).

DO i = 1 TO {&NumDuplicatesLoops}: /*with 3000 duplicates (30%), both methods are about equal in speed.  With less than that, the suggested method is faster.*/
  CREATE ttMetricValue2.
  ASSIGN
   ttMetricValue2.deMetricDecimalValue = i
   ttMetricValue2.cMetricLiteralValue  = STRING(i)
  NO-ERROR.

  IF ERROR-STATUS:NUM-MESSAGES > 0 /* we'll let Progress validate the uniqueness */
  THEN DELETE ttMetricValue2.
END.
iMethodTime = ETIME.
cResults    = cResults + SUBSTITUTE("~nMethod 2 with duplicates: &1 + &2 = &3", iMethod2Time, iMethodTime - iEmptyDuplicateLoopTime, iMethod2Time + iMethodTime - iEmptyDuplicateLoopTime).
EMPTY TEMP-TABLE ttMetricValue2.

BUFFER ttMetricValue2:COPY-TEMP-TABLE (BUFFER ttMetricValue:TABLE-HANDLE).
ETIME(TRUE).

DO i = 1 TO {&NumDuplicatesLoops} ON ERROR UNDO, NEXT: /*with 3000 duplicates (30%), both methods are about equal in speed.  With less than that, the suggested method is faster.*/
  CREATE ttMetricValue2.
  ASSIGN
   ttMetricValue2.deMetricDecimalValue = i
   ttMetricValue2.cMetricLiteralValue  = STRING(i)
  .

  CATCH e AS Progress.Lang.Error :
    DELETE ttMetricValue2.
  END CATCH.
END.
iMethodTime = ETIME.
cResults    = cResults + SUBSTITUTE("~nMethod 3 with duplicates: &1 + &2 = &3", iMethod3Time, iMethodTime - iEmptyDuplicateLoopTime, iMethod3Time + iMethodTime - iEmptyDuplicateLoopTime).
EMPTY TEMP-TABLE ttMetricValue2.

MESSAGE cResults VIEW-AS ALERT-BOX.

Some results:
5% duplicates, #2 is 27% faster, #3 is 33% faster

--Results (10000 entries, 500 duplicates)--
Method 1: 408
Method 2: 290
Method 3: 257
Method 1 with duplicates: 408 + 7 = 415
Method 2 with duplicates: 290 + 15 = 305
Method 3 with duplicates: 257 + 21 = 278

20% duplicates, #2 is 20% faster, #3 is 22% faster

--Results (10000 entries, 2000 duplicates)--
Method 1: 409
Method 2: 291
Method 3: 259
Method 1 with duplicates: 409 + 29 = 438
Method 2 with duplicates: 291 + 60 = 351
Method 3 with duplicates: 259 + 83 = 342

30% duplicates, #2 is 16% faster, #3 is 15% faster

--Results (10000 entries, 3000 duplicates)--
Method 1: 407
Method 2: 288
Method 3: 257
Method 1 with duplicates: 407 + 45 = 452
Method 2 with duplicates: 288 + 92 = 380
Method 3 with duplicates: 257 + 126 = 383

80% duplicates, #1 and #2 are equal, #3 is 11% slower

--Results (10000 entries, 8000 duplicates)--
Method 1: 410
Method 2: 284
Method 3: 252
Method 1 with duplicates: 410 + 120 = 530
Method 2 with duplicates: 284 + 247 = 531
Method 3 with duplicates: 252 + 337 = 589