Ten joins per query is quite nothing. I tend to see the opposite problem, where new devs denormalize a bunch of tables thinking it is going to improve performance when it fact it duplicates data everywhere in tables and indexes and makes things much less cache efficient, the CPU spending much more time stalled (https://en.wikipedia.org/wiki/CPU_cache#CPU_stalls) moving different copies of the same data in and out of ram and caches (or god forbid disk).
JOINS are a performance _enhancement_ in an efficient, indexed schema, especially with narrow tables when the joined pages are small enough to stick in caches longer.
This is why columnar stores like elasticsearch effectively only allow single column tables so every column always has to be joined. They also aggressively compress pages of these columns to make them more cache efficient. Its much faster for the cpu to compress/decompress/join on the fly than to move uncompressed denormalized data from different levels of cache.
In SQL world, if you keep your tables narrow and normalized and you get the same benefits plus the benefits of only having the indexes you need (also more cache efficient) rather than index everything and throw hardware at it like elastic. With SQL, you also avoid the issue of sending data over potentially congested and less reliable networks that distributed DBs like elastic have.
> JOINS are a performance _enhancement_ in an efficient, indexed schema, especially with narrow tables when the joined pages are small enough to stick in caches longer.
When used correctly nearly everything is good. But in reality most production applications in the world you'll find them doing joins where it's not performant. You'll find 1 to 1 relationships all over the place.
In many scenarios the application and database would run quicker if you do 11 queries instead of doing 1 query and doing joins to fetch the other data. In many real world scenarios. And you can literally demostrate that to some developers with a pull request that performs better and they'll continue to write their crappy SQL.
Ten joins per query is quite nothing. I tend to see the opposite problem, where new devs denormalize a bunch of tables thinking it is going to improve performance when it fact it duplicates data everywhere in tables and indexes and makes things much less cache efficient, the CPU spending much more time stalled (https://en.wikipedia.org/wiki/CPU_cache#CPU_stalls) moving different copies of the same data in and out of ram and caches (or god forbid disk).
JOINS are a performance _enhancement_ in an efficient, indexed schema, especially with narrow tables when the joined pages are small enough to stick in caches longer.
This is why columnar stores like elasticsearch effectively only allow single column tables so every column always has to be joined. They also aggressively compress pages of these columns to make them more cache efficient. Its much faster for the cpu to compress/decompress/join on the fly than to move uncompressed denormalized data from different levels of cache.
In SQL world, if you keep your tables narrow and normalized and you get the same benefits plus the benefits of only having the indexes you need (also more cache efficient) rather than index everything and throw hardware at it like elastic. With SQL, you also avoid the issue of sending data over potentially congested and less reliable networks that distributed DBs like elastic have.