Dynamics 365 Business Central and bulk inserts

Today I’ve discovered that not everyone knows how Dynamics 365 Business Central (but also Dynamics NAV) handles inserts on the SQL backend, so I think it’s worth writing a quick post on explaining this.

Let’s consider this piece of AL code:

In this code I’m retrieving an existing Item record (the first) and then I’m doing an INSERT operation that obviously fails.

Then after that line of code, the code does other operations (here I’ve used a Sleep call to simulate waiting), does other queries on the database and other processing again.

Question: when will the exception be thrown?

Common answer: when the Item.Insert() row is executed.

NO!!

If you try this piece of code, you will see that the message is displayed and also the other processing is done. You will have the error at the end of the process. Shocked? Why this?

This is honestly not a new thing or a new strange behaviour (like the famous Save as you type feature for example 😉 ).

By default, Dynamics 365 Business Central (and also Dynamics NAV) automatically buffers inserts in order to send them to the backend (SQL Server) at one time. By using bulk inserts, the number of server calls is reduced and performances are increased. This is a quite common pattern to apply when writing data layers. Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction.

The Bulk insert behaviour has some constraints to remember:

Records are immediately sent to the backend (SQL) when the following occurs:

• You call COMMIT to commit the transaction.
• You call MODIFY or DELETE on the table.
• You call any FIND or CALC methods on the table.

Records are not buffered if any of the following conditions are met:

• The application is using the return value from an INSERT call; for example, if you write “IF (Insert.INSERT) THEN” in the previous code, the behavior changes.
• The table that you are going to insert the records into contains any of the following:
  • BLOB fields
  • Fields with the AutoIncrement property set to True

Please remember that the transaction consistency is always guaranteed also with bulk inserts. The transaction is simply rolled back on the first COMMIT to the database or at the last possible moment.

In Dynamics 365 Business Central on-premise you can disable bulk inserts. To do that you need to set the BufferedInsertEnabled parameter in the CustomSettings.config file of the Business Central Server to FALSE.

Should you do that? Honestly, no! Bulk insert improves performances and it can help on optimizing (reducing) lockings.

If you want to do a quick test in C#, try to do an insert like this:

var cmdText = @"
    insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress)
    values (@Id, @FirstName, @LastName, @Street, @City, @State, @PhoneNumber, @EmailAddress)";
foreach (var customer in customers)
{
    using (var connection = new SqlConnection(connectionString))
    {
        var command = new SqlCommand(cmdText, connection);
        command.Parameters.AddWithValue("@Id", customer.Id);
        command.Parameters.AddWithValue("@FirstName", customer.FirstName);
        command.Parameters.AddWithValue("@LastName", customer.LastName);
        command.Parameters.AddWithValue("@Street", customer.Street);
        command.Parameters.AddWithValue("@City", customer.City);
        command.Parameters.AddWithValue("@State", customer.State);
        command.Parameters.AddWithValue("@PhoneNumber", customer.PhoneNumber);
        command.Parameters.AddWithValue("@EmailAddress", customer.EmailAddress);
        connection.Open();
        command.ExecuteNonQuery();
    }
}

Here we loop through Customers and we perform an INSERT on every record on SQL. Time to complete this code for 100 customers is about 1200ms.

Now do something like this:

using (var copy = new SqlBulkCopy(connectionString))
{
    copy.DestinationTableName = "dbo.Customers";
    // Add mappings so that the column order doesn't matter
    copy.ColumnMappings.Add(nameof(Customer.Id), "Id");
    copy.ColumnMappings.Add(nameof(Customer.FirstName), "FirstName");
    copy.ColumnMappings.Add(nameof(Customer.LastName), "LastName");
    copy.ColumnMappings.Add(nameof(Customer.Street), "Street");
    copy.ColumnMappings.Add(nameof(Customer.City), "City");
    copy.ColumnMappings.Add(nameof(Customer.State), "State");
    copy.ColumnMappings.Add(nameof(Customer.PhoneNumber), "PhoneNumber");
    copy.ColumnMappings.Add(nameof(Customer.EmailAddress), "EmailAddress");
    copy.WriteToServer(ToDataTable(customers));
}

Here we use a bulk insert operation on SQL (using the SqlBulkCopy class of .NET Framework). Time to complete this code for 100 customers is about 5ms.

Is this a good pattern to apply or not? I think the answer is YES…