Avoiding Table Extension Hell

It’s been a tough couple of weeks. Lessons have been learnt and now need to be shared. I fear if I don’t, then many more will experience the challenges we have and are facing.

The culprit is that Business Central capability that came with Al extensions namely table extensions. Yes, I know, they are a great new feature and I celebrated their introduction by using them with abandon. The ability to segment our development into separate extensions was massive, it made our testing and deployment infinitely easier and flexible. All that’s true but I ignored the warning and didn’t see the crash coming until we were in it.

We all know those table extensions are stored at the database level in a separate table with the primary key from the table being extended, duplicated into the table extension table right? When you testing your extension that seems a brilliant flexible and neat way of organising your data structure, just stick a couple of fields onto that core table with an extension exactly as we would have done historically onto the table itself with C/AL.

So my problem results from that fact that everyone is doing the same and critically, often for the same tables. I’d heard Microsoft give warning at different events but didn’t change my behaviour. It was just too easy and convenient to ignore them.

The performance impact of one table extension is negligible but for key tables such as the Sales Header, Item or Customer tables, almost every ISV is ‘extending’ them individually, before you even get to the clients per tenant extensions.

That means the performance impact of, in my instance 14, additional joins in separate table extension tables, whenever a sales header record is read, is no longer negligible. It makes the lists and cards take many seconds to appear to the point where usability is questionable. To give you a specific, on a SaaS tenant, it was taking roughly ten seconds to open any sales order card. Would you be happy using that?

Like a lot of performance issues, it's only a factor you will see when the complete systems are constructed, is full of data and then loaded with multiple simultaneous sessions. That’s typically when its live, so restructuring is so much harder.  

Especially true on SaaS where those extension tables only get removed when you uninstall the extension they are part of. It’s crazy but an extension table full of fields marked obsolete still gets read with every master table read, so you’ve gained nothing.

We can restructure the per tenant extensions to consolidate, in our example that consolidates six of the tables into one, and we’ve put lots of fields into their own tables because record size also affects performance. Moving the tables extension into a separate extension and then making dependencies on it works. Having lots of extensions seems to have no performance impact that I can measure. Closer to exchanging table extension hell for dependency hell though.

How about the other eight which are from AppSource ISV’s? No so easy to get them to change is it? Especially as in isolation, they perform just fine. Your code is not used in isolation though in the new AppSource world.

The crazy thing is that some of the table extensions have fields in them that don’t need to be in a table extension.

As an example, I’ll point to Clever Dynamics Document Links, as I’m also the MD of Clever Dynamics I’m only embarrassing myself. It had a table extension on the Sales Header of one integer field for ‘Document Set ID’.  That field is never going to be needed by a user, will never feature in a page extension because it’s a pointer that means nothing on its own. Its an internal working field.

As such, I lose nothing by putting it into a separate table rather than an extension table. Yes, I’d need to maintain the primary key instead of it being done for me but the upside is that I can control when its read and make sure it's only when it needs to be, not every time a sales header record is read. Only the performance impact that’s needed.

Clever Dynamics are not the only company that’s made this mistake here. I’ll go so far as to say I’ve yet to see an ISV that hasn’t. As a community we have to change behaviour, table extensions have to be the exception rather than the rule.

For AppSource entries its especially important because you can’t remove the table once it's there. Changing your extension ID once you’ve migrated the data and uninstalling the original is not an option. Microsoft will need to provide a function in an upgrade codeunit to disable or destroy a table where all the fields are obsolete but that’s a wave or two away.

And before anyone responds that why did Microsoft allow this situation to occur, just don’t. They create the tools but it’s our responsibility how we use them. That we didn’t take on board the warnings is not their fault. With great power comes great responsibility. What would you prefer? That you don’t get the great power? Then use it responsibly.

So in summary what have I learnt? Two new rules.

1.     We only use table extensions if the field is required on a page, even then we try to put them in a factbox and lookup from a separate table. We treat then as circumspectly as we have flow fields the last few years. Only if it’s absolutely unequivocally needed!

2.     When assessing ISV solutions we look at how many table extensions they create and on what tables. Too many on the popular tables and especially if they put every field they might possibly want (popular e-commerce  ISV I’m looking at you) in that extension and you're banned!

If you're like us, then you’ve got a bit of review and refactoring work to do. If you’ve already adopted these principles then relax and have a few drinks, you deserve to be smug, I congratulate you.

You die twice, the first time when you stop learning.