Home page load Very Slow in 4.0
There is a problem with loading home page after upgrade gemini from 3.7 to 4.0. Home page are loading about 15-20 seconds. I check, that the main problem is this SQL query:
SET DATEFIRST 1
SELECT
CAST(i.projectid AS INT) AS ProjectID,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, getdate()) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3,
SUM(CASE WHEN i.created > u.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, getdate()) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW0,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW1,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW2,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW3,
SUM(CASE WHEN i.closeddate > u.previouslogindate THEN 1 ELSE 0 END) AS ClosedSinceLastLogin,
MIN(ISNULL(c.CreatedW0, 0)) AS CommentCreatedW0,
MIN(ISNULL(c.CreatedW1, 0)) AS CommentCreatedW1,
MIN(ISNULL(c.CreatedW2, 0)) AS CommentCreatedW2,
MIN(ISNULL(c.CreatedW3, 0)) AS CommentCreatedW3,
MIN(ISNULL(c.CreatedSinceLastLogin, 0)) AS CommentCreatedSinceLastLogin,
SUM(CASE WHEN s.isfinal = 0 THEN 1 ELSE 0 END) AS OpenIssues
FROM gemini_issues i
LEFT JOIN (
SELECT
ii.projectid,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, getdate()) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3,
SUM(CASE WHEN ic.created > uu.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin
FROM gemini_issuecomments ic INNER JOIN gemini_issues ii ON ii.issueid = ic.issueid INNER JOIN gemini_users uu ON uu.userid = 68
WHERE ((ii.visibilitymembertype=2 and 68 in (select gg.userid from gemini_globalgroupmembership gg where gg.globalgroupid = ii.visibility)) or (ii.visibilitymembertype=3 and 68 in (select pg.userid from gemini_projectgroupmembership pg where pg.projectgroupid = ii.visibility)))
AND ((ic.visibilitymembertype=2 and 68 in (select gg.userid from gemini_globalgroupmembership gg where gg.globalgroupid = ic.visibility)) or (ic.visibilitymembertype=3 and 68 in (select pg.userid from gemini_projectgroupmembership pg where pg.projectgroupid = ic.visibility)))
GROUP BY ii.projectid
) c ON i.projectid = c.projectid
INNER JOIN gemini_users u ON u.userid = 68
INNER JOIN gemini_issuestatus s ON s.statusid = i.issuestatusid
WHERE ((i.visibilitymembertype=2 and 68 in (select gg.userid from gemini_globalgroupmembership gg where gg.globalgroupid = i.visibility)) or (i.visibilitymembertype=3 and 68 in (select pg.userid from gemini_projectgroupmembership pg where pg.projectgroupid = i.visibility)))
AND (i.projectid in (35) and (i.reportedby = 68 or 68 in (select ir.userid from gemini_issueresources ir where ir.issueid = i.issueid)) or i.projectid in (29,27,25,36,34) )
GROUP BY i.projectid
What can i do, to increase speed of this query and Home page?
User32684
· 1 |
|
Friday, May 6, 2011, 10:28:39 AM |