Because extensions and events are being used more-and more, a lot of tables will have events configured for onUpdated, onUpdateing, onDeleted, onDeleted, etcetera. As a result all set_based operations (like update_recordset) are being executed as row-by-row operations. This is frequently causing large performance impact.
As a possible solution for this issue, i would like to suggest the option of writing a new type of event. Let's call this one "onUpdatedRecordSet". When this type of event has been configured on a table, this event should be called instead of the normal events. This event type must then be written in a set_based manour too.. :)
An alternative option for reducing the impact of this trend, would be to add the option of calling the existing .skipEvents() method for a specific event. This will allow developers to skip known events, but will still cause possible new events (developed after developing the recordset code) to be triggering a row-by-row solution.
Comments
Only after posting the comment, I realized that the idea is in the F&O category, not Business Central. I can't delete the comment, so let it be here as a reminder that we experience similar issues. :-)
Category: Development
I prepared my description for the same idea, but then found that there is one already, so I will upvote and add my comments.
Account Schedule KPI in Financial Reports is a good example of this kind of set update.
https://github.com/microsoft/BusinessCentralApps/blob/main/App/Layers/W1/BaseApp/Finance/FinancialReports/AccSchedKPIEventHandler.Codeunit.al
Here, the web service setup is updated when a G/L Budget is changed. To track budget changes, the event handler codeunit subscribes to database insert / update / delete events, which forces ModifyAll and DeleteAll to trigger separate Update / Delete statements on each record, and Insert operation will not collect the bulk insert buffer. This can greatly undermine performance of massive updates - especially ModifyAll and DeleteAll operations.
These updates could be triggered once after DeleteAll or ModifyAll is executed and avoid subscribers bound to OnAfterDeleteEvent and OnAfterModifyEvent - if Business Central platform published such events.
I suggest to introduce two database events: OnAfterDeleteSet and OnAfterModifySet.
OnAfterDeleteSet event should be called after Delete or DeleteAll statement is executed.
Respectively, OnAfterModifySet should be invoked after Modify or ModifyAll.
Events can have the following signature:
OnAfterDeleteSet(RecRef: RecordRef)
OnAfterModifySet(RecRef: RecordRef)
The primary key of the RecRef is initialized only if the event is triggered by an operation on a single record (Delete or Modify). When the event is invoked from bulk operations, the primary key remains uninitialized, but filters from the source record are transferred to the RecRef, so the client subscribing to the even can retrieve the range of affected records.
Category: Development