[Vtigercrm-developers] Performances with duplicate handling

[Aissa belaid]

Hi David,
because you have a join between integer col & varchar col in the sql query.
With 100 line, no problem ;-)
With 100 000 you can wait 2h.

Exemple query for contacts :


select vtiger_contactdetails.contactid as
recordid,vtiger_users_last_import.deleted,$table_cols
                    FROM vtiger_contactdetails
                    INNER JOIN vtiger_crmentity ON
vtiger_crmentity.crmid=vtiger_contactdetails.contactid
                    INNER JOIN vtiger_contactaddress ON
vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid
                    INNER JOIN vtiger_contactsubdetails ON
vtiger_contactaddress.contactaddressid =
vtiger_contactsubdetails.contactsubscriptionid
                    LEFT JOIN vtiger_contactscf ON
vtiger_contactscf.contactid = vtiger_contactdetails.contactid
                   * LEFT JOIN vtiger_users_last_import ON
vtiger_users_last_import.bean_id=vtiger_contactdetails.contactid**
                    LEFT JOIN vtiger_account ON
vtiger_account.accountid=vtiger_contactdetails.accountid
                    LEFT JOIN vtiger_customerdetails ON
vtiger_customerdetails.customerid=vtiger_contactdetails.contactid
                    LEFT JOIN vtiger_groups ON vtiger_groups.groupid =
vtiger_crmentity.smownerid
                    LEFT JOIN vtiger_users ON vtiger_users.id =
vtiger_crmentity.smownerid
                    INNER JOIN (select $select_clause from
vtiger_contactdetails t
                            INNER JOIN vtiger_crmentity crm ON
crm.crmid=t.contactid
                            INNER JOIN vtiger_contactaddress addr ON
t.contactid = addr.contactaddressid
                            INNER JOIN vtiger_contactsubdetails subd ON
addr.contactaddressid = subd.contactsubscriptionid
                            LEFT JOIN vtiger_contactscf tcf ON t.contactid =
tcf.contactid
                            LEFT JOIN vtiger_account acc ON
acc.accountid=t.accountid
                            LEFT JOIN vtiger_customerdetails custd ON
custd.customerid=t.contactid
                            WHERE crm.deleted=0 group by $select_clause
HAVING COUNT(*)>1) as temp
                        ON
".get_on_clause($field_values,$ui_type_arr,$module)."
                    WHERE vtiger_crmentity.deleted=0 $sec_parameter ORDER BY
$table_cols,vtiger_contactdetails.contactid ASC

*Here uou kill the perfs. Contactid is an INT type, and bean_id a varchar.
You don't need this patch for small database.


2010/12/8 David V. <davidv.net at gmail.com>

> Aïssa,
>
> Could you explain why it improves perfs ?
>
> David V.
>
>
> 2010/12/8 Aissa belaid <aissabelaid at gmail.com>
>
>> Hi team,
>> there is a problem with database schema for duplicate handling & import
>> function.
>> This is a fix to improve 10x the perfs:
>> ALTER TABLE `vtiger_users_last_import` CHANGE `assigned_user_id`
>> `assigned_user_id` INT( 19 ) NULL DEFAULT NULL ,
>> CHANGE `bean_id` `bean_id` INT( 19 ) NULL DEFAULT NULL
>> Cheer,
>> --
>> Aïssa Bélaïd,
>> Expert CRM/BI
>> Email : aissabelaid at gmail.com
>> Blog : www.aissabelaid.fr
>>
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20101208/fe0cbfe8/attachment-0002.html