Dynamics CRM Update All Records
There are two parts to this question regarding our instance of Microsoft Dynamics CRM 2015 on-prem.
1- What is the best way to run a workflow or make a change to large numbers of records?
In the past we would run workflows on 1000 records at a time by manually changing the view limit in SQL. The recent upgrade to 2015 has resulted in that work around no longer working. This means I would need to run the workflows on 250 records at a time. When we are talking 20,000-50,000 records this become prohibitive. These are not workflows that can be designed based on on-change events on the form as many time they are changes to employees territory or other unpredictable changes.
2- One solution that has been theorized is to create a field in the team record called update pending. Have a workflow that runs on all records(started on create) and waits for that field to be yes, then runs a child workflow, and waits a few days before restarting itself. There would be a few other caveats to reduce usage on old records, prevent the workflow running more then once on a record for each job, and ensure it did not re-run continuously. The theory is that each record would look up to the owning team and wait for that pending update to change yes then run child workflow. That workflow could be changed to run any child workflow that could be configured to do whatever is needed. This would allow us to run a workflow on all records by configuring the workflow and then setting pending update to yes. Wait a few days and change it back to no. Our greatest concern in regards to this approach is the impact it would have on performance. In essence we would be triggering 50,000-75,000 workflows at once. Does anyone have any ideas on what kind of impact this would have on performance when those workflows wake up?
This is the only information I have been able to find on this so far:
https://social.microsoft.com/Forums/en-US/79bda2c5-ad1d-4d9c-af8d-47f0aa992035/crm2011-waits-and-timeouts-in-workflows-how-efficient-are-they?forum=crmdevelopment
1- What is the best way to run a workflow or make a change to large numbers of records?
In the past we would run workflows on 1000 records at a time by manually changing the view limit in SQL. The recent upgrade to 2015 has resulted in that work around no longer working. This means I would need to run the workflows on 250 records at a time. When we are talking 20,000-50,000 records this become prohibitive. These are not workflows that can be designed based on on-change events on the form as many time they are changes to employees territory or other unpredictable changes.
2- One solution that has been theorized is to create a field in the team record called update pending. Have a workflow that runs on all records(started on create) and waits for that field to be yes, then runs a child workflow, and waits a few days before restarting itself. There would be a few other caveats to reduce usage on old records, prevent the workflow running more then once on a record for each job, and ensure it did not re-run continuously. The theory is that each record would look up to the owning team and wait for that pending update to change yes then run child workflow. That workflow could be changed to run any child workflow that could be configured to do whatever is needed. This would allow us to run a workflow on all records by configuring the workflow and then setting pending update to yes. Wait a few days and change it back to no. Our greatest concern in regards to this approach is the impact it would have on performance. In essence we would be triggering 50,000-75,000 workflows at once. Does anyone have any ideas on what kind of impact this would have on performance when those workflows wake up?
This is the only information I have been able to find on this so far:
https://social.microsoft.com/Forums/en-US/79bda2c5-ad1d-4d9c-af8d-47f0aa992035/crm2011-waits-and-timeouts-in-workflows-how-efficient-are-they?forum=crmdevelopment
ASKER
Thank you for the feedback. We have considered those first two options and may look into using the programmatic solution if we have to, but we would like to avoid it unless absolutely necessary. Excel has too many caveats and risks involved with the data.
Scribe looks interesting but as you said it cost money and I doubt I will be able to secure funds for it.
Any other ideas?
Scribe looks interesting but as you said it cost money and I doubt I will be able to secure funds for it.
Any other ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It seems there is no simple solution to this problem. Thanks.
ASKER
I eventually developed a C# based solution using the Microsoft SDK . Not my favorite option but it has gotten the job done.
1. If you have the ability, this can be done through code using CRM's web services. You could create a job that runs overnight and avoids workday slowness. Of course, this requires you have the technical resources to develop it.
2. You could also do this using Excel. This would require you to export the data and then re-import it. Obviously, this carries some risks so you'd want to run it on a smaller set first.
https://ajcrm.wordpress.com/2012/01/22/using-excel-to-easily-update-dynamics-crm-2011-records/
3. Finally, we use a tool called Scribe to do a lot of things with CRM. It's very powerful and maybe overkill (and of course it also costs money):
http://www.scribesoft.com/solutions/crm-integration/microsoft-dynamics-crm/