[Vtigercrm-developers] Performances with duplicate handling

[David V.]

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/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20101208/1dbfb5db/attachment.html