[Building Sakai] Question pool slowness

[Will Humphries]

Hi George,

It sounds like your users are having trouble viewing pools. This is a 
little tangential but maybe it will be helpful anyway - maybe once users 
start publishing quizzes which use those pools. We had performance 
issues with users taking and checking grades on quizzes which pulled 
from several large pools this summer. One particular quiz pulled 10 
random questions each from 9 pools which ranged from a few dozen to a 
few hundred questions. We're running 2.5.4 and have the code changes 
from SAK-10053, SAK-14585, and SAK-10079 in our copy of samigo already.

We worked with our db group to identify a few indexes which they thought 
would improve performance. It turned out 4 of the 5 indices they 
recommended were in 
/sam/samigo-pack/src/sql/oracle/sakai_samigo_post_schema_update.sql . We 
already had all but 4 of the indices in this file in our schema. Adding 
the indices we didn't already have dramatically improved performance. We 
didn't have, and ended up including, the following:

create index SAM_PUBITEMTEXT_ITEM_I on SAM_PUBLISHEDITEMTEXT_T (ITEMID) 
compute statistics;
create index SAM_PUBANSWER_ITEMTEXT_I on SAM_PUBLISHEDANSWER_T 
(ITEMTEXTID) compute statistics;
create index SAM_PUBANSWERFB_ANSWER_I on SAM_PUBLISHEDANSWERFEEDBACK_T 
(ANSWERID) compute statistics;
create index SAM_PUBLISHEDASSESSMENT_I on SAM_ASSESSMENTGRADING_T 
(PUBLISHEDASSESSMENTID) compute statistics;

At the recommendation of our db group we also added this:

create index VT_SAM_ITEMGRADING_AG_PUBITEM
    on SAM_ITEMGRADING_T ( ASSESSMENTGRADINGID, PUBLISHEDITEMID )
       compute statistics;

I'm not sure if this will be as useful in MySQL as it was in Oracle. I 
noticed 
/sam/samigo-pack/src/sql/mysql/sakai_samigo_post_schema_update.sql 
doesn't include most of these indexes...

-Will

George Pipkin wrote:
> We are receiving complaints from our users about how slow it
> is to load question pools. In one case, a question pool has 192 
> questions in it,
> and takes 50 – 60 seconds to display after clicking on it in the question
> pools page. Is there any way to improve the performance of this function?
> Is there a practical limit on the size of question pools ?
>
>
> - George Pipkin
> U.Va.
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
>