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:

• Dynamics 365 Business Central 2023 Wave 2 release: measuring the impact of the new data structure – Stefano Demiliani
• A new data model for Table Extension: the talk of the town – Dynamics 365 Business Central tales, myths, legends. And something serious. (duiliotacconi.com)

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 ;-))!