D365 Finance & Operations and Dynamics AX Forum

 View Only
  • 1.  X++ - Inserting Data & select forupdate

    Posted Sep 10, 2019 11:42 AM
    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
    London ON

  • 2.  RE: X++ - Inserting Data & select forupdate
    Best Answer

    Posted Sep 11, 2019 08:58 AM

            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:
    static void Job7(Args _args)
    TCI_ScanningSetup scanningSetup;
    scanningSetup.ItemBuyerGroupId = 'VMI';


    Corey Vantilborg
    ERP Analyst
    Tigercat International Inc.
    Brantford ON

  • 3.  RE: X++ - Inserting Data & select forupdate

    Posted Sep 25, 2019 05:04 PM
    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.

If you've found this thread useful, dive deeper into User Group community content by role