Spanner pretend() is 100x slower than the mysql driver pretend()

[matthewjumpsoffbuildings]

As the title suggests, for some reason when I run the same exact code within a DB::pretend() with this driver vs the standard mysql driver, I am seeing around 100x slower speed to run it and get back the result of the queries that would be run.

A pretend() callback that takes around 2 seconds with a mysql driver connected to a mysql instance running on google cloud, takes over 210 seconds when run with the spanner driver connected to a spanner instance.

I am not sure why pretend() would be taking so incredibly long? Shouldn't every query being done within the callback not require touching the database connection, rather simply be calculated in memory/locally via the Grammar/Builder etc?

[taka-oyama]

Shouldn't every query being done within the callback not require touching the database connection

AFAIK https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Connection.php#L775 indicates that it does connect to the database. Even though it doesn't send any queries. Also, creating a session takes ~1 second per session so if you're creating a connection each time, that might cause problems.

I suggest you check your system insights / query insights to see if there are any calls being made.

[matthewjumpsoffbuildings]

I found the solution, its really simple.

In affectingStatement(), here https://github.com/colopl/laravel-spanner/blob/6f2448363736768edfe547294c4589adf86025e6/src/Connection.php#L334 - if its in a transaction we just return $runQueryCall(); - however if its not it creates a single use transaction.

None of this needs to happen when pretending(), since inside the body of $runQueryCall, the first line simply returns 0 if its pretending, here https://github.com/colopl/laravel-spanner/blob/6f2448363736768edfe547294c4589adf86025e6/src/Connection.php#L317

The solution was to simply add an additional check at the end of affectingStatement() like so:

// Add this to make pretending lightning fast
if($this->pretending()) {
    return $runQueryCall();
}

if ($this->inTransaction()) {
    return $runQueryCall();
}

// Create a temporary transaction for single affecting statement
return $this->transaction($runQueryCall);

[matthewjumpsoffbuildings]

Obviously what was happening is a lot of the queries that were being run inside my pretending block were single use transactions, so it was creating a transaction every time, which obviously has a bunch of unnecessary checks for connection/session etc.

Do I need to create a pull request to add 3 lines to affectingStatement() or are you okay to sort it?

[matthewjumpsoffbuildings]

Created PR #191