[SAK-39928] Samigo > minimize redundant UPDATE queries on SAM_ITEMGRADING_T Created: 26-Apr-2018 Updated: 25-May-2018 Resolved: 01-May-2018
|Component/s:||Tests & Quizzes (Samigo)|
|Affects Version/s:||11.4, 12.0, 12.1|
|Fix Version/s:||11.5 [Tentative], 12.2, 19.0|
|Reporter:||Brian Jones||Assignee:||Brian Jones|
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
|Comment by Brian Jones [ 26-Apr-2018 ]|
Yes, we've backported
|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.