Eager loading vs. many queries with PHP, SQLite

Question

I have an application that has an n+1 query problem, but when I implemented a way to load the data eagerly, I found absolutely no performance gain. I do use an identity map, so objects are only created once.

Here's a benchmark of ~3000 objects.

first query + first object creation: 0.00636100769043 sec.
memory usage: 190008 bytes

iterate through all objects (queries + objects creation): 1.98003697395 sec.
memory usage: 7717116 bytes

And here's one when I use eager loading.

query: 0.0881109237671 sec.
memory usage: 6948004 bytes

object creation: 1.91053009033 sec.
memory usage: 12650368 bytes

iterate through all objects: 1.96605396271 sec.
memory usage: 12686836 bytes

So my questions are

1. Is SQLite just magically lightning fast when it comes to small queries? (I'm used to working with MySQL.)
2. Does this just seem wrong to anyone? Shouldn't eager loading have given much better performance?

Answer 1

Part of your "problem" is SQLITE is lightning fast on small queries!

The other symptom of the "problem" is because SQLITE is embedded code accessing a single local file there is none of the normal overhead of marshalling parameters, sending over the network, opening table space files, marshalling replies etc. that you get with a heavy database.