Considerations for working with large tables

Starting with Version 6, there are new considerations for dealing with large tables in tableBASE. Both the local TSR and VTS-TSRs are now in Data Spaces. The maximum size of a Data Space is 2G.

In addition you should consider the region size, the number of tasks running in the region, processing sequence, and the frequency and type of access activity. The choice of one search strategy over another can impact system performance.

What is large? For the purposes of the following recommendations, a table is considered very large when the total size is 50 MB or larger, or the number of records is greater than 1 million. The maximum row size a table can have is 32 KB (the maximum positive value in a half-word).

Recommended

  • Binary or Hash search methods for tables with more than 300-400 rows.

Not recommended

  • High frequency of Inserts with low expansion factor.
Note:
Starting with Version 6, all tables are now treated as Pointer tables and have an Index, even if they are listed as being True tables. The change is transparent to the user, but Pointer and True tables are now treated internally as Pointer tables.

TSR

The TSR is in a Data Space starting with Version 6. Rollouts of tables (using TBTSLIB) is no longer supported with this release. The Data Space must be large enough to hold all tables that were paged tables in prior releases.

Binary searches

Starting with Version 6, all searches cache the first 4 bytes of the key. This greatly improves search performance. With a large table, where these 4 bytes may be the same throughout all rows of the tables, these benefits may not be realized. Better results can be achieved by using a key that varies within the first 4 bytes.

Open/Store activity

The size of the table determines the load on the I/O system, possibly causing slowdowns in an online environment.

Creation method

For a keyed table, creation in sequential order can be much more efficient than in random order.

If created in random order, consider defining the table as Random initially, and after loading the Data Table, change the definition to Sequential.

Expansion factor

This controls the amount of additional memory that will be obtained to expand non-Hash tables. It should be set sufficiently large so all insertions between opens of the table will fit. For example, if the table has 100,000 rows, and a maximum of 10,000 rows are inserted with no deletions when it is opened, an expansion factor of 100 (10%) is sufficient.