In this article, learn how to use delete_from in D365. The delete_from keyword in x++ is used to delete specified records from a table as efficiently as possible. This command is extremely fast because it generates a single SQL statement that is sent to the server once. In contrast, other times developers will write a ‘while select‘ statement to loop through records in a table. Then, inside the loop, the delete() method is called to delete that record. This is much slower because a command is sent to the database for each record to be deleted. In this article, I will explain when to use each approach.
How To Delete Data In D365
Before learning how to use delete_from in D365, I recommend you start by reading my article “How to Write A While Select Statement“.
To remind you, a common approach is to select data in a table buffer using a ‘select’ or ‘while select’ statement, then call the ‘delete‘ method on that table buffer.
At that point, the system creates a SQL statement that deletes that record from the database where the RecId matches the RecId in the table buffer.
When deleting a single record, using a select statement is totally fine. However, when multiple records are expected to be deleted, users should use a delete_from statement over a ‘while select‘ statement whenever possible.
Single Call Versus Multiple Calls
When deleting a single record, using a select statement is totally fine. However, when multiple records are expected to be deleted, users should use a delete_from in D365 over a ‘while select‘ statement whenever possible. This is because calling delete_from only makes a single call to the database, instead of many.
The best way to explain is to look at a few examples.
Multiple Calls Using Delete
In the example below, I created a runnable class (job) to delete records from the RetailGiftCardTable where the status is ‘Expired‘. Name the job ‘tutorial_delete‘.
class tutorial_delete
{
public static void main(Args _args)
{
RetailGiftCardTable retailGiftCardTable;
while select forUpdate retailGiftCardTable
where retailGiftCardTable.Status == RetailGiftCardStatus::Expired
{
if (retailGiftCardTable.canDelete())
{
retailGiftCardTable.delete();
}
}
}
}
Notice, the code loops through each record in the RetailGiftCardTable where the status is ‘Expired’, then calls the ‘delete‘ method.
In this example, every time the delete method is called, the system generates a SQL statement and runs it against the database. The delete method gets called every time the while loop is entered. Which occurs for every record in the RetailGiftCardTable where the status is ‘Expired’.
Therefore, pretend there are 100,000 records in the RetailGiftCardTable table. In this scenario, the system would have to generate and execute 100,000 distinct SQL statements, communicating back and forth with the database to retrieve the next record to delete. This would be terribly slow.
However, in this case, it seems the ‘while select‘ loop is necessary because we need to first check if the record can be deleted before calling ‘delete‘. If it were possible to incorporate the checks that are in the ‘canDelete‘ method into the record set statement, the performance could be improved.
Single Call Using Delete_From
Now, let us look at how to use delete_from in D365 to delete the same data but a whole lot faster.
Create a new runnable job (class), and name it ‘tutorial_delete_from‘.
Next, populate the class with the following code.
class tutorial_delete_from
{
public static void main(Args _args)
{
RetailGiftCardTable retailGiftCardTable;
RetailGiftCardTransactions retailGiftCardTransactions;
delete_from retailGiftCardTable
where retailGiftCardTable.Status == RetailGiftCardStatus::Expired
notexists join retailGiftCardTransactions
where retailGiftCardTransactions.cardNumber == retailGiftCardTable.entryId;
}
}
Delete_From Explained
In order to understand how to write an delete_from in D365, we need to understand each of the parts.
- First, we use the syntax “delete_from <table buffer variable name>” to tell the system which table we are deleting data from.
- Second, add a ‘where‘ condition to filter which records to delete. Most likely, you will not want to delete every record in the table. Additionally, table joins can also serve as filters. For a reminder, read my article on How To Write An X++ Select Statement.
D365 delete_from example
Additionally, if you want to see a real example of an delete_from in D365, you can search the source code. Specifically, look at the class McrSalesOrderCancellation. Then, look at the method ‘cancelLineMiscCharges‘.
In this method, the system deletes all miscellaneous charges for the specified SalesLine record using delete_from in D365.
NotExists Join
Lastly, there is one more common trick when writing an delete_from in D365 that is important to understand. When writing the delete_from statement, you can optionally join additional tables to further control which records are updated. Doing so, just like a select statement with D365 joins, acts as a filter for which records are updated.
As in the example above, where we used a notExists join, this is helpful when the criteria for whether a record should be deleted are in a related table.
See the method ‘cleanupAfterDeleteForInvoiceTables‘ on the class PurchParmCleanUp, for an example.
Microsoft Documentation
For further information and examples on how to use delete_from in D365 see Microsoft’s documentation.
Additionally, if you are interested, view the documentation on the TSQL ‘delete‘ statement that the system converts the delete_from into.
Microsoft Documentation
For further information and examples on how to use delete_recordset in D365 see Microsoft’s documentation.
Additionally, if you are interested, view the documentation on the TSQL ‘delete‘ statement that the system converts the delete_recordset into.
Code In The Delete Method
Importantly, if there is code in the ‘delete‘ method of the table, delete_recordset will revert to deleting each row rather than performing a set-based update in a single database call. Therefore, it is important that you check the table being updated for code in the ‘delete‘ method. If the code in the ‘delete’ method does not need to be run for your specific update, then call <table buffer>.skipDataMethods(true);. This tells the system NOT to run the code in the ‘delete’ method, which allows it to perform a set-based delete in a single database call.
Additionally, you may need to use the override methods skipDeleteActions, skipDatabaseLog, or skipEvents to ensure the record set operation is not converted to a lower record-by-record operation. See this this chart from Microsoft for more details on when these overrides may be needed.
Conclusion
Using a delete_from in D365 can significantly reduce the time it takes the system to delete records from a table. It is especially effective compared to looping through many records and deleting each one individually. However, there are only specific scenarios in which the code can be changed to use delete_recordset. If there is complex x++ code inside the ‘while loop‘ needed to evaluate if it is ok to delete each record, you may not be able to convert the code to use a delete_recordset. However, as a developer, you should try to use delete_recordset whenever possible. This can improve the performance of your code significantly.
Leave a Reply