#71 performance issues 0.9 [SOLVED]
Our installation of HelpmeICT contains about 14 sites and 1350 users. (These users can work on all 14 sites so the usersites table contains about 19000 records). We have about 20000 issues.
The "Find Issue" page took about 8 seconds to display which frustrated the support engineers. We have optimized the queries so that it takes about 1,5 seconds now to display the page.
Maybe others can benefit from these changes, or, these can be made default in the helpmeict distribution.
The improvents where achieved bij using supqueries and only select columns that you actually need:
old: $categoriesRS = db_recordset("SELECT DISTINCT tbl_ProblemCategories.* FROM tbl_ProblemCategories,tbl_ProblemDetails,tbl_Issues WHERE tbl_ProblemCategories.id=tbl_ProblemDetails.category AND tbl_ProblemDetails.id=tbl_Issues.detail AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_ProblemCategories.description");
new: $categoriesRS = db_recordset("SELECT DISTINCT tbl_ProblemCategories.id,tbl_ProblemCategories.description FROM tbl_ProblemCategories WHERE tbl_ProblemCategories.id IN (SELECT DISTINCT tbl_Issues.category FROM tbl_Issues WHERE tbl_Issues.domain=$_SESSION[_domain]) ORDER BY tbl_ProblemCategories.description");
old: $detailsRS = db_recordset("SELECT DISTINCT tbl_ProblemDetails.* FROM tbl_ProblemDetails,tbl_Issues WHERE tbl_ProblemDetails.id=tbl_Issues.detail AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_ProblemDetails.description");
new: $detailsRS = db_recordset("SELECT DISTINCT tbl_ProblemDetails.id, tbl_ProblemDetails.category, tbl_ProblemDetails.description FROM tbl_ProblemDetails WHERE tbl_ProblemDetails.id IN (SELECT DISTINCT tbl_Issues.detail FROM tbl_Issues WHERE tbl_Issues.domain=$_SESSION[_domain]) ORDER BY tbl_ProblemDetails.description");
old: $usersRS = db_recordset("SELECT DISTINCT tbl_Users.* FROM tbl_Users,tbl_Issues WHERE tbl_Issues.reportedby=tbl_Users.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Users.name");
new: $usersRS = db_recordset("SELECT DISTINCT tbl_Users.id, tbl_Users.name FROM tbl_Users WHERE tbl_Users.id IN (SELECT DISTINCT tbl_Issues.reportedby FROM tbl_Issues WHERE tbl_Issues.domain=$_SESSION[_domain]) ORDER BY tbl_Users.name");
old: $assignedtoRS = db_recordset("SELECT DISTINCT tbl_Users.* FROM tbl_Users,tbl_Issues WHERE tbl_Issues.assignedto=tbl_Users.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Users.name");
new: $assignedtoRS = db_recordset("SELECT DISTINCT tbl_Users.id, tbl_Users.name WHERE tbl_Users.id IN (SELECT DISTINCT tbl_Issues.assignedto FROM tbl_Issues WHERE tbl_Issues.domain=$_SESSION[_domain]) ORDER BY tbl_Users.name");
old: $createdbyRS = db_recordset("SELECT DISTINCT tbl_Users.* FROM tbl_Users,tbl_Issues WHERE tbl_Issues.createdby=tbl_Users.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Users.name");
new: $createdbyRS = db_recordset("SELECT DISTINCT tbl_Users.id, tbl_Users.name WHERE tbl_Users.id in(SELECT DISTINCT createdby FROM tbl_Issues WHERE tbl_Issues.domain=$_SESSION[_domain]) ORDER BY tbl_Users.name");
Optional changes to speed up things:
old: $statusesRS = db_recordset("SELECT DISTINCT tbl_Statuses.* FROM tbl_Statuses,tbl_Issues WHERE tbl_Issues.status=tbl_Statuses.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Statuses.sortorder");
new: $statusesRS = db_recordset("SELECT DISTINCT tbl_Statuses.status, tbl_Statuses.id, tbl_Statuses.sortorder FROM tbl_Statuses WHERE tbl_Statuses.domain = $_SESSION[_domain] ORDER BY tbl_Statuses.sortorder");
please note that this also selects the statuses that are not assigned to issues!
old: $sitesRS = db_recordset("SELECT DISTINCT tbl_Sites.* FROM tbl_UserSites,tbl_Sites,tbl_Issues WHERE tbl_UserSites.\"user\"=$_SESSION[_id] AND tbl_UserSites.site=tbl_Sites.id AND tbl_Sites.domain=$_SESSION[_domain] AND tbl_Issues.site=tbl_Sites.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Sites.site");
new: $sitesRS = db_recordset("SELECT DISTINCT tbl_Sites.id, tbl_Sites.site FROM tbl_Sites WHERE tbl_Sites.domain=$_SESSION[_domain] ORDER BY tbl_Sites.site");
please note that this query selects all sites. Also the ones that are not assigned to an issue!
old: $levelsRS = db_recordset("SELECT DISTINCT tbl_Levels.* FROM tbl_Levels,tbl_Issues WHERE tbl_Issues.level=tbl_Levels.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Levels.sortorder");
new: $levelsRS = db_recordset("SELECT DISTINCT tbl_Levels.id, tbl_Levels.level, tbl_Levels.sortorder FROM tbl_Levels,tbl_Issues WHERE tbl_Issues.level=tbl_Levels.id AND tbl_Issues.domain=$_SESSION[_domain] ORDER BY tbl_Levels.sortorder");
please note that this query selects all levels. Also the ones that are not assigned to an issue!
