Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-39928

Samigo > minimize redundant UPDATE queries on SAM_ITEMGRADING_T


    • 12 status:
    • 11 status:
    • Test Plan:

      Please add a Test Plan here.

      Please add a Test Plan here.


      When students navigate over questions without making changes, there are certain question types that update SAM_ITEMGRADING_T entries with nothing more than a new date stamp. In situations where there are a lot of questions in a quiz, with a lot of students taking the quiz at the same time, this can lead to poor performance at the database level. We found that in at least one case this code was responsible for putting full table locks on SAM_ITEMGRADING_T in high succession, which resulted in blocking hundreds of connections to the database. These blocking connections had to be killed manually by our DBA for the process to recover.

      We identified three specific question types with high usage that exhibit this behaviour: fill in the blank, multiple choice multiple correct, and numeric response.

      For fill in the blanks and numeric responses, oldAnswerText and newAnswerText differ when the student changes their actual response. For MCMR, oldAnswerId and newAnswerId differ. So the fibMap.get(itemId) != null, finMap.get(itemId) != null and mcmrMap.get(itemId) != null checks aren't determining if there were changes to the question: they only allowed the code to enter the 'if' block and as a result it adds unchanged FIB/MCMR/NR responses to the update set.

      We've had this code running in our production instance for over 2 years now, with multiple rounds of internal QA, and no reports of lost/missing updates of data.

        Gliffy Diagrams


            Issue Links



                • Assignee:
                  bjones86 Brian Jones
                  bjones86 Brian Jones
                • Votes:
                  0 Vote for this issue
                  3 Start watching this issue


                  • Created:

                    Git Source Code