This article describes how to overcome a common problem that slows the process of merging records in Microsoft Dynamics CRM.
Originally published in Q3 2017 D365UG/CRMUG Magazine
When you merge records in Microsoft Dynamics CRM, the system needs to search for any references to the old record and update them to the new master record. So, when you merge two contacts, Microsoft Dynamics CRM has to look for any accounts where the contact is the Primary Contact of, any activities they are part of, any leads they are related to, and so on for every 1:N and N:N relationship defined in the Contact entity.
While this works pretty well for a default Microsoft Dynamics CRM installation, every custom relationship you create on the lead, contact, account, or case entities will only slow down the merging process. Worse still, when you create a custom relationship, Microsoft Dynamics CRM does not create an index on the underlying table to allow it to be searched efficiently. If you create a 1:N relationship on contacts in a database with 100K contacts, you’ll likely see the time taken to merge contacts go up by several seconds. Not a problem if you’re just doing occasional merges, but if you have several such relationships, or you’re doing a large-scale data cleanse, those few seconds quickly add up.
Luckily fixing the problem isn’t too hard with a few well-placed indexes on the SQL database. If you’re running on-premises, you can create these yourself. For Microsoft Dynamics CRM Online, you’ll need to raise a support ticket with Microsoft to create them.
If you’re running on-premises, you might want to use a script to find all the columns you need to add an index to. The query to the right finds any relationships that aren’t already covered by an index and gives you a suggested CREATE INDEX statement to index them.
May all your merges be lightning fast!