What's New: Server and Database - A Faster Data Stack (2023 release wave 2)

Overview

The session explains why table extensions stored in separate tables caused n-way joins, and how the new schema keeps one companion table for all extension fields. It reports read performance as the biggest gain, with smaller gains for modify, insert and delete. Gains depend on the number of extensions and on code organization, and a single extension shows little difference.

It then covers cloud migration support in version 23, database views for on-premises ETL and BI tools, SetLoadFields and a new method for base table owners, cleanup of orphaned extension data, and a new locking model based on read committed. It closes with upcoming changes and a recommendation to test with BCPT.

Key points

• Table extension fields are now stored in one companion table per base table, so a read needs one join instead of n. A customer with 70 extensions goes from a 70-way join to a one-way join.
• Reported gains: reads scale nearly flat as extensions increase, modify up to 2x faster, insert about 2x, delete 2 to 5x. The benefit is visible mainly with 2 or more extensions. Server caches must be flushed when measuring reads.
• Version 23 includes cloud migration support from earlier versions to the new schema. On-premises upgrades write more data and may take longer.
• On-premises ETL or BI tools with direct database access can keep working through database views that mimic the new schema. Direct database access is not officially supported, and code samples are on GitHub.
• SetLoadFields is still useful for limiting loaded fields and hitting covering indexes. A new method lets base table owners set all base fields at once, shown on the VAT Entry table.
• The new locking model uses read committed instead of update lock for new record instances. It is on by default for new tenants. Upgraded tenants opt in via feature management and must restart the environment. It will be enabled by default in the next version and becomes the only locking model from version 25. Test it with BCPT for errors, locking issues and deadlocks.
• Orphaned extension data left after uninstall now affects performance and capacity, and can be deleted in installed extensions. Force Order and Loop Joins server settings are removed in 2024 release wave one. On-premises writes to the app database stop in the next release.

Features

AL objects mentioned

As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.

• table 254 “VAT Entry” at 13:07
• table 4 “Currency” at 17:51

Quotes

• 0:58 “the primary key for the base is also the primary key for all companion tables that’s the name we call these physical tables”
• 1:34 “the SQL Server query Optimizer has a hidden Li limit around seven or eight joints where it will stop optimizing and simply use one”
• 3:49 “now we simply have the base table and we have one companion table that stores the fields for all table extensions and this minimize”
• 4:30 “read performance is definitely where we see the most impact but be careful when you measure here because the NST the server caches data”
• 8:35 “if you are that customer with 70 table extensions you would go from a 70 way join to a one way join”
• 9:16 “all good uh version 23 exposed the mapping from previous versions We updated the tool so these as a a data Factory clows now”
• 13:07 “maybe this uh new method will be for you as well to lower your maintenance cost on your on your code and still make”
• 14:07 “the main thing here is there is no really no action on you right we we just made things faster for all code”
• 15:06 “if you leave data behind then it now has a performance impact because before as I mentioned it would just sit in a separate”
• 17:31 “before and this may not be obvious to everyone we had the um approach that the moment we entered into a right transaction then”
• 19:14 “further reads on other um record instances pointing to the same table will now be done with a read committed”
• 24:02 “for new tenants this is automatically enabled but you can flip this back and forth at at will um so that but it is”
• 24:43 “from the next version we will enable this by default but you can still disable it and then from version 25”
• 26:04 “for on premises you might run with the ability to write to the app database we will remove that um possibility from the next”
• 26:17 “test the new locking scheme try to run some bcpt tests and check for errors has check for logging or Deadlocks”
• 26:38 “if you write to the appdb stop in six months this will stop working so um this is um yeah we we’re getting rid”

Disclaimers in the video

• 9:56 subject-to-change: on premises since we are changing the data model there is quite a bit of data writing going on during upgrade so when you upgrade an un premise installation you should probably expect that to take a little longer
• 10:42 other: at the time of this recording we we don’t have it on BCT Tech we might get a version of that on BCT Tech so let’s uh let’s see
• 22:00 subject-to-change: we have added this as a feature management switch so you can enable it uh and it should be noticed of course things will still work functionally
• 22:41 subject-to-change: you might get the another user has modified this in case you have certain code paths we have not seen issues with this in our base app but we cannot rule out
• 25:43 coming-later: there are also a few changes that are upcoming in 2024 release way one the server settings for Force order and loop joints will be removed
• 26:04 coming-later: also for on premises you might run with the ability to write to the app database we will remove that um possibility from the next release

Presenters (as heard): Yen, Kenny.