Options

Calculate Size (in bytes) of a Table

moboxmobox Member Posts: 4
Good morning,

I'd like to ask if there was any tool to calculate the size (by adding up field lenghts) of a Table.

Thanks in advance

Comments

  • Options
    garakgarak Member Posts: 3,263
    do you need the BLOB Size of an table :?: or other existing Object :?:

    Take a look into Table "Obect" here is the Field "BLOB Size".

    If you need the size of an file you can use WSH or the following function:
    MyFile.CREATEINSTREAM(IStream);
    FileSizeOfMyFile := GetBlobLength(IStream);
    ....
    ....
    ....
    GetBlobLength(IStream : InStream) length : Decimal
    REPEAT
      BytesRead := IStream.READ(Txt);
      length += BytesRead;
    UNTIL BytesRead <= 0;
    EXIT(length);
    

    Regards
    Do you make it right, it works too!
  • Options
    moboxmobox Member Posts: 4
    Thanks for the code mate but I need a tool which calculates the size of all fields. This would be done by finding out the byte size of the field length.

    Any ideas? :)
  • Options
    garakgarak Member Posts: 3,263
    check table "FIELD"
    Do you make it right, it works too!
  • Options
    moboxmobox Member Posts: 4
    garak wrote:
    check table "FIELD"

    :?:
  • Options
    garakgarak Member Posts: 3,263
    in Table "field" are all fields stored with his type, name and, size. So here you can see the len of a decimal , an integer etc.
    Do you make it right, it works too!
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    mobox wrote:
    Good morning,

    I'd like to ask if there was any tool to calculate the size (by adding up field lenghts) of a Table.

    Thanks in advance

    I assume you are doing this to work out the maximum size of the table, I guess so you don't exceed the imposed limits by NAV in adding too many fields. (I can't think of another reason).

    In which case it is required so rarely that I don't think anyone has come up with this.

    When I need this, I normally just do what Garak suggests, and copy the field definitions into Excel, and do the math from there. Its pretty easy once you put the field lengths into a table. The lengths are all in the ADG.pdf manual.

    Interger = 4, Code = len+2 rounded up to 4 etc.

    I guess you could do it in excel and the upload the excel here for the rest of us.
    David Singleton
  • Options
    jversusjjversusj Member Posts: 489
    hello all,
    I am trying to calculate the size of a table and i have referenced the ADG and the forum. I thought I had it figured out (I was able to reconcile a calculation with a NAV error message). I then tried another table to prove my understanding, and it does not seem right at all. I am calculating a value way over the 4k limit, and yet this table still compiles.

    Options, Integer, Date, Time, Boolean, Dateformula = 4 bytes each
    DateTime = 8 bytes (two 4 byte values)
    Decimal = 12 bytes
    Code field = Max field length +2, rounded to 4 (if 10 + 2 = 12, do i round up to 16, or leave at 12?)
    text field = Max field length + 1, rounded to 4

    Here are the field break-downs - all fields are enabled so I am not counting disabled fields.
    I have 123 fields worth 4 bytes (492 bytes)
    I have 1 datetime worth 8 bytes (8 bytes)
    I have 31 decimal fields worth 12 bytes (372 bytes)
    I have 108 code fields (2020 bytes?)
    I have 39 text fields (1328 bytes?)

    This totals 4220 bytes.

    If I get rid of flowfields I can drop the total to 3872 bytes which makes more sense. I have not seen this confirmed elsewhere regarding table size calculation. Am I taking a convenient assumption, or am I correct that flowfields do not contribute to table size (fundamentally I would not think they do)?

    Thanks!
    kind of fell into this...
  • Options
    FDickschatFDickschat Member Posts: 380
    Your assumption is correct: Flowfields do not contribute to table size. Can easily be checked: Create Fields until you reach the max of a table. Then add another flowfield and you can still save the table.
    Frank Dickschat
    FD Consulting
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    Flow fields are not stored in the table they are stored somewhere else, so they would never be counted in the total?
    jversusj wrote:
    Code field = Max field length +2, rounded to 4 (if 10 + 2 = 12, do i round up to 16, or leave at 12?)

    Remember that ALL fields are double word aligned for performance, so every field rounds up to the nearest 4.
    David Singleton
  • Options
    jversusjjversusj Member Posts: 489
    hi. I am unfortunately back on this topic again. :(

    I thought I had this figured out, but now I am seeing evidence that I do not and that my past estimates of 'space remaining' are not to be trusted.

    When I round code fields up (say code 10 = 16 bytes), I end up with a value > 4000 bytes, yet the table compiles. When I leave the code field at a multiple of 4 (code 10 = 12 bytes), I end up with a value that suggests I have X bytes free on the table. When I attempt to create a field to test that free space, I get an unexpected error. I know I am missing something here.

    First, I copy all of my fields to Excel and sort by Field Class, Data Type, Length.
    I remove all the flowfilter and flowfield fields from consideration.

    I set:
    Options, Integer, Date, Time, Boolean, Dateformula = 4 bytes each
    DateTime = 8 bytes (two 4 byte values)
    Decimal = 12 bytes
    Code field = Max field length +2, rounded to 4
    text field = Max field length + 1, rounded to 4

    I have added fields to a test table until I get an error that the table is 4004 bytes and I am attempting to reconcile. My efforts thus far have not been successful.

    Here is my test table:
    58 Boolean fields (232 bytes)
    24 Date fields (96 bytes)
    2 DateFormula fields (8 bytes)
    1 DateTime field (8 bytes)
    8 Decimal fields (96 bytes)
    6 Integer fields (24 bytes)
    17 Option fields (68 bytes)
    39 Text fields (1328 bytes)
    2 Time Fields (8 bytes)
    109 Code fields (see below)

    For my code fields, I have a limited number of field lengths (2, 3, 10, 20, 30, 43). If I calculate the bytes as 4, 8, 12, 24, 32, 48 (respectively), I get 2080 and a total table size of 3948 bytes. If I calculate the bytes as 8, 8, 16, 24, 36, 52 (respectively), I get 2280 and a total table size of 4148 bytes. Neither calculation gets me to the 4004 byte error message from NAV.

    The Text field calculation is fairly straightforward and I think I have it calculated correctly, without ambiguity. The integer/decimal/boolean fields are really cut and dry. This suggests my error must be in how I am calculating the Code fields. Any help would be appreciated. I want to be confident with my table size calculations.

    What may I be doing wrong? I can copy/paste my excel data here if it would help. Thanks!
    kind of fell into this...
  • Options
    loicloic Member Posts: 2
    Hi,

    I'm really interesting on this point.

    Do you find what was wrong for you ?

    Regards.
  • Options
    alextrimbergeralextrimberger Member Posts: 1
    Following on from jversusj's post - I think I've managed it.

    I copied all of the fields out of my table with all columns, including "Enabled" and "Field Class". Paste into Excel, format as table, filter out disabled fields and flowfields/flow filters.

    The formula I used is -
    =IF(ISNUMBER(VLOOKUP([@[Data Type]],$O$1:$P$10,2,FALSE)),VLOOKUP([@[Data Type]],$O$1:$P$10,2,FALSE),IF([@[Data Type]]="Code",CEILING([@Length]+2,4),CEILING([@Length]+1,4)))
    
    O1:P10 is -
    Option	4
    Integer	4
    Date	4
    Time	4
    Boolean	4
    DateFormula	4
    DateTime	8
    Decimal	12
    BLOB	8
    

    This gave me a total of 4000 bytes, which seems correct to me. If I try to insert a new integer field, the error message states 4004 bytes. Hope this helps someone.
Sign In or Register to comment.