Phalcon bulk insert performance. Models or Raw SQL

Question

Is there a way to do bulk inserts in phalcon php?

I tried inserting 10000 records using \Phalcon\Mvc\Model create and also the same using raw SQL: insert into 'tablename' (a, b, c) values (), (), (), ... ()

I set up this very simple test, and was surprised by the results

using \Phalcon\Mvc\Model took around 36 seconds
using raw sql took 3,6 seconds.

\Phalcon\Mvc\Model:

$transactionManager = new TransactionManager(); 

$transaction = $transactionManager->get();

$entity = new Entity();
$entity->setTransaction($transaction);
for($i = 0; $i < 10000; $i++){
    $entity->setValue1(rand(1,50));
    $entity->setValue2(rand(1,50));
    $entity->setValue3(rand(1,50));
}
$entity->create();
$transaction->commit();

Raw SQL:

$query = "insert into 'tablename' (a, b, c) values ";

for($i = 0; $i < 10000; $i++){
    $values .= "(" . rand(1,50) . ", " . rand(1, 50). ", " . rand(1, 50). "),";
}

$values = substr($values, 0, strlen($values) - 1);
$query .= $values;
$this->db->query($query);

mosid mosid · Accepted Answer · 2014-05-12T11:34:55

5

votes

A Phalcon user created a library for this issue, check it out here .

Phalcon bulk insert performance. Models or Raw SQL

2 Answers