Bulk insert performance, models or raw sql

Question

Bulk insert performance, models or raw sql

lloiacono Feb '14

Created Feb '14	Last Reply Oct '14	Replies 3	Views 3543	Votes -1

lloiacono 3.5k

Feb '14

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

1

Dylan Anderson · Answer 1 · 2014-02-14T13:19:04-07:00

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.

Max Galbusera · Answer 2 · 2014-02-17T04:17:54-07:00

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

lloiacono · Answer 3 · 2014-02-17T04:26:13-07:00

@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.

Yvan · Answer 4 · 2014-10-03T02:47:42-07:00

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