[Vtigercrm-developers] report performance improvement (possibly)

[Appu]

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