The statistics for temp-tables and their indexes are available by reading the _TableStat and _IndexStat virtual system tables, while the tables are in use. However, this information is destroyed once the temp-tables (and their indexes) are deleted. It is possible, however, to collect the temp-table usage statistics for later analysis if needed. There are two ways to accomplish this:
  • Use the TTStats log entry type to log all of the information into the client or server log file
  • Use the _TableStatHist and _IndexStatHist tables to capture the historical usage information for the session.

With either method you must set the startup parameter -tttablerangesize (if you want table statistics) and/or -ttindexrangesize (if you want index statistics) to an appropriate value greater than 0 (the default is 0 for both).

Use the TTStats log entry type

If you enable TTStats with a logging level of 2 or greater, and the startup parameter -tttablerangesize is set to a value greater than 0, the ABL virtual machine (AVM) logs an entry that contains temp-table statistics and includes the table name, and the procedure or class name. The AVM writes the data to the log file before the temp-table is deleted.

If you enable TTStats with a logging level of 3 or greater, and the startup parameter -ttindexrangesize is set to a value greater than 0, the AVM logs an entry that contains index statistics and includes the index name, and the procedure or class name. The AVM writes the data to the log file before the index of the temp-table is deleted.

The following shows an example of the parameters to set for a client session:

-tttablerangesize 100 
-ttindexrangesize 100 
-clientlog logfilename 
-logentrytypes TTStats 
-logginglevel 3

For a Progress Application Server (PAS) for OpenEdge session, you can set the proper values in the PAS instance configuration file, in the agentLogEntryTypes, and agentStartupParam properties.

The following example shows the log entries for both temp-table index and temp-table statistics:
4GL -- Logging level set to = 3
4GL -- Log entry types activated: TTStats
4GL TTSTATS        Index: ttTest.idx1 (myproc.p)
4GL TTSTATS           Create: 10021 Delete: 3334 Read: 3334 OS-Read: 0 Split: 22 Block-Delete: 0
4GL TTSTATS        Table: ttTest (myproc.p)
4GL TTSTATS           Create: 10000 Update: 0 Delete: 3334 Read: 3334 OS-Read: 0

Use the _TableStatHist and _IndexStatHist tables to capture the historical usage information for the session

In order to analyze the data of application temp-tables that are deleted during the session, archive the statistical information provided by _TableStat and _IndexStat virtual system tables. If you set Progress.Database.TempTableInfo:ArchiveTableStatistics to true, the AVM will archive and maintain the information in a temp-table. You cannot delete the temp-table when archiving is enabled.

The temp-table name is in the table archive, and the index name is in the index archive. The procedure used for creating the table is also archived. The application is able to look at the statistics for a particular temp-table on a given procedure for each instantiation of the temp-table individually, or accumulate the values to obtain the overall statistics for a particular temp-table across all instantiations in a session. Thus, _TableStat and _IndexStat provide control over how you want to handle the temp-tables. The ArchiveTableStatistics property and ArchiveIndexStatistics property enables the archiving of statistics for a temp-table and its index. These properties belong to Progress.Database.TempTableinfo.

Note: The statistics are maintained only if their IDs are within the range specified by these startup parameters: -ttbasetable, -ttbaseindex, -tttablerangesize, and -ttindexrangesize.
Note: You cannot see statistics for any temp-table from within the same procedure/scope of temp-tables you wish to track. This information can only be captured after the procedure that defines the temp-tables have finished executing (the temp-tables must be out of scope).
To capture statistical data about table and index usage in temp-tables, follow these steps:
  1. Start your ABL session with the parameters that define the range of IDs you want to get statistics on, using the -tttablerangesize, -ttbasetable, -ttindexrangesize and/or -ttbaseindex, as appropriate. If only tracking temp-table statistics, you can omit the parameter for indexes. For example, this will track the table and index statistics for objects with IDs in the range of 1 to 100:
    proenv>prowin -tttablerangesize 100 -ttindexrangesize 100
    Note: Nothing will be captured if no range is defined for the resource (tables/indexes) you wish to capture using these parameters.
  2. Before running the code you wish to track, enable the Progress.Database.TempTableInfo:ArchiveTableStatistics and/or Progress.Database.TempTableInfo:ArchiveIndexStatistics properties. For example:
    USING Progress.Database.*.

TempTableInfo:ArchiveTableStatistics = TRUE.
TempTableInfo:ArchiveIndexStatistics = TRUE.
  3. Run the code you wish to track to completion.
    /* sampleApplicationCode.p */
DEFINE TEMP-TABLE ttTest NO-UNDO
    FIELD f1 AS INT
    FIELD f2 AS CHAR
    INDEX idx1 IS PRIMARY UNIQUE f1.
    
DEFINE VARIABLE iCount    AS INTEGER     NO-UNDO.

DO iCount = 1 TO 10000: 
    CREATE ttTest. 
    ASSIGN ttTest.f1 = iCount 
           ttTest.f2 = STRING(iCount). 
END. 

DO iCount = 1 TO 10000 BY 3: 
    FIND ttTest WHERE ttTest.f1 EQ iCount NO-ERROR. 
    DELETE ttTest. 
END.
  4. Read, view and/or archive the data to XML or JSON. The following program demonstrates writing out XML and JSON as well as displaying the history results in BROWSE widgets.
    /* queryTTUsageHistory.p */
USING Progress.Database.*.

/* Define handle variables to hold the TempTable and Index info */
DEFINE VARIABLE hTTInfo     AS HANDLE      NO-UNDO.
DEFINE VARIABLE hIdxInfo    AS HANDLE      NO-UNDO.

DEFINE VARIABLE hTTBuff     AS HANDLE      NO-UNDO.
DEFINE VARIABLE hIdxBuff    AS HANDLE      NO-UNDO.
DEFINE VARIABLE hTTQuery    AS HANDLE      NO-UNDO.
DEFINE VARIABLE hIdxQuery   AS HANDLE      NO-UNDO.

DEFINE VARIABLE cOutputFile AS CHARACTER   NO-UNDO 
    INITIAL "tempTableStats_&1_&2_&3.".

DEFINE BROWSE brTT  WITH SIZE 75 BY 10.
DEFINE BROWSE brIdx WITH SIZE 75 BY 10.

DEFINE FRAME fStats brTT SKIP(1) brIdx.

/* Use GetTableStatHistoryHandle() and GetIndexStatHistoryHandle() 
   methods to get the handles of the associated temp-tables. */
ASSIGN hTTInfo  = TempTableInfo:GetTableStatHistoryHandle()
       hTTBuff  = hTTInfo:DEFAULT-BUFFER-HANDLE
       hIdxInfo = TempTableInfo:GetIndexStatHistoryHandle()
       hIdxBuff = hIdxInfo:DEFAULT-BUFFER-HANDLE.

/* Add a timestamp to the output filename */
cOutputFile = SUBSTITUTE(cOutputFile,"~&1",
                         REPLACE(STRING(TODAY,"99/99/9999"),"/",""),
                         REPLACE(REPLACE(STRING(TIME,"HH:MM:SS AM"),":","")," ","")).

/* Demonstrate writing out XML with the Table and Index history */
hTTInfo:WRITE-XML("FILE",SUBSTITUTE(cOutputFile,"table") + "xml").
hIdxInfo:WRITE-XML("FILE",SUBSTITUTE(cOutputFile,"index") + "xml").

/* Demonstrate writing out JSON with the Table and Index history */
hTTInfo:WRITE-JSON("FILE",SUBSTITUTE(cOutputFile,"table") + "json").
hIdxInfo:WRITE-JSON("FILE",SUBSTITUTE(cOutputFile,"index") + "json").

/* Demonstrate accessing the data using BROWSE widgets */
CREATE QUERY hTTQuery.
hTTQuery:SET-BUFFERS(hTTBuff).
BROWSE brTT:QUERY = hTTQuery.

ASSIGN BROWSE brTT:SENSITIVE  = FALSE
       BROWSE brTT:EXPANDABLE = FALSE.

BROWSE brTT:ADD-COLUMNS-FROM(hTTBuff).

ASSIGN BROWSE brTT:SENSITIVE  = TRUE
       BROWSE brTT:EXPANDABLE = TRUE.
       
CREATE QUERY hIdxQuery.
hIdxQuery:SET-BUFFERS(hIdxBuff).
BROWSE brIdx:QUERY = hIdxQuery.

ASSIGN BROWSE brIdx:SENSITIVE  = FALSE
       BROWSE brIdx:EXPANDABLE = FALSE.

BROWSE brIdx:ADD-COLUMNS-FROM(hIdxBuff).

ASSIGN BROWSE brIdx:SENSITIVE  = TRUE
       BROWSE brIdx:EXPANDABLE = TRUE.

hTTQuery:QUERY-PREPARE("FOR EACH " + hTTBuff:NAME).
hTTQuery:QUERY-OPEN().

hIdxQuery:QUERY-PREPARE("FOR EACH " + hIdxBuff:NAME).
hIdxQuery:QUERY-OPEN().

ENABLE ALL WITH FRAME fStats.
WAIT-FOR CLOSE OF THIS-PROCEDURE.

FINALLY:
    /* Clean up */
    IF VALID-HANDLE(hTTQuery) AND
       hTTQuery:IS-OPEN THEN
        hTTQuery:QUERY-CLOSE() NO-ERROR.
        
    DELETE OBJECT hTTQuery NO-ERROR.
    
    IF VALID-HANDLE(hIdxQuery) AND
       hIdxQuery:IS-OPEN THEN
        hIdxQuery:QUERY-CLOSE() NO-ERROR.
        
    DELETE OBJECT hIdxQuery NO-ERROR.
END FINALLY.