I have noticed recently that the performance of the ProjectsWS.GetAllProjects web service seems to be degrading quite a bit in my Gemini Installation, so I did some research to find out why.  As I was researching this, I found that the webservice above was calling the geminigetworkload stored procedure.  I looked at the procedure, and found that the code of this procedure is listed below.  Specifically, in the second select statement, you are including the customfielddefs table in your from list, but not using any data from that table, and not actually joining that table to any other table in your query.  Therefore, the results of joining the customfielddata table and the issuesview are repeated once for every record in the cusomfielddefs table.  Since I had 611 customfielddefs in my database, this turned out to be quite a performance overhead.  When I commented out the highlighted code, it reduced the size of the recordset from 178,480 records (99.8% of them duplicates) down to 270 distinct records.  I thought you might like to know about this in case you are not already aware of it, so you can fix it in your next release.

create procedure geminigetworkload 
 @userid numeric(10,0) 
as 
begin 
 -- Send back the issues
 select a.projid,b.ProjName,a.issueid,a.issuekey,a.summary, 
  a.typedesc,a.statusdesc,a.resdesc,a.assigneddesc,a.prioritydesc, 
  a.isstype,a.issstatus,a.issresolution,a.isspriority,a.compname,
  a.startdate,a.duedate,estimatedays,estimatehours,estimateminutes,
  isnull(t.hourslogged,0) as hourslogged, isnull(t.minuteslogged,0) as minuteslogged
 from projects b,issuesview a
 left outer join
  (select issueid,sum(hours) as hourslogged, sum(minutes) as minuteslogged
  from timetracking
  group by issueid) t
  on a.issueid = t.issueid
 where a.assignedto=@userid
  and a.projid=b.projid
  and a.issstatus not in (select statusid from issuestatusfinal)
 order by a.projid asc,a.duedate

 -- Send back the custom fields for the issues
 select a.issueid, b.customfieldid, b.fielddata
 from customfielddata b,customfielddefs c, issuesview a
 where a.assignedto=@userid
  and a.projid=b.projid
  and a.issstatus not in (select statusid from issuestatusfinal)
  and b.issueid=a.issueid
 order by a.issueid
end