Improving pagination performance about laravel-oci8 HOT 6 OPEN

This is working in production fairly well.

    protected function compileTableExpression($sql, $constraint, $query)
    {
        if ($query->limit == 1 && is_null($query->offset)) {
            return "select * from ({$sql}) where rownum {$constraint}";
        }

        // Apply ROW_NUMBER() for pagination
        $orderBy = $this->compileOrders($query, $query->orders);

        // If no ORDER BY is specified, use ROWID for ordering
        if (empty($orderBy)) {
            $orderBy = "order by ROWID";
        }

        return "select * from (
                    select t1.*, row_number() over ({$orderBy}) as row_num
                    from ({$sql}) t1
                ) where row_num {$constraint}";
    }

Example query generated, down from 20s to 650ms:

select * from (
    select t1.*, row_number() over (order by ROWID) as row_num
    from (select * from "INV"."ITM" where "ITM_CD" is not null and "DROP_DT" is null) t1
) where row_num between 126 and 150

Implemented in AppServiceProvider like so:

class_alias (App\Database\Query\Grammars\OracleGrammar::class, \Yajra\Oci8\Query\Grammars\OracleGrammar::class);

from laravel-oci8.

Welcome! We literally just override this function.

It would be worth testing this out though before a PR, as although it works for us, it may encounter other scenarios where it doesn't (i don't know the full scope of the package).

PR submitted: #878

from laravel-oci8.

Just FYI, I tried implementing @alexc-jeromes override in our app, and it was great for a slow query issue we were having, but unfortunately we got errors in other queries, specifically ones with subqueries. For instance, we got ORA-01445 in a few places.

I'm doing a lot of research, and I'm wondering if modifying compileRowConstraint() might be better, using OFFSET and FETCH? I'm not an Oracle expert, by any means...

from laravel-oci8.

@alexc-jeromes thank you for taking your time in reviewing this issue. Can you please submit a PR, I will review it asap.

from laravel-oci8.

@mixaster Yeh i suspected that might be the case. We're using it with quite simple queries and the solution is only needed for record browsing without complexity, so it will need a bit of tweaking. I can see the PR failed too. I'm not heavy enough into OCI8 to know the best way to do Oracle-specific driver work. We're moving to PG thankfully.

from laravel-oci8.

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

from laravel-oci8.