Microsoft KB Archive/77849

= ISAM Record with Aggregate Data Type Causes Faster File Growth =

Article ID: 77849

Article Last Modified on 11/21/2006



This article was previously published under Q77849



SUMMARY
Elements of an ISAM record that are larger than 255 bytes in size are referred to as &quot;aggregate&quot; or &quot;binary&quot; 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.



Example #1
&quot;Month&quot; 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 &quot;test1.mdb&quot; FOR ISAM CustInfo &quot;CustInfo&quot; AS #1 CREATEINDEX #1, &quot;index1&quot;, 0, &quot;CustNum&quot;

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 &quot;chunk&quot; 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 &quot;test2.mdb&quot; FOR ISAM CustInfo &quot;CustInfo&quot; AS #1 CREATEINDEX #1, &quot;index1&quot;, 0, &quot;CustNum&quot;

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.

Additional query words: BasicCom 7.00 7.10

Keywords: KB77849

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.