4 ms·

>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

>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.
- 189523954 8y ago
  Yes well it would be ideal if I had code to show you and compare it to the SQL, but all of that is at my old workplace and I will get PTSD if I ever look at it again. I agree that loops in SQL are not great. Loops in programming languages are fine obviously. There are just many more and nicer constructs in a programming language to manipulate data. Maybe you have a root table that is anchoring your query, say a Staff table. The Staff table has a Manager column, which is another row in the Staff table. You then need to do a bunch of aggregate stuff. So in a programming language, you can maybe query the database 3 times, once for the staff you need, and then again for maybe shifts completed and so on. You can then easily put the staff into a dictionary with virtually no code. Then you loop over the non-dictionary staff array, and you have something like: for (var staff in shittyStaff) { processedStaff.add({ manager: staffDic[staff.manager], shiftsCompleted: shifts[staff.id].length, shiftsWithManager: shifts[staff.id].filter(s => s.coworker == staff.manager.id).length }); Or if it is setup with entity framework/c# stuff, it is just: var staff = db.Staff .Include(s => s.Manager) .Include(s => s.Shifts) .Select(s => new { Manager: s.Manager, ShiftsCompleted: s.Shifts.Count(), ShiftsWithManager: s.Shifts.Where(shift => shift.CoWorker == s.Manager).Count() }); Probably a terrible and not particularly complex example because I made it up, but to do that in SQL requires a lot more stuff, a self-join, group by with count etc, etc.
  - astine 8y ago
    I use both raw SQL and the entity framework on a regular basis. That Linq to entities query gets translated pretty directly to SQL. Each of those includes translates directly to a left join on whatever column is specified as the key. You would need a group by, but no self joins. Assuming that you are familiar with the database schema and are proficient in SQL, it shouldn't be any slower to write the SQL version than the C# version. It depends a little on the exact columns you need, but it would look something like this, which is a supper common form for a SQL query: select m.id Manager, count(sh.id) as ShiftsCompleted, sum(iif(sh.coworker = m.id,1,0) as ShiftsWithManager from staff s left join manager m on m.id = s.manager_id left join shifts sh on sh.id = s.shifts_id group by s.id, m.id The SQL version has the advantage that it's more intuitive to specify the columns that you need so if your query is running slow because you're pulling too much data (something that's happened to me a bunch,) you can omit unneeded columns pretty easily.
nick_kline 8y ago
I think a big point of the article was that these more recent relational dbs (like memsql) figured out how to make distributed joins across multiple shards scale really well - that's one of their core value adds. So you can shard your for example customer and order data stored across multiple nodes and partitions and do distributed joins, aggregations etc. Scaling to use hardware resources on multiple machines is a crucial aspect of these systems. Disclaimer, I work at Memsql, speaking for myself.

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.

189523954 8y ago
Yes well it would be ideal if I had code to show you and compare it to the SQL, but all of that is at my old workplace and I will get PTSD if I ever look at it again. I agree that loops in SQL are not great. Loops in programming languages are fine obviously. There are just many more and nicer constructs in a programming language to manipulate data. Maybe you have a root table that is anchoring your query, say a Staff table. The Staff table has a Manager column, which is another row in the Staff table. You then need to do a bunch of aggregate stuff. So in a programming language, you can maybe query the database 3 times, once for the staff you need, and then again for maybe shifts completed and so on. You can then easily put the staff into a dictionary with virtually no code. Then you loop over the non-dictionary staff array, and you have something like: for (var staff in shittyStaff) { processedStaff.add({ manager: staffDic[staff.manager], shiftsCompleted: shifts[staff.id].length, shiftsWithManager: shifts[staff.id].filter(s => s.coworker == staff.manager.id).length }); Or if it is setup with entity framework/c# stuff, it is just: var staff = db.Staff .Include(s => s.Manager) .Include(s => s.Shifts) .Select(s => new { Manager: s.Manager, ShiftsCompleted: s.Shifts.Count(), ShiftsWithManager: s.Shifts.Where(shift => shift.CoWorker == s.Manager).Count() }); Probably a terrible and not particularly complex example because I made it up, but to do that in SQL requires a lot more stuff, a self-join, group by with count etc, etc.
- astine 8y ago
  I use both raw SQL and the entity framework on a regular basis. That Linq to entities query gets translated pretty directly to SQL. Each of those includes translates directly to a left join on whatever column is specified as the key. You would need a group by, but no self joins. Assuming that you are familiar with the database schema and are proficient in SQL, it shouldn't be any slower to write the SQL version than the C# version. It depends a little on the exact columns you need, but it would look something like this, which is a supper common form for a SQL query: select m.id Manager, count(sh.id) as ShiftsCompleted, sum(iif(sh.coworker = m.id,1,0) as ShiftsWithManager from staff s left join manager m on m.id = s.manager_id left join shifts sh on sh.id = s.shifts_id group by s.id, m.id The SQL version has the advantage that it's more intuitive to specify the columns that you need so if your query is running slow because you're pulling too much data (something that's happened to me a bunch,) you can omit unneeded columns pretty easily.

nick_kline 8y ago

I think a big point of the article was that these more recent relational dbs (like memsql) figured out how to make distributed joins across multiple shards scale really well - that's one of their core value adds. So you can shard your for example customer and order data stored across multiple nodes and partitions and do distributed joins, aggregations etc. Scaling to use hardware resources on multiple machines is a crucial aspect of these systems. Disclaimer, I work at Memsql, speaking for myself.