Moodle
  1. Moodle
  2. MDL-4303

quiz reports: very large queries

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.5.2
    • Fix Version/s: None
    • Component/s: Quiz
    • Labels:
      None
    • Environment:
      All
    • Database:
      Any
    • Affected Branches:
      MOODLE_15_STABLE
    • Rank:
      8550

      Description

      We have some problems with the logic that generates the sql for the reports.

      The $users array that gets imploded into an IN() is just not efficient with large numbers of users. I don't understand the logic really - why can't you just either get all attempts out of the attempts table and just do a straight JOIN mdl_user u ON u.id = attempt.userid or whatever it is, and then for the show users with no attempts bit, do a LEFT JOIN on null with mdl_user_students + user.

      At the moment we're dying on this page It just times out.

        Activity

        Hide
        Martin Dougiamas added a comment -

        From (penny at catalyst.net.nz) Wednesday, 9 November 2005, 12:09 PM:

        attached example huge query.

        From (penny at catalyst.net.nz) Friday, 11 November 2005, 08:52 AM:

        I've been working on a patch for this which I shall post after it's been thoroughly tested on a large dataset, however I believe that the current behaviour of 'show students with no attempts' is flawed - it should show students with no attempts only, not both students with and without attempts. I am considering this a bug and at the point where I reworked the sql to actually work on large datasets, I have changed it accordingly.

        Also I have changed the behaviour for the site course - it makes no sense to introduce 'show students with no attempts' = we have 45000 users. So I have disabled the checkbox for any quiz belonging to the site course.

        From (penny at catalyst.net.nz) Monday, 14 November 2005, 07:45 AM:

        Gustav, I've attached a patch - it would be good to talk to you about this, I'll try and get you on skype.

        From (penny at catalyst.net.nz) Monday, 14 November 2005, 09:30 AM:

        uploading a new version that handles paging better (q2.diff)

        From (penny at catalyst.net.nz) Wednesday, 16 November 2005, 06:25 AM:

        closing - after discussion with gustav I have committed to head & stable.

        From Jean_Michel Vedrine (jean-michel.vedrine at wanadoo.fr) Saturday, 19 November 2005, 02:06 AM:

        There is a problem with this change as applied to the stable branch (not tested for head). All results are dissapearing from the overview report after this fix. Reverting to the prior code solve the problem. Other Moodle users seems to have the same problem (see MDL-4345)

        No problem with item analysis report, results are fine.

        I will try to study what is happening.

        From (penny at catalyst.net.nz) Saturday, 19 November 2005, 05:05 AM:

        I've assigned the other one to me (4345) and committed a fix, so closing this one again. Any further discussion should happen in 4345.

        Show
        Martin Dougiamas added a comment - From (penny at catalyst.net.nz) Wednesday, 9 November 2005, 12:09 PM: attached example huge query. From (penny at catalyst.net.nz) Friday, 11 November 2005, 08:52 AM: I've been working on a patch for this which I shall post after it's been thoroughly tested on a large dataset, however I believe that the current behaviour of 'show students with no attempts' is flawed - it should show students with no attempts only, not both students with and without attempts. I am considering this a bug and at the point where I reworked the sql to actually work on large datasets, I have changed it accordingly. Also I have changed the behaviour for the site course - it makes no sense to introduce 'show students with no attempts' = we have 45000 users. So I have disabled the checkbox for any quiz belonging to the site course. From (penny at catalyst.net.nz) Monday, 14 November 2005, 07:45 AM: Gustav, I've attached a patch - it would be good to talk to you about this, I'll try and get you on skype. From (penny at catalyst.net.nz) Monday, 14 November 2005, 09:30 AM: uploading a new version that handles paging better (q2.diff) From (penny at catalyst.net.nz) Wednesday, 16 November 2005, 06:25 AM: closing - after discussion with gustav I have committed to head & stable. From Jean_Michel Vedrine (jean-michel.vedrine at wanadoo.fr) Saturday, 19 November 2005, 02:06 AM: There is a problem with this change as applied to the stable branch (not tested for head). All results are dissapearing from the overview report after this fix. Reverting to the prior code solve the problem. Other Moodle users seems to have the same problem (see MDL-4345 ) No problem with item analysis report, results are fine. I will try to study what is happening. From (penny at catalyst.net.nz) Saturday, 19 November 2005, 05:05 AM: I've assigned the other one to me (4345) and committed a fix, so closing this one again. Any further discussion should happen in 4345.
        Hide
        Michael Blake added a comment -

        assign to a valid user

        Show
        Michael Blake added a comment - assign to a valid user

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: