Salesforce

DBI File grows when UNDOable temp-tables are used

Printable View
« Go Back

Information

 
TitleDBI File grows when UNDOable temp-tables are used
URL Namedbi-file-grows-when-undoable-temp-tables-are-used
Article Number000145326
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x, 12.x
OS: All supported platforms
Question/Problem Description

If a program uses undoable temp-tables, creating and deleting records in the same table repeatedly within the same transaction can cause the DBI file to grow.

Even re-creating a relatively small set of records repeatedly can result in a DBI* temp-file of several gigabytes if this is repeated often enough in the same transaction.

Steps to Reproduce
Clarifying Information
Code similar to the following will demonstrate DBI file growth:
  • Although there are at most 500 small temp-table records at any one time, the DBI file will grow to several megabytes.
  • Increasing the number of iterations on the outer loop will cause further growth of the DBI file while keeping the effective number of records the same.
DEFINE TEMP-TABLE ttData
    FIELD Data AS CHARACTER
    INDEX Data IS UNIQUE Data.

DEFINE VARIABLE i1 AS INTEGER     NO-UNDO.
DEFINE VARIABLE i2 AS INTEGER     NO-UNDO.

DO TRANSACTION:
    DO i1 = 1 TO 400:
        DO i2 = 1 TO 500:
            CREATE ttData.
            ASSIGN ttData.Data = FILL(STRING(i2,"999999"),2).
        END.
        EMPTY TEMP-TABLE ttData.
        PROCESS EVENTS.
    END.
END.
Error Message
Defect Number
Enhancement Number
Cause
This is expected behavior.

The UNDO mechanisms used by OpenEdge will preserve the ROWIDs of records - this includes temp-table records.
  • Because OpenEdge ROWIDs are directly tied to an offset within memory or within a file, this means that to preserve the ROWIDs, part of the space allocated does not become available for re-use until the end of the transaction when a record is deleted.
  • Because the space is not re-used, more space needs to be allocated, and that is what causes the DBI* file to grow.
Resolution
Depending on the use case:

1. Make the temp-tables NO-UNDO if possible.This allows the allocated space to be reused immediately and avoids the issue.

There are known issues in the early OpenEdge 11 versions:
2. If the temp-table needs to be undoable, avoid repopulating it with duplicate data where possible to reduce the impact of this behavior to a minimum.

 
Workaround
Notes
Keyword Phrase
Last Modified Date10/19/2022 2:59 PM
Powered by