The new table extension model: the storage side

NOW YOU SEE IT

Take a sample database on-premises 22.whatever with approx. 175 GB and, during the upgrade process to 23.0, run Sync-NAVTenant to move it into the new table extension data model (combined table extension tables).

NOW YOU DON’T !

Look at the overall dimensions post sync-navtenant: 173 GB (-2GB, -1.1% Total Space).

THE TRICK REVEALED

I am not getting crazy. Well, not more than I currently am, or that I know of. And you might say “pfwww, 2GB. Peanuts!…”.

Well, give me back those peanuts, then!

In SaaS, every byte counts. With the default 80 GB free capacity limits per tenant (1 Production and 3 Sandboxes – that I swear they are a copy of the production in 90% of the cases -), the new extension model not only looks super-promising in terms of performances, but it has also a small payback in storage space.

But where all of this is coming from?

When moving fields from an old companion table, you move all of them except for the Primary Key (PK) fields. And this is happening – obviously – from the 2^nd companion table and onwards (from the 2^nd Table Extension and onwards, in AL-ish).

Let’s put some meat on the table. Eat this! (NOTE: this is coming from a SaaS environment).

Table Name	Table No.	No. of Records	Number of Joins
Sales Invoice Line	113	540.213	14
Sales Line	37	343.072	14
Sales Shipment Line	111	349.480	10
Item Ledger Entry	32	1.431.724	4

PK Sales line:                            key(Key1; “Document Type”, “Document No.”, “Line No.”)

PK Sales Invoice line:             key(Key1; “Document No.”, “Line No.”)

PK Sales Shipment Line:       key(Key1; “Document No.”, “Line No.”)

PK Item Ledger Entry:           key(Key1; “Entry No.”)

Do the math. The formula to apply:

PK average occupancy * No. of Records * (Number of Joins -1)

 And considering the above examples:

Sales line:                            (“Document Type” + “Document No.”+ “Line No.”) * 4.459.936

Sales Invoice line:            (“Document No.”+ “Line No.”) * 7.022.769

Sales Shipment line:       (“Document No.”+ “Line No.”) * 3.145.320

Item Ledger Entry:           (“Entry No.”) * 4.295.172

It is trivial to say that the PK occupancy is different for every record, depending on the current column value. This is the reason why I have added the average occupancy in the formula. Otherwise, if you prefer, you could change it into the geeky:

Where:

PK = Primary Key fields occupancy

n = number of records in the table

N = Number of companion tables.

CONCLUSION

Moving to 2023 Wave 2 (version 23) will also have a return in storage space depending on

1. The number of table extensions currently deployed.
2. The number of records in the table.
3. The quantity of the fields in the primary key.
4. The quality of the fields in the primary key.

And remember: every free byte count. Now you see it… Now you don’t.