5 ms·
> 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
by matwood 8y ago
> 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.
- mmt 8y ago> 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.
- matwood 8y agoTouché. You're right, at some point the data must be joined and filtered. My point was to let the tool do its job :)
- jandrewrogers 8y agoDoing 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.
- mmt 8y agoThanks 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.
- dman 8y agoCould you point out any open source supercomputing / data products that get this right (ie have decentralized data flows?).
- mmt 8y agoI'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.
- infinite8s 8y agoTake a look at parallel simulation codes (for example, LAMMPS in chemical physics).
- 189523954 8y ago>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.
- lkschubert8 8y agoTo the last point linq/ef actually generates one SQL query for whatever you are doing if you use it properly.
- lmm 8y ago> 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).
- meowface 8y ago>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.