[Building Sakai] Question pool slowness

[George Pipkin]

Actually we are running v2.5.3 with mysql.  So it looks like we already 
have these.....

                                  - George

Karen Tsao wrote:
> Hi Will,
>
> Thanks. I just want to add a note about why these four sql are not in 
> MySql sakai_samigo_post_schema_update.sql script. These indices 
> actually have been defined in Hibernate hbm files. In MySql, these get 
> created automatically, but not in Oracle. So for Oracle, we decided to 
> add them manually using sakai_samigo_post_schema_update.sql.
>
> Karen
>
> On Wed, Sep 9, 2009 at 7:07 AM, Will Humphries <whumphri at vt.edu 
> <mailto:whumphri at vt.edu>> wrote:
>
>     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
>     <mailto: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
>     <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a
>     subject of "unsubscribe"
>     >
>     >
>     _______________________________________________
>     sakai-dev mailing list
>     sakai-dev at collab.sakaiproject.org
>     <mailto: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
>     <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a
>     subject of "unsubscribe"
>
>