ISAM Record with Aggregate Data Type Causes Faster File Growth (77849)

SUMMARY

Elements of an ISAM record that are larger than 255 bytes in size are referred to as "aggregate" or "binary" data types. Aggregate data types are stored in a separate area of the database file, physically outside the associated record. The record itself contains only a small reference field, which points to the appropriate aggregate data type. This scheme allows records to hold more data than the maximum ISAM record size (1950 bytes).

While the above scheme allows a record to hold more than 1950 bytes of data, it is less efficient than directly storing data in the record itself. Aggregate data types are stored in a separate area of the database, and management of this separate area requires overhead in addition to that already in the record. This extra overhead can make an ISAM file appear to grow faster than it should. This is expected behavior; the additional overhead associated with storing aggregate data types will cause the database file to be larger than if you stored all data in the record itself.

This information applies to the Microsoft Basic Professional Development System (PDS) versions 7.0 and 7.1 for MS-DOS and version 7.1 for MS OS/2.

MORE INFORMATION

Example #1

"Month" is an aggregate type and causes the data base to be 622,592 bytes with 1000 records:

TYPE MonthInfo
        SerialNum AS DOUBLE
        DailyAmnt AS INTEGER
        Chrg AS LONG
END TYPE

TYPE CustInfo
        CustNum AS LONG
        Flag AS STRING * 1
        FirstDate AS DOUBLE
        LastDate AS DOUBLE
        Month(1 TO 31) AS MonthInfo

END TYPE

DIM testrec AS CustInfo

OPEN "test1.mdb" FOR ISAM CustInfo "CustInfo" AS #1
CREATEINDEX #1, "index1", 0, "CustNum"

FOR x = 1 TO 1000
  INSERT #1, testrec
NEXT
END

The following is an ISAMPACK report for the table above:

   Column Name       Column Type       Maximum Size
   -----------       -----------       ------------

   CustNum           Long              4
   Flag              VarText           1
   FirstDate         Double            8
   LastDate          Double            8
   Month             Binary            64K

The Month column, which is larger than 255 bytes in size, is an aggregate (or binary) data type. Only a reference to the location of the actual data is stored in the record itself; the actual data for the Month column would be stored in a separate area of the file where all aggregate data types would be managed. Note that ISAMPACK reports a maximum size of 64K for the Month column; this is the limit for all aggregate data types, and not indicative of the amount of space the month column actually occupies. The actual size of each Month column is 14 * 31 = 434 bytes.

In the example below, the information from the Month column has been broken into three separate arrays, each of less than 255 bytes. Because no column of the record is now over 255 bytes, no aggregate data type is created. For a file of 1000 records, the data base in question is 589,824 bytes, or about 5 percent smaller than the one above. Because ISAM files grow in 32K chunks, the example above using aggregate data types is really only one "chunk" larger than the database without aggregate data types.

Example #2

Aggregate types have been eliminated and the resulting database is 589,824 bytes with 1000 records:

TYPE CustInfo
        CustNum AS LONG
        Flag AS STRING * 1
        FirstDate AS DOUBLE
        LastDate AS DOUBLE
        SerialNum(1 TO 31) AS DOUBLE
        DailyAmnt(1 TO 31) AS INTEGER
        Chrg(1 TO 31) AS LONG
END TYPE

DIM testrec AS CustInfo

OPEN "test2.mdb" FOR ISAM CustInfo "CustInfo" AS #1
CREATEINDEX #1, "index1", 0, "CustNum"

FOR x = 1 TO 1000
  INSERT #1, testrec
NEXT
END

The following is an ISAMPACK report for the table above:

   Column Name       Column Type         Maximum Size
   -----------       -----------         ------------

   CustNum           Long                4
   Flag              VarText             1
   FirstDate         Double              8
   LastDate          Double              8
   SerialNum         VarString           248
   DailyAmnt         VarString           62
   Chrg              VarString           124

As you can see, storing all data within the record itself is more size-efficient.