Issue "Find" Performance
Find issue performance is extremely slow. It takes a minimum of 30 seconds to find a string (such as "FTP"). We have less than 12,000 total issues, of which less than 1000 are current.
We are using Gemini 3.5.3 (commercial license), with SQL 2008, with NO customizations (other than that allowed via the software), on a 4 core machine.
The web server and database servers are about 2% utilized before a find operations. During the find, the SQL server utilization only goes to 25%. Yet, the query ALWAYS takes 30 seconds.
Upon a detail investigation, and tracing in SQL, the main query is pulling from gemini_issuesview. The tables that this and the find operation use, contain the following record counts:
geminiglobalgroupmembership 520 geminiissuepriorities 7 geminiissuepriorityschemeitems 7 geminiissueresolutions 7 geminiissuerisklevels 4 geminiissues 11482 geminiissueseverity 6 geminiissueseverityschemeitems 6 geminiissuestatus 23 geminiissuetypes 24 geminiissueworkflowitem 35 geminiprojectgroupmembership 90 geminiprojects 25 geminiusers 153 gemini_versions 173
The sql monitor reports that the query is performing 415,852 reads (even with only 11,482 total issues). These appear to be because of all of the left joins, "and" and "or" statements.
When we perform a direct sql query looking for a key word, the operation returns in less than 1 second.
We have the database growth set to 100mb, have run the recommended tuning indexes (from sql), and have run the update statistics operation on all tables, all with no change in performance.
The query operation (below) is highly complex and is doing some type of full table scan, at least 32 times (per the read counters).
Does anyone know what might be causing this or any way to address it?
The offending query is:
exec spexecutesql N'SELECT distinct this.issueid as y0, this.revised as y1_ FROM geminiissuesview this left outer join geminiissuecomments comments1 on this.issueid=comments1.IssueID left outer join geminicustomfielddata customfiel2 on this.issueid=customfiel2.IssueID left outer join geminitimetracking timeentrie3 on this.issueid=timeentrie3.IssueID left outer join geminiaffectedversions affectedve6 on this.issueid=affectedve6.IssueID left outer join geminiissueresources resources4 on this.issueid=resources4.IssueID left outer join geminiissuecomponents components5 on this.issueid=components5.IssueID WHERE ((this.visibilitymembertype=2 and @p0 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = this.visibility)) or (this.visibilitymembertype=3 and @p1 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = this.visibility))) AND this.projectid in (@p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18) and not this.issuestatusid in (@p19, @p20) and (this.summary like @p21 or this.longdesc like @p22 or comments1.comment like @p23 or customfiel2.fielddata like @p24) ORDER BY this.revised desc, this.issueid desc',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 int,@p12 int,@p13 int,@p14 int,@p15 int,@p16 int,@p17 int,@p18 int,@p19 int,@p20 int,@p21 nvarchar(6),@p22 nvarchar(6),@p23 nvarchar(6),@p24 nvarchar(6)' ,@p0=50,@p1=50,@p2=40,@p3=41,@p4=42,@p5=43,@p6=28,@p7=44,@p8=3,@p9=11,@p10=37,@p11=38,@p12=39,@p13=2,@p14=25,@p15=33,@p16=31,@p17=30,@p18=32,@p19=4,@p20=27,@p21=N'%FTP%',@p22=N'%FTP%',@p23=N'%FTP%',@p24=N'%FTP%'
Thanks!
|
markrees
· 1 |
|
| Friday, June 25, 2010, 9:15:04 PM | |
|
|
|
||||
|
|||||
|
|
|
||||
|
|||||
|
|
|
||||
|
|||||
|
|
|
||||
|
|||||
|
|
|
||||
|
|||||