Long loading time of Finding Duplicate Contacts

[aeszq]

HI Guys,

I have about 37,000 contacts in Civicrm database. The page never finishes loading when I do 'find and merge duplicate contacts', in orther words, the Mysql query of finding duplicate contacts takes extremely long time to run( I've let it run for 10 hours ,but it still not finished). The rule I use is checking first name, last name and email address. I wonder if this is normal because 37k contacts is not a small number?


Cheers,
George.

[roland]

Further to George's message, We are actually using the GUI under "administer > manage > Find and Merge Duplicate Contacts" to perform this task. We didn't think this process would take that long so perhaps there are some additional primary key allocation required within the DB that we are unaware of.

The contact records will increase from around 37,000 to over 70,000 in the coming weeks and we believe there will be a number of duplicates. We would prefer to use the functionality with Civi (and allow the administrator to run this process) but at the moment we cannot. Our other alternative is to write our own code to determine the duplicates and merge them.

Anyway advice with this issue would be greatly appreciated.

Sincerely,
Roly.

[Donald Lobo]

hey roly:

check: http://forum.civicrm.org/index.php/topic,12563.msg55510.html#msg55510

dave and rob give a few ideas on what we can potentially do. if you can take the next step and help optimize the queries / code and contribute it back that would be great You can also potentially sponsor the core team to tackle that and optimize it (though it would be done for 3.2 and might need to be backported for your use case)

lobo

[roland]

Hi Lobo,
Sounds good. I'll get George to take a look and get back to you. I'll see if I can get some "sponsorship" funds as well.

Cheers,
Roly.

[jbertolacci]

I have been talking to dlobo about sponsoring work. His estimate for a "base set of changes / sql fixes would be between 40-50 hours to optimize all queries and take less resources" is about $5k of work.

I can sponsor half of that and am looking for a co-sponsor. Would anyone like to be a co-sponsor to improve the the merge duplicate code and sql in civi?

[roland]

Hi,
I think I can get a sponsorship of around $AU2,500....but I am trying for more. When do you think the project can kick off?

Lobo, I think I can have get the funds by mid-May. Is that a reasonable timeframe?

We're still trying to sort out the data dictionaries from the external system.

Cheers,
Roly.

[Donald Lobo]

we starting on the 3.2 QA process, so putting an optimized dedupe in the 3.3 release cycle is probably a good idea. We do hope that 3.2 hits the beta cycle by the end of may (at which time we start working on 3.3)

lobo

[Donald Lobo]

hey roly:

u should also check:

http://forum.civicrm.org/index.php/topic,12563.msg57472.html#msg57472

i think if u combine forces with jason, we can get some major changes into 3.3 (or maybe even 3.2.x)

lobo

[jase700]

One strategy that can help a bit here.

Create smartgroups based on .com, .net, .org, .edu, etc.  Then when you go through the merge workflow, just work through each of these groups.  Will create a much smaller subset to work with.  I have 135,000 contacts and it's near impossible to do what I need to do.

[xavier]

Could you try installing a 3.3 version (beta, do that on a copy of the prod)? The duplicate is *much* faster now.

X+