7 ms·

>You know what's really slow? Creating joins in code. Ahh, you have combined two separate points into a new point that I didn't make and took it out of context

>You know what's really slow? Creating joins in code. Ahh, you have combined two separate points into a new point that I didn't make and took it out of context. As I mentioned, I was writing queries for analytics software. This software needed fresh data once per day. There was no requirement for speed. I was referring to speed of development, not speed of the query. As for the other point, yes joins are slow, and yes, multiple queries can scale better, even if they are not the fastest when you time them in an isolated one-off scenario. Multiple queries are simpler in code and scale better. Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database. Then there is the argument that "well you won't be operating at that scale". Then in that case, you don't have to worry about the minute differences between a single network call and multiple calls. There are so many approaches and realities that make your argument just a theoretical argument rather than a practical one. There is caching, denormalizing, and then there is the fact that people use a vast array of languages from slow as hell like python and upwards. As for joins in code in particular, they are slower than in a database that is often written in c++ sure. It depends on the specific situation as to how fast things need to be. Did you know that many ORMs join in code? This is because of all the redundant data that the join creates. So you have to factor in whether the larger amount of data is slower to send than smaller data in multiple network calls. From django: However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one. prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python.

Amezarak 8y ago
> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database. I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cross joins with no filters. I have never seen an instance where doing a join was faster in code and I don't see how it could be except perhaps in some unusual edge cases. I'm sure things change at Google or Facebook scale, but almost nobody is Google or Facebook scale. I've never understood the "joins are slow" meme or where it came from. I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. Once you have that, the only thing slowing you down is your typing speed, and I don't think a more compact syntax would really make a difference.
- 189523954 8y ago
  >I've never understood the "joins are slow" meme or where it came from. Well SQL databases generally don't support joining across a sharded database, which is usually necessary to scale unless you try to scale vertically with high powered machines and your data fits into memory and so on. They are also obviously slow compared to denormalizing and querying without a join. Then there is the other fact I mentioned that they contain redundant data so if the query you need to pull into code is large enough, it is a lot of data that has to get sent over the network. >I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures. I am talking about queries that require joins, self-joins, sub-queries, multiple types of joins, group bys layered on top of each other and so on. They are horseshit and terrible compared to nice programming languages and maybe using a couple of queries instead of one. >In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. This takes a while, but so does writing the query. My non-programming coworker, while good with SQL, spent entire days trying to write the query to a query that he already knew in concept (as in, he knew what he wanted). So I don't agree with your point that understanding what you need is going to take so much time.
  - Amezarak 8y ago
    > Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures. I guess this is where we differ. I've written many SQL queries many hundreds of lines long taking advantage of all kinds of SQL features. I don't see how I could make them "nicer" by writing them in Javascript: SQL has plenty of warts, but well-formatted and organized SQL is hard to beat for expressing exactly what you want without all the cruft associated with how you;re getting it. Once you know what you want, it comes out pretty quick (IME), only your typing speed is the limit. I find you have to think much more carefully about what you're doing in other languages because you have to think more about how to do it without the db abstracting all of that away. IME loops in SQL are a huge code smell - everything should almost always be done using set logic to be clean and performant.

Amezarak 8y ago

> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database. I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cross joins with no filters. I have never seen an instance where doing a join was faster in code and I don't see how it could be except perhaps in some unusual edge cases. I'm sure things change at Google or Facebook scale, but almost nobody is Google or Facebook scale. I've never understood the "joins are slow" meme or where it came from. I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. Once you have that, the only thing slowing you down is your typing speed, and I don't think a more compact syntax would really make a difference.

189523954 8y ago
>I've never understood the "joins are slow" meme or where it came from. Well SQL databases generally don't support joining across a sharded database, which is usually necessary to scale unless you try to scale vertically with high powered machines and your data fits into memory and so on. They are also obviously slow compared to denormalizing and querying without a join. Then there is the other fact I mentioned that they contain redundant data so if the query you need to pull into code is large enough, it is a lot of data that has to get sent over the network. >I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures. I am talking about queries that require joins, self-joins, sub-queries, multiple types of joins, group bys layered on top of each other and so on. They are horseshit and terrible compared to nice programming languages and maybe using a couple of queries instead of one. >In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. This takes a while, but so does writing the query. My non-programming coworker, while good with SQL, spent entire days trying to write the query to a query that he already knew in concept (as in, he knew what he wanted). So I don't agree with your point that understanding what you need is going to take so much time.
- Amezarak 8y ago
  > Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures. I guess this is where we differ. I've written many SQL queries many hundreds of lines long taking advantage of all kinds of SQL features. I don't see how I could make them "nicer" by writing them in Javascript: SQL has plenty of warts, but well-formatted and organized SQL is hard to beat for expressing exactly what you want without all the cruft associated with how you;re getting it. Once you know what you want, it comes out pretty quick (IME), only your typing speed is the limit. I find you have to think much more carefully about what you're doing in other languages because you have to think more about how to do it without the db abstracting all of that away. IME loops in SQL are a huge code smell - everything should almost always be done using set logic to be clean and performant.