Hacker News Reader

Comment by matwood | Hacker News Reader

> Everybody uses an ORM with a SQL database.

I'm ripping ORMs out of any backend code and using things like jOOQ.

> I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more".... > Joins are slow and complicated

You know what's really slow? Creating joins in code.

> This is not true at all. Programming languages are vastly superior at querying a database. Just go write a complex query and compare it to the one in linq or whatever you use.

It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

View on HN

> It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

But then it wouldn't be distributed processing! :)

Seriously, though, consider it for a moment.. this pattern has similar features to something like Hadoop. The data comes from storage nodes (database server and, hopefully, their read replicas) and goes to processing nodes (app server) to have the work done and is then new data is written back out over the network to storage nodes and replicated across the network (to the replica/slave database servers).

If the data volume is particularly low or the compute load (CPU and/or RAM) is particularly high, the distributed method would make intuitive sense. I haven't seen it yet, however.

Doing distributed joins correctly requires an architectural/technical capability that most distributed database engines don't have: decentralized parallel orchestration. If you have this, you can do joins even with very high data volumes efficiently given good parallel scheduling algorithms. Most databases are designed such that there is a single point of control that declaratively schedules all data flows required to execute the query; this scales poorly for operations like joins, never mind recursive joins, which is why you don't see it.

Letting individual database nodes dynamically schedule and orchestrate their own data flows with each other, essentially allowing each node in the parallel system construct its own execution plan in relation to other nodes as it goes along, does not fit within the "giant distributed file system" paradigm that most distributed systems are based on. People who design codes for supercomputers are often familiar with parallel orchestration idioms that work at extremely large scales but it hasn't crossed over into ordinary distributed database engines. (This is also a good litmus test for what makes a database "parallel" as distinct from "distributed".)

Most distributed database architectures are much more centralized than they need to be, particularly around control of execution planning, and this limits their expressiveness. It is quite difficult to hack together a distributed join that performs better than a centralized one without good support for parallel orchestration.

Thanks for that detail. I knew there were more interesting, technical reasons than my oversimplification for why I had never seen it.

> People who design codes for supercomputers are often familiar with parallel orchestration idioms that work at extremely large scales but it hasn't crossed over into ordinary distributed database engines. (This is also a good litmus test for what makes a database "parallel" as distinct from "distributed".)

My understanding was that this is primarily (or also) due to the lack of low-latency (and high-bandwidth) interconnects in the ordinary distributed environments, as those enable that orchestration.

I admit to wanting that to be true, as it supports a mantra I like to say to managers with programming-only (no Ops/sysadmin or even DBA) backgrounds: not all problems can be solved with software.

Could you point out any open source supercomputing / data products that get this right (ie have decentralized data flows?).

I'm obviously not the parent commenter and have no inside knowledge of the HPC world, but my guess is the main open source supercompting projects would be from NASA, the US national labs, and CERN.

You might search HN for the recent announcements about new clusters, especially top500, and look for the comments discussing using MPI (versus something custom, I think?), as my recollection is that those topics would yield further pointers to the actual examples you're looking for.

Take a look at parallel simulation codes (for example, LAMMPS in chemical physics).

Touché. You're right, at some point the data must be joined and filtered. My point was to let the tool do its job :)

> You know what's really slow? Creating joins in code.

It really isn't. In a good programming language expressing a join should be just as easy as in SQL.

> It makes zero sense to pull a bunch of records back from the db using multiple network calls to join and then filter. Let the db do its job.

Agreed that you need a way to do aggregate queries on the server, but plenty of NoSQL systems give you ways to do that. I'd far rather write a map-reduce in javascript/erlang/... than express the operation in SQL and hope that the query planner does what I think it's going to do (which ends up a bit like trying to program in Prolog - it's magic until the day it doesn't work and you then have no idea what's gone wrong or how to fix it).

>It really isn't. In a good programming language expressing a join should be just as easy as in SQL.

I don't think they were saying it wasn't easy, just that it isn't as performant as letting the DB do the join. Which should be true in pretty much all cases.

To the last point linq/ef actually generates one SQL query for whatever you are doing if you use it properly.

>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.

> 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.

> never experienced any performance problem that was the result of a join, unless you count cross joins with no filters.

An unindexed join will have major performance problems, and look exactly like an indexed join.

> 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.

Faster to do the same thing? No. Easier to see which things are fast and which things are slow? Yes. Also easier to avoid the deadlocks that traditional databases' overzealous application of ACID can easily lead to.

> 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.

I find the pseudo-English syntax of SQL is always very hard to follow - it slips into a kind of uncanny valley - the grammar of what goes where can be backwards from what I'd expect. Tooling is also rather limited compared to a "real" programming language. Just basic things like unit testing your queries are much harder than they should be.

>An unindexed join will have major performance problems, and look exactly like an indexed join.

Then add an index.

Its not exactly difficult in a database. Using a database without indexes is kind of stupid. Why would you do that?

And personally I find SQL to be one of the easiest languages to read. I agree that it's kind of back to front in many ways, but its way easier than trying to work out what happening in some nested loops that someone else has written.

> Then add an index.

Then you're adding work to your writes, and your database will block your live write transactions until the corresponding index updates are done.

> Its not exactly difficult in a database. Using a database without indexes is kind of stupid. Why would you do that?

You wouldn't intend to, but you might do it by accident. The failure modes can be pretty bad, since an SQL database will take whatever nonsense query you give it and try to run it, even if doing so impacts your live operations. Whereas in many NoSQL systems if you try to use an index that doesn't exist it'll fail fast.

SQL is also not very composeable. Queries and their constituent parts are not first class concepts in SQL. There is no way to, for instance, pass a query to a piece of code and have that code add part of a WHERE clause to that query. That results in a lot code dynamically generating SQL queries.

>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.

> 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.

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.

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.

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.

Reply on news.ycombinator.com