That would require that your evaluation stays correct when the stored data scaled by X%, which isn’t necessarily the case. There are tradeoffs involved in how much ahead in time you plan and build for.
I do agree with this one, though mostly due to the context of ecto, where a CTE or a subquery or a lateral join can look almost the same if you compose the nested queries separately.
I don’t think you’re getting why people try to keep things easy. It’s not about being able to grasp the alternative or not. It’s much more about the fact that in software we usually work in a context of uncertainty.
Yes you can spend an hour today trying to optimize it for an imagined future or you can put the simple solution in use, get more information and spend that hour at a later time when you’re more certain about the context the optimization is needed for. Which path is more useful depends on how certain you are/can be about future requirements and not technical ability.
My reply was a bit glib – I believe neither of us was actually “correct,” because what is correct is rather complicated and dependent on circumstances.
There are no true absolutes here: You shouldn’t ignore performance, nor should you ignore readability. But you should acknowledge that each has trade-offs and focusing on one over the other may make sense at times.
I disagree. Readability is far more important than performance.Code is usually read more often then it is written/changed. And while I could quote Robert C. Martin for this:
While I don’t agree we spend 10x more time reading code than writing it (like Robert C. Martins suggests), the data I found clearly suggests we spend more time doing maintenance tasks than writing new code.This aligns with my personal experience. I should mention this study focuses on companies, projects with certain qualities (such as being open source, big companies VS small companies, etc), so if your use case is not covered, that’s fine. The survey does somewhat cover my use case.
At the end of the day, if I write code my team cannot easily understand, I am the one in trouble. At the end of the day, unless the performance difference from both solutions is drastically different, I will always opt for code that I find more readable and more dumb. You are in your right to have the opinion that Lateral Joins are easier to read/understand. Just be mindful of that - its your personal opinion.
I agree.
I now see this post has grown incredibly out of proportion. My intent was never to fight over which is best/more performant, was only to get something to work. I can then later re-evaluate my choice, and if needed, change things.
Future readers will clearly see my selected answers, and all the alternatives. I find no purpose on continuing this debate here. A healthy amount of arguments were brought forward, and I leave to future readers to make up their mind.
I argued pointing to specific items that you didn’t touch upon at all. The first person to try to refute it even managed to say “Maybe lateral join is easier at first for most people” which is sort of my point (and your point, it seems), I want my code easy to understand both for beginners and more seasoned SQL devs.
I don’t think anyone can objectively claim that X or Y is better than the other (especially since plotting the two argued solutions into an explain analyze should yield fairly similar plans even) so I don’t really see the point of saying that something is a personal opinion when in fact the entirety of what is being discussed is the personal opinion of what is easier for someone to mentally parse and read. As experienced senior devs I would say that is probably some of the best contributions we can give to anyone reading historical posts on this forum!
I personally found this entire post to turn into a good informed debate of the various styles of doing correlated subqueries, whether those are done through lateral joins or with a partition over() query
