Hi, Readers.
Dynamics 365 Business Central 2023 wave 2 (BC23) is generally available. More details: General availability: Dynamics 365 Business Central 2023 release wave 2 (BC23)
I will continue to test and share some new features that I hope will be helpful.
Better performance when working with multiple extensions:
Business value:
Improving the data loading speed on the server benefits all usage scenarios, including web service integrations, background jobs, and interactive sessions.Feature details:
This release focuses on optimizing an essential part of the data stack in the Business Central server: The data model for table extensions.In previous releases, when a developer extended a table, the fields from the table extension were stored in a separate table called a companion table in the database. This design was chosen to make the app lifecycle (install, update, uninstall) nondisruptive to normal usage of the base table. Unfortunately, it also had impact on the runtime performance on the system because the Business Central server needed to join data from table extensions when reading data from a table. As an example, for a table with 7 table extensions, the server might need to do a 7-way join.
In this release, we change the data model for table extensions so that added fields from all extensions to a table are now stored in the same companion table. In this new model, the server will never need to do more than a single join of the base table to its companion table. We expect this to drastically reduce the performance impact of table extensions to base tables, while still maintaining good stability of app lifecycle events. The cloud migration tool supports this new schema when migrating data from earlier versions to version 23.
Note that this change does not impact the table model as seen from the AL language, so no code changes are needed from extension/app publishers. An additional benefit of this new data model on the database is that developers can now organize code with table extensions the way they want to, without having to think about the performance impact of where table extensions are located in apps.
https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/data-loads-faster-server
Okay, this is a big change in this major update. First let’s take a look at the data structure so far. Taking the standard Customer table in BC22.4 as an example, in the On-Premises version of the local SQL database, you can see that there are two Customer tables.
The default naming method of the BC table is ‘Company Name’ + ‘$’ + ‘Table Name’ + ‘$’ + ‘App Id’. Company Name is related to the DataPerCompany Property, so we will not discuss it further this time.
DataPerCompany Property: Sets a value that indicates whether the table data applies to all companies in the database or only the current company (The default value is true).
CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972: Base
CRONUS International Ltd_$Customer$70912191-3c4c-49fc-a1de-bc6ea1ac9da6: Table Extension
To make it easier to understand, I created five table extensions to the Customers table.
The contents of one of them:
Publishing to the BC 22.4 On-Premises environment:
Extension details:
In Extension Management page:
In Customer Card page:
If you go back to SQL Database at this time, you will find that the tables has been added. So for customer table with 5 table extensions, the server might need to do a 5-way join. More details for Joins (SQL Server)
So the previous data structure was as follows: Six tables
Even though the Join has no impact on the final result, it still affects performance. So what is the new data model for table extensions in BC23?
Business Central 2023 release wave 2 (BC23) on-prem is available today.
Download link to on-prem version: Download Microsoft Dynamics 365 Business Central On-Premises (All Versions) – Direct download links
I downloaded and installed it right away.
Version: W1 23.0 (Platform 23.0.12831.0 + Application 23.0.12034.12841)
After opening the standard database, we can find that there are also two Customer tables. But the name of the second one was changed.
CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972
CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext (Companion table)
Then I publish the same five extensions that were just published in BC22.4.
On the page:
Go back to the database and you will find that the table has not been added.
The fields in each table extension are added to table CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$ext (Companion table).
As you can see from the above, in the new version, if one or more table extensions exist, Microsoft creates a new ext table/companion table (customer$ext) to hold all the new fields that are not in the standard/base application. The app id of the extension is then used in the field name to differentiate it. So no matter how many extensions and table extensions you have, in the actual database, there will always be only two tables👏👏👏. So in this new model, the server will never need to do more than a single join of the base table to its companion table.
The new data structure is as follows: Two tables
This change does not impact the table model as seen from the AL language, so no code changes are needed from extension/app publishers.
Very nice improvement. Microsoft expect this to drastically reduce the performance impact of table extensions to base tables, while still maintaining good stability of app lifecycle events. Stefano Demiliani and waldo have already performed performance tests on the new models, and you can refer directly to their posts.
Business Central vNext (v23) – The “new data model” | waldo.be
END
Hope this will help.
Thanks for reading.
ZHU
コメント