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
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.
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.
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.
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,,,
Great blog!!!! Please, continue distracting you.
LikeLiked by 1 person
Only your blog post made me indirectly aware of the last bullet point within this docs article section:
I’ve never seen a SetCurrentKey used with the return value, nor an error message due to a failed SetCurrentKey …
Get well and pain free soon!
LikeLiked by 1 person
Sorry, if i do not understand something here.
Correct me if i misunderstood you.
You wrote, that SetCurrenKey is important to advise SQL- Server to use the right index. I just run a test with BC14. In my test the SSMS- Monitor shows that SQL server always uses Order By with OPTIMIZE FOR UNKNOWN.
That shows to me that SetCurrentkey is useless, because SQL-Server uses it’s overall statistics and not the statistics for specific query values.
But perhaps newer Versions behave different. So perhaps you can explain the difference.
Yes you are correct, SetCurrenyKey converts into Order By and by that determines the Index.