DF_FILE_MAX_RECORDS

Level

Table

Supported by

The Embedded Database

Type

Numeric, permanent

Access

Read / Write

Values

1 ~ 16,711,679

Remarks

The DF_FILE_MAX_RECORDS attribute holds the guaranteed maximum number of records that can be stored in a table.

This attribute is part of the basic set of attributes that must be supported by all drivers. However, it does not make sense in some back ends. That is why the Pervasive.SQL and DataFlex SQL Drivers will return a constant value for every table accessed; trying to set the attribute will be ignored.

In the Embedded Database, the value of this attribute is used to determine the number of levels in the indices defined on the table. An index is a tree-like structure. Depending on the segments in the index, an index node can have a constant number of child nodes. When DF_FILE_MAX_RECORDS is set, all indices are given the number of levels to accommodate that number. This attribute does not hold the “real” maximum number of records. It is usually possible to store more records than the value in this attribute.

The indices of a table in the Embedded Database are pre-allocated. The tree structure needed to store the information is already in place; only the leaf level must be filled. This ensures constant performance when using the index, regardless of the number of records in the table. Finding a record will be just as fast when there’s only one record in the table as when there are millions of records. Other databases will automatically let an index grow to accommodate the number of records, including expanding the tree with another level. This approach uses less disk space but results in unpredictable performance. For such databases, the time it takes to find a record depends on the number of records in the table.

Assume a table with two indices defined: index 1 can have 3 child nodes per node, and index 2 can have 5 child nodes per node. The following table lists the possible number of records that can be indexed by the indices defined on the table.

Number of Levels	Index 1	Index 2
1	3	5
2	9	25
3	27	125
4	81	625
5	243	3125
6	729	15625
7	2187	78125
8	6561	390625
9	19683	1953125

The “real” maximum number of records for different settings of DF_FILE_MAX_RECORDS is listed below:

Max Records	“Real” Maximum	Levels in Index	Levels in Index 2
10	25	3	2
100	125	5	3
1000	2187	7	5
10000	15625	9	6

In the table above, the index that defines the “real” maximum number of records is indicated by making the value for its levels bold.

Note that sometimes increasing the DF_FILE_MAX_RECORDS value will not result in a change of the “real” maximum. If, in the example above, we were to increase the value from 1000 to 2000, the “real” maximum would not change.

Some applications use logic to automatically increase the maximum number of records setting when the actual number of records in the table exceeds a certain threshold, more than 80% filled, for example. The operation of increasing the maximum number of records involves a rebuild of all indices defined for the table. That is quite an expensive operation. One would want to be sure the operation does make sense before starting it. That is why you should make sure you use the real maximum to check against.

The sample code below demonstrates how the “real” maximum number of records for a given table can be calculated.

Define C_MAXINDEXCAPACITY For |CI$00feffff

Function IndexCapacity Integer iKeylength Integer iLevels Returns Integer
    Integer iCapacity
    Integer iBlocking
    Move (1024.0 / (iKeylength + 3)) To iBlocking
    Move (((iBlocking ^ iLevels) - 1) Min C_MAXINDEXCAPACITY) To iCapacity
    Function_Return iCapacity
End_Function // IndexCapacity

Function RealMaximum Handle hTable Returns Integer
    Integer iLastIndex
    Integer iIndex
    Integer iNumSegments
    Integer iKeyLength
    Integer iLevels
    Integer iIndexCapacity
    Integer iTableCapacity
    Move 0 To iTableCapacity
    Get_Attribute DF_FILE_LAST_INDEX_NUMBER Of hTable To iLastIndex
    For iIndex From 1 To iLastIndex
        Get_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To iNumSegments
        If (iNumSegments > 0) Begin
            Get_Attribute DF_INDEX_KEY_LENGTH Of hTable iIndex To iKeyLength
            Get_Attribute DF_INDEX_LEVELS Of hTable iIndex To iLevels
            Get IndexCapacity iKeyLength iLevels To iIndexCapacity
            If (iTableCapacity = 0 Or iTableCapacity > iIndexCapacity) ;
                Move iIndexCapacity To iTableCapacity
        End
    Loop
    Function_Return iTableCapacity
End_Function // RealMaximum

The sample below demonstrates how to loop through a number of tables and set the DF_FILE_MAX_RECORDS attribute for the tables in the array tables to 10,000 if it is not at least that number already.

Procedure StartProcess
    String sDataFolder sTable sTableToAffect
    Integer i iNumberOfTables iMaxRecordsCurrent iMaxRecordsDesired
    String[] tables
    Boolean bExists
    Handle hTable

    Move "C:\DataFlex Projects\Project One\Data" to sDataFolder
    Move 10000 to iMaxRecordsDesired
    Move "Customer" to tables[0]
    Move "Vendor" to tables[1]
    Move "Part" to tables[2]
    Move "Acct" to tables[3]
    Move (SizeOfArray(tables)) to iNumberOfTables
    Decrement iNumberOfTables

    For i from 0 to iNumberOfTables
        Move (sDataFolder + "\" + tables[i] + ".dat") to sTableToAffect
        File_Exist sTableToAffect bExists
        If bExists Begin
            Open sTableToAffect as FlexErrs
            Move FlexErrs.File_Number to hTable
            Get_Attribute DF_FILE_MAX_RECORDS of hTable to iMaxRecordsCurrent
            If (iMaxRecordsCurrent < iMaxRecordsDesired) Begin
                Structure_Start hTable "DataFlex"
                Set_Attribute DF_FILE_MAX_RECORDS of hTable to iMaxRecordsDesired
                Structure_End hTable
            End
            Close FlexErrs
        End
        Else Begin
            Send Stop_Box ("Table " + sTableToAffect + " not found") "Error"
        End
    Loop
    Send Info_Box "Done"
End_Procedure

Send StartProcess