Dynamics 365 Business Central 2023 Wave 2 release (v23) introduces important changes on the new data structure for table extensions (I’ve talked about that time ago here).
Instead of creating a companion table for every tableextension object you have for a specific table (as we have until version 22.X), Dynamics 365 Business Central v23 will store all tableextension fields for a specific table in a single companion table.
If you imagine to have a main table and 2 extensions extending that table, the database structure in V22 and V23 will be as follows:
In V23 we have only ONE single companion table containing all fields.
When I wrote the first post announcing this change, some of you asked about performances.
What’s the real impact on performances after this data structure change?
To really discover if we have a gain on performances and measure it, I’ve prepared some tests. I created a MAIN extension that defines a Data table with some fields (Text, Decimal etc.). Then I’ve created other 5 extensions extending that Data table (adding other Text and Decimal fields via tableextension objects).
I’ve then installed the 6 extensions (main + 5 dependent extensions) on a Dynamics 365 Business Central version 22.5 environment and on a Dynamics 365 Business Central version 23 environment.
Here the extensions installed on the 22.5 environment:
And here the same extensions installed on the V23 environment:
Then I’ve decided to create the following 4 performance tests:
- Creation of 100000 records.
- Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table (no partial record loading).
- Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table + sum of the Amount 2 field defined in a dependent extension (via tableextension of the Data table) (no partial record loading).
- Deletion of all the 100000 records of the Data table.
What are the results?
Tests executed on Dynamics 365 Business Central version 22.5
Test 1: Creation of 100000 records:
Test 2: Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table:
Test 3: Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table + sum of the Amount 2 field defined in a dependent extension (via tableextension of the Data table):
Test 4: Deletion of all the 100000 records of the Data table:
Tests executed on Dynamics 365 Business Central version 23
Test 1: Creation of 100000 records:
Test 2: Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table:
Test 3: Looping through all 100000 records and performing a calculation (sum) on the Amount field defined in the MAIN extension in the Data table + sum of the Amount 2 field defined in a dependent extension (via tableextension of the Data table):
Test 4: Deletion of all the 100000 records of the Data table:
What’s the verdict of the performance test?
The number clearly say that Dynamics 365 Business Central 2023 Wave 2 release (version 23) is absolutely a lot more fast on reading and writing bulk amount of data. The new data structure for companion tables helps on reducing the number of tables to fill when a record is created or to empty when a record is deleted. The new data structure also helps on reducing the number of joins, so the underlying SQL queries are much faster.
Probably charts give you a more clear vision of the performance gains:
- Writing performances increase by +90.3433%.
- Deleting performances increase by +44.4524%.
- Looping through records and perform a calculation on a main table field performances increase by +51.726%.
- Looping through records and perform a calculation on a main table field + companion table field performances increase by +56.3673%.
And this percentages are higher if you have more records on the table.
Dynamics 365 Business Central 2023 Wave 2 release (v23) is absolutely a release where you need to move if you want to optimize performances.
The new release also contains features for reducing locking (like ReadCommitted transaction isolation level and tri-state locking, see here).
For tri-state locking:
- All reads have the READUNCOMMITTED hint applied as long as no writes has been done to the table in the current transaction nor
LockTablecalled on a record of the table type. - If writes have been done against the table in the current transaction, further reads will have the READCOMMITTED hint applied.
- If
LockTablehas been called on a record of the tables type in the current transaction, further reads will have the UPDLOCK hint applied.
The tri-state locking feature key can be enabled/disabled via the Feature Management page (check for Enable Tri-State locking in AL). It affects the entire environment and is first enabled from the next session:
Stefano Demiliani 
Quite impressive really.
Curious…What happens to extensions when it’s upgraded from say 22.5 to 23.. will it bring them into 1 extension table, or will it only apply to newly published ones?
LikeLike
The data upgrade process will move all data to a single companion table automatically and it will remove the old (and not used) comapnion tables.
LikeLiked by 1 person
Regaring BC this is fantastic. Great explanation. But regarding integrations directly to SQL, would it be possible for you to add SQL screendumps, to view the difference there?
LikeLike
The only difference is that now you have a single companion table instead of N. No other differences. But I will add it if needed.
LikeLike
It would be great if you could add it, to visualise the data strcture difference. Thank you very much! Much appreciated!
LikeLike
What if two or more extensions define a column with the same name?
I hope they insert some kind of prefix or postfix on the column name because otherwise is a total mess.
LikeLike
Exactly like now two extensions cannot extend the same table defining a new field with the same name. You receive an error.
LikeLike
Shouldn’t the use of namespaces allow same name in different extensions?
LikeLike
@GrumpyNAVdev: yes but this is not working at the moment (namespaces are not ready to be used now). In the future two extensions could add a field with same name on different namespaces but under the hoods it create two fields with two different names.
LikeLike
Sigh, you forgot to include the actual baseline. You have included tests matching tables like the Sales Line where Microsoft has the base table. But you didn’t include the “Our table” example where we have been specifically putting all table data into a “Core extension” to avoid this sort of abysmal performance penalty. Do we have to keep doing that? Have Microsoft fixed the performance problem in general? Or have they left an issue which would have been better fixed by switching back to the V1 table modification methodology where adding columns is handled by SQL?
How about something like your previous https://demiliani.com/2020/12/28/dynamics-365-business-central-the-impact-of-tableextensions/ but with (a) Core extension has all fields (b) V22 with lots of extensions (c) V23 with lots.
LikeLike
Sorry but I’ve not understood what I missed. The new data structure simply creates 1 single companion table for an extended table (MS table or custom table). No more N joins but a single join now. The post simply describes that.
LikeLike
Yup. Which does improve the status quo considerably. But, as you know, in the “good old days” (V1 extensions and before) there would be no distinction between a “Base table” and a “Companion table”. All columns would be added to “the table” and SQL would be able to process that single table as quickly as it’s size (and the wide select statements) allowed. If you have narrow selects included columns on indexes could help without significantly impacting most operations.
Today if we have multiple extensions with a base table defined in one of them (ie: not a Microsoft table) and extended in others we generally have to move all the data dictionary definitions into the base table to get performance. With this change Microsoft have reduced it to two tables rather than six (N). But according to your previous blog these two tables are still a performance hit vs throwing everything into the base table. The important question is how much of a hit; Microsoft seem to have decided that it’s small enough to ignore. Should we agree?
LikeLike
Yes it’s always a performance hit compared to old days where we have ony 1 single table. Now a data retrieval operation requires at max 2 joins, not N joins as until version 22. Much more performant (you can meaure by yourself) but I agree there’s still more a hit compared to no joins. A single join is however a great improvement (no big performance degradations).
LikeLike
I just hope they will allow creating indexes across base and extension tables. Of course not a regular index, but they could make an indexed view which I believe will be used by joins.
LikeLike
Work in progress on it…
LikeLike
Do you see any performance problem during deployof an extension on large databases?
LikeLike
No. It requires more time only during tenant upgrade because the process needs to move data.
LikeLike
Is there any information from Microsoft to estimate the duration of the upgrade process (for the extension tables) for large databases?
LikeLike
There’s no an official estimate at the moment but internally MS has done migration tests of very large databases and process will be completed in few hours. So you should not care too much about that.
LikeLike
Is there a maximum record size limit anymore?
LikeLike
The Azure SQL row size limit is now handled differently. SQL Server supports row-overflow storage, which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row. For more info: https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16#large-row-support
LikeLike