Improve sqlite performance in yii

Question

I saw some discussions similar obviously but couldn't found a solution(if there is one). I have a project running on linux machine.
The problem is that working with the Database takes forever. For example 1000 inserts takes approximately 10 seconds.
I tried reducing the time in a different ways with little success so I thought I just place here a part of my code and maybe there is something critical I'm not doing right.
First of all, in main.php the database is configured like this:

'db'=>array(
        'pdoClass' => 'NestedPDO',
        'connectionString' => 'sqlite:/tmp/mydb.db',
        'class'            => 'CDbConnection',
        'schemaCachingDuration' => 100)

I work with the database in the following way:

    $connection=Yii::app()->db; 
    $transaction = $connection->beginTransaction();

    try
    {
        Some Code..
        $transaction->commit(); 
    }
    catch (Exception $ex)
    {
        $transaction->rollback();
    }

Inside Some Code there could be calls to different functions that the connection variable is passed to.

Finally each sqlite command (for example, 1000 inserts) is written like:

$statement= 'insert into my_tbl (id, name) VALUES(:id, :name)';
$command=$connection->createCommand($statement);
$command->bindParam(":id", $id);
$command->bindParam(":name", $name); 

$command->execute();

[This answer](http://stackoverflow.com/a/1712873/2382734) maybe could be useful. — Renzo, Jun 22 '16 at 07:25

score 0 · Answer 1 · answered Jun 22 '16 at 09:59

0

First make sure where the bottleneck is.

try
{
    $t_start = time();
    $transaction->commit();
    $elapsed = time() - $t_start;
}

I have add somewhat good success by committing more often.

answered Jun 22 '16 at 09:59

thomas

325
3
11

Improve sqlite performance in yii

1 Answers1