3 ms·

> not bothering with optimization (≈200 requests per page) What amount of SQL queries per page render is considered sensible? When I run more than 20 queries

> not bothering with optimization (≈200 requests per page) What amount of SQL queries per page render is considered sensible? When I run more than 20 queries per request in my Rails apps (smallish internal tools for different companies) I get uneasy. I usually deploy the app on the same machine where the DB (not SQLite) runs, but I imagine if that weren't the case the app-DB roundtrips could soon dominate the whole thing.

vbsteven 6y ago
When using an external db every query needs to cross the connection boundary and thus perform network IO and deal with the threading implications for that IO. With SQLite running in-process a round trip just means a function call, and when data is available in the cache it won’t even hit the disk.
deckard1 6y ago
> app-DB roundtrips could soon dominate the whole thing and they do. We have a GraphQL API backed by multiple microservices at a place I work. You become increasingly paranoid at every call you need to make. It's also where ORMs completely fall over. ORMs are designed for, generally, one record to one query mapping. Which is why they are always leaky abstractions which need heroic acts of clever hacking to overcome. You know, rather than doing the sensible thing and just writing a single query that fetches data from multiple tables. SQLite sort of encourages many queries, though, due to their design. I recall their docs even mention this point. But I've seen a few people in various threads that get bitten by this when they switch to a client/server DB. I think it's perfectly fine to design your app around the characteristics of your database. Otherwise, you're missing out on optimizations and features you could be using and what would even be the point in favoring one DB over another if it's all the same to your app? You should pick the DB that matches the characteristics of your app anyway.
renewiltord 6y ago
The sqlite position on this is articulated here https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html > N+1 Queries Are Not A Problem With SQLite > > 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.

vbsteven 6y ago

When using an external db every query needs to cross the connection boundary and thus perform network IO and deal with the threading implications for that IO. With SQLite running in-process a round trip just means a function call, and when data is available in the cache it won’t even hit the disk.

deckard1 6y ago

> app-DB roundtrips could soon dominate the whole thing and they do. We have a GraphQL API backed by multiple microservices at a place I work. You become increasingly paranoid at every call you need to make. It's also where ORMs completely fall over. ORMs are designed for, generally, one record to one query mapping. Which is why they are always leaky abstractions which need heroic acts of clever hacking to overcome. You know, rather than doing the sensible thing and just writing a single query that fetches data from multiple tables. SQLite sort of encourages many queries, though, due to their design. I recall their docs even mention this point. But I've seen a few people in various threads that get bitten by this when they switch to a client/server DB. I think it's perfectly fine to design your app around the characteristics of your database. Otherwise, you're missing out on optimizations and features you could be using and what would even be the point in favoring one DB over another if it's all the same to your app? You should pick the DB that matches the characteristics of your app anyway.

renewiltord 6y ago

The sqlite position on this is articulated here https://sqlite.org/np1queryprob.html https://sqlite.org/np1queryprob.html > N+1 Queries Are Not A Problem With SQLite > > 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.