Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.5
    • Fix Version/s: 2.5
    • Component/s: Tool Multiple Choice
    • Labels:
      None

      Description

      Implement autosave feature for MCQ tool.
      As a template QA can be taken.

        Activity

        Hide
        dronych Andrey Balan added a comment -
        done
        Show
        dronych Andrey Balan added a comment - done
        Hide
        ernieg Ernie Ghiglione added a comment -
        Tested and closed. Thanks Andrey
        Show
        ernieg Ernie Ghiglione added a comment - Tested and closed. Thanks Andrey
        Hide
        marcin Marcin Cieslak added a comment -
        When working a large database, the first query from patch20130812.sql
        DELETE FROM tl_lamc11_usr_attempt...
        fails saying "Lock wait timeout exceeded; try restarting transaction"
        There is probably a deadlock caused by using the same table in outer and inner part of the query.
        I can provide such large DB for tests.
        Show
        marcin Marcin Cieslak added a comment - When working a large database, the first query from patch20130812.sql DELETE FROM tl_lamc11_usr_attempt... fails saying "Lock wait timeout exceeded; try restarting transaction" There is probably a deadlock caused by using the same table in outer and inner part of the query. I can provide such large DB for tests.
        Hide
        dronych Andrey Balan added a comment -
        Applied Marcin's idea of breaking one SQL command into several ones along with creating a temporary table.
        And then adding index to that table seems to improve performance quite dramatically.

        Marcin, can you please confirm the execution time is acceptable now?
        Show
        dronych Andrey Balan added a comment - Applied Marcin's idea of breaking one SQL command into several ones along with creating a temporary table. And then adding index to that table seems to improve performance quite dramatically. Marcin, can you please confirm the execution time is acceptable now?
        Hide
        marcin Marcin Cieslak added a comment -
        Since we started playing with SQL, I run a few tests.
        My tl_lamc11_usr_attempt table has 460k rows with 399k rows to be deleted.

        Using a temporary table without index took 17 seconds to create it and more than 35 minutes (!) to delete rows (eventually I interrupted the query).
        This would probably confuse customers trying to update their large DBs. We would have to warn them of the lengthy process.

        Andrey's idea of adding an index works perfectly.
        It took 17 seconds to create the table, 4 seconds to add index and 8 seconds to delete rows.
        This is certainly acceptable.


        I also gave it a go with a table that has an index from the very start:
        CREATE TABLE temp_select (uid BIGINT(20) PRIMARY KEY) ENGINE=InnoDB;

        INSERT INTO temp_select SELECT attempt.uid
        FROM tl_lamc11_usr_attempt attempt ...

        It gave a meaningless improvement:
        It was created immediately and the insert took 18 seconds, so it is only 3 seconds better than create+add index.

        The current patch works well now.

        The only addition that would probably be wise to make is to add such statement in the very beginning:
        DROP TABLE IF EXISTS temp_select;
        so if previous script run failed (for example, user interrupted it because it took too long), running it again would be easier (no errors saying such table already exists).
        Show
        marcin Marcin Cieslak added a comment - Since we started playing with SQL, I run a few tests. My tl_lamc11_usr_attempt table has 460k rows with 399k rows to be deleted. Using a temporary table without index took 17 seconds to create it and more than 35 minutes (!) to delete rows (eventually I interrupted the query). This would probably confuse customers trying to update their large DBs. We would have to warn them of the lengthy process. Andrey's idea of adding an index works perfectly. It took 17 seconds to create the table, 4 seconds to add index and 8 seconds to delete rows. This is certainly acceptable. I also gave it a go with a table that has an index from the very start: CREATE TABLE temp_select (uid BIGINT(20) PRIMARY KEY) ENGINE=InnoDB; INSERT INTO temp_select SELECT attempt.uid FROM tl_lamc11_usr_attempt attempt ... It gave a meaningless improvement: It was created immediately and the insert took 18 seconds, so it is only 3 seconds better than create+add index. The current patch works well now. The only addition that would probably be wise to make is to add such statement in the very beginning: DROP TABLE IF EXISTS temp_select; so if previous script run failed (for example, user interrupted it because it took too long), running it again would be easier (no errors saying such table already exists).

          People

          • Assignee:
            dronych Andrey Balan
            Reporter:
            dronych Andrey Balan
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development