Developers in Microsoft Dynamics 365 for Finance and Operations can use the D365 skipDataMethods method to skip the record-specific code and allow the system to perform database operations in bulk. To execute database operations as quickly as possible, developers often use x++ SQL recordset operations such as insert_recordset, update_recordset, and delete_from. However, the system may revert to slower record-by-record operations if code is present in the insert, update, or delete methods. Therefore, in certain situations, the D365 skipDataMethods allows developers to skip this code and perform bulk database operations.
Example Scenario
For example, pretend you are writing a runnable class (job), or a batch job, and you need to update some records. Oftentimes, a developer will start with writing a ‘while select‘ loop, with an ‘update‘ method inside each loop. Consider this example:
class tutorial_skipDataMethods
{
public static void main(Args _args)
{
CustTable custTable;
while select forupdate custTable
where custTable.InventSiteId == 'EAST'
{
ttsbegin;
custTable.InventSiteId = 'WEST';
custTable.update();
ttsbegin;
info(strFmt("Customer %1 site changed to WEST", custTable.AccountNum));
}
}
}
The code loops through all customer records where the InventSiteId field is set to ‘EAST’, sets the InventSiteId field to ‘WEST’, then updates the record.
Since no set-based operation is in use, we do not need to use the D365 skipDataMethods method in this example.
In this case, this code will work. However, can we use a set-based operation to improve performance?
Set Based Operation
Depending on how complicated the logic is inside of the ‘while select’ loop, the code could be rewritten to use a set based operation. Specifically, the code could use update_recordset, insert_recordset, or delete_from.
This is an example of the same work rewritten to use update_recordset.
class tutorial_skipDataMethods
{
public static void main(Args _args)
{
CustTable custTable;
//while select forupdate custTable
// where custTable.InventSiteId == 'EAST'
//{
// ttsbegin;
// custTable.InventSiteId = 'WEST';
// custTable.update();
// ttsbegin;
// info(strFmt("Customer %1 site changed to WEST", custTable.AccountNum));
//}
update_recordset custTable
setting InventSiteId = 'WEST'
where custTable.InventSiteId == 'EAST';
info("All customers from EAST site changed to WEST using update_recordset.");
}
}
Notice, this version does not have the ‘info‘ statement inside the ‘while select‘ statement. Therefore, it cannot print out each customer account that was updated. That loss of visibility will need to be weighed against the performance benefits of this approach.
A Problem
Unfortunately, when we run this version of the code, the system still sends an update instruction to the SQL database for each record. This is because there is code inside the CustTable.update() method.
To see this for yourself, first open Visual Studio, then search the Application Explorer for ‘CustTable‘.
Second, right-click on the ‘CustTable‘ node under AOT>Data Model>Tables>CustTable, then select ‘View Designer‘.
Third, expand the ‘Methods‘ node, and scroll down until you find the ‘update‘ method.
Fourth, double-click on the ‘update’ node to open the code editor window.
When code is in the update method, the system cannot run a set-based operation that tells the database how to update all records and execute the code required for each record. This is why understanding the D365 skipDataMethods method is essential.
Code In The Insert, Update, or Delete Methods
Importantly, if there is code in the ‘Insert‘, ‘update‘, or ‘delete‘ methods of the table, the set-based operation will revert to a record-by-record operation. This is far slower because, instead of making a single database call, the system calls the database for every record. Even for a few records this results in far worse performance than it could be with a set based operation.
Therefore, it is important that you check the table for code in the ‘insert‘, ‘update‘, or ‘delete‘ methods, depending on which operations you are trying to run. Then, evaluate whether it is safe to skip that code and run a set-based operation. If it is, call the D365 skipDataMethods method.
If the code in the ‘insert‘, ‘update‘, or ‘delete‘ method does not need to be run, then call <table buffer>.skipDataMethods(true);. This tells the system NOT to run the code in the ‘update’ method, which allows it to perform a set-based update in a single database call.
Making The Determination
How do you know if you can skip the code in the ‘insert‘, ‘update‘, or ‘delete‘ methods?
The answer is to evaluate what change you are trying to accomplish with the logic inside the table method.
In the example shown, the developer is trying to update the InventSiteId field. Therefore, if code in the ‘update‘ method uses the value of the InventSiteId field, you likely cannot skip that code without losing functionality.
However, if there is no logic based on the fields we are changing, it may be safe to skip the code in the table method.
Evaluating
Fortunately, there is no code that uses the InventSiteId field in the ‘update‘ method of the CustTable table. Look at the code below:
void update(boolean _updateSmmBusRelTable = true, boolean _updateParty = true)
{
CustTable this_Orig = this.orig();
RecVersion rv = this_Orig.RecVersion;
// <GEERU>
#isoCountryRegionCodes
// </GEERU>
ttsbegin;
// <GEERU>
if (SysCountryRegionCode::isLegalEntityInCountryRegion([#isoRU]))
{
this.setInventProfileId_RU();
}
// </GEERU>
super();
// Update the full text search table
MCRFullTextSearch::update(this);
this.SysExtensionSerializerMap::postUpdate();
if (_updateSmmBusRelTable)
{
smmBusRelTable::updateFromCustTableSFA2(this, '', false);
}
if (this_Orig.CustGroup != this.CustGroup)
{
ForecastSales::setCustGroupId(this.AccountNum,
this_Orig.CustGroup,
this.CustGroup);
}
smmTransLog::initTrans(this, smmLogAction::update);
// If the customer group has changed
if (this.CustGroup != this_Orig.CustGroup)
{
// clear the ledger cache
LedgerCache::clearScope(LedgerCacheScope::PartyMainAccountDimensionListProvCust);
}
if (SysCountryRegionCode::isLegalEntityInCountryRegion([#isoMX]) && _updateParty)
{
this.copyInfoToParty();
}
// <GBR>
if (BrazilParameters::isEnabled())
{
if (!CustVendTableFiscalInformationCopyCheckFlight::instance().isEnabled() || !FiscalInformationCopy_BR::twoCustVendTableHaveSameFiscalInformation(this_Orig, this))
{
FiscalInformationCopy_BR::copyFiscalInfoToCustVend(this);
}
}
// </GBR>
ttscommit;
}
Remember to look at the code inside each method called within the process as well.
SkipDataMethods
Finally, we can call the D365 skipDataMethods method to skip the code in the insert, update, and delete methods. Running this will send a single instruction to the SQL database, updating all records that meet the criteria.
See the new code here:
class tutorial_skipDataMethods
{
public static void main(Args _args)
{
CustTable custTable;
//while select forupdate custTable
// where custTable.InventSiteId == 'EAST'
//{
// ttsbegin;
// custTable.InventSiteId = 'WEST';
// custTable.update();
// ttsbegin;
p
// info(strFmt("Customer %1 site changed to WEST", custTable.AccountNum));
//}
custTable.skipDataMethods(true);
//custTable.skipDeleteActions(true); Not needed because we are not performing a delete_from.
custTable.skipDatabaseLog(true);
custTable.skipEvents(true);
update_recordset custTable
setting InventSiteId = 'WEST'
where custTable.InventSiteId == 'EAST';
info("All customers from EAST site changed to WEST using update_recordset.");
}
}
Maintain Fast SQL Operations
Additionally, you may need to use the override methods skipDeleteActions, skipDatabaseLog, or skipEvents to prevent the record set operation from converting to a lower record-by-record operation. Review this Microsoft chart for details on when these overrides may be needed.
Conclusion
Developers should use set-based operations to change data in the fastest way. However, if there is business logic in the insert, update, or delete methods, the system will not use the faster options, unless told it is ok. Business logic in these methods must not be skipped unless it is ok to do so. Therefore, developers should carefully review these methods and if possible, use the D365 skipDataMethods method, along with skipDatabaseLog and skipEvents, to ensure the system can make changes as fast and efficiently as possible.
Leave a Reply