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

[Thomas Amsler]

Raúl,

Can you please create a GRBK JIRA for this?
https://jira.sakaiproject.org/browse/GRBK

Thank you

Best,
-- Thomas


On Mon, Feb 4, 2013 at 6:08 AM, RAUL SANCHEZ VEGAS <raulsv at um.es> wrote:

>  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
>
> _______________________________________________
> gradebook2-dev mailing list
> gradebook2-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/gradebook2-dev
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/gradebook2-dev/attachments/20130204/95463186/attachment.html