Comment by wenc
14 days ago
For analytic queries, yes, a single SQL query often beats many small ones. The query optimizer is allowed to see more opportunities to optimize and avoid unnecessary work.
Most SQLite queries however, are not analytic queries. They're more like record retrievals.
So hitting a SQLite table with 200 "queries" is similar hitting a webserver with 200 "GET" commands.
In terms of ergonomics, SQLite feels more like a application file-format with a SQL interface. (though it is an embedded relational database)
> The query optimizer is allowed to see more opportunities to optimize and avoid unnecessary work.
Let's also not forget that db servers can have a memory, in that they can tweak query optimization based on previous queries or scans or whatever state is relevant. SQLite has no memory, in that sense. All query optimizations it makes are based solely upon the single query being processed.