X++ supports set – based manipulation on the database. There are three set-based operators available in Microsoft Dynamics 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:
- 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).
- Caching table using the EntireTable setting.
- RLS (Record-level security) is enabled on the target table.
- Setting alerts to trigger when by inserts into the target table.
- ValidTimeStateFieldType property on table is not set to None.
- 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.
