Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-34055

Database function for fast bulk inserts

    XMLWordPrintable

    Details

    • Story Points:
      8
    • Sprint:
      BACKEND Sprint 9

      Description

      When inserting a very large number of records, insert_record() can be quite slow (a few hundred ms per insert).

      I've done some testing and found that it's possible to get ~100x performance boost by bundling multiple inserts into one query using the syntax:

      INSERT INTO table (field1, field2, field3) VALUES (a1, a2, a3),(b1, b2, b3),(c1, c2, c3),...

      I've written a generic function to do bulk inserts:

      Insert multiple records into a table using batch insert syntax for speed}}

      @param string $table The database table to be inserted into
      @param iterator $iterator An iterable object (such as moodle_recordset or array)
      containing objects to be inserted into $table
      @return true
      @throws dml_exception if error

      public function insert_records_via_batch($table, $iterator) {}

      The function builds the query by adding records from the iterator until it reaches either the maximum query length or maximum record count (both predefined constants), then it does an insert and starts building a fresh query.

      By accepting an iterator you can pass in a whole array, or a recordset (to avoid storing all the data to be inserted in memory).

      Although it works in simple cases I'm finding that there are quite a few subtleties in the depths of dmllib and to make it work with all field types and databases I think some core dml changes might be needed.

      I'm wondering if there would be any interest in getting this into core?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              skodak Petr Skoda
              Reporter:
              simoncoggins Simon Coggins
              Peer reviewer:
              Ankit Agarwal
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Jérôme Mouneyrac
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              2 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                12/May/14