[Vtigercrm-developers] Performances with duplicate handling

[Joe Bordes]

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 
> <mailto:aissabelaid at gmail.com>>
>
>     I have no access for vtiger trac
>
>     2010/12/8 David V. <davidv.net <http://davidv.net>@gmail.com
>     <http://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
>         <mailto: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 <http://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
>             <http://davidv.net>@gmail.com <http://gmail.com>>
>
>                 Aïssa,
>
>                 Could you explain why it improves perfs ?
>
>                 David V.
>
>
>                 2010/12/8 Aissa belaid <aissabelaid at gmail.com
>                 <mailto: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
>                     <mailto:aissabelaid at gmail.com>
>                     Blog : www.aissabelaid.fr <http://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 <mailto:aissabelaid at gmail.com>
>     Blog : www.aissabelaid.fr <http://www.aissabelaid.fr>
>
>
>
>
> -- 
> Aïssa Bélaïd,
> Expert CRM/BI
> Email : aissabelaid at gmail.com <mailto:aissabelaid at gmail.com>
> Blog : www.aissabelaid.fr <http://www.aissabelaid.fr>
>
>
> _______________________________________________
> http://www.vtiger.com/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20101208/8b0d063a/attachment-0002.html