[Vtigercrm-developers] Performances with duplicate handling

[Aissa belaid]

Thank you Sreenivas.

2010/12/9 Sreenivas Kanumuru <svk at vtiger.com>

> Aissa,
>
> We welcome your contributions. We will be emailing your  trac account
> details shortly.
>
> Regards,
> Sreenivas
>
> On Wed, Dec 8, 2010 at 3:29 PM, Aissa belaid <aissabelaid at gmail.com>wrote:
>
>> I'm not interested to work, discuss... with any abonline members (company
>> i have funded in 2005).
>> I have a tons of improvements for vtigerCRM (and large customers like
>> Dexia, leboncoin.fr...), so i can exchange with vtiger team, you joe and lot
>> of other community members.
>> Regards,
>>
>>
>> --
>> Aïssa Bélaïd,
>> Expert CRM/BI
>> Email : aissabelaid at gmail.com
>> Blog : www.aissabelaid.fr
>>
>>
>> 2010/12/8 Joe Bordes <joe at tsolucio.com>
>>
>>>  Hi,
>>>
>>> These are very important optimizations. Thank you.
>>> You should get together with Boris Clement on the optimizations project:
>>> http://forge.vtiger.com/projects/vtsql-opt/
>>>
>>> Thanks
>>> Joe
>>> TSolucio
>>>
>>> El 08/12/10 15:27, Aissa belaid escribió:
>>>
>>> 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
>>>
>>>
>>> _______________________________________________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/20101209/ca61620d/attachment-0002.html