[gradebook2-dev] Gradebook2: Students' list takes a long time to load completly

[RAUL SANCHEZ VEGAS]

Hi all

We are experimenting a problem with Gradebook2. When a site has too many 
sections (i.e. 10 sections), the list of students takes more than 3 or 4 
minutes to load completly. When the instructor changes to another page, 
it takes again more than 3 or 4 minutes until the students list is 
displayed.

The reason is that Gradebook2 tries to load all students' scores of all 
assignments using the following query.

*/Class: GradebookToolServiceImpl.java, /**/
/**/method: /**/List<AssignmentGradeRecord> 
getAllAssignmentGradeRecords(final Long gradebookId, final String[] 
realmIds, final String[] roleNames)/*

/SELECT   assignment0_.ID AS ID1032_, //
//        assignment0_.VERSION AS VERSION1032_, //
//        assignment0_.GRADABLE_OBJECT_ID AS GRADABLE4_1032_, //
//        assignment0_.STUDENT_ID AS STUDENT5_1032_, //
//        assignment0_.GRADER_ID AS GRADER6_1032_, //
//        assignment0_.DATE_RECORDED AS DATE7_1032_, //
//        assignment0_.POINTS_EARNED AS POINTS8_1032_, //
//        assignment0_.IS_EXCLUDED_FROM_GRADE AS IS9_1032_ //
// FROM   GB_GRADE_RECORD_T assignment0_, //
//        GB_GRADABLE_OBJECT_T gradableob1_, //
//        SAKAI_REALM realm2_, //
//        SAKAI_REALM_RL_GR realmgroup3_, //
//        SAKAI_REALM_ROLE realmrole4_ //
//WHERE       assignment0_.OBJECT_TYPE_ID = 1 //
//        AND assignment0_.GRADABLE_OBJECT_ID = gradableob1_.ID //
//        AND assignment0_.STUDENT_ID = realmgroup3_.USER_ID //
//        AND realmgroup3_.ROLE_KEY = realmrole4_.ROLE_KEY //
//        AND realm2_.REALM_KEY = realmgroup3_.REALM_KEY //
//        AND gradableob1_.GRADEBOOK_ID = :1 //
//        AND (realm2_.REALM_ID IN (:2, :3, :4, :5, :6, :7, :8, :9, :10, 
:11)) //
//        AND (gradableob1_.REMOVED = 0 OR gradableob1_.REMOVED IS NULL) //
//        AND (realmrole4_.ROLE_NAME IN (:12)) //
/
This query takes a long time if there are too many sections. We have 
investigated the way to reduce this time:

The query is searching for all users (in that particular site) matching 
in rol and sections, and that also have assignments with grades in 
gradebook2. We think that the query should be split into 2 queries:


First query

/SELECT REALMGROUP3_.USER_ID//
// FROM   SAKAI_REALM realm2_,//
//        SAKAI_REALM_RL_GR realmgroup3_,//
//        SAKAI_REALM_ROLE realmrole4_//
//WHERE   realmgroup3_.ROLE_KEY = realmrole4_.ROLE_KEY//
//        AND realm2_.REALM_KEY = realmgroup3_.REALM_KEY//
//        AND (realm2_.REALM_ID IN (//:2, :3, :4, :5, :6, :7, :8, :9, 
:10, :11)//) //
//        AND (realmrole4_.ROLE_NAME IN (//:12//))/

Second query

/SELECT   assignment0_.ID AS ID1032_,//
//        assignment0_.VERSION AS VERSION1032_,//
//        assignment0_.GRADABLE_OBJECT_ID AS GRADABLE4_1032_,//
//        assignment0_.STUDENT_ID AS STUDENT5_1032_,//
//        assignment0_.GRADER_ID AS GRADER6_1032_,//
//        assignment0_.DATE_RECORDED AS DATE7_1032_,//
//        assignment0_.POINTS_EARNED AS POINTS8_1032_,//
//        assignment0_.IS_EXCLUDED_FROM_GRADE AS IS9_1032_//
// FROM   GB_GRADE_RECORD_T assignment0_,//
//        GB_GRADABLE_OBJECT_T gradableob1_//
//WHERE       assignment0_.OBJECT_TYPE_ID = 1//
//        AND assignment0_.GRADABLE_OBJECT_ID = gradableob1_.ID//
//        AND (gradableob1_.REMOVED = 0 OR gradableob1_.REMOVED IS NULL)//
//        AND gradableob1_.GRADEBOOK_ID = //:1 //
//        AND assignment0_.STUDENT_ID IN//
//        (:2)/

The first query returns the list of the users (in that particular site) 
matching in rol and sections. The second query returns all scores of all 
assignments from the students obtained from the first query. These 2 
queries together take about 4 or 5 seconds. The original query takes 
about 3 or 4 minutes.

Has anyone faced the same problem? Does anyone have other solutions?

Thanks,
Raúl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/gradebook2-dev/attachments/20130204/b0cd8079/attachment.html