[Vtigercrm-developers] report performance improvement (possibly)

PlaymaN Tepes sshhocckk at gmail.com
Thu Feb 19 08:51:29 GMT 2015


Hi,

I did this and still very slow.

Can someone have another solution?

2013-04-11 6:37 GMT+02:00 Appu <apparao at vtiger.com>:

> Alan,
>
> Thank you so much for your feedback.
>
>
> Regards,
> Apparao G
>
> *TEAM*
>
> On Thu, Apr 11, 2013 at 5:27 AM, Alan Bell <alan.bell at libertus.co.uk>
> wrote:
>
>> with a rather large database we were having problems with report
>> performance, particularly with reports that reference multiple secondary
>> modules. Looking at the slow query logs I found the very large queries that
>> were involved and in the middle of them there are subqueries like this one:
>>
>> ... loads of sql ....fiedby left join (select vtiger_account.* from
>> vtiger_account inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid
>> and vtiger_crmentity.deleted=0) as vtiger_account on vtiger_contactdetails.
>> accountid=vtiger_account.accountid left join vtiger_crmentity as
>> vtiger_crmen... loads more sql
>>
>> where the bit in brackets is basically selecting the not-deleted rows of
>> vtiger_account then aliasing the result to be vtiger_account. This is
>> massively slow. If I remove that chunk altogether, making it just point at
>> the full vtiger_account table like this:
>>
>> ... loads of sql ....fiedby left join vtiger_account on
>> vtiger_contactdetails.accountid=vtiger_account.accountid left join
>> vtiger_crmentity as vtiger_crmen... loads more sql
>>
>> the query is very fast indeed. I think a better way to filter deleted
>> things is to do
>>
>> ... loads of sql ....fiedby left join vtiger_account on
>> vtiger_contactdetails.accountid=vtiger_account.accountid
>> inner join vtiger_crmentity as crmentityaccount on
>> crmentityaccount.crmid=vtiger_account.accountid and
>> crmentityaccount.deleted=0
>> left join vtiger_crmentity as vtiger_crmen... loads more sql
>>
>> I believe this to be equivalent, but I may be wrong.
>> the diff below to data/CRMEntity implements this change and I would be
>> interested in feedback on whether this is in fact equivalent to the
>> original SQL and whether this improves performance of reports for people.
>> For me it changed a 5614 second query into a 12 second query.
>>
>>
>> diff --git a/data/CRMEntity.php b/data/CRMEntity.php
>> index 66dccbf..be25c66 100644
>> --- a/data/CRMEntity.php
>> +++ b/data/CRMEntity.php
>> @@ -2030,8 +2030,11 @@ class CRMEntity {
>>              $condvalue = $table_name . "." . $column_name;
>>              $condition = "$pritablename.$secfieldname=$condvalue";
>>          }
>> -        $secQuery = "select $table_name.* from $table_name inner join
>> vtiger_crmentity on " .
>> -                "vtiger_crmentity.crmid=$table_name.$column_name and
>> vtiger_crmentity.deleted=0";
>> +//        $secQuery = "select $table_name.* from $table_name inner join
>> vtiger_crmentity on " .
>> +//                "vtiger_crmentity.crmid=$table_name.$column_name and
>> vtiger_crmentity.deleted=0";
>> +                $secQuery2 = "inner join vtiger_crmentity as
>> crmentity$table_name on " .
>> + "crmentity$table_name.crmid=$table_name.$column_name and
>> crmentity$table_name.deleted=0";
>> +
>>          $query = '';
>>          if ($pritablename == 'vtiger_crmentityrel') {
>>              $condition = "($table_name.$column_name={$tmpname}.{$secfieldname}
>> " .
>> @@ -2051,7 +2054,10 @@ class CRMEntity {
>>
>>          }
>>
>> -        $query .= " left join ($secQuery) as $table_name on
>> {$condition}";
>> +//        $query .= " left join ($secQuery) as $table_name on
>> {$condition}";
>> +//performance improvement, this should be equivalent to the subquery but
>> masses faster
>> +                $query .= " left join $table_name on {$condition}";
>> +                $query .= " $secQuery2";
>>
>>          return $query;
>>      }
>>
>>
>> --
>> Libertus Solutions
>> http://libertus.co.uk
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20150219/391b104e/attachment-0001.html>


More information about the vtigercrm-developers mailing list