Maintain Fast SQL Operations avoid set based operation to fall back to row by row operation using skip methods in Dynamics ax

Sometimes when importing large number of records (Eg. millions of lines), the quickest way is to use:

• insert_recordset
• update_recordset
• delete_from

But these set base operation will roll back to row-by-row operation when either of the following condition is true:

• it is not an SQL table (Eg. temporary table)
• Database log is enabled for the table
• Alert is setup for this table
• Record level security is enabled for the table
• AOSValidation method is overwritten
• when using insert_recordset
  • the .insert() method is overwritten
• when using update_recordset
  • the .update() method is overwritten
• when using delete_from
  • the .delete() method is overwritten
  • DeleteAction is defined

To counter this, you can call a number of skip* methods:

• common.skipDataMethods(true) will skip the insert/update/delete methods
• common.skipDeleteMethod(true) will skip the delete method
• common.skipDeleteActions(true) will not execute the delete actions
• common.skipEvents(true) if alert is setup

see: https://docs.microsoft.com/en-us/dynamicsax-2012/developer/maintain-fast-sql-operations

I saw an example in this post: https://dynamicsuser.net/ax/f/developers/76285/update_recordset

public server static void MyCustomMethod(SalesLine _salesLine)
{
    salesLine           salesLineUpd;
    InventDim           inventDim;
    
  if ( _salesLine.MyField )
  {
    inventDim = _salesLine.inventDim();

    salesLineUpd.skipDataMethods( true );

    ttsBegin;

    update_recordSet salesLineUpd
    setting PER_ChairSerialNo = inventDim.inventSerialId
    where salesLineUpd.SalesId == _salesLine.SalesId
    && salesLineUpd.RecId != _salesLine.RecId; // update every other line with serial id

    ttsCommit;
  }

I also saw in this other post some suggestions regarding database log
http://www.artofcreation.be/2014/08/11/what-you-should-know-about-database-logging-functional-impact/

If you really want to activate database logging but you have code that need to do a set-based operation, you can get around this issue by using the skipDatabaseLog method in combination with the other skip* methods.

However, in my opinion it is better not to use database log in the first place. So these are my recommendations about database logging:

• Do not use it.
• If you do use it, make sure it is for a good reason and document why.
• Do not use database logging because you do not trust your employees or as a form of “security”.
• When activating the database log for a table, pay close attention to the TableGroup property of the table. It is fairly safe to activate the database log on tables with table group Main, Group and Parameter. Activating it for other groups such as Transaction, TransactionHeader, TransactionLine, WorksheetHeader and WorksheetLine is usually bad.
• If you are a consultant, capture the need for database logging in the analysis phaseand set this up in your DEV/TST/ACC/… environments as early as possible.
• Do not simply activate database logging in a production environment and expect everything to go well, test it first in an other environment as if it were a code change