6,414
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
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.
@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).
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.
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.
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.
Delete_from, if you want to delete the all table, or use conditions if you want to delete only some records.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (