<div dir="ltr">Hi,<br><br>I did this and still very slow.<br><br>Can someone have another solution?</div><div class="gmail_extra"><br><div class="gmail_quote">2013-04-11 6:37 GMT+02:00 Appu <span dir="ltr"><<a href="mailto:apparao@vtiger.com" target="_blank">apparao@vtiger.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><font color="#000000"><font face="verdana,sans-serif">Alan,</font></font><div><font color="#000000"><font face="verdana,sans-serif"><br></font></font></div><div><font color="#000000"><font face="verdana,sans-serif">Thank you so much for your feedback.<br clear="all">
</font></font><div><div dir="ltr"><span style="color:rgb(102,102,102);font-family:verdana,sans-serif"><font color="#000000"><br><br></font><span style="font-family:verdana,sans-serif">Regards,</span></span><br style="font-family:verdana,sans-serif">
<font style="background-color:rgb(255,255,255);color:rgb(0,0,0)"><span style="font-family:verdana,sans-serif">Apparao G</span></font><br><br style="font-family:verdana,sans-serif"><b style="color:rgb(51,51,51);background-color:rgb(255,255,255)"><font style="font-family:verdana,sans-serif;color:rgb(102,102,102)"><img src="https://gpmb7.vtiger.com/test/logo/vt1.PNG" height="24" width="96"><font size="1">TEAM</font></font></b><br>
<br></div></div><div><div class="h5"><div class="gmail_quote">On Thu, Apr 11, 2013 at 5:27 AM, Alan Bell <span dir="ltr"><<a href="mailto:alan.bell@libertus.co.uk" target="_blank">alan.bell@libertus.co.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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:<br>
<br>
... loads of sql ....fiedby left join (select vtiger_account.* from vtiger_account inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_<u></u>account.accountid and vtiger_crmentity.deleted=0) as vtiger_account on vtiger_contactdetails.<u></u>accountid=vtiger_account.<u></u>accountid left join vtiger_crmentity as vtiger_crmen... loads more sql<br>
<br>
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:<br>
<br>
... loads of sql ....fiedby left join vtiger_account on vtiger_contactdetails.<u></u>accountid=vtiger_account.<u></u>accountid left join vtiger_crmentity as vtiger_crmen... loads more sql<br>
<br>
the query is very fast indeed. I think a better way to filter deleted things is to do<br>
<br>
... loads of sql ....fiedby left join vtiger_account on vtiger_contactdetails.<u></u>accountid=vtiger_account.<u></u>accountid<br>
inner join vtiger_crmentity as crmentityaccount on crmentityaccount.crmid=vtiger_<u></u>account.accountid and crmentityaccount.deleted=0<br>
left join vtiger_crmentity as vtiger_crmen... loads more sql<br>
<br>
I believe this to be equivalent, but I may be wrong.<br>
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.<br>
<br>
<br>
diff --git a/data/CRMEntity.php b/data/CRMEntity.php<br>
index 66dccbf..be25c66 100644<br>
--- a/data/CRMEntity.php<br>
+++ b/data/CRMEntity.php<br>
@@ -2030,8 +2030,11 @@ class CRMEntity {<br>
$condvalue = $table_name . "." . $column_name;<br>
$condition = "$pritablename.$secfieldname=$<u></u>condvalue";<br>
}<br>
- $secQuery = "select $table_name.* from $table_name inner join vtiger_crmentity on " .<br>
- "vtiger_crmentity.crmid=$<u></u>table_name.$column_name and vtiger_crmentity.deleted=0";<br>
+// $secQuery = "select $table_name.* from $table_name inner join vtiger_crmentity on " .<br>
+// "vtiger_crmentity.crmid=$<u></u>table_name.$column_name and vtiger_crmentity.deleted=0";<br>
+ $secQuery2 = "inner join vtiger_crmentity as crmentity$table_name on " .<br>
+ "crmentity$table_name.crmid=$<u></u>table_name.$column_name and crmentity$table_name.deleted=<u></u>0";<br>
+<br>
$query = '';<br>
if ($pritablename == 'vtiger_crmentityrel') {<br>
$condition = "($table_name.$column_name={$<u></u>tmpname}.{$secfieldname} " .<br>
@@ -2051,7 +2054,10 @@ class CRMEntity {<br>
<br>
}<br>
<br>
- $query .= " left join ($secQuery) as $table_name on {$condition}";<br>
+// $query .= " left join ($secQuery) as $table_name on {$condition}";<br>
+//performance improvement, this should be equivalent to the subquery but masses faster<br>
+ $query .= " left join $table_name on {$condition}";<br>
+ $query .= " $secQuery2";<br>
<br>
return $query;<br>
}<span><font color="#888888"><br>
<br>
<br>
-- <br>
Libertus Solutions<br>
<a href="http://libertus.co.uk" target="_blank">http://libertus.co.uk</a><br>
<br>
______________________________<u></u>_________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br>
</font></span></blockquote></div><br></div></div></div></div>
<br>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>