Reducing round-trips between database and server in AX 2012

insert_recordset

delete_from

update_recordSet

If necessary conditions are met set-based approach theoretically should reduce number of round-trips to database from one call for every single record like in record-based (loop through every record and insert()) to just one call to database. Which is great news especially when we are dealing with very large tables. I know that it might be more cryptic and less intuitive but it comes in handy and it is best to use at early stage of development for example reports because it saves us time which we would otherwise waste to localize inefficient ‘insert loops’.

So instead of using this:

//Insert

while select CreditMax, Currency, AccountNum from custTable {

custTableTmp.clear();

custTableTmp.CreditMax = custTable.CreditMax;

custTableTmp.Currency = custTable.Currency;

custTableTmp.AccountNum = custTable.AccountNum;

custTableTmp.insert();

}

//Delete

while select forUpdate custTable

where custTable.CustGroup == ’10’ {

custTable.delete();

}

//Update

while select forUpdate custTable

where custTable.CustGroup == ’10’ {

custTable.CreditMax = 10;

custTable.update();

}

Use this:

//Insert

insert_recordset custTableTmp (CreditMax, Currency, AccountNum)

select CreditMax, Currency, AccountNum from custTable;

//Delete

delete_from custTable

where custTable.CustGroup == ’10’;

//Update

update_recordSet custTable setting CustGroup = 10

where custTable.CustGroup == ’10’;

Set-based operations are being downgraded back to record-based if one of conditions below is met:

1. Insert or aosValidateInsert methods is overridden on the target table. (it doesn’t matter if these methods contain only default code, what matters is solely the fact that they are implemented or not).
2. Caching table using the EntireTable setting.
3. RLS (Record-level security) is enabled on the target table.
4. Setting alerts to trigger when by inserts into the target table.
5. ValidTimeStateFieldType property on table is not set to None.
6. Database log is configured to log inserts into the target table.

If not caching table using the EntireTable setting we can avoid the downgrade by using skip methods, but it is dangerous and should be used with caution and only in special cases, it can for example prevent logic that was inserted in the insert method from being executed.

‍