Bulk insert performance, models or raw sql

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);

Thanks in advanced

edited Oct '14

You didn't create a transaction for the raw queries. I doubt that affected the end result too much, but it is an omission.

Obviously raw sql is going to be faster - that's the price you pay for abstraction. You could possibly speed up the model saving by defining the schema in-model. Otherwise, Phalcon has to do a bunch of queries to find out the structure of the db table. I'm not sure what caching happens with that (it might be once per model), but that will certainly speed up the model time.

Also, your code highlighting should have the backticks right beside each other, not separated with a space.

There's something wrong with the phalcon code, isn't the $entity supposed to be created inside the loop? You're just setting different values on the same model...

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

$transaction->commit();


2.0k

@maxgalbu

Yes, my bad there, I fixed it after uploading the post comment and forgot to add it here. BTW I'm using a reset (all properties = null) method I created since is faster than creating the entity each time.

I'm now testing using Annotations as Metadata Strategy, but it takes 9 seconds to insert 10K records while my raw insert now takes 0,130 secs.



268
edited Oct '14

@lloiacono: you're not comparing the same thing here. On one side you run multiple inserts, on the other you run only one INSERT statement. You may try to compare it to multiple INSERTs (of course that would be slower). Then you can argue about the speed :-)

Note also that:

$values = substr($values, 0, strlen($values) - 1);
// simpler and faster!
$values = substr($values, 0, -1);