How are large sites handling duplicate contact scanning?

Question

On sites with fairly small numbers of contacts, running a "Find duplicate contacts" operation on all contacts is not an issue.

But as the number of contacts grows, this becomes increasingly impossible. (And I acknowledge that a lot of good work has been done in recent years to address this, which is great!) So we run the duplicate search against smaller groups of contacts. And we tweak the server parameters, and maybe database indexes, to facilitate longer and/or more efficient searches. We also try to avoid rules that might skip indexes (e.g. rules using a Length specifier).

That last one is reasonable enough, but the first two remedies have significant limitations:

• By searching on increasingly smaller groups, we increase the tedium of the search operation, which can lead to a snowballing tendency to avoid running duplicate scans (it becomes more tedious as our database grows and thus more likely to be avoided by busy staff).
• Tweaking server configurations requires specialized knowledge or access which is beyond the reach of most organizational staff.

For sites with a "larger" number of contacts (where "larger" means "too large to run a duplicate search on all contacts given my current server configuration"), what's the most common approach for finding potential duplicates, other than the above-mentioned options?

Answer 1

You may want to have a look at the Extension X-Dedupe which was built for a number of use cases built in dedupe features cannot handle.

Answer 2

Reserved Rules

I've found that the reserved rules are much more performant that custom dedupe rules created.

• Name and Address (reserved)
• Name and Email (reserved)

In one use case, a database of all ~1 million contacts would complete the scan in 20-30 seconds. For custom rules, it would take a multitude times longer or just fail. It would be interesting to investigate why these are so different.

External Processing

With some more technical help, you can scan your MySQL for dupes and then re-import the known dupe contact ID's to a group to target just those.

Related Tips for Site Admins

Other tips worth mentioning, even though they fall into the limitations category you mentioned.

It does avoid user frustration by setting "Default limit for dedupe screen" at Administer > Misc (Undelete, PDFs, Limits, Logging, etc.) or via settings file

$civicrm_setting['CiviCRM Preferences']['dedupe_default_limit'] = '20000';

From a practical standpoint as a site admin, I encourage them to put their most valuable contacts (contributors, members, event attendees) into a group and work thru those dupes first.

Finally, if you setup a dedupe rule that is absolutely bullet proof I've run process_batch_merge scheduled job in aggressive mode to handle dupes straight away that should have never occurred in the first place and are simple merges.