Business Central Cloud | Performance & SetCurrentKey

Imagine the following scenario:

You are the product owner of an industry specific solution that is used by dozens of companies with the same codebase. One day you get an email from a project manager telling you about a performance issue that they have at a specific implementation.

After careful investigation using Application Insights you find out that this customer has an issue in a part of your application where all other customers run just fine.

How is that possible?

The answer might be data distribution in the database. The expensive term that SQL Server specialists are using for this is “cardinality“.

If you implement Business Central for a webshop where customers sporadically buy items the cardinality of Customer No. can be equal to Item No.

If the same webshop now sells the same items over and over again to the same customer the number of records you’ll find with that filter may result in dozens of records and you might want to add a filter on “open” or the current Inventory Period.

Make SQL Server use the right Index

As I wrote yesterday you can now add indexes to your App using Index Packages. Keep the number of indexes in your AppSource App to only nessesairy indexes and add new ones based on the need during the implementation,

The big question is, will SQL Server/SQL Azure actually use the index?

Here the answer is SetCurrentKey

The Myth

For a long time there seems to be a myth in our ecosystem that with Business Central running on SQL SetCurrentKey is no longer important.

This statement is wrong. It is very important.

The reason why people think it can be removed is that SQL Server keeps statistics of your data and it will determine the index based on your filters and knowing the cardinality of your values.

OPTIMIZE FOR UNKNOWN

For Business Central this feature has been disabled by adding a query hint. This means statistics are a lot less important for determining the index.

ORDER BY

With statistics out of the picture the Order By clause becomes more important and this is converted directly from SetCurrentKey

SQL Server will look at your ordering and compare the Order By to the Clustered Index and then determine if it makes sense to use an index or not.

SetCurrentKey ≠ Key

Since a very long time it is allowed in AL to use SetCurrentKey with ANY field in a table, even a flowfield.

Needless to say that if you do a SetCurrentKey on fields that don’t have an index there will be a performance decrease.

Customize SetCurrentKey

In my previous post I learned you how to customize the keys. Now let’s look at how you can implement a new key in a codebase managed on AppSource.

For this we will look at the Microsoft BaseApp. Codeunit Whse.-Purch. Release.

        PurchLine.SetCurrentKey("Document Type", "Document No.", "Location Code");
        PurchLine.SetRange("Document Type", PurchHeader."Document Type");
        PurchLine.SetRange("Document No.", PurchHeader."No.");
        PurchLine.SetRange(Type, PurchLine.Type::Item);
        PurchLine.SetRange("Drop Shipment", false);
        PurchLine.SetRange("Job No.", '');
        PurchLine.SetRange("Work Center No.", '');
        OnAfterReleaseSetFilters(PurchLine, PurchHeader);
        if PurchLine.FindSet() then begin

The Event Publisher OnAfterReleaseSetFilters allows you to change the SetCurrentKey and influence which index SQL Server might use.

Be Careful!

Adding events like this to your App is like handing a PowerTool to a toddler if it is not used in a correct way.

But used in a right way it allows you to do performance tuning in cloud scenario’s.

Leave a comment

Do you like the blogs? Then please leave a comment. I am writing them as I am recovering from surgery and they distract me from the pain and being bored to death,.. Hopefully there will be a new blog tomorrow with an update from MARS…

Girls Just Want to Have Fun,,,