It’s been a while since I last blogged about Dynamics 365 Business Central. Why? Well, I was busy, for one, but alongside that, I’ve also felt covered.
Visual Studio Code, AL and extensions were growing fast, and due to it’s open source atmosphere the improvements have been coming from all directions at the same time. Until now, there was simply nothing to blog about that hadn’t been discussed by others already, or was already on the road to being solved swiftly, or both. Wonderful!
Today, however, that changes. The past year I’ve spent a solid amount of my time on an upgrade of three large Business Central environments from V14 with C/Side code to V24 On Prem with AL. There are a number of things I ran into during this project which I want to blog about in the future, also problems that make me happy that I haven’t pushed harder to go to the cloud in one go, but probably I’ll save the summary of subjects for a story in hindsight once my employer is live on BC24.
That one issue I’d like to address today is the key problem: All of us have been heavily customizing Dynamics NAV and Navision in the past, lots of businesses built on a solid foundation of large add-ons and customizations. We’ve worked hard on removing what we don’t need and/or can be done outside of BC, but some functionality heavily relies on standard Business Central processes, and simply has no other good place than in our ERP. Because of the vast amount of data we process on a daily basis, our system is also heavily “tuned” with custom keys, speeding up batch processing of both ordering and production. Sometimes, these keys span over both standard and custom fields, like in the example I made here:
Most technical specialists will know why this is happening: In all Business Central versions 15 and up, custom fields in a standard table are separated on SQL level in a companion table.
It’s logical, there’s no way around it: on SQL level, an index cannot span over multiple tables.
Or can it? Yes, it can!
If you’re into MS SQL, you already know it’s easily possible to create a view over multiple tables. In the past, way before we had PowerBI and other Power Apps, we would use this to allow easy access to our data for analysis purposes, without unlocking the key to our complete databases.
Sometimes, for example when we would be offering views based on complex queries, the performance would suck (pardon my French). For those situations, we got a beautiful thing called Indexed Views.
The obvious problem here is that Business Central won’t automagically create or select our view to query a certain table, and we have no way from AL to actually query that view ourselves. So if there’s one request I have to Microsoft, it’s the following technical modification to the Business Central engine. This is the most simple version:
- As soon as an app contains a key spanning fields in both the standard table and the companion table, create an indexed view with the fields in the created key;
- When the combined key is required, query the index first, join fields from the other two tables as required;
- When the key is obsoleted/removed, the view can of course also be removed from the database.
One thing I considered trying might be an option for users with (obviously) Business Central on prem: If my memory serves me right, the Enterprise Edition of SQL Server has a built-in query optimizer that chooses a different key if that seems more valid for the query that’s being executed. I don’t know if that functionality is powerful enough to also consider indexed views over multiple source tables if the query partially matches the join in the view, but it’s worth a try.
Do you think this would be a viable option to solve our issues, if Microsoft adds this to future versions of Business Central? Let me know!