[Vtigercrm-developers] Performances with duplicate handling

[Aissa belaid]

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