[Vtigercrm-developers] Slow performing query

[Lee Valentine]

Hey Guys,

We were having performance issues with the crm taking ages to load so
I had all the queries being executed printed to the screen with the
duration each query was taking.  I found:

select distinct(setype) from vtiger_crmentity where crmid in (select
parent_id from vtiger_troubletickets)
106.894966125 seconds.

I tracked this down to modules/CustomView/CustomView.php line 1212 (in
function getSalesRelatedName)
$sql = "select distinct(setype) from vtiger_crmentity where crmid in
(select ". $adb->sql_escape_string($fieldname)." from ".
$adb->sql_escape_string($tablename).")";

I replaced it with the following:
$sql = "select distinct(setype) from vtiger_crmentity c INNER JOIN
".$adb->sql_escape_string($tablename)." t ON
t.".$adb->sql_escape_string($fieldname)." = c.crmid";

And received the result:
select distinct(setype) from vtiger_crmentity c INNER JOIN
vtiger_troubletickets t ON t.parent_id = c.crmid
0.0270490646362 seconds.

This has given our crm a MAJOR performance increase.

Thanks,
Lee