Business Central vNext (v23) – The “new data model”

This is just going to be a brief post, because there have been other blogs about this topic – I just wanted to add a small piece on top of it ;-).

Our two Italian friends Stefano and Duilio already wrote some blogs about the upcoming performance improvements regarding the new feature in Business Central, that includes a new data model for table extensions.  In Short: in stead of having a new table for every single table extension, the extension-tables will be consolidated into only one extension table – which avoids a massive amount of JOINs in the SQL statement, which obviously – you can already guess – will result in a performance boost.  You can read Microsoft’s announcement here: Better performance when working with multiple extensions | Microsoft Learn

Here you can find their blogs and more measurements:

With this post, I just wanted to put a few more on the table.

waldo.BCPerfTool

By writing this post – I again realized I still have a blog that I need to do about this tool I wrote quite some time ago, which I baptised “waldo.BCPerfTool“.  I wrote it for my BCTechDays 2022 session “Coding 4 Performance”, and have been using it ever since as the go-to demo tool for a lot of coding samples during sessions and workshops :-).

Now, during that session, I was drilling into “the TableExtension problem”, and showed how the use of “SetLoadFields” influenced performance, in an attempt for people to pick up the usage.  Here you can find the fragment.

V22

What you might remember from the session, or from a workshop I did at some point, is this graph:

If you don’t, let me explain.

Pillar 1,3,5,7,9 are representing all queries without Partial Records (so, without the use of SetLoadFields) on a table with more and more table extensions (the right pillar has 8 table extensions).

This is the code:

Pillar 2,4,6,8,10 represents the same table then the one on its left – but WITH SetLoadFields, so there was only 1 JOIN going on.

The code is very similar, only one line extra: the SetLoadFields, obviously:

Moral of the story was simple: SetLoadFields (or “Partial Records” for that matter) eliminates the issue of JOINs.  Or: use SetLoadFields.

But now, there is …

V23

Since – from this version on – we will always have only 1 JOIN (at most), we can expect a far lesser performance influence when having more and more TableExtensions.  And yes, Stefano and Duilio already proved that in length!  But what’s the deal in terms of Partial Records?  Or – how does the graph look like now?

Here you go!

Not much different, you say?  Look at the scale on the Y!  It’s a massive difference!

Data access will be much more stable, no matter how many table extensions.  In fact, the reason why the pillars are still rising the more table extensions you have, is solely by the fact that there are more and more fields in that table – which simply makes it more costly to access the table when you perform a “SELECT *”

Which kind of like answers the question as well: Is “SetLoadFields” still beneficial?

Of course it is!

You see pillar 2,4,6,8,10, which are all (pretty much) equal!  They represent the use of SetLoadFields (partial records), which is twice as fast as the counterpart (the bad practice of not using it).

To look at it from some different angles, I created some more graphs.

First – let’s look at the data where we don’t take care about SetLoadFields:

You can clearly see that out-of-the-box, it has a significant impact on performance. 

But if you are already applying Partial Records all over the place, the impact is somewhat less:

That’s obviously not bad new, just something that was to be expected.  And again – I’m only showing you the read-performance.  Please read into the blogs I mentioned above that drill more into writing as well!

In my opinion, from all features that we get in V23 – this is right up there on top of the list (for me at least ;-))!

5.00 avg. rating (98% score) - 2 votes

Permanent link to this article: https://www.waldo.be/2023/09/22/business-central-vnext-v23-the-new-data-model/

7 comments

1 ping

Skip to comment form

    • Herman on September 22, 2023 at 8:57 am
    • Reply

    Nice, Only new challenge we now get is that the customer runs the Table information and says Why in the world is that table so big, which app is doing that? and that is a bit harder to findout.

    • Fiddi on September 22, 2023 at 10:15 am
    • Reply

    I agree that this example shows the advantage of SetLoadFields in BC22, but i am not sure if this will have the advantage and influence in a real life BC23 database.
    Why: Every partially record is read into the BC cache (f.e. Item). In many cases a partial loaded record is needed as a full record by the same or a different process later and will cause an additional database reads, which will reduce the overall database performance.

      • waldo on September 22, 2023 at 10:23 am
        Author

      Well – let’s agree to disagree.

      I noticed that the NST cache only works with 100% the same statements, including filters and all. That rarely happens – sure, for same process (but that would be the same for partial records too) – but it would be very rarely share cache over different processes.

      In my tests, using partial records boosts performance almost 50%. I think it’s a bad practice to drop performance “just in case” you might get lucky to use caching.

      But that’s just my opinion .. .

      If you’re talking about JITLoads – I’m totally not worried about that – just because in my test, on a multitude of versions – JIT Loads still seem to be much faster than 2 separate loads. Can’t explain why – but the performance is there. 🤷‍♂️

    • Fiddi on September 22, 2023 at 10:57 am
    • Reply

    We do not disagree, we only have a look from different viewpoints. My concerns aren’t really measurable, you won’t see them in every simple tenant, but there might be problems in heavily customized databases if not thought about in deep before using it.
    To add additional points:
    – SetLoadFilds requires additional management code. So if you really would compare performance, you should compare systems without SetLoadFields (perhaps older one with only one extension) and with SetLoadFields. (Would be interesting to know what the difference to BC2020.1 is)
    – SetLoadFields does not know if there is an extension that needs additional fields, so an installed app might cause a JTL. (that’s the main cause for my concern with heavily used tables)
    – SetloadFields needs a deep thought how an where to use ( i think it’s not a good idea to use it on document tables that are base of the document pages)
    OK, a point why JTL- load is faster, might be, that the record is already created in Memory, and only the additional field values need to be filled in. ( but i don’t know if this a large impact, and if it is, there is something different wrong 😉)

    • curate_ideas on September 22, 2023 at 11:00 am
    • Reply

    Thanks for sharing, waldo! I was in your workshop back then and followed all examples with your PerfTool. With that it’s so much easier to proof impact with real data than just gut-feeling.

    • Ted Johnston on September 22, 2023 at 3:26 pm
    • Reply

    Quick question from a NAV dinosaur moving to BC.

    If I use a SetLoadFields to only load the fields needed for a custom page, then the user customizes the page to add fields I didn’t include, what happens?

    Does the system load the fields with a second call and performance suffers but only for that user? Do the fields not show?

      • waldo on September 22, 2023 at 3:28 pm
        Author

      That’s exactly what happens:
      https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-partial-records#jit

      It’s something to avoid for sure, but I’m not sure it’s all that bad even .. . I might come back to that in a later post – I need time to investigate..

  1. […] Business Central vNext (v23) – The “new data model” […]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.