The Dynamics Connect portal has moved to the CRM Ideas Portal. Most of my existing ideas were carried forward. I had to re-create the delete performance idea I added last month. It must have been added after the conversion. You can now vote for that item in the CRM Ideas Portal!.
Monday, August 8, 2016
Wednesday, June 29, 2016
Dynamics CRM Slow Deletes and Imports
Update February 16th, 2017: Initial testing (and a look under the hood) show that Dynamics 365 On-Premise (8.2.0.749) has received this performance enhancement! Go try it out now!
Original Post follows.
I've posted a new item on the CRM Ideas Portal to try to get Microsoft to look at the terrible delete and import times which occasionally occur in Microsoft Dynamics CRM. If you commonly experience slow deletes, slow imports, or see messages like this in your event viewer (which means your users are seeing this):
then I encourage you to up-vote this item on the portal. (Yes, that is 53 minutes to import what contained some security roles!) In my research I've found the compile time of the fn_CollectForCascadeWrapper is horrendous (in particular fn_CollectForCascadeDelete, fn_CollectForCascadeDeleteSchema, and fn_CollectForCascadeRemoveLink). These methods are generated based on your metadata. The more entities and relationships you add to CRM the worse these methods get. The more organizations you have on the SQL server, the more likely you are to have the issue to occur.
In it I suggest an alternative that proved to use 28x less CPU, execute 4x faster, and use nearly 10x less memory in the SQL plan cache (on one sample data set).
This was testing against CRM 2016 with SP1 but goes back to at least CRM 2011.
Update October 03, 2016: A great escalation engineer at Microsoft took on my case, quickly reproduced, and opened a design change request with the development team. The Work Item is 346488. He tells me other customers are asking to implement my proposal also. THANK YOU dear reader for pushing for this change with me. Please continue to do so! Currently it may be 2 releases (about a year) before we know if this will be implemented.
Original Post follows.
I've posted a new item on the CRM Ideas Portal to try to get Microsoft to look at the terrible delete and import times which occasionally occur in Microsoft Dynamics CRM. If you commonly experience slow deletes, slow imports, or see messages like this in your event viewer (which means your users are seeing this):
Query execution time of 3183.0 seconds exceeded the threshold of 10 seconds. Thread: 22; Database: Hoven_2016051_MSCRM; Server:v2016sql; Query: select * from dbo.fn_CollectForCascadeWrapper(System.Collections.Generic.List`1[Microsoft.SqlServer.Server.SqlDataRecord], 7102, 0, 1, 0) order by i.
then I encourage you to up-vote this item on the portal. (Yes, that is 53 minutes to import what contained some security roles!) In my research I've found the compile time of the fn_CollectForCascadeWrapper is horrendous (in particular fn_CollectForCascadeDelete, fn_CollectForCascadeDeleteSchema, and fn_CollectForCascadeRemoveLink). These methods are generated based on your metadata. The more entities and relationships you add to CRM the worse these methods get. The more organizations you have on the SQL server, the more likely you are to have the issue to occur.
In it I suggest an alternative that proved to use 28x less CPU, execute 4x faster, and use nearly 10x less memory in the SQL plan cache (on one sample data set).
This was testing against CRM 2016 with SP1 but goes back to at least CRM 2011.
Update October 03, 2016: A great escalation engineer at Microsoft took on my case, quickly reproduced, and opened a design change request with the development team. The Work Item is 346488. He tells me other customers are asking to implement my proposal also. THANK YOU dear reader for pushing for this change with me. Please continue to do so! Currently it may be 2 releases (about a year) before we know if this will be implemented.
Wednesday, April 20, 2016
Dynamics "Connect" Items for Xrm.Page form script writers
As a Dynamics developer, you may use the Xrm.Page client side API extensively. I sure do. You probably have lots of ideas for how to improve it. I often find there are unsupported things I need to do to accomplish my desired experience... often things I think should be obvious, fit right in without a lot of work from Microsoft, and others will instantly benefit from. (Often things that are undocumented but Microsoft is already doing).
I've added a couple of items to Connect recently. If you find them useful, I encourage you to up vote them so we may have these items in the future.
1. CRM 2016 Client Side API: Add an onRefresh event
I would like to know after all the data on the form has been refreshed. Often I have convoluted rules triggered off of multiple fields that impact whether other fields on my form are visible, enabled, etc. I set my IFRAMEs to a read-only mode if the form is deactivated. I would like to evaluate these rules once, after all onchange events have fired.
Microsoft already implements this event for their own purposes but doesn't expose it to us.
2. CRM 2016 - Client side API: Distinguish between onchange fired by user, asynchronous refresh, and attribute.fireOnChange
Also related to forms refreshing after a save/activate/deactivate. If I change a field in plug-ins and the form refreshes, I may want to treat it differently then if it changes by the user in the UI. I often have fields with side effects which dirty other fields when they are changed. However, if some of our plug-in business logic runs changing that field, I don't want to reprocess that logic (and otherwise dirty the just saved form).
Part of the problem with onchange events firing from the asynchronous refresh is they start firing events before all of the fields have been updated. If 3 fields have to be refreshed, the first event only represents the changes to that first field. You're running on stale data.
Take the two combined: Essentially I would like to have the option to ignore onchange events sourced from an asynchronous refresh and then process the updated state of the record in a new onrefresh event. Or queue up my changes until the onrefresh method is hit and then process them manually.
Post your Connect items you think would benefit the Dynamics community.
I've added a couple of items to Connect recently. If you find them useful, I encourage you to up vote them so we may have these items in the future.
1. CRM 2016 Client Side API: Add an onRefresh event
I would like to know after all the data on the form has been refreshed. Often I have convoluted rules triggered off of multiple fields that impact whether other fields on my form are visible, enabled, etc. I set my IFRAMEs to a read-only mode if the form is deactivated. I would like to evaluate these rules once, after all onchange events have fired.
Microsoft already implements this event for their own purposes but doesn't expose it to us.
2. CRM 2016 - Client side API: Distinguish between onchange fired by user, asynchronous refresh, and attribute.fireOnChange
Also related to forms refreshing after a save/activate/deactivate. If I change a field in plug-ins and the form refreshes, I may want to treat it differently then if it changes by the user in the UI. I often have fields with side effects which dirty other fields when they are changed. However, if some of our plug-in business logic runs changing that field, I don't want to reprocess that logic (and otherwise dirty the just saved form).
Part of the problem with onchange events firing from the asynchronous refresh is they start firing events before all of the fields have been updated. If 3 fields have to be refreshed, the first event only represents the changes to that first field. You're running on stale data.
Take the two combined: Essentially I would like to have the option to ignore onchange events sourced from an asynchronous refresh and then process the updated state of the record in a new onrefresh event. Or queue up my changes until the onrefresh method is hit and then process them manually.
Post your Connect items you think would benefit the Dynamics community.
Subscribe to:
Posts (Atom)