[SAK-39928] Samigo > minimize redundant UPDATE queries on SAM_ITEMGRADING_T Created: 26-Apr-2018  Updated: 25-May-2018  Resolved: 01-May-2018

Status: Verified
Project: Sakai
Component/s: Tests & Quizzes (Samigo)
Affects Version/s: 11.4, 12.0, 12.1
Fix Version/s: 11.5 [Tentative], 12.2, 19.0

Type: Feature Request Priority: Critical
Reporter: Brian Jones Assignee: Brian Jones
Resolution: Fixed Votes: 0
Labels: performance

Issue Links:
relates to SAK-36814 update SAM_ITEMGRADING_T multiplies a... Verified
12 status: Resolved
11 status: Resolved
Test Plan:

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.

Comment by Sam Ottenhoff [ 26-Apr-2018 ]

Have you checked out SAM-3325 for your 11.x install?

Comment by Brian Jones [ 26-Apr-2018 ]

Yes, we've backported SAM-3325 as well. I think both fixes are applicable.

Comment by Daniel Merino Echeverría [ 25-May-2018 ]

I have recently backported this patch for our 11.4 and I have added a PR for 11.x branch. HTH.

Generated at Sun Feb 23 09:24:57 CST 2020 using Jira 8.0.3#800011-sha1:073e8b433c2c0e389c609c14a045ffa7abaca10d.