Best way to delete large amount of records

(0) Share

Report

Posted on by Johnny

6,478

Hi,

We have some duplicated records in LogisticsAddresssCity table (Some cities are inserted several thousand times) and we need to clear them up and only keep cities which has one specific customized field filled.

Before deletion we do the update of zip codes with correct record from city.

Which is best to use? "Delete_from" or "while select" and should I foresee something else?

Thanks.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Community Member on at

Like (2)

Report

Delete_from, if you want to delete the all table, or use conditions if you want to delete only some records.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (2)

Report

You can rephrase it to "is it faster to send a single DELETE FROM command to database (assuming you know how to define the WHERE condition) or first fetching all the data from database and then delete them one by one?". It's obvious that delete_from will be many times faster. Just make sure that there isn't anything that would force AX to fall back to row-by-row processing.

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

Thanks for replies.

What I'm thinking is maybe it's better to be done directly in SQL.

The data that should be deleted is around 900.000 records.

First there should be update of zip code table (there is 1:1 relation between zip codes and cities and not 1:N). I think this happened during data migration for each zip code new record in city table where inserted (wrong).

Also, LogisticsPostalAddress table should be updated and after it we need to delete duplicated records from cities. It means that we are working with millions of records.

What would you suggest?

Thanks.

Was this reply helpful? Yes No
Suggested answer

Ajit 8,755 on at

Like (0)

Report

Deleting from SQL would be much faster but we should take care of related records as well. In your case it looks like straight forward. You want to delete records from LogisticsAddressCity table where custom column is blank, Right? You would have to update the LogisticsAddressZipCode table with correct city recid.

Was this reply helpful? Yes No
sureshch 20 on at

Like (0)

Report

If you want to delete only the records in this table not in related table you can use sql delete statement with your customized field value in where condition

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (1)

Report

@Joni: If you used the same SQL code as generated for the delete_from statement, how could it be faster? It would be identical. Another topic would be if you found an optimization that can be only written in SQL and not in X++, but you didn't mention anything like that. And if you bypass AX business logic and use a direct SQL statement, it's easy to make some mistake (e.g. forgetting about a delete action) and end up with corrupted data consistency.

By the way, I wonder which country has exactly one zip code for each city (1:n). Usually one city is divided to many zip codes (1:n).

Was this reply helpful? Yes No
Johnny 6,478 on at

Like (0)

Report

Hi Martin,

thanks for suggestions.

That's the mistake done probably during the data migration or zip code upload. Indeed city should be divided to many zip codes, but something has happened and now we have for each zip code the record in city table. e.g. zip1, zip2, zip3 should be related to city1, but we have three records for city1 in table and it's a mistake.

Was this reply helpful? Yes No