Notifications
Announcements
No record found.
Hello,
I need to delete records from the table which contains more than 24 million records. I need to delete them based on the created date time field. I used delete_record and there is an index on the filed "created date time" but anyway it takes too much time. I checked then in SQL management studio that it doesn't fall to record level deleting row by row. It is still delete_from command on the SQL server and doesn't turn into while select{delete}is there any chance to somehow delete records using order by and only first 10 thousand using AX?
Or I need to write direct SQL statement?
something like this
WITH MyCTE AS ( SELECT TOP (10) * FROM SalesOrderDetail ORDER BY SalesOrderID DESC) DELETE FROM MyCTE;
thanks.
Hi Azat,
If it's a one-time operation, it's fine to do it directly in SQL with CTE statement.
In x you can try to implement a day by day while loop and delete only one day per delete_from statement, it will decrease transaction time per delete statement, but it will not perform faster.
TransDate deleteToDate = ...; select minof(CreatedDateTime) from salesOrderTable; TransDate startDate = salesOrderTable.CreatedDateTime 1; //Add one day while (startDate <= deleteToDate) { delete_from salesOrderTable where salesOrderTable.CreatedDateTime < startDate; startDate ; }
Another way is to use temp table to keep RecIds to be deleted and then use it as exist join table in the delete_from table statement. But if I remember correctly tempDB temp tables are not supported in AX2009
thanks. I will do something out of two above.
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 503 Most Valuable Professional
André Arnaud de Cal... 434 Super User 2025 Season 2
BillurSamdancioglu 278 Most Valuable Professional
