Business Central Under the Hood episode 5: How To Make Your AL Code Super Fast

Overview

Episode 5 of Business Central Under the Hood explains why database calls dominate AL execution time. A line of AL code that does not touch the database takes about a microsecond, while a SQL call takes at best about a millisecond and often seconds. The speakers cover how to find slow queries, how to cut the number of queries, and how caching works at the company session level.

The second half covers indexing, why page search can cause full table scans, and how locking affects concurrency in sales order posting. It also describes work on number sequences, concurrent warehouse entry posting in the BC25 October release, planned SQL full text indexing for page search, and tools for diagnosis: Application Insights, the database locks page, the missing indexes page, and the in-client profiler, which is demoed.

Key points

• Database statements cost far more than non-database AL statements, so reduce the number of SQL calls first; avoid nested loops that look something up for each record.
• Microsoft logs queries longer than 750 milliseconds via telemetry; partners can use Application Insights to monitor their own customers.
• Use flow fields so SQL does sums and lookups, and use SetLoadFields to load only the fields you need instead of whole records.
• Data cached at company session level is shared by all users of that company; the first users in the morning pay the cost of filling the cache.
• Page search covers all visible columns except numbers and booleans, which can lead to full table scans. SQL full text indexing is planned, hoped for BC25.
• For locking, do the expensive work first and the update at the end. Sales order posting currently takes a semaphore-style lock, so concurrent posters queue up. Read isolation helps concurrency but needs developer uptake.
• For BC25 (October release) warehouse entries use non-locking entry number assignment (number sequences), allowing concurrent warehouse posting; other entry tables are to follow, with parallel bulk posting as the longer-term goal.

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.

• page “database locks page” at 25:33
• page “missing indexes page” at 25:33

Not found in BC28-30: page “database locks page”, page “missing indexes page”.

Quotes

• 1:32 “executing a line of Al code or a statement that doesn’t look up in the database takes approximately a microc seconds that means a”
• 1:52 “SQL calls and at best they are one millisecond uh often it’s seconds I mean full seconds but even a millisecond is thousand times”
• 2:14 “the ratio between database statements and non database statements is approxim approximately 10 to one if you combine these numbers it means that basically”
• 3:56 “when it’s longer than 750 milliseconds then we Define it is long enough to be interesting for us to log and then we log”
• 6:13 “nested Loops you know you loop our all customers and for each customer you do something something look something up or if it’s posting”
• 9:01 “SQL itself is blistering fast that’s what SQL is it’s very good at doing this kind of operations I mean the biggest companies in”
• 12:49 “the first time you would be the record setad of records that would be maybe a little bit slow it will at least take”
• 13:30 “Monday you should start at 10 before like everybody has has actually hydrated the cash”
• 14:05 “primary key lookup that’s like the get you know get this entry with this pair of shoes that you get by way the almost”
• 15:14 “we can’t have an index for each field yeah because index are also a little bit expensive right they are expensive both in maintenance”
• 17:01 “the Page search actually searches on all visible columns okay except for numbers and booleans and stuff like that”
• 19:14 “locking is a good thing as such it’s a good feature that sequel as invented why why it doesn’t sound good like why would”
• 20:56 “good pattern is to do all the expensive stuff first mhm and then at the very end do a quick update update”
• 22:37 “so just to be sure we take a semaphor lock if you will mhm basically locking the system out for anyone else”
• 22:57 “we’re saying now we we are alone in this big room nobody else can come in here everybody we close the door nobody this”
• 23:17 “90% of the time is you know what we call the po actual posting where we lock everybody else out and this may not”
• 26:34 “we introduced already a while ago yeah it’s it was in 202 maybe”
• 27:35 “you know the customer calls complains um but I don’t I may not have access to this system”
• 29:54 “we have the incline performance profiler which is a gem uh that I think many people are not looking enough at”
• 30:55 “it takes like 16 seconds I think says here on have to get each row so that’s a very long it’s that’s extremely long”
• 34:12 “for bc25 in October release we actually have done this for warehouse entries”
• 35:45 “very on a very short time frame we uh we are going to try to solve the issue of Page search by introducing the”
• 36:06 “on a longer Horizon we think the concurrence and parall ation is the big thing to do”

Disclaimers in the video

• 26:34 subject-to-change: read isolation feature maybe not a game changer but at least it will help with concurrency
• 35:45 coming-later: very on a very short time frame we uh we are going to try to solve the issue of Page search by introducing the SQL s full text indexing meaning that hopefully from pc25
• 36:26 coming-later: on a longer Horizon we think the concurrence and parall ation is the big thing to do

Presenters (as heard): Vincent Nicholas, B Nutsen, Christian.