Why code code wrapped inside DB::transaction() is executed so much faster than without it?-CodePudding

I have a database seeder that seeds 10k entries into a database; When running the code without DB:transaction, the while loop takes more than 5 minutes to execute against less than one minute if i wrap it inside DB::transaction (more than 5x faster!). Where the performance boost comes from?

Wrapped:

DB::transaction(function () {
    $fp = gzopen(database_path() . '/seeders/cars.json.gz', 'r');
    $i = 0;
    
    while ($json_text = fgets($fp)) {
        $json = json_decode($json_text, true);

        DB::table('cars')->insert([
            'full_name' => $json['full_name'],
            'slug_name' => $json['slug_name'],
            'year' => $json['Ano'],
            'model' => array_key_exists('model', $json) ? $json['model'] : null,
            'name' => $json['name'],
            'brand_id' => Brand::where('name', $json['brand'])->first()->id,
            'content' => $json_text,
            'created_at' => new \Datetime(),
            'updated_at' => new \Datetime()
        ]);
        
        echo "Inserted " .   $i . " cars\n";
    }
    
    fclose($fp);
});

Not wrapped:

$fp = gzopen(database_path() . '/seeders/cars.json.gz', 'r');
$i = 0;
    
while ($json_text = fgets($fp)) {
    $json = json_decode($json_text, true);
    
    DB::table('cars')->insert([
        'full_name' => $json['full_name'],
        'slug_name' => $json['slug_name'],
        'year' => $json['Ano'],
        'model' => array_key_exists('model', $json) ? $json['model'] : null,
        'name' => $json['name'],
        'brand_id' => Brand::where('name', $json['brand'])->first()->id,
        'content' => $json_text,
        'created_at' => new \Datetime(),
        'updated_at' => new \Datetime()
    ]);

    echo "Inserted " .   $i . " cars\n";
}
    
fclose($fp);

CodePudding user response：

using SQL without transactions in the code, will actually be a pseudo transaction per action. The flow could be described as below.

Transaction begin
Insert car 1
Transaction end
Transaction begin
Insert car 2
Transaction end

If you Wrap it in a transaction it will be more like this.

Transaction begin
Insert car 1
Insert car 2
Transaction end

This is commonly described in other articles, meaning you only have to write 1 time to the database, being more of a sequential write and index, foreign keys etc does not have to be checked each insert. Unless it is a huge problem, i wouldn't worry about it and focus on readable code, instead of clever optimizations.

CodePudding user response：

I think its in part due to the fact that the indexes are not written to disk until the transaction is committed. This could be significant if you have several indexes on the table.