0

I have the following process in my script. The method this is used in is rather lengthy but the majority of the time is spent right here. Im looking for a way to possibly increase its performance but not exactly sure how to accomplish that. Currently if there are ~700 items in $list the process can take upwards of 15 seconds. I would like to reduce that as much as possible. How might I begin going about that (within the scope of whats seen below)?

    try{
        $this->connect();
        $this->mysqli->begin_transaction();
        
        $sql = "TRUNCATE TABLE test_table";
        $this->query($sql);
        
        $sql = "INSERT INTO test_table (a, b, c, d) VALUES (?,?,?,?)";
        foreach ($list as $item){
            $this->run($sql, [$item['a'],$item['b'],$item['c'],$item['d']]);
        }

        $this->mysqli->commit();
    } catch (exception $e){
        $this->mysqli->rollback();
        return $e;
    } finally {
        $this->close();
    }

One thought I had to somewhat reduce it was to make $item a non-associative array so that it would just be ...->run($sql, $item); but I imagine the performance increase just from that would be near to negligible.

Edit: As to be expected, the main issue is with the inserts.

Newb 4 You BB
  • 1,205
  • 1
  • 11
  • 30
  • You can try and insert [in batches](https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql) instead of inserting them one by one. You should also reuse the statement (not sure what `->run()` really does). – M. Eriksson Aug 12 '20 at 18:00
  • Have you seen my answer here: https://stackoverflow.com/a/63318861/1839439 – Dharman Aug 12 '20 at 22:00

0 Answers0