How to calculate the size of records and tables on disk

Hi,
I had a database which went from 400 Mb to 1,7 Gb in 2 months !
But how to guess where is the datas in tables and in records ?

I did it and implemented it in ogDevTools, which is free for compiled version now (found in ogTools HDI).
But I need to improve it and I want to know precisely how to calculate with a better accuracy.

So I expose her the code of the low level method, how to calculate the space taken with a specific field - then you can loop on fields for record, and loop on tables for table.


C_POINTER($ptr_field)
$ptr_field:=$1

$type:=Type($ptr_field->)
Case of 
	: ($type=Is alpha field)
		CONVERT FROM TEXT($ptr_field->;"UTF-16";$bytes_blob)
		$size:=BLOB size($bytes_blob)
		  // ok ? Stored in UTF-16 ?
		
	: ($type=Is text) 
		CONVERT FROM TEXT($ptr_field->;"UTF-16";$bytes_blob)
		$size:=BLOB size($bytes_blob)
		
	: ($type=Is date)
		$size:=8
		  // Ok ? 
		
	: ($type=Is time)
		$size:=8
		  // Ok ?
		
	: ($type=Is boolean)
		$size:=1
		  // Ok ?
		
	: ($type=Is integer)
		$size:=2
		  // Ok ?
		
	: ($type=Is longint)
		$size:=4
		  // Ok ?
		
	: ($type=Is integer 64 bits)
		$size:=8
		  // Ok ?
		
	: ($type=Is real)
		$size:=8
		  // Ok ?
		
	: ($type=Is BLOB)
		$size:=BLOB size($ptr_field->)
		  // Ok ?
		
	: ($type=Is picture)
		$size:=Picture size($ptr_field->)
		  // It really gives the size of what is saved ?
		
	: ($type=Is object)  // appliquons le principe du texte
		CONVERT FROM TEXT(JSON Stringify($ptr_field->);"UTF-16";$bytes_blob)
		$size:=BLOB size($bytes_blob)
		// Ah ah ! That's the hard stuff... What do you suggest ?
		
	Else 
		$size:=0
End case 
$0:=$size

  // And what about junk bytes for every field, and for every record ?
  // Based on my calculation, I estimate 133 extra bytes per record.
  // Is it true ?




Hi Olivier :slight_smile:

Does this knowledge base article help at all? I’m not sure what will happen with object fields now, given they have been made available through SQL since this not was written.

https://kb.4d.com/assetid=77945

Hi Justin :wink:

Thanks for the article I had a look in already.
Ah ? Object available now in sql is good to know, I’ll do a try.

This tool “tables size” added and soon available in ogDevTools.

And also you can have a look at a specific record of a table, with a given method in the component. Very helpfull: oups, this image is 175 Mb !!!

Olivier,
The calculation that you use for JSON is not very accurate. I’ve recently done some experimentation with this, and also using my “LogTools” program: I think I’ve found an accurate calculation for Object field size. It’s like this:

Object Storage Space:
19 bytes (top level header): Uncertain about this number in actual records: Probably just a portion of this.
2 bytes (data type header)
For each property in the object:
3 bytes + (NameLenth*2) bytes

  • Property Value Length:
    boolean/null: 0
    numbers: 8 bytes
    Dates: 8 bytes
    Times: 8 bytes
    Text: 4+(2*Length) bytes
    Object: 2 bytes (data type header) + object size (calculation)
    Collections: (4 bytes) + (1 byte/element) + (Value size (same as above))
    For objects or collections that are shared by multiple parent objects:
    then it’s 16 bytes overhead for the sharing: and the sub-object just takes it’s own space once.

Note also that each record in the data file is stored in 128 byte increments: so a 120 byte record will take 128 bytes: and a 130 byte record will take 256 bytes.

1 Like

Hi Tony,
did you try a “to blob” from the field object?
I’d like to know if blobing + blob size is a alternate way to evaluate the size…

Big thanks to you Tony !
That was my post’s request.
I’ll take in account all of what you say.

Yes arnaud, I’ll try this also. For sure better than my first attend to it.

Hi Tony, I’m not sure with this : what it is, where ?
For each property, then, 2 bytes + 3 bytes ?

I know this is a but confusing:
So: for every property in the object, it seems to be

  • 3 bytes
    • (Length of the name * 2) bytes
    • the property value length (varies per data type)

It’s pretty easy to do a test: use VARIABLE TO BLOB to put an object into a blob: and check the blob size. What goes into the blob is the same data size that would go into a record (with a possible header difference of 19 bytes that I’m un-certain of)

So: for a test, do this:

	$ob:=New object
	For ($ii;1;1000)
		$ob[""+String($ii;"0000")]:=1 `Try different values here
	End for 
	ARRAY TEXT($aPN;0)
	OB GET PROPERTY NAMES($ob;$aPN)
	VARIABLE TO BLOB($ob;$data_x)
	$Size_i:=BLOB size($data_x)
1 Like

Ok your example is very understandable, thanks.

But finally, what I need is only :

	: ($type=Is object) 
		VARIABLE TO BLOB($ptr_field->;$bytes_blob)
		$size:=BLOB size($bytes_blob)

Is-it so simple ?

Olivier: Yes!
(it might be 19 bytes smaller than the blob size: that’s the overhead I’m un-certain about: I’d have to do some digging to be sure)
I actually thought of that this morning, and was planning to write you. You’re one step ahead of me.

Ah ah ah ! One step ahead ? Not sure !
With the given code, I was not able to find about 19 bytes in object ?

I’m not sure these 19 bytes have to be considered.

As usual, a blob resulting from “variable to blob” starts with “RVLB” (hex 5256 4C42, I suppose it stands for "blvr, “variable vers blob” in french, inverted since little endian), followed by the variable type (here 26 hexa = 38 decimal = object). After I gave up.

But I don’t think this header exists in the data, it seems unnecessary to embed it, the field type is obviously known by 4D. To know how it’s made in data, we’d need a “field to blob” command instead, or open a test data with an hexadecimal reader and cry. Or trust Tony’s values and neglect the header, much easier.

Just for information, I had to copy/paste a table from v16 to v13 recently, that table had an object field. When pasted in v13, the field had a blob icon.

:rofl: blob icon !!! Interesting…

What else could it be? What sounds amazing to me is not the container and the internal structure, but the way we can query these fields.
About reading that internal structure, during my daily prisoner walk I thought about this: change an existing object field type to blob, use blob to document to read and export some, dive in with an hexa reader.