Home Home
  login

Gemini Community Support Site

This Gemini community support site can be used to find solutions to product issues. You can log in using Open Id, Google Profile and even Facebook. Feel free to ask a question or browse FAQs and documentation. Product tour videos are also available along with how-to videos demonstrating key Gemini capabilities.




Home page load Very Slow in 4.0

database

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
User32684
Replies (4)
helpful
0
not helpful
ANSWER

That query was there for 3.7 as well. It might be that your database is fragmented. What is your database size and increment settings?

Try updating the stats on the gemini_issues table:

update statistics gemini_issues with fullscan


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

The database size is 61MB, and increment settings is 1MB. I also did update statistics on this table, but no results.


User32684
· 1
User32684
helpful
0
not helpful

Your data is fragmented as the increment is too low! Make it at least 10mb. Can you try and re-index your database? As I've said before that query didn't change between 3.7 and 4.0.


Mark Wing
· 9108
Mark Wing
helpful
0
not helpful

Yes, i changed it to 200MB and re-indexed database. Now query takes about 30ms. Thanks!


User32684
· 1
User32684