My company is implementing a record retention policy. I need to update 15,000 records of a custom entity. Specifically, I need to update their statuscode (Not retained), statecode (Inactive), and title (Record deleted as per the company retention policy).
I was thinking of creating a custom PHP script that uses a CRM connection library to do this. I created the script, but the problem is that it takes like 6 hours to update 15,000 records on a dev environment, which seems unreasonably long. This is likely because the update requests are done 1-by-1, so they aren't optimized for efficiency and speed. The php library doesn't seem to have a way to do a more efficient bulk update.
I haven't done much with CRM .NET plugins yet, so I'd rather not get into that for this task, as learning a new pipeline and SDK will bloat the required time to accomplish this task.
Since the CRM is on premise, an obvious solution is to update the SQL database directly through a query. Should be faster since 1 SQL update query would be optimized for bulk updates. However, I've read many times that direct database updates are not supported and can have bad consequences.
I've seen mentions of using a workflow to update the records that match the criteria, but I'm not understanding how it would get triggered. An on-demand workflow would have to be executed manually 30 times since there's the 500 record limit per page in a view.
Do you have any suggestions?
EDIT:
I'm actually thinking of just running my current script overnight. Will give it plenty of time to finish. I only need to do this bulk update once. Afterwards, this same script will run on a schedule at night on a much smaller set of data. Just gotta test the full run on a dev environment first, likely tonight.
