[Vtigercrm-developers] duplicates and paging

[Alan Bell]

Hi all,

if you use the find duplicates function and you have rather a lot of 
duplicates then it starts paging, by default it pages at 25 lines (or 
whatever your $list_max_entries_per_page is set to) which is an odd 
number so that if your duplicates are pairs then the 13th pair is split 
over two pages, which is a bit inconvenient. Setting 
$list_max_entries_per_page to an even number might not help as there 
could be a triplicate contact in there too.

If you move to page 2 of the duplicates it does an ajax update of the 
page and doesn't set the javascript event on the merge buttons. If you 
refresh the page after turning to page 2 then the merge buttons work.

The performance sucks with a big database, I think it is proportional to 
the cube of the number of entities being deduplicated (I might be wrong 
about that, but it is doing this epic query that I couldn't optimise 
indexes for)

SELECT vtiger_contactdetails.contactid AS 
recordid,vtiger_contactdetails.email FROM vtiger_contactdetails INNER 
JOIN vtiger_crmentity ON vtiger_crmentity.crmid = 
vtiger_contactdetails.contactid INNER JOIN vtiger_contactaddress ON 
vtiger_contactaddress.contactaddressid = vtiger_contactdetails.contactid 
INNER JOIN vtiger_contactsubdetails ON 
vtiger_contactsubdetails.contactsubscriptionid = 
vtiger_contactdetails.contactid INNER JOIN vtiger_contactscf ON 
vtiger_contactscf.contactid = vtiger_contactdetails.contactid INNER JOIN 
vtiger_customerdetails ON vtiger_customerdetails.customerid = 
vtiger_contactdetails.contactid LEFT JOIN vtiger_users ON 
vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON 
vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN 
vtiger_users_last_import ON 
vtiger_users_last_import.bean_id=vtiger_contactdetails.contactid INNER 
JOIN (SELECT vtiger_contactdetails.email  FROM vtiger_contactdetails 
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = 
vtiger_contactdetails.contactid INNER JOIN vtiger_contactaddress ON 
vtiger_contactaddress.contactaddressid = vtiger_contactdetails.contactid 
INNER JOIN vtiger_contactsubdetails ON 
vtiger_contactsubdetails.contactsubscriptionid = 
vtiger_contactdetails.contactid INNER JOIN vtiger_contactscf ON 
vtiger_contactscf.contactid = vtiger_contactdetails.contactid INNER JOIN 
vtiger_customerdetails ON vtiger_customerdetails.customerid = 
vtiger_contactdetails.contactid LEFT JOIN vtiger_users ON 
vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON 
vtiger_groups.groupid = vtiger_crmentity.smownerid  WHERE 
vtiger_crmentity.deleted = 0 GROUP BY vtiger_contactdetails.email HAVING 
COUNT(*)>1) AS temp
  ON  ifnull(vtiger_contactdetails.email,'null') = 
ifnull(temp.email,'null') WHERE vtiger_crmentity.deleted = 0 ORDER BY 
vtiger_contactdetails.email,vtiger_contactdetails.contactid ASC LIMIT 
25, 26;


it turns out to be much more efficient to not have paging at all on the 
find duplicates view, I added

                 $pagingModel->set('limit', 1000);

around line 79 of modules/Vtiger/views/FindDuplicates.php and it now 
shows up to 1000 lines in the same time it did 25, with no issues of 
duplicates splitting over page boundaries.

Alan.