1. technology and computing
2. software
3. databases

TEMP-TABLE
Performance Tuning &
Other Important Stuff
Dan Foreman
Progress BravePoint
[email protected]
Introduction- Dan Foreman
Progress user since 1984
This year is my 30 year anniversary
with Progress
Guest Speaker at dozens of Progress
Conferences 1990 until Today
Dan Foreman Publications
Progress Performance Tuning Guide
Progress Database Admin Guide
Progress System Tables Guide
All updated through V11.3
Hope to have them in ePub and/or
Kindle format in the next few months
Who Are You?
Progress V6, V7, V8, V9, V10, V11
Miscellaneous
Mobile phones on silent or vibrate
please
TT = Temp Table
V11 = OE11
To Do for Next Time
Does monitoring EMPTY TEMP-TABLE “lie” about the
number of records deleted?
Example showing dynamic temp-tables
Program to show all fields for the various “pseudo
VSTs”
Why does tt-data1/3.p not show the dynamic TTs?
What are the mystery tables (count 2, peak 9) ????
Benchmark the overhead of turning on the TT monitors
Before TT there were WORK-FILEs
100% Memory resident
No indexes
Record access is sequential
FIND FIRST > FIND LAST > FIND FIRST
Increasing the size of a record can be
very expensive
Can potentially crash a system with
three 4GL statements…….
Temp-Table Basics
Similar to DB Tables
CRUD (Create, Read, Update, Delete)
Changes are not logged to the Before
Image or After Image files
Subject to LBI activity unless NOUNDO is used
Type 2 Storage “Areas”
No record locking
No latching (in DB shared memory)
TEMP-TABLE Basics
TT data is written to a Client
temporary file with a DBI prefix
DBI file is similar to a “real” DB but
with no AI, BI, LG, LK files
Location of Client Temp Files can be
set with –T parameter
Temp Files are hidden on Win & Unix
Unix/Linux:
Windows:
-t
DIR /ah
Emptying a TEMP-TABLE
EMPTY TEMP-TABLE tablename
Much faster than a Loop
NO-UNDO
NO-UNDO on the TEMP-TABLE
Definition
If NO-UNDO isn’t used, changes to TT
inside of a transaction are logged to a
Client Temp file with lbi prefix
TT Startup Options
-tmpbsize – the block size used for
the TT “database”
Client Startup Parameter
The default has changed more than once
so we recommend setting it explicitly
-tmpbsize 8 recommended
-Bt – memory buffers for Temp-Tables
Client Startup Parameter
Memory = (-Bt * -tmpbsize) * (# of Clients)
Pre-V11 TT Monitoring Options
None – at least nothing that’s built in
Monitoring the size of the DBI file is
about the only useful metric available
V11 Delayed TT Instantiation
The app doesn't incur the overhead of
instantiating a TT until it's actually
referenced
The pre-V11 model instantiates all
TT's when a new program is run,
regardless of whether they are
referenced or not
Use -nottdelay to revert to the old
functionality
Temp Table Logging
“The OpenEdge Logging Infrastructure
has been enhanced so that application
developers can trace the creation and
deletion of temp-tables in their
applications. This logging capability
strengthens the ability to troubleshoot
applications that utilize temp-tables and
ProDataSets as their primary data
structures.”
VSTs for Temp Tables
“This ABL enhancement allows clients to
gather information about the temp-tables
used by the application via Virtual
System Tables. These tables give the
application access to database activity
and status information, enabling an
application to understand, debug, and
tune the use of temp-tables within their
application at runtime.”
Progress.Database.TempTableInfo Class
“Provides information about a
temp-table and its indexes and provides
static properties and methods for
retrieving and archiving temp-table
information for an ABL session”
TempTableInfo Properties
ArchiveIndexStatistics
ArchiveTableStatistics
TempTableCount
TempTablePeak
TempTableInfo Methods
GetIndexInfoByID()
GetIndexStatHistoryHandle( )
GetTableInfoByID( )
GetTableStatHistoryHandle( )
GetTableInfoByPosition( )
GetVSTHandle( )
Starter Code
USING Progress.Database.*.
Progress.Database.TempTableInfo:ArchiveTableStatistics = YES.
Progress.Database.TempTableInfo:ArchiveIndexStatistics = YES.
Instant Error
Cannot set
Progress.Database.TempTableInfo:ArchiveTableStatistics (15247)
So you lookup Message# 15247:
“Update to the property or field is not
allowed. This can happen if the given
object only provides read-only access to
the property based on its state.”
What ????
V11 Client Startup Parameters
-ttbasetable
-tttablerangesize
Default is 0; no statistics are recorded
Default for the DB –tablerangesize is 50
V11 Client Startup Parameters
-ttbaseindex
-ttindexrangesize
Default is 0; no statistics are recorded
Default for the DB –indexrangesize is 50
Try Again
Using this .pf file
-ttbaseindex
-ttindexrangesize
-ttbasetable
-tttablerangesize
1
100
1
100
Progress.Database.VSTTableId Class
“Provides static properties that identity
the VST for returning specific temptable information for an ABL session”
VSTTableId Properties
ActBufferId
ActIOTypeId
ActSpaceId
BuffStatusId
IndexStatId
TableStatId
UserIndexStatId
ActIndexId
ActOtherId
ActSummaryId
DbStatusId
MstrBlkId
TransId
UserTableStatId
ActIOFileId
ActRecordId
BlockId
FileListId
StatBaseId
UserIOId
Demonstration
basic.p
tt2.p
tt-data1.p
Questions?
Questions?
Conference Evaluations
Thank You!
Contact:
Dan Foreman
[email protected]
+1 541 908 3437 (but not right now please)