It is now trendy to write about the change in how Table Extension objects are reflected in the backend structure. ‘Course I fall behind this trend too. Why? Because it could really be a game changer in terms of performance and everyone that loves this product was really dreaming about it since a while.
It is like when you fall asleep and, instead of jumping sheeps, you have all your development ghosts and customer emails about poor performance surrounding you. And all of them automagically vanishing. Gone.
Yeah, you got it. Precisely that feeling.
And during the day, you are counting hours waiting for version 23.x to be released officially to flip in environment’s upgrade to performance heaven as soon as possible.
… Now get back to planet heart.
The Announcement
When it was first published in July, it was just one single line lost in the 2023 Wave 2 What’s New pages. Something around optimizing data model for table extensions.
After a little while, Microsoft updated documentation with a bit more info on that
Data loads faster in the server | Microsoft Learn
Knowing a couple of good fellas who are behind these changes, they had my full respect in the past and now they get much more. No flashlight, no red carpet for this feature in the What’s New officially. But for me – and I believe many others – just this single entry was worth already the entire release.
The History
Well, it was under the sun that join, join and join again would have made SQL mad (read slowing down) sooner than later.
That was known since the advent of Table Extension paradigm but, if my mind recalls it right, the first throwing the stone and speak officially, it was James Crowter on LinkedIn. Sign the date: February 2020.
Avoiding Table Extension Hell | LinkedIn
And after that, other stones have been thrown. Below just a couple of them, to reference an old and a rather new (pretty interesting) ones
Dynamics 365 Business Central: the impact of tableextensions – Stefano Demiliani
How scary are table extensions? (keytogoodcode.com)
The New Data Model
Many other passionate have already blogged about it. You might find out unleashed in their posts what to expect from October 2023 onwards. I do not want to repeat that and encourage you in read all of them, if you haven’t already done.
Below summarized some of the most interesting ones related to this topic – and feel free to reach out to me if you have one that would like to be enlisted, I will be happy to add it. As of now, it seems an Italian affair, but I swear it is not -:
The same companion table – Simone Giordano
Just to add more on these “experiments”, also our company R&D did few research and tests in these days and find out a promising landscape from October ahead, in relation of Table Extensions.
Cannot share further details on specific tests made, that is the max that I am allowed to say about it (but you could reach out to me @Directions EMEA in Lyon in November to have a beer/chat 😉).
Fine. Then what I could provide more within this blog post that others haven’t already posted?
The Switch
First. A small terminology alias on the now-unique SQL companion table: it could also be called combined table extension table.
How is this change done under the hood? That is a deep secret (in Italy we call it: Pulcinella’s secret). As many others, we have just picked up the bits from the artifact in preview and converted few databases (from a vanilla Cronus up to ones with a discrete number of AppSource and Per Tenant Extensions).
Basically, the switch from the old to the new structure is happening when performing Sync-NavTenant operation.
Running a SQL profiler trace while synchronization happens, we find out that the sequence is: CREATE TABLE (the new combined table extension table), INSERT INTO the new table of all joined records and in the end DROP TABLE repeated N times, with N = Number of old companion tables.
Something like the following (NOTE: GUIDs have been created randomly 😉):
Followed by inserting data using the N Join from the old companion tables into the new combined table extension table
And finalized by a deep cut (DROP) of all old companion tables
A picture worth a thousands words:
One last thing to be noticed. To perform all the mambo-jumbo, a couple of fields have been added in the $ndo$navappschemasnapshot table: schemahash and movestatus. These seems to be vital for all the magic happens at SQL Server side.
Et voilà: the new model is served.
The Downside
The big pros on performances introduced with this change in data model are pure gold and something never seen since a while.
But I would not want to be in the shoes of who has implemented direct data pumps from on-premises deployments (SQL Server or Azure SQL, it does not matter) since these poor guys have to refactor heavily the connection structure. The brave ones using Dynamics 365 Business Central Online will have no impact on that since APIs and/or Connectors (that are based on APIs themselves) expose natively a summarized table structure for any table entity.
Another con, could be the time to upgrade to 23.x from older versions.
Aside that, other downsides have all to be discovered – if any -. And In any case, Microsoft will bash all of them down at the speed of cloud since this is really a tremendous opportunity to improve performances. And it is here to stay.
Dynamics 365 Business Central tales, myths, legends. And something serious. 