id	summary	reporter	owner	description	type	status	priority	milestone	component	version	resolution	keywords	cc
6108	Subquery in CustomView.php causing slow load of homepage	brett.hooker	developer	We were having performance issues with 5.1 taking ages to load the Homepage so\r\nwe had all the queries being executed printed to the screen with the\r\nduration each query was taking.  We found:\r\n\r\nselect distinct(setype) from vtiger_crmentity where crmid in (select\r\nparent_id from vtiger_troubletickets)\r\n106.894966125 seconds.\r\n\r\nWe tracked this down to modules/CustomView/CustomView.php line 1212 (in\r\nfunction getSalesRelatedName)\r\n$sql = "select distinct(setype) from vtiger_crmentity where crmid in\r\n(select ". $adb->sql_escape_string($fieldname)." from ".\r\n$adb->sql_escape_string($tablename).")";\r\n\r\nI replaced it with the following:\r\n$sql = "select distinct(setype) from vtiger_crmentity c INNER JOIN\r\n".$adb->sql_escape_string($tablename)." t ON\r\nt.".$adb->sql_escape_string($fieldname)." = c.crmid";\r\n\r\nAnd received the result:\r\nselect distinct(setype) from vtiger_crmentity c INNER JOIN\r\nvtiger_troubletickets t ON t.parent_id = c.crmid\r\n0.0270490646362 seconds.\r\n\r\nThis has given our crm a MAJOR performance increase.	defect	closed	major	5.1.0	vtigercrm	5.1.0-wip	fixed