Laravel PDO slows down exponentially; native PDO does not – why?

I’ve found lots of threads, both here and on StackOverflow, of people complaining about Eloquent being slow for larger queries, but everywhere I just see suggestions to check indexes, checking the SQL query for efficiency, using raw statements instead of Eloquent/query builder to avoid creating collections of model instances, etc. – none of which answers the question on its own.

To be perfectly clear: I am aware that building a collection of model instances takes longer than just returning an array. I am aware that chunking or paginating result sets reduces memory usage and CPU time. I am aware that Eloquent inherently has overhead that makes it less efficient than native PDO queries. These are all factors that play in, but they are by no means enough to explain the huge discrepancy between native PDO and Laravel’s PDO.

Setup

A remote VPS server running Windows Server 2019, managed through Plesk
An SQL Server (SQLEXPRESS 2019) database on the remote server
My local dev machine, two Macs running macOS 10.15.7 and 12.2.1

On the dev machines, I have PHP, nginx, etc. set up through Homebrew, and Laravel set up using Valet. I don’t have Laravel deployed to the remote server yet.

The problem

Whenever I retrieve data from the remote database in my local Laravel app, queries are fine and snappy as long as they don’t return too many rows; but as the number of rows returned grows, query times grow exponentially – which they don’t normally, outside of Laravel.

For example, I have a product table with about 2,500 rows and 44 columns (which is way too much, but I didn't design it). To fetch all current products in a sensible order, I do this:

// Eloquent query
$products = Product::where('active', 1)->orderBy('year', 'desc')->orderBy('available_date', 'desc')->orderBy('name', 'asc')->get();

// Raw SQL query
DB::getPdo()->query("SELECT * FROM products WHERE active = 1 ORDER BY year DESC, available_date DESC, name ASC")->fetchAll(\PDO::FETCH_ASSOC);

That query returns just over 2,000 rows.

The columns used in the where and order clauses are all indexed; other columns are not. Non-indexed columns include an nvarchar(3000) column and an ntext column – so yes, indexes and sheer data volume are of course an issue, but not the whole story.

Timing queries from different sources

When I run the query from within Laravel as an Eloquent query, it takes about 20 seconds. And that’s just the query. Debugbar gives almost identical running times for the two versions of the query, and almost identical total page load times as well, so it’s not even generating the 2,000 model objects that makes it slow – associative array vs collection of objects adds less than a second to the total load time.
If I run the query from Azure Data Studio (ADS) on my Mac, the Messages section says the query takes about 0.4 seconds, about 50 times faster than Laravel. Displaying the results in the Results grid takes perhaps a second or so after that.
If I run the query using a plain PDO connection in a ‘plain’ PHP file on the remote server (where there’s no Laravel installed at all), the query takes about 0.2 seconds, so it’s about 100 times faster.

If I only select the indexed columns and leave everything else out, things look rather different:

Laravel (Eloquent or ‘plain’) fetches the rows in about 400ms
ADS does it in about 50-60ms
Plain PDO locally from the remote server does it in about 17ms

Given that at least some of the time for Laravel and ADS is used on just sending and receiving the request from the remote server, the actual differences are even smaller than this. So ADS still takes about twice as long as the local request, but Laravel now ‘only’ takes perhaps 20 times as long, not 100 times.

The same can be observed if I reduce the number of rows, but keep all the unindexed columns. Reducing to 500 / 50 rows (respectively):

Laravel does it in about 500ms / 5 seconds
ADS does it in 50-60ms (same as above) / 200ms
Local request does it in 28ms / about 80ms

Compared to the local requests, the Laravel requests go from about 8 times slower at 50 rows to 18 times slower at 500 rows, and then of course 100 times slower at 2,000 rows.

What can be the cause of this?

It’s understandable if Laravel, being a complete ORM, is slower than plain PHP, but it should not be 100 times slower – and certainly, it should not be exponentially slower for any reason I can fathom. I would expect it to be slower at a reasonably constant rate.

What are some possible pitfalls and reasons why running a query through Laravel can be that much slower, in particular exponentially so?

(The one test really missing is of course running a plain PDO query from my dev machine – but unfortunately I simply cannot get the connection to work. I can connect using Laravel’s DBAL-based drivers, but I can’t get a simple PDO connection up and running from my localhost.)