Many Small Queries Are Efficient In SQLite
1. Executive Summary
200 SQL statements per webpage is excessive for client/server database engines like MySQL, PostgreSQL, or SQL Server.
But with SQLite, 200 or more SQL statement per webpage is not a problem.
SQLite can also do large and complex queries efficiently, just like client/server databases. But SQLite can do many smaller queries efficiently too. Application developers can use whichever technique works best for the task at hand.
2. The Perceived Problem
The Appropriate Uses For SQLite page says that dynamic pages on the SQLite website typically do about 200 SQL statements each. This has provoked criticism from readers. Examples:
"200 SQL statements is a ridiculously high number for a single page"
"For most sites, 200 queries is way, way, way too much."
"[This is] bad design"
Such criticism would be well-founded for a traditional client/server database engine, such as MySQL, PostgreSQL, or SQL Server. In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag. This is sometimes called the "N+1 Query Problem" or the "N+1 Select Problem" and it is an anti-pattern.
3. N+1 Queries Are Not A Problem With SQLite
SQLite is not client/server, however. The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.
4. The Need For Over 200 SQL Statements Per Webpage
The dynamic webpages on the SQLite website are mostly generated by the Fossil version control system. A typical dynamic page would be a timeline such as https://sqlite.org/src/timeline. A log of all SQL used by the timeline is shown below.
The first group of queries in the log are extracting display options from the "config" and "global_config" tables of the Fossil database. Then there is a single complex query that extracts a list of all elements to be displayed on the timeline. This "timeline" query demonstrates that SQLite can easily process complex relational database queries involving multiple tables, subqueries, and complex WHERE clause constraints, and it can make effective use of indexes to solve the queries with minimal disk I/O.