[Vtigercrm-developers] Performances with duplicate handling

[Aissa belaid]

I have no access for vtiger trac

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

> Thank you for pointing this out.
>
> Maybe you could also report it in Trac.
>
> David V.
>
>
> 2010/12/8 Aissa belaid <aissabelaid at gmail.com>
>
>> 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/
>>>
>>
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>



-- 
Aïssa Bélaïd,
Expert CRM/BI
Email : aissabelaid at gmail.com
Blog : www.aissabelaid.fr
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20101208/7f629f1a/attachment-0001.html