Business Central Under the Hood episode 12: Evolving AL for Performance

Overview

Episode 12 of Business Central Under the Hood, published 2025-12-05 (37 minutes). A member of the AL runtime team describes how AL runs on the NST through C# compilation, and how the team chooses performance investments, including where abstractions leak.

The episode goes through features that need code changes (data transfer, set load fields, read isolation) and changes that need none (many table extensions to one, tri-state locking now on by default). It clarifies that lock table and set current key do not do what their names suggest. It ends with early ideas: dynamic queries, a query builder pattern, bulk delete and modify events, and a move toward set-based operations. The speaker says none of these are in code and gives no ship dates.

Key points

• Data transfer moves large datasets in bulk using SQL directly. It needs manual code changes and works only against SQL (not temporary tables or CRM). It matters mostly for large tables: one case with 17 million rows went from days to 40 minutes.
• Set load fields (originally called partial records) loads only the chosen fields. It is applied automatically for API calls on API pages, which is where the biggest gains were seen. Elsewhere it needs manual adoption. Its value dropped after many table extensions were consolidated into one companion table.
• Read isolation lets code choose read uncommitted, read committed, repeatable read or update lock. Tri-state locking adds a read committed state after a modification instead of update lock. It started as opt-in, is now used by everyone, and is disabled when code calls lock table.
• Lock table does not take a table lock in SQL at that moment. It makes later reads use update lock hints and affects the data cache.
• Set current key does not pick an index. It orders the result by the given fields, which can lead SQL to choose a different index or sort afterwards.
• Temporary tables are in-memory AVL trees inside NST process memory, so memory grows with data volume. A dictionary gives constant-time lookup instead of logarithmic, but suits only simple lookup cases.
• Dynamic queries, bulk delete and modify events, and a move toward set-based operations are ideas only, not in code. Today the runtime falls back to row-by-row processing when row triggers exist. Use the sampling profiler to find hot paths, such as upgrade codeunits that process row by row.

Features

Every feature below: status not stated.

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.

• other “AL set range” at 4:40
• other “AVL tree” at 24:27
• other “delete all” at 29:22
• other “modify all” at 29:22
• other “on after modify trigger” at 29:50
• other “on before modify” at 29:50
• other “on before delete” at 29:50
• other “on after get current record” at 33:04
• other “on after get record” at 33:04
• other “dictionary data type” at 35:15
• other “set load fields” at 35:53

Quotes

• 5:41 “So, so uh lately we have done a lot of investment in uh in to improve performance um and more specifically uh performance of”
• 7:36 “want to use like the super performant things you kind of begin saying like well the abstraction is this but if we”
• 10:25 “17 million rows which is a fairly large amount of data from a real customer”
• 11:59 “set load fields requires manual uptake in your AL code. Um, and and we have seen quite a bit of success there.”
• 12:23 “set load fields is automatically being applied if you do API calls on API pages and that was actually where we saw the biggest”
• 13:21 “Nowadays, I think you know removing one join is still great and removing an amount of fields, right?”
• 15:54 “They kind of wanted this in between which is generally recommitted. So we wanted to give them that ability and then we just also”
• 18:04 “for a long time we had tri-state as an opt-in feature”
• 21:05 “Lock table first and foremost it doesn’t actually do anything in the moment like it doesn’t go to SQL and takes a big lock”
• 22:16 “What we actually do is we say um you need to order the data like the resulting data in the order of whatever field”
• 25:39 “And I think that one is maybe even closer to being real. This is idea level like this is one step before.”
• 26:02 “One of the problems with queries as they are right now is that you kind of have to write them as an AL object”
• 26:20 “It’s a design time decision. You can’t can’t change them at one time.”
• 26:51 “I want to read from this base table, join in this other table and then maybe join in a third table but I only”
• 30:04 “if you have that then we actually say well then we can’t use the bulk statement. We can’t just do one big delete. Then”
• 31:35 “this is not next release or the one after this is like”
• 32:20 “when you we do for example um upgrade code units that often touch a lot of stuff. They’re doing things row by row is”
• 34:08 “figure out like is this something that gets executed you know 10 times a day or 10,000 times a day or maybe millions of”
• 35:32 “Dictionaries is way better you know asically it’s uh it’s login versus constant time”

Disclaimers in the video

• 14:10 subject-to-change: we actually want to do tri-state locking first but then we swapped it around and did read isolation first
• 25:16 other: we don’t we don’t say anything when it’s going to be shipped
• 25:39 coming-later: none of these are actually in code. This is idea level like this is one step before
• 28:50 preview: we might experiment with it there first
• 31:35 coming-later: this is not next release or the one after this is like

Presenters (as heard): Mass, Matt.