Talent can be a blessing and a curse. Or… as Johan Cruijff said: “every disadvantage has its advantage”.
“every disadvantage has its advantage”
Johan Cruijff
For as long as I can remember my talent has been making sure performance problems are fixed. I already did that in the days with Navision and it’s Native Database and later with Dynamics NAV on SQL Server.
With Business Central running in the cloud, performance problems don’t just magically disappear. Azure SQL can easily scale up and down allowing you to postpone performance tuning just a bit but eventually when your system get’s bigger you’ll end up having to take care of it, just as we always did in the past.
We have more tooling and telemetry than ever. That is not what this post is about. There are many people in our community writing excellent blogs about Application Insights and there is extensive documentation and training available.
Back to performance tuning Marije?
Hell no. Never again. I like working with people and doing performance tuning means spending hours and hours behind your screen analyzing telemetry and making decisions that result in carefully crafted index plans and sometimes changes to code or even business processes.
But, every now and then questions come my way about the topic. Not so much anymore because I killed all my old domain names and email addresses so you’ll actually have to ping me via LinkedIn and most people don’t bother to do that. Problem solved.
In this case the problem did find me… the customer (a partner in this case) actually called me on my cell. Guess I have to get a new phone number too.
I told them that I am not doing that kind of work anymore. I moved to the more functional part of upgrading/implementing Business Central and I was busy doing a project.
On top of that, I was preparing for a big surgery which meant both physically and mentally getting in shape in order to go into surgery as healthy as possible with the goal of a fast recovery. In other words… my mind was off, somewhere else.
However, this specific question ended up haunting me for quite a while. My brother Rene did some functional analysis to the processes and my former company SQL Perform looked at the SQL Server side of things.
That did not completely solve all things and I ended up talking to the customer care manager of the partner again just before my surgery.
We talked and decided that I would look at the problem as a distraction while I do initial recovery… which is where we are today.
So what is the problem?
In this specific case, the problem was performance of flowfields and/or locking.
Hasn’t this been an issue for a long time in our ecosystem? I spent hours, days, weeks of my life finetuning SIFT using SIFT levels (yes, I am old) and MaintainSiftIndex.
Later we started using covering indexes or indexes with included columns.
All of the above are now available directly from AL.
So when to use which?
In this case the development team was asking the question: “Marije, please tell us which one of these to use…. which one is best”.
And then the nasty answer is “it depends”.
So how do you deal with that if you have a large vertical solution and you want to implement a single codebase at dozens or hundreds of customers.
The Solution: Buy yourself as much flexibility as possible!
Since a short while Microsoft allows you to create Table Extensions with only new indexes that you can deploy as a per tenant extension on top of your functional app.
Essentially this means that SQL Server Indexes and Indexed Views are finally it’s own Object Type. Many of you will remember that I’ve asked Microsoft to do this many, many times.
And now it exists!
An Example
I have created an example repository on GitHub with two branches. One branch with a “vertical app” and one with an “Index Package”
The Vertical App has a table like this
table 60000 "Test Table"
{
DataClassification = SystemMetadata;
fields
{
field(1; "Entry No."; Integer)
{
AutoIncrement = true;
DataClassification = SystemMetadata;
}
field(2; Description; Text[50])
{
DataClassification = SystemMetadata;
}
field(3; Amount; Decimal)
{
DataClassification = SystemMetadata;
}
}
keys
{ key(PK; "Entry No.") { Clustered = true; } }
}
You can easily see that there is no secondary index. Only the clustered index.
What if you need an index on Description with Amount as SumindexField?
You can add that to your vertical solution, but maybe only one customer needs it, or maybe in a specific implementation the system will run faster if you have a covering index or included columns.
tableextension 60000 MyExtension extends "Test Table"
{
fields
{
// Add changes to table fields here
}
keys
{ key(CustomerKey; Description) { SumIndexFields = Amount; } }
}
Instead of adding the index to your vertical solution, it might be an idea to create a table extension for it. This way you can solve specific issues for specific implementations without having the risk of over indexing your whole application and trying to make a once-size-fits-all solution.
The Rule: Buy Flexibility
There is no rule when to use SumIndexFields, Covering Indexes or Included Columns. It depends often on how your vertical solution is implemented.
The only rule that you can teach your development team is to allow the implementation teams to be flexible. You don’t want your developers to go into discussion about every single index that each implementation team needs in the field.
So Marije, can we still call you if we have issues?
That depends… I guess there is also no rule for that. Looking back at the last few years I did two or three performance projects. Often they are extremely complicated implementations with very large databases and hundreds or thousands of users.
The good news: the surgery is done, it went well and I am recovering. This will take up to six months and I am grateful that most of this will be in the summer period and I have scheduled to work a lot less in this period.
Life is better as a girl… just don’t tell the guys…
Like this:
Like Loading...
Marije Brummel Blog | Business Central, Azure and more... 