D365 Finance & Operations and Dynamics AX Forum

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
------------------------------

Curtis,

        You do not need to do the select forupdate when doing an insert.   That example from Microsoft is poor,  it would select the entire CustTable for no reason.

        This would work just fine:


Regards,
Corey





------------------------------
Corey Vantilborg
ERP Analyst
Tigercat International Inc.
Brantford ON
------------------------------

Original Message:
Sent: Sep 10, 2019 11:41 AM
From: Curtis Watson
Subject: X++ - Inserting Data & select forupdate

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
------------------------------

I agree with Corey, you don't have to user 'forupdate' for insert operation. you can also use 'insert_recordset' and 'update_recordset' while updating bunch of records. This gives better performance than 'select forupdate'.

------------------------------
vishal Dhawgaye
Hitachi Solutions America, Ltd.
------------------------------

Original Message:
Sent: Sep 10, 2019 11:41 AM
From: Curtis Watson
Subject: X++ - Inserting Data & select forupdate

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
------------------------------