[Question] Execution time problem

[KKSzymanowski]

Hi.

I know this is probably not your package's issue, but I was hoping for some explanation, maybe solution.

Problem

I have a simple query:

Person::where('PATIENT_ID', 'LIKE', '770915%')->paginate(10);

It essentially executes two queries:

SELECT count(*) as aggregate FROM "PERSON" WHERE "PATIENT_ID" LIKE '770915%'

and

SELECT first 10 skip 0 * FROM "PERSON" WHERE "PATIENT_ID" LIKE '770915%'

Everything works perfectly, but the problem is execution time. When I run these queries in IBExpert I get Execution time = 16ms(sometimes 32ms, sometimes 0). However if ran with Laravel, Clockwork shows respectively 600ms and 350ms.

I dug a bit in the Laravel Eloquent core and found, that the eg. first query looks like this:

SELECT count(*) as aggregate FROM "PERSON" WHERE "PATIENT_ID" LIKE ?

Then, Laravel binds parameters to the prepared statement and after that fetches all rows.

I thought I'd fiddle with raw PHP PDO to replicate this and I found out that adding single quotes around the question mark speeds up the query from 600ms to 30ms. Similar thing happens in IBExpert. If I type:

SELECT count(*) as aggregate FROM "PERSON" WHERE "PATIENT_ID" LIKE :patientID

and execute, there is a popup window for parameter input and after filling out the form the query executes for 600ms. If I however skip the parameter and pass the value explicitly into the query it goes back to 16ms.

Possible solution

When Laravel binds where clause parameters, it goes though Illuminate\Database\Grammar::parameter() method. It looks like this:

public function parameter($value)
{
    return $this->isExpression($value) ? $this->getValue($value) : '?';
}

If I override that in FirebirdGrammar with following code:

public function parameter($value)
{
    if($this->isExpression($value)) {
        return $this->getValue($value);
    }

    if(is_string($value)) {
        return "'?'";
    }

    return "?";
}

I get a huge improvement in both queries. Clockwork now shows 90ms and 20ms respectively. It's slower than when ran with IBExpert probably because of the VPN connection and PDO overhead.

Conclusion

Can you check if

1. This issue also exists in your environment?
2. This change doesn't break any of your existing code?

Do you have any knowledge of Firebird or PHP's Firebird PDO that can explain this behaviour?

I'm using Laravel 5.3 with laravel-firebird@5.3-support and Firebird 2.5.3