Calculate Size (in bytes) of a Table

mobox · 2008-08-27

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

garak · 2008-08-27

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

mobox · 2008-08-27

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?

garak · 2008-08-27

check table "FIELD"

mobox · 2008-08-27

garak wrote:

check table "FIELD"

:?:

garak · 2008-08-27

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.

David_Singleton · 2008-08-27

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.

jversusj · 2010-01-18

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!

FDickschat · 2010-01-18

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.

David_Singleton · 2010-01-18

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.

jversusj · 2010-06-07

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!

loic · 2011-11-29

Hi,

I'm really interesting on this point.

Do you find what was wrong for you ?

Regards.

alextrimberger · 2015-09-02

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.

Calculate Size (in bytes) of a Table

Comments

Categories