Monday, August 8, 2016

Dynamics Connect has Moved

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!.

Wednesday, June 29, 2016

Dynamics CRM Slow Deletes and Imports

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.

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.

Sunday, March 4, 2012

Deletion State Code Attribute Leftovers in 2011 from Dynamics CRM 4

Once of the things I had not noticed thus far in CRM 2011 was that Microsoft had gotten rid of the deletion state code column.  If you're not familiar, it was a column CRM used to mark items as deleted and a service came along later to clean them up.  You may have run in to situations, particularly testing, where you insert an item with a fixed GUID, delete it, run again and it fails because the GUID is not unique.


An overview and history of this can be seen over at the Avanade blog.


I just starting hitting an issue with the removal of the deletion state code.  When instances are upgraded from CRM 4 to CRM 2011, the deletion state code columns on lookups (those attributes with the same name as your lookup, ending in dsc) was being included.


However, when you export your solution from CRM 2011 and import to a clean CRM 2011 instance, the DSC columns are not deployed.  So what we ended up with was our staging instance full of DSC columns and our production environment for a new customer who hadn't been using CRM 4 without DSC columns.


When we went to move a change to an entity in its own solution, a bunch of DSC columns were being exported as required dependencies in the solution.  When we went to import in the production instance, those required dependencies were not found and CRM would not let us continue with the import.


The import of the solution 'Your Solution' failed.  The following components are missing in your system and are not included in the solution.  Import the managed solutions that contain these components (Active) and then try importing this solution again.


Following was the clean-up necessary.  I highly recommend you back up the database before performing these actions.  I also recommend you run the select and understand everything you will be deleting!  These scripts were the answer to one particular scenario and I figure others will eventually have the same problem.



-- Delete the custom DSC attributes from the database.
DELETE a
--select A.LogicalName 
FROM   metadataschema.attribute a
       INNER JOIN metadataschema.entity e
         ON e.entityid = a.entityid
WHERE  a.attributeof IS NOT NULL
       AND a.attributetypeid = '00000000-0000-0000-00AA-110000000019'
       AND a.logicalname LIKE '%dsc'
       AND a.iscustomfield = 1

-- Delete the dsc attribute map columns
DELETE am
--select AM.* 
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
       INNER JOIN attributemapbase am
         ON am.attributemapid = dnb.objectid
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid
                                      FROM   dependencynodebase dnb
                                             LEFT JOIN metadataschema.attribute
                                                       a
                                               ON dnb.objectid = a.attributeid
                                      WHERE  dnb.componenttype = 2
                                             AND a.logicalname IS NULL)

-- delete attribute map required dependencies on dsc columns
DELETE db
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
WHERE  db.dependentcomponentnodeid IN (SELECT dnb.dependencynodeid
                                       FROM   dependencynodebase dnb
                                              LEFT JOIN metadataschema.attribute
                                                        a
                                                ON dnb.objectid = a.attributeid
                                       WHERE  dnb.componenttype = 2
                                              AND a.logicalname IS NULL)

-- Delete the attribute dependencies on dsc columns
DELETE db
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid
                                      FROM   dependencynodebase dnb
                                             LEFT JOIN metadataschema.attribute
                                                       a
                                               ON dnb.objectid = a.attributeid
                                      WHERE  dnb.componenttype = 2
                                             AND a.logicalname IS NULL)

-- Delete the dependency nodes
DELETE dnb
-- select A.LogicalName 
FROM   dependencynodebase dnb
       LEFT JOIN metadataschema.attribute a
         ON dnb.objectid = a.attributeid
WHERE  dnb.componenttype = 2
       AND a.logicalname IS NULL 



After this recycle the asynchronous service and CRM application pool just to be safe.  Re-export your solution and things should be solid.