Answers.Body: <p>You may want to have a look at the <a href="https://docs.civicrm.org/xdedupe/en/latest/" rel="nofollow noreferrer">Extension X-Dedupe</a> which was built for a number of use cases built in dedupe features cannot handle.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-03T13:56:55.767Z
Answers.Text: Yep, saw that. Haven't made time to play with it yet. The docs say "Larger datasets can quickly bring the built-in deduplication to its limits .... XDedupe follows another general approach ...". This sounds like addressing the issue in my question. Anywhere I can read more about this "another general approach"?
Answers.UserDisplayName: TwoMice
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-03T08:56:45.183Z
Answers.LastActivityDate: 2023-02-03T08:56:45.183Z
Answers.UserDisplayName: Fabian
Answers.Body: <h2>Reserved Rules</h2>
  <p>I've found that the reserved rules are much more performant that custom dedupe rules created.</p>
  <ul>
  <li>Name and Address (reserved)</li>
  <li>Name and Email (reserved)</li>
  </ul>
  <p>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.</p>
  <h2>External Processing</h2>
  <p>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.</p>
  <h2>Related Tips for Site Admins</h2>
  <p>Other tips worth mentioning, even though they fall into the limitations category you mentioned.</p>
  <p>It does avoid user frustration by setting &quot;Default limit for dedupe screen&quot; at Administer &gt; Misc (Undelete, PDFs, Limits, Logging, etc.) or via settings file</p>
  <pre><code>$civicrm_setting['CiviCRM Preferences']['dedupe_default_limit'] = '20000';
  </code></pre>
  <p>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.</p>
  <p>Finally, if you setup a dedupe rule that is absolutely bullet proof I've run <a href="https://docs.civicrm.org/user/ca/latest/initial-set-up/scheduled-jobs/#job_process_batch_merge" rel="nofollow noreferrer">process_batch_merge scheduled job</a> in aggressive mode to handle dupes straight away that should have never occurred in the first place and are simple merges.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-06T19:02:43.280Z
Answers.Text: Interesting comment about the speed of Reserved. Have you made an equivalent rule that is not reserved, and then run it as a direct comparison?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-06T20:33:31.170Z
Answers.Text: I don't have access to the install to test this anymore but that would definitely be the next test to confirm. It just adds to civicrm_dedupe_rule and civicrm_dedupe_role_group just like reserved rules do, not a custom option group etc. Pretty unrelated but similar slowness is experienced when importing to custom fields and not only core fields.
Answers.UserDisplayName: Andy Burns
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-03T20:08:53.807Z
Answers.LastActivityDate: 2023-02-03T20:16:32.430Z
Answers.LastEditDate: 2023-02-03T20:16:32.430Z
Answers.UserDisplayName: Andy Burns
Body: <p>On sites with fairly small numbers of contacts, running a &quot;Find duplicate contacts&quot; operation on all contacts is not an issue.</p>
  <p>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).</p>
  <p>That last one is reasonable enough, but the first two remedies have significant limitations:</p>
  <ul>
  <li>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).</li>
  <li>Tweaking server configurations requires specialized knowledge or access which is beyond the reach of most organizational staff.</li>
  </ul>
  <p>For sites with a &quot;larger&quot; number of contacts (where &quot;larger&quot; means &quot;too large to run a duplicate search on all contacts given my current server configuration&quot;), what's the most common approach for finding potential duplicates, other than the above-mentioned options?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-02T18:26:25.763Z
Comments.Text: Is the issue that running it is too slow, or gives too many results?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-03T13:52:25.727Z
Comments.Text: Petednz, non-technical site admins will answer with "The problem is that the browser times out, and meanwhile my site is frozen." My impression is that in most cases it's an N-squared problem caused by searching on a large group (or full db) against the full db. More complex dedupe rules make it worse, but even simple rules will hit this problem at larger values of N.
Comments.UserDisplayName: TwoMice
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-03T19:20:51.723Z
Comments.Text: we did some work so that a Sch Job runs once a day, builds a Group based on all the matching rules (3 of them) which we wanted to use for a Dedupe, and added a feature so that you can specify a Group to dedupe from (as well as against) so it becomes much more specific, runs more quickly. I can ask Jitendra to explain share more if interested
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-02-03T20:26:59.540Z
Comments.Text: Yes petednz very interested. Sounds like some potential for a generalizable solution (insert caveat here etc.)
Comments.UserDisplayName: TwoMice
Tags: <duplicate-contacts>
Title: How are large sites handling duplicate contact scanning?
ThreadText: # How are large sites handling duplicate contact scanning?
  
  (Asked by: username_0 on 2023-02-02)
  
  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?
  
  **Tags:** duplicate-contacts
  
  > **Comments:**
  > **username_1**: Is the issue that running it is too slow, or gives too many results?  
  > **username_0**: username_1, non-technical site admins will answer with "The problem is that the browser times out, and meanwhile my site is frozen." My impression is that in most cases it's an N-squared problem caused by searching on a large group (or full db) against the full db. More complex dedupe rules make it worse, but even simple rules will hit this problem at larger values of N.  
  > **username_1**: we did some work so that a Sch Job runs once a day, builds a Group based on all the matching rules (3 of them) which we wanted to use for a Dedupe, and added a feature so that you can specify a Group to dedupe from (as well as against) so it becomes much more specific, runs more quickly. I can ask Jitendra to explain share more if interested  
  > **username_0**: Yes username_1 very interested. Sounds like some potential for a generalizable solution (insert caveat here etc.)  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2023-02-03. Score: 3
  
  You may want to have a look at the [Extension X-Dedupe](https://docs.civicrm.org/xdedupe/en/latest/) which was built for a number of use cases built in dedupe features cannot handle.
  
  > **Comments:**
  > **username_0**: Yep, saw that. Haven't made time to play with it yet. The docs say "Larger datasets can quickly bring the built-in deduplication to its limits .... XDedupe follows another general approach ...". This sounds like addressing the issue in my question. Anywhere I can read more about this "another general approach"?  
  
  ---
  
  ### Answer by username_3 on 2023-02-03. Score: 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](https://docs.civicrm.org/user/ca/latest/initial-set-up/scheduled-jobs/#job_process_batch_merge) in aggressive mode to handle dupes straight away that should have never occurred in the first place and are simple merges.
  
  > **Comments:**
  > **username_1**: Interesting comment about the speed of Reserved. Have you made an equivalent rule that is not reserved, and then run it as a direct comparison?  
  > **username_3**: I don't have access to the install to test this anymore but that would definitely be the next test to confirm. It just adds to civicrm_dedupe_rule and civicrm_dedupe_role_group just like reserved rules do, not a custom option group etc. Pretty unrelated but similar slowness is experienced when importing to custom fields and not only core fields.  
  
  ---
  
  