Once I was reviewing a code and I found that at multiple places the joins were avoided in favor of multiple selects ⚡ Upon asking, I got the response - joins are expensive and we should avoid them 🤦♂️
This is not true at all. Let me debunk the myth and give you a way to understand the costs involved.
Joins are expensive, but only when the data we are joining is huge, or your table lacks the right indexes, or when we are joining a large number of tables (>12). A join query can always be substituted with multiple select statements, but you may end up with an n+1 query problem.
Select statements are great but remember every select statement you fire, needs to go to the database over the network and come back to the client. Each query requires connection establishment, query parsing, query execution, disk access, and data transfer.
Even if the statement looks simple, the overhead of network roundtrip and query execution over multiple retrievals is substantial; and in most transactional cases will be greater than the cost of a join query.
A single join query completes the same task in a single shot. Thus one round trip, one-time query parsing, query optimization, and execution and you get the intended result.
Coming back to the incident, we replaced multiple select with a join and the response time of API was reduced from 1.2 seconds to 152ms. Pretty amazing.
Again, this is contextual to the data we had and the use case we were solving. So, instead of blindly following some tips and advice you hear on the internet, it is better to benchmark it on your own codebase and pick the better one.
Beware when someone says such generalized statements; take them with a pinch of salt. Knowing the nuances makes you a better engineer.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
youtube.com/c/ArpitBhayani