Low performance with CLOB columns

[Albvadi]

Summary of problem or feature request

When I use a CLOB to save an indeterminate length text, the queries are slow. Seeding is good with a speed of 2,5 seconds to load 1700 rows. But select queries are slow, with a speed of 5 seconds to load only 200 rows.

Code snippet of problem

// Migration
public function up()
{
    Schema::create('test_table', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->text("comments_clob");
        $table->string("comments_varchar2");
        $table->string("comments_varchar2_greater_limit", 600);
        $table->timestamps();
    });
}

// TestFactory

use Illuminate\Database\Eloquent\Factories\Factory;
use Faker\Provider\Lorem;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Test>
 */
class TestFactory extends Factory
{
    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        $this->faker->addProvider(new Lorem($this->faker));

        return [
            'name' => $this->faker->name(),
            'comments_clob' => $this->faker->paragraph(),
            'comments_varchar2' => $this->faker->paragraph(2), // here only 2 paragraphs because 3 exceeds the field limits
            'comments_varchar2_greater_limit' => $this->faker->paragraph(),
        ];
    }
}

//Test route in routes/web.php
Route::get('/db', function () {

    DB::listen(function ($query) {
        dump("{$query->time} ==> {$query->sql}");
    });

    Test::select('name', 'comments_clob')->get();
    Test::select('name', 'comments_varchar2')->get();
    Test::select('name', 'comments_varchar2_greater_limit')->get();
});

The result of the queries varies a lot depending on whether the CLOB field is added or not.

System details

• Operating System: Windows 10 21H2 x64
• PHP Version: PHP 8.0.6
• Laravel Version: 9.0.2
• Laravel-OCI8 Version: 9.0.0

With lower versions of Laravel (8.8.0) and Laravel-OCI8 (8.6.1) the results are the same.

[yajra]

Yes, I think it's because CLOB / BLOB are loaded as an object in Oracle and thus additional processing is required to load the actual value as string. In Oci8\Statement.php, you will find a code something like $lob->load(); which I think is the reason for slower response time.

Open to suggestions / fix if you can find a way to improve it. Thanks!

[ericpp]

You can include smaller CLOBs with query results by wrapping CLOB fields with TO_CHAR() in your query. This only works if the CLOB is small enough to be expressed as a CHAR type.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.