Hey there,
We've developed some components within D365 that generate some data and inserts it into tables.
The approach taken leveraged the "select forupdate <tableBuffer>" methodology to insert data.
In profiling the SQL statements, I have noticed that the "select forupdate <tableBuffer>" performs a SELECT statement against the target table; if modified to "select firstonly forupdate <tableBuffer>", it results in a SELECT TOP(1) statement against the target table. This was resulting in performance degradation, where applying an index against [dataAreaId, partition] was needed, with include columns on all the table fields.
I realize I'm probably doing something a bit incorrect here - I understand the "forupdate" clause would normally be used with a selection of record to actually update (i.e. "select forupdate <tableBuffer> where recId = <value>").
Is there a different approach to achieve this? Even in the Microsoft documentation, where it mentions the use of ".insert()" table method, there is still the "select forupdate <tableBuffer> statement (
https://docs.microsoft.com/en-us/dynamicsax-2012/developer/insert-table-method).
I know for fact the data I am trying to input is an insert; there would never be a need for an update. Is there any change I can make to avoid having to apply these other indexes?
------------------------------
Curtis Watson
StarTech.com
London ON
------------------------------