[Vtigercrm-developers] Performances with duplicate handling

[Aissa belaid]

idem with this column : vtiger_troubletickets.product_id and
vtiger_troubletickets.parent_id

*ALTER* *TABLE* `vtiger_troubletickets` CHANGE `product_id` `product_id`
INT( 19 ) NULL DEFAULT NULL;

*ALTER* *TABLE* `vtiger_troubletickets` CHANGE `parent_id` `parent_id` INT(
19 ) NULL DEFAULT NULL;

2010/12/8 Aissa belaid <aissabelaid at gmail.com>

> 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
>
>


-- 
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/5953f9ed/attachment.html