search

microsoft / AL

Home of the Dynamics 365 Business Central AL Language extension for Visual Studio Code. Used to track issues regarding the latest version of the AL compiler and developer tools available in the Visual Studio Code Marketplace or as part of the AL Developer Preview builds for Dynamics 365 Business Central.
MIT License
730 stars 243 forks source link

Refactor TableExtensions #6625

Closed sgiordy closed 3 years ago

sgiordy commented 3 years ago

Dear all,

smart people admit mistakes and TableExtensions are the first cause of performance killing in BC. What designers expects? That BC will be used for 1 invoice a day?

What does it means this KB?

An alternative when doing data modeling for extending a table with new fields is to use a related table and define a FlowField on the base table.

Seems a joke! The author cannot be serious!

And what about this undocumented behaviour?

Shot2

KB says:

Using ModifyAll and DeleteAll can improve performance by limiting the amount of SQL calls needed. However, be aware that ModifyAll and DeleteAll will revert to individual calls if any of the following conditions exist: There is trigger code on the table. There are event subscribers to the following events: OnBeforeModify, OnAfterModify, OnGlobalModify, OnBeforeDelete, OnAfterDelete, OnGlobalDelete, and OnDatabaseModify. Security filtering is active. The table contains Media or MediaSet data type fields. There are fields that are added through companion tables.

But omits that the behaviour exists also in case of TableExtensions.

OK, prefix are a good idea. OK, GUID to avoid collision are a good idea.

But splitting tables you wasted years of performance research; you prevent to add keys mixing standard and custom fields; you confuse SQL optimizer with incredible queries.

Have you ever run a SQL Profiler in a mid-size installation with dozen of APPS? I think no or your designers are missing pratical sense.

We need, you need to revert TableExtensions to old simple and effective behaviour. Adding field to tables! With prefix and GUID you don't need to split anything and 8K record limit is very very rare.

I repeat: smart people admit mistakes.

Thank you. S.

atoader commented 3 years ago

Hi! Welcome to the AL repository! Please take a minute to familiarize yourself with the purpose of this repository https://github.com/microsoft/AL/blob/master/README.MD and with the FAQ https://github.com/microsoft/AL/wiki/Frequently-Asked-Questions .

We believe that the issue you have opened is outside the scope of this repository.

atoader commented 3 years ago

We are aware that the table extension implementation is not ideal in all scenarios. We are working on improving this design, one effort being the support for partial records https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-partial-records .

Although we appreciate the feedback, this repository is not the right place to post it (I am aware the issue was transferred from ALAppExtensions). I suggest that you use the appropriate channel to ensure that your feedback gets to the right teams.