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
Another option to export a static temp-table to a new Excel file (Windows ONLY)