The incredible BULK (INSERT)

Take this trivia quiz.

SaaS Version 23.0, 23.1, 23.2, etc. How many SQL Executes it takes to:

1. INSERT 10.000 Records in Sales Line table?
2. INSERT 25.000 Records in Area table?

You have 42 seconds to answer (because 42 IS the answer to everything).

QUIZ RESOLUTION

One feature that was implemented with Dynamics NAV, and now proudly integrated in Dynamics 365 Business Central, since a decade is Bulk Insert. 

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/optimize-sql-bulk-inserts

I would love to report here this important feature as it was described by Microsoft:

“By default, Business Central automatically buffers inserts to send them to Microsoft SQL Server at one time.

By using bulk inserts, the number of server calls is reduced, thereby improving performance.

Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.

Application developers who want to write high performance code that utilizes this feature should understand the following bulk insert constraints.”

In practical terms, it is a way to snooze the ping-pong between the battery of NSTs within SaaS cluster and your Azure SQL database.

This feature is working just fine unless

1. There is a BLOB field in metadata definition (media and mediaset are just fine)
2. There is an Autoincrement field in metadata definition
3. There is a return value when doing the INSERT.
4. There are FIND, MODIFY, COMMIT, CALC or similar record manipulation within the INSERT.
5. There are subscribers to database event OnAfterInsert or similar (a modern spin-off from point 4.)

And now the solution to the trivia (I know that you are so impatient to read the solution and see if you know everything about bulk insert – and sales line -). If you want to test what will follow, I have prepared a small experimental solution made of 2 extension that you could download here: https://github.com/duiliotacconi/DT.AL/tree/main/BULK%20INSERT

Of course, I have activated telemetry in a SaaS Sandbox and also run a SQL Profiler trace behind the scenes (in an equivalent on-premises) to have more detailed insights.

Back to Sales Line. This should be a very good candidate, on the chart, for buffered insert since it does respect, at least, first 2 points, inherited from the glorious old Dynamics NAV (R.I.P.) days.

Let’s have a look at the output in telemetries after running actions one by one.

A DUE EXPLANATION

Sales Line, as you have them in (almost all) standard deployments takes 30.000 execute to insert 10.000 records.

WHY?

InsertDTSalesLines: I did my diligence and cloned sales line object, removing all trigger code, functions and publishers. As it should be on the chart, it should perfectly fit into a good candidate for bulk insert. And it does. To insert such depurated version of Sales Line takes 5.000 execute, insert is buffered with 2 records each. What about the other 25.000 execute, then?

InsertDTSalesLinesWithExt: Same as above but this time, I have added a stupid table extension to such table with a dummy decimal field and a media (BLOB are not allowed, of course). Such 10.000 Sales Lines prototype with Table Extension takes 10.000 executes: 5.000 Executes to insert records in the source table, 5.000 executes to insert records in the $ext table. In shorts, you have 2 records written per execute in Sales Line table and 2 records per execute in the $ext table. We are making progress here. What about the remaining 20.000 execute?

InsertDTSalesLinesWithExtAndSubs: Say “ARRIVEDERCI” to the performance in all its form. The real tombstone on bulk insert (and performance debt) is in the OnAfterInsert database event subscribers. In the case of the real Sales Line is codeunit 5496 “Graph Mgt – Sales Order Buffer” that I have copied exactly as it is in the standard and left the UpdateCompletelyShipped function.

codeunit 50496 “DT Graph Mgt – S.O. Buffer”

{

    [EventSubscriber(ObjectType::Table, Database::”DT SL With Ext and Subs”, ‘OnAfterInsertEvent’, ”, false, false)]

    local procedure OnAfterInsertSalesLine(var Rec: Record “DT SL With Ext and Subs”; RunTrigger: Boolean)

    begin

        UpdateCompletelyShipped(Rec);

    end;

}

Now. If you do not trust me nor telemetry, trust SQL Profiler trace:

10.000 INSERT in the Sales Line table

10.000 INSERT in the Sales Line $ext table

10.000 SELECT due to the code in the subscriber event

Let’s abandon the sad sales line to inspect a table with a very short metadata – and one of my favorite example of AL minimalism – : the Area table. This is made up of one Code and one Text field. Period.

Now, when writing 25.000 rows in this table, without any table extension, there will be just 5.000 executes: practically 5 records inserted in bulk per execute.

CONCLUSION (and TRIVIA RESOLUTION)

INSERT 10.000 Records in Sales Line table

30.000 SQL Executes

INSERT 25.000 Records in Area table

5.000 SQL Executes

Well, there is no secret that bulk insert can max to 5 records inserted per execute. You could spot this out from the on-premises customsettings.config parameter from the Dynamics 365 Business Central service.

So dos and donts for developers here are clear:

1. avoid BLOB field in the table definition
2. do not add autoincrement property in the table definition, if you want to enable bulk insert feature. NOTE: this is a good performance option to have a faster number assignment. Since there is a trade-off between bulk insert and autoincrement, be sure to choose the appropriate design.
3. Keep the record definition as much as small as possible.
4. MOST IMPORTANT. Watch out what you are subscribing to.

And now a letter to Santa Microsoft.

• Why only max 5 (still) and, overall, just 2 for worldwide used record like sales lines?  This might be just one of the “magic” numbers that was part of a (good) implementation dated back more than 10 years ago and never touched by anyone along the years, I believe. Today, with SaaS environments, latency is one of the worst enemy since it cannot be controlled a priori so easily. Past several moons since this feature was implemented, it is time to experiment with double or triple, or even more, the value of records that could (should) be inserted in a bunch. And change that magic number.

“Remember: the answer is always 42.” 😊

• It is good to have bashed down the number of JOINS applying just one single table extension ($ext) for all of them. But still there is a price to pay for extensibility since it still doubles the number of execute and, overall, insert statement. But as Jens MP answered to the audience at Directions EMEA in Lyon, at the performance roundtable, “this is just the beginning of touching the table extension model”. I am counting on this.
• Bulk Insert is a good and sacred platform feature that should be more considered within the standard by the application team. Sales Line are a good example of table that might need more performance joy. I fully understand that ISV and VAR might kill it at any time but leading by examples is alwyas appreciated. 

I would love to see more incredible Bulk than Bruce Banner tables.