1

I'm using AgileToolkit 4.2.4 and experiencing some performance problems when using Model_Table for inserting a large amount of items into a MySQL 5.5.31 database with InnoDB engine. After some testing I found out that the model becomes slow when using references, for instance with hasOne().

Performance tests:

<?php
$t = $this->add('Model_Test');

for ($i = 0; $i < 500; $i++)
{
    $t->unload();

    $t->set('field1', 1);
    $t->set('field2', 1);

    $t->save();
}
?>

Using model 1 (2.7 sec for 500 inserts):

<?php
class Model_Test extends Model_Table
{
    public $table = 'test';

    function init()
    {
        parent::init();

        $this->addField('field1')->length(45);
        $this->addField('field2')->length(45);
    }
}
?>

Using model 2 (21.3 sec for 500 inserts):

<?php
class Model_Test extends Model_Table
{
    public $table = 'test';

    function init()
    {
        parent::init();

        $this->hasOne('Test', 'field1');
        $this->hasOne('Test', 'field2');
    }
}
?>

For this example in model 2 I refer to instances of the same model Test, but it also occurs when I refer to some other model.

Is there anything I can do to overcome this problem? Furthermore, is this the right way to insert items using a model; adding it only once and do an unload() inside the loop?

Dave Hillier
  • 18,105
  • 9
  • 43
  • 87
Neman
  • 1,237
  • 2
  • 13
  • 16

1 Answers1

0

try this

$t = $this->add('Model_Test');

for ($i = 0; $i < 500; $i++) {
    $t->set('field1', 1);
    $t->set('field2', 1);
    $t->saveAndUnload();     //  <--------
}

Method save() saves data and load them from database again to populate your model (this is how id autoincrement field appears in model). On other hand saveAndUnload() do not select any data from database after saving. If you add ->debug() to your model you will see two requests when you use save() - insert and select.

Vadym
  • 749
  • 3
  • 15