[Vtigercrm-developers] report performance improvement (possibly)

[Alan Bell]

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