Home > MS CRM 4.0 > Large DuplicateRecord Table

Large DuplicateRecord Table


Dynamics CRM 4 has great capabilities in helping to keep a crm system relatively duplicate free by using duplicate detection rules. However, care must be taken when creating these duplicate detection rules. Each time a duplicate detection rule runs and a match is found, the matches are written to the ‘DuplicateRecordBase’ table. If the rules are poorly written this table can drastically grow in size thus causing large database growth, async service issues, and duplicate detection issues.

A few items to keep in mind when writing duplicate detection rules.

  1. Watch for blanks. For example, if 90% of your contacts do not have an email address, do not create a duplicate detection rule on email address as it will be triggered 90% of the time.
  2. Turn off duplicate detection rules during import.
  3. Do not create a ton of rules. Think careful about how you can possible detect duplicates and even examine the existing data before creating the rules.

So what do you do if you do encounter a large DuplicateRecordBase table?

The first step is to identify the rule that is causing the large growth. Run this query:

select
COUNT(1)
as NbrRows, rules.name, rules.duplicateruleid from duplicaterecordbase asrecs , duplicaterulebase as rules
where recs.duplicateruleid = rules.duplicateruleid
group
by rules.duplicateruleid, rules.name

This query will list the Number of Rows, followed by the duplicate detection rule name and guid.

After identifying the culprit, fix or delete the rule. If your table is not super large, deleting the culprit rule will delete the matches from the ‘DuplicateRecordBase’ table.

However, if your table is massive, this will timeout. You now have two choices: the supported method, and the unsupported method.

The supported method:

Open a ticket with Microsoft as you aren’t supposed to delete anything manually from the crm database.

The unsupported method:

First, drink a good cup of coffee and don’t do this smack in the middle of business hours.

Second – identify the ‘bad rule’ by running the query above.

Third – delete away.

delete
from DuplicateRecordBase
where DuplicateRuleId =
’945E3FD2-3E93-DE11-9EEE-00215AF2E1A2′

Advertisements
Categories: MS CRM 4.0
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: