Thanks for the quick reply and suggestions -- I took the liberty and replied via the official support email address with my findings (which I'll also post below) so I could track it through the appropriate channels.
Basically, we did notice a few things about our Gemini DB -- notably:
- The data size setting was way off. The increment setting was 1MB and only have few MB left. We bumped the data file size to 12GB so it is 3-4GB empty and change the increment setting to 50MB.
- We also set the update statistics to run weekly on all tables.
The problem still persisted, but with a little digging, here is what our DBA's found:
-----
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman","serif";}
I track down the query behind it
and found that
OR operation causes the problem.
SELECT distinct this_.issueid as y0_, this_.revised as y1_
FROM gemini_issuesview this_
left outer join gemini_issuecomments comments1_ on this_.issueid=comments1_.issueid
left outer join gemini_customfielddata customfiel2_ on this_.issueid=customfiel2_.issueid
left outer join gemini_timetracking timeentrie3_ on this_.issueid=timeentrie3_.issueid
left outer join gemini_affectedversions affectedve6_ on this_.issueid=affectedve6_.issueid
left outer join gemini_issueresources resources4_ on this_.issueid=resources4_.issueid
left outer join gemini_issuecomponents components5_ on this_.issueid=components5_.issueid
WHERE (( this_.visibilitymembertype=2
and 33 in (select gg.userid from gemini_globalgroupmembership gg where gg.globalgroupid
= this_.visibility))
or (this_.visibilitymembertype=3 and 33 in (select pg.userid from gemini_projectgroupmembership
pg where pg.projectgroupid
= this_.visibility)))
AND this_.projectid in (1)
and (this_.summary like '%project%'
OR this_.longdesc like '%project%'
OR comments1_.comment like '%project%'
OR customfiel2_.fielddata
like '%project%')
If I replace it with
UNION.
SELECT …
AND this_.projectid in (1) and (this_.summary like '%project%')
UNION
SELECT …
AND this_.projectid in (1) and (this_.longdesc like '%project%')
UNION
SELECT …
AND this_.projectid in (1) and (comments1_.comment like '%project%')
UNION
SELECT …
AND this_.projectid in (1) and (customfiel2_.fielddata
like '%project%')
It reduces the
running time from 40+ second to 4 sec. the query was called from application so
it can only be changed there. Can you convey this message to CounterSoft?
-----
Sorry if the forum and the email go to the same team, but hopefully you can reconcile that on your end and possibly give an update if this feedback is valuable.
Let us know! Thanks..
jg
