delete records from the very heavy table

(0) Share

Report

Posted on by dark_knight

1,271

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.

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

Sergei Minozhenko 23,095 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
dark_knight 1,271 on at

Like (0)

Report
Copy link

Link copied!

thanks. I will do something out of two above.

Was this reply helpful? Yes No