You’re offline. This is a read only version of the page.
285
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.
*This post is locked for comments
XRM Tool Box seems to be the winner for the initial bulk record update. Very simple to set up, and can automatically split the operations in batches of up to 1000! Took around 30 minutes to finish the task on 15,600 records on a low powered dev stack. Expect that time to be cut in half on the more powerful production stack. Definitely a step up from 6 hours!
The scheduled script will run with php every day on only a few records that would match the retention criteria, so I don't mind it being a bit inefficient or taking up 10 minutes to finish.
Hi Artemy,
I would update the records using an SSIS Package or a console application. However, the Bulk Workflow Execution Plugin in the XrmToolBox might be the easiest approach for you.
I'm concerned that the library and doc you linked are talking about Dynamics 365, but my instance is version 2015. I'm also not sure if that library will work with PHP 5.3. The current PHP library I'm using uses SOAP requests still.
Do you have much experience with .NET for CRM? Can .NET scripts be run independently on a schedule using Windows task scheduler? I realized that the pipeline won't be too different since I'm not looking to create a plugin, just a script. Are there any links you could point at to get me started?
You could combine 100 of your php calls into a single bulk execute request and run it that way see docs.microsoft.com/.../execute-batch-operations-using-web-api for some guidance and github.com/.../RDynamics seems to be a PHP library that supports batches.
You really shouldn't be updating the records via SQL. While it may work, it may not and its definitely supported by Microsoft if things went wrong...
Apart from my first suggestion I really can't give you any better answers.
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (