58 ms·

For Want of a JOIN

268 points by subset 4y ago

Jupe 4y ago
Firstly, I'd suggest the author look at this differently; perhaps "For Want of a Code Review". Especially code from a relatively recent graduate, on a piece of code for which the engineer in question has little experience. With that said, the JOIN is a very powerful concept which, unfortunately, has been given a terrible reputation by the NoSQL community. Moving such logic out of the database and into to DB's client is just a waste of IO and computing bandwidth. SQL has been the ONLY technology/language that has stuck with me for > 25 years. The fact that it is (apparently) not being taught by institutions of higher learning is just a shame.
- leononame 4y ago
  I agree. It took me 3 years or so to actually land in a project and learn SQL for the first time. Before it was all with ORMs. I didn't know what a join was for the first couple of years of my career. Understanding SQL and being able to work with data interactively has made me a better software engineer. This tech is important enough that it should be taught in university/coding camps.
  - miiiiiike 4y ago
    You DIDN’T learn SQL in school? Probably my most useful class. I hated it at the time, I was a desktop and embedded dev, and this was before SQLlite roamed the earth.
    - rjbwork 4y ago
      >Probably my most useful class. Ditto. My teacher was hardcore. He was a graybeard who was around before Codd's now famous paper. He worked with some of the old pre-relational hierarchical databases. We had to take SQL queries, turn them into relational calculus and algebra, turn that into a query plan, then come up with an estimate for the time the query would take to run given various hardware speed numbers and the size of the data. We had to implement our own (primitive!) database engines, including various join algorithms. To date it's one of the hardest, yet most rewarding, learning experiences I've had.
      - miiiiiike 4y ago
        That.. Sounds graduate-level. How's your PhD?
        avidphantasm 4y ago
        That sort of course was very much par for undergrad courses at CMU when I was taking CS classes there 25 years ago. The OS course was very intense. I wasn’t a major and didn’t have time to take it, but my networks course was of similar rigor (i.e., implement a toy TCP/IP stack).
        rjbwork 4y ago
        I took it during summer, and there were some masters and PhD students in there, but I took it as an undergrad course. It was very intense, 3 hours per day, 3 days per week, and 1.5 hours per day the other two.
        miiiiiike 4y ago
        Man, I got downvoted into oblivion for my comment, but seriously, yeah, graduate level. That sounds fun. My most intense undergrad class series was one where we soldered together a M68HC11 computer and learned assembly one class, built an OS for it the next, and then turned it into a robot with control software running on our OS-es.
        miiiiiike 4y ago
        This was obviously was a joke. Sounds like there's a range in SQL training that goes from "I've heard of SQL" to "I implemented a PostgreSQL compatible db my Sophomore year."
        Izkata 4y ago
        Not necessarily. One of my undergrad courses (~2008) had us implementing a basic inverted index (think Solr or Elasticsearch), which gave me some insight into Solr that my co-workers didn't have that helped with performance issues. If we had a database course that went that in-depth, I'd've definitely taken it, too.
    - pineconewarrior 4y ago
      I took several classes on it and I still didn't really 'get' it until I had to work on challenging problems in the real world. Granted, my education was not great quality overall.
    - cfeduke 4y ago
      I just enrolled in an online CS degree course. The database class is an elective. Crazy, I know.
    - TheCapn 4y ago
      For me the first time we dabbled with SQL was in a 3rd year Software Engineering course where the focus of the class was a single group project that we managed among ourselves by splitting tasks, conducting code reviews and handling the build and release in teams. I recall one group doing the project login which went much along the lines of what the OP's article touched on. Their code was esseentially var success = false var query = SELECT * FROM users while query.read { if query(user) == input_user && query(password) == input_pass { success = true } } Yes. They selected the entire user table. Yes. They iterated over the entire result (even if first returned result was valid) Yes. That was "shipped" for the project No. My complaints notion they should be leveraging the database for all the things they're doing wrong were ignored. It was performant! Look! It logs in instantly! YEah, because there's 8 users on the database for this project, what about when it ""ships"" and there's 100,000? More? --- My first real job dealing with a database wasn't much better. We were using a MS Access database with no normalized data. Our client's primary transaction data was across a table with 70 some columns, many of which were often duplicated values in some form or utilizing very bad practices. Since joining this company I've sped up queries in almost immeasurable ways and done things my older coworkers initially derided because they couldn't understand the syntax. TL;DR SQL, for some stupid reason, is still treated as second class to core langauges and it is a god damn shame
      - marcus_holmes 4y ago
        > SQL is still treated as second class Agree so much. And if you've ever seen a real SQL wizard in action, you realise how much can be done with it. Like most of the business logic of a system can be in the database, with an interface that's a set of stored procs/functions. And fast.
        toyg 4y ago
        > most of the business logic of a system can be in the database The problem of this approach is the tooling and lock-in. If databases had first-class versioning support for their code objects (which could easily interoperate with git), testing automation, and a parvence of standardization across the industry, then a lot of people would be very happy to work with that model. But they don't.
    - stephenhuey 4y ago
      When I was at Rice a couple decades ago, the database class was a 400-level class in which we learned relational algebra and relational calculus before SQL. The professor must have been good at teaching because I loved learning the formal underpinnings even though my memory of them has faded, but I do recall that I went from zero SQL knowledge to being very excited by its power. So many of my CS classes were very theoretical, and even though we learned some theory in the database class, it was definitely one of the single most (the single most?) pragmatic & practical of all the CS classes I had. I was so zealous about normal forms that I complained loudly at one job where they used an old D3 database with multivalue fields. It was so glaring to me because we actually used all hand-rolled SQL instead of an ORM in those days. Years later, after growing less tech-centric and more thoughtful of business needs, I realized that sparingly using multivalue fields was not a hill to die on. :) Fast forward many years to my first startup in Boston. Google App Engine was new and I wasted precious time trying to figure out how to shoehorn a typical relational data model into the early NoSQL data store available for App Engine at the time. This was just after the financial crisis and I hadn't yet heard the mantra to pick boring technologies, and I learned through sheer pain that unless you really really really need to, don't waste effort by walking away from relational databases. And also, most apps can get by with whatever the ORM does and if there's a performance issue, optimize that one query instead of trying to optimize all your SQL from the beginning. There's a lot I still don't know about pushing heavily complex queries down to the db level, but for expensive problems I'd reach for expensive assistance, because it's worth it (after trying to play with the SQL myself).
  - tracker1 4y ago
    Agreed... ORMs can be nice, but one should understand how it works. I'm a pretty big proponent of simple mappers (Dapper for .Net, template literals for JS/TS) with straight SQL over ORMs at this point.
- maratc 4y ago
  I was at a place that used sharding, so the data was scattered across 128 database servers. SELECT works there but JOIN doesn’t, as your right side may reside at another shard.
  - Jupe 4y ago
    IMO... If the query is for OLAP the data may need to be extracted to another data store. If the query is for OLTP, then the design is wrong. I don't know your problem space, but pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea.
    - paulmd 4y ago
      > If the query is for OLTP, then the design is wrong. I don't know your problem space, but pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea. well, that's the basic idea of microservices lol. forget living on a different shard, lots of times your data is going to round-trip to JSON and back a couple times and then be manually joined in some backend/service layer, or in graphql! one bad abstraction I see a lot from microservice teams (that don't really understand it past the high-level concept) is "every table is a service", or "every minimal set of tables and its codeset is a service" and that's exactly how that ends up. Microservices really ought to be chunky enough to do their business without ending up calling 27 different services under the hood just to do simple operations. Obviously there is a point where it's too chunky, but too micro is also bad too.
    - maratc 4y ago
      > pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea. To the contrary, pulling data from 128 shards can be done in parallel, and about 127 of them don't have any data to return.
  - higeorge13 4y ago
    If you had to do joins on different shards, then you have implemented sharding wrong.
  - 4y ago
- jjice 4y ago
  Our SQL course in uni left a lot to be desired. Very little time spent on join, much more on subqueries, oddly. My first job our of school there was a SQL portion and they were impressed by my overuse of subqueries. Best SQL I learned was on the first few months in a real database with real information, instead of a student-courses mock DB with 15 rows that seems to be the academic standard for teaching.
  - tracker1 4y ago
    I'm frankly surprised that some of the larger MS based data sets aren't more standard for learning. MS SQL Server isn't generally my first choice (preferring PostgreSQL for standards and portability), but it's got some pretty great example data out there.
- Beltiras 4y ago
  Oh it's taught. I have a bone to pick with how. I'd rather have spent a lot of time on the practical application of SQL than the theoretical background of column and table operations.
- lultimouomo 4y ago
  > Firstly, I'd suggest the author look at this differently; perhaps "For Want of a Code Review". Especially code from a relatively recent graduate, on a piece of code for which the engineer in question has little experience. I assume the story is made up, but if we were to take it at face value the title would be "For Want of Basic Human Decency"; the author is saying that they saw this whole easily preventable train wreck happen in slow motion and did not lift a finger to prevent it, instead laughing, taking notes and thinking of the fabulous snarky blog bost that would have come out of it.
  - axus 4y ago
    The way I read it, they accepted the decisions of those higher in the hierarchy, after providing their feedback. It wasn't clear if lots of money was lost, just lots of time. I didn't think it was made up.
  - nisegami 4y ago
    Eventually you learn it's not always a great idea to stop your employer from getting burned. Some people don't learn until it hurts.
  - Epskampie 4y ago
    From the article: > I’d definitely commented on the JOIN statements during the initial code review
    - boxed 4y ago
      How about when it became a problem? Or the second time? The third?
yyyk 4y ago
The worst part is not the missing JOIN. This happens, especially with juniors. It's the 'all signup errors warranted paging the on-call even on 4am' bureaucratic decision followed by being unable to apply any fix quickly. No surprise the author did not stay.
- weego 4y ago
  The worst part is more senior devs being happy to watch it happen and even accept the commits, and then write a long winded story of how this 'car crash unfolded' apparently unaware that they're a tacitly active contributor to it happening and then escalating out of control. If you're going to be aper of throwing junior devs under the bus, at least have the self awareness not to brag about it on the itnernet.
Thaxll 4y ago
The other problem with the double join is that it's not an atomic operation so between the two select data could have changed.
- deleted 4y ago
  [deleted]
- ttfkam 4y ago
  I really wish more people understood this. Thank you. ACID is not just a good time on a Saturday night.
deleted 4y ago
[deleted]
funstuff007 4y ago
> With the exception of NPM modules, most tools are designed to solve problems, possibly the ones you have Upvoted just because of the chuckle this gave me.
btown 4y ago
As someone who’s primarily worked with monoliths, I often wonder how often this exact problem happens, but where A and B are [micro]services owned by two different teams, one is required by company policy to use their APIs not their raw databases, and escalation of each of these issues e.g. query size/rate limiting runs the risk of burning political capital on top of everything else. How does one JOIN across not just tables but opaque services, in the general case? Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly?
- pwg 4y ago
  > Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly? What tends to be by far more common is that each team fails to envision that someone, somewhere, sometime in the not so distant future will want or be required to retrieve more than one "element" at a time via their APIs. And so panic ensues when "other entity" begins feeding 20 API retrievals per second at their "one-at-a-time API" and their performance goes off the cliff it was always sitting near.
  - NegativeLatency 4y ago
    Extra points if you manage to do it to your own team’s APIs because of bad design and planning
    - tracker1 4y ago
      You mean like 8+ joins to get that single record and then having it bottleneck when you get a few hundred requests a minute? (not bitter at all here)
- thatwasunusual 4y ago
  > How does one JOIN across not just tables but opaque services, in the general case? You (should) never do that. It's as simple as that. If you create microservices that are atomically depending on each other, you are doing something _extremely_ wrong.
Ayesh 4y ago
That was a fun read, and I loved that little joke with NPM packages. I find SQL, Regular Expressions, DNS, Client-side caching, CORS, TLS, and a few other things to be a MUST when hiring people, because most of the over-engineered crap can be avoided with a little bit of expertise with these. I spend most of my semi-leisure time with some good Regex books and golfing too. Modern databases are amazing. Every few months, I take pleasure and not shy away in refactoring some complex and frequent queries into SQL views, carefully replace data logic (but not business logic) into stored procedures, and replace certain batch scripts with one-off queries.
- jjice 4y ago
  Favorite regex books? Only one I've read is Mastering Regular Expressions by Jeffrey Fridel and I loved it. If you have any more recs, I'm all ears.
  - ttfkam 4y ago
    Excellent book! I was only about 25-30 pages into the first edition when it all finally clicked for me. I've never feared a regex since.
- masto 4y ago
  I was recently discussing something that involved knowing whether a string consisted of only a single repeated character. Having spent many years in the trenches with Perl, my first thought was /^(.)\1*$|^$/, which is the kind of thing people dismiss as "line noise" because they haven't spent a few minutes learning a language that can easily express what you want. We have this trend now from people who like languages like Go where answering the question "does this string consist of a single repeated character" begins with "I would now like to reserve space for a 64-bit integer which I shall henceforth refer to as 'i'...", and that's considered a virtue.
  - cbm-vic-20 4y ago
    Let's see if I remember Perl regexps: '/': this is a regular expression. '^' at the beginning of a line, '(.)' match any character, and remember it for later. '\1' match the same character that you just remembered, '*' zero or more times. '$' then match the end of the line. '|' Or, '^$' match the beginning and end of the line with nothing in between.
rubyist5eva 4y ago
This article speaks to me. So many times I have needed to go back and fix queries that were naively written this way like it was some kind of "optimization". There is no difference in effort between writing a join or doing the ORM-double-round-trip in the vast majority of cases. People are so afraid of doing joins I see people doing subqueries with the id in a subselect because "joins are slow". The worst is usually some kind of pseudo-join and then an aggregate or filtering in the application code. It drives me up the wall when I see it in code review, usually because I get into some argument about "joins are slow" (with no evidence) and then I have to go and rewrite the query and maybe add an index to show that, yes - an aggregate that takes seconds and a ton of memory in the application code can in fact take milliseconds in the database. The NoSQL people have really done a lot of brain-damage to this industry. It's so pervasive that I've starting using this kind of question in our technical interviews, doing a double round-trip ends the interview for anyone higher than a junior.
- giovannibonetti 4y ago
  Here is some actionable advice for speeding up a join with indexes. You'll need one in each table with the join columns. For example: SELECT ... FROM table_A JOIN table_B ON table_A.column_A1 = table_B.column_B1 AND table_A.column_A2 = table_B.column_B2 You can add indexes like this: - table_A(column_A1, column_A2) - table_B(column_B1, column_B2) If both tables are large enough, this query can probably take advantage of those indexes to perform a merge join. Postgres tip: you can also add columns to the include part of the index to speed up filters in the WHERE conditions. You might even get an index-only scan! Look into covering indexes to learn more about it.
  - boredemployee 4y ago
    Not related, but here in my job our query just stopped working because the data got too big. we use left joins everywhere because we don't want to lose the main table data. do you think the trick you just mentioned could optimize our left join as well?
    - 4y ago
bjornsing 4y ago
Shipping it the first time may have made sense. Second time? Not so much.
LudwigNagasena 4y ago
I would call it “for want of reasonable hiring and onboarding processes”. How does someone get into a data engineering job without any knowledge of SQL and doesn’t even get basic onsite training?
- brocha 4y ago
  Largely due to the fact that "Data Engineer" is defined differently at every company. Some want a SRE, some want a Database Architect, some want a software engineer that knows some SQL, others want only SQL junkies. As a result, I have picked up a variety of skills to fit into whatever my company dictated what a Data Engineer should handle
phamilton 4y ago
I've learned that "a month in the lab saves an hour in the library" usually can be distilled to "A shallow understanding produces complex solutions. A deeper understanding is usually required to create simple solutions." While the original example of not understanding JOIN might just be a lack of of general knowledge, the later steps are great examples of this, especially if someone else comes along and is told to fix the error. Making something execute slow code in parallel is pretty easy to do generically. It doesn't require understanding much about the slow code. It's fairly low risk, you probably won't have to tweak tests, there won't be additional side effects. The major risks will be around error handling and it's easy to turn a blind eye to partial success/failure and leave that as a problem for a future team. You can confidently build the parallel for loop, call the task done and move on. Striving for a deeper understanding requires a lot more effort and a lot more risk. Re-writing the slow code is a lot more risk. All side effects must be accounted for. Tests might have to be re-written. The new implementation might be slower. The new index might confuse the query planner and make unrelated queries slower somehow. It's not just a matter of investing time, it's investing energy/focus and taking on risk. But the result will have comparatively fewer failure modes, it'll be cheaper to operate and less likely to have security implications. I've been in both spots and while I wish I could say we always went with the deeper understanding that wouldn't be an honest statement. But the framing has been really helpful, especially as I work with other execs in the company to prioritize our limited resources.
- isoprophlex 4y ago
  "Simplicity is the highest form of sophistication"
  - hoosieree 4y ago
    "less is more"
    - boffinism 4y ago
      <>>
outsidetheparty 4y ago
I got a chuckle out of "With the exception of NPM modules, most tools are designed to solve problems, possibly the ones you have," but have to agree with some other commenters that a better title for this would have been "for want of a mature code review process"
- deltarholamda 4y ago
  I have to wonder if the code review process wouldn't be as big a deal if they hired a proper DBA. This problem would not have been an issue if the DBA had been told "we need to do X," and the DBA would craft a stored procedure for X. I get that stored procedures aren't a cure-all, and sure, they can get out of hand, but doing this stuff in code is often worse than letting the DB do its job.
data-ottawa 4y ago
In the spirit of HN I should confess I’ve done exactly this, not the using Python to feed data back to SQL, but writing terrible hacks to get around resource limits on deadlines. On a recent project I needed to process a couple years of data for a hard deadline of Monday, and it was Friday. Our DB had a query timeout and a resource memory limit which blocked doing the full analysis without building new data models which would take days to get shipped and to build the new data models. The deadline couldn’t be moved so hacks were needed. The solution: write some Python code to generate one query per week of data going back two years (over 100 queries), save the results to individual scratch tables, and then use a second query to union all the results together in our BI tool. Of course the first time I ran it serially it was too slow, so I parallelized it. That was too many queries so I added a limit. Then one query failure broke the whole thing so I added retries… by the end of the day it looked exactly like this article. It worked though! I got all the data we needed processed for Monday, I presented it to our execs and our project was approved. We only needed to manually run that script once more before I built the real solution and deleted the script.
- mijoharas 4y ago
  This reminds me of some log parsing code I wrote that had to batch download sets of logs from our log provider, and then used some gnu parallel, jq, and some general shell tools to spit things out (may have thrown the data into some format and used textql for the end of it.) (I can't recall why the general log searching tools we had didn't work in this situation, I think it was because I needed to get data from a lot of disparate logs at once, or it was driven by having to make lots of separate downloads of the logs.)
jeffreygoesto 4y ago
"If you encounter an unusually round system limit, you’re probably using the system in a way its designers never imagined." Haha, so true. We triggered a static code analyzer error "Cyclomatic Complexity bigger than 1.000.000.000!". The vendor was very interested in that code snippet (generated classifier code) and we shared a good laugh.
Ensorceled 4y ago
We hired a data engineering consulting company and none of their team of SQL experts had heard of upsert or merge. I find it weird that people don't spend a bit of time searching for a better way of doing stuff before just jumping into a long, hard way of doing things.
- higeorge13 4y ago
  Unfortunately many data engineers don’t know basic stuff about sql snd databases, but are experts in etl tools and data warehouses where such features are not relevant or don’t exist. You were probably looking for some dba who are something different.
bayesian_horse 4y ago
An SQL query goes into a bar, walks up to two tables and asks: May I join you?
dagss 4y ago
It is one thing when a junior does this because they haven't learned better. It's quite another when experienced seniors ban the use of SQL features because it's not "modern" or there is an architectural principle to ban "business logic" in SQL. In our team we use SQL quite heavily: Process millions of input events, sum them together, produce some output events, repeat -- perfect cases for pushing compute to where the data is, instead of writing a loop in a backend that fetches events and updates projections. Almost every time we interact with other programmers or architects it's an uphill battle to explain this -- "why can't just just put your millions of events into a service bus and write some backend to react to them to update your aggregate". Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute -- instead of a new microservice or whatever and some minutes of compute. People bend over backwards and basically re-implement what the databases does for you in their service mesh. And with events and business logic in SQL we can do simulations, debugging, inspect state at every point with very low effort and without relying on getting logging right in our services (because you know -- doing JOIN in SQL is not modern, but pushing the data to your service logs and joining those to do some debugging is just fine...) I think a lot of blame is with the database vendors. They only targeted some domains and not others, so writing SQL is something of an acquired taste. I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation).
- joeblubaugh 4y ago
  How easy is it to verify that the configuration of your database matches a checked-in configuration or source file these days? My beef with a lot of installed procedures in a SQL database comes down to deployment and rollback difficulty.
  - mh- 4y ago
    Depends on the DBMS, but pt-config-diff works well for MySQL. edit: This will only help for configuration, not procedures. https://docs.percona.com/percona-toolkit/pt-config-diff.html https://docs.percona.com/percona-toolkit/pt-config-diff.html
  - Wicher 4y ago
darepublic 4y ago
Yes I remember inheriting a project where in a similar fashion people were allergic to join. So we got js code selecting entire table, looping over the rows and then doing inner loops with further selects. I eventually had to switch everything around to using joins. There seemed to be a huge disdain for SQL.. like if you ever endeavoured to try some raw SQL you were playing with matches. Sure OK but code that is handling db operations that inefficiently is 100x worse tho...
- tracker1 4y ago
  I tend to push for the other direction, especially if using JS/TS... template literals are so useful here... const foo: MyType[] = await db.query` SELECT ... FROM ... WHERE bar = ${baz} `; And simply understanding how the queries work... very similar with Dapper in C#... I'm kind of all out against ORMs at this point.
  - ryanbrunner 4y ago
    Uhhhh, you should be extremely careful with string interpolation around DB statements. The code sample you posted is pretty much a textbook case of a SQL injection vulnerability if the value of ${baz} is ever provided by a user.
    - tracker1 4y ago
      No, it isn't... db.query method recieves the parameters separately from the string parts and will turn it into a parameterized query. You're confusing/conflating db.query`...` with db.query(``); https://www.javascript.christmas/2020/11 https://www.javascript.christmas/2020/11
      - ryanbrunner 4y ago
        Ah, sorry! I haven't used templates much and an interpolation inside a string triggered my "oh shit security problem" sense.
nightpool 4y ago
I'm not sure I understand how a JOIN would have fixed this problem. That is, if each chunk is fetching 1k rows, and you're doing 50 simultaneous chunks, then you're doing a 50,000 row query, and that's ALSO going to be extremely slow, in terms of exclusive database contention (less of an issue with bigquery) and result set memory usage (definitely still a huge issue for python). In fact, one of my most frequent pieces of feedback to junior engineers who are just working on a larger backend for the first time is "this query tries to fetch too much data at once, it will take too long and use way more memory then it needs to, please use find_each to automatically batch the query so that we balance memory usage and database contention". Indeed, Rails by default will use the exact same batching strategy the junior engineer chose in this case: fetch 1k items, process those items, and move on to the next 1k items. I understand that the author rankles about things not being done the "right way" with JOIN, but I question whether their focus on "best practices" is preventing them from seeing the optimization forest (split things up into parallel background tasks, don't try to keep the entire dataset in memory at once) for the "doing things right" trees (use JOIN)
- civilized 4y ago
  Two clear inefficiencies in the junior's code: 1. It is pushing the entire id table back and forth through the network connection, bit by bit. Replacing with a join completely eliminates this. 2. A query with an IN clause is (probably) doing a hash join under the hood to calculate the result of the IN clause. So the junior's code is effectively submitting a join query over and over, each time with a slightly different tiny chunk of data, rather than asking for the joined data once and processing the result in batches. It is also worth considering if the entire data processing pipeline can be in SQL, but I can't tell if that's the case from the blog post.
- HelloNurse 4y ago
  Do you know what's slower than fetching 50000 rows with a big join? Fetching them with the overhead of thousands of tiny queries instead of one, and repeating disk reads thousands of times because you cannot consolidate the thousands of query executions.
  - 4y ago
pier25 4y ago
The aversion to SQL by younger devs is pretty amazing. Yes it has a weird cognitive model and a learning curve but it's a cornerstone of web dev. Instead they resort to convoluted and technically inferior solutions like Prisma just because of a superficial DX advantage. I'm certain Mongo only became popular because of this even though for many years it was crap. That said I do think we need a better SQL. It's still not there but EdgeDB looks very promising.
- CodesInChaos 4y ago
  Mongodb is very convenient for CRUD operations, while relational databases need a complex ORM to handle that sanely. Consider how much CRUD a typical application contains, I certainly get the appeal. However aggregatipn framework is horrible and thr 16MB limitation very annoying. Im general I find SQL/relational models easy to understand conceptually, but maps badly to both the rest of the application and the problem domain. I also hope that edgedb will help with that. When I modeled one of my applications in its SDL it was a very clean match. I don't have much experience with its query languange. But so far it looks much nicer than SQL, but still uglier than functional programming.
  - ttfkam 4y ago
    Those who fail to learn the lessons of SQL are doomed to re-implement it… poorly. SQL-92 is no longer the baseline. Now we have CTEs, laterals, graph queries, JSON storage, system versioning, and more within a reasonably concise DSL for set theory. And even more showing up all the time like function pipelines. https://docs.timescale.com/timescaledb/latest/how-to-guides/hyperfunctions/function-pipelines/ https://docs.timescale.com/timescaledb/latest/how-to-guides/... SQL is the Rodney Dangerfield of programming languages. It don't get no respect!
    - setr 4y ago
      My main issue is: 1. The ORMs are really not updated to utilize these useful features, largely because they try to support all the databases, and end up supporting really just the ANSI standard set of features, with maybe a few extensions. 2. Without the ORM, you're writing SQL-as-string, and its as demented and awful as one would expect of writing all your code inside a random string. It doesn't help that all SQL features are made inconsistent language-wise, so you're basically guaranteed to have at least one syntax error using anything novel (with an utterly useless error message from your favorite SQL compiler), which can only be caught at runtime (because your IDE's DB linting and language support is also limited to mostly ANSI SQL, because they also try to support all the databases). 3. If you make it a stored proc/function/view, the DB IDE tooling is shit across the board. They're all miles behind any decent app-lang IDE in terms of features/tooling they provide. It's actually impressive how pathetic an environment DBA's put up with. You're really not going to get much more support than an autocomplete on table/column names, and maybe datatypes. So ultimately, the act of writing SQL is terrible compared to doing your normal app-logic. The only reason I'm willing to put up with it is because the positives of using an RDBMS properly dramatically outweighs the negatives. But that tradeoff isn't immediately visible to the novice, so this absurdity of reimplementing SQL with not-SQL becomes reasonable. The engine is beautiful. The relational algebra -- glorious. The language, tooling and ecosystem? It's all stuck in the 80s.
johnthuss 4y ago
"Don’t let junior SWEs get 2000 lines into a change before submitting a pull request." This is good advice. Share your code early and often so you can get feedback before you're fully committed to one approach.
- feoren 4y ago
  Yeah, this is insane to me. 2000 lines written well in an expressive programming language is a small but complete library. It's 15 to 30 code files. 2000 lines is just about enough to write a complete spelling and word-use checker with a persistence layer. It's enough to write a generic mass-balance process simulator, or a web security library, or a moderately complex workflow-management library, or a toy game. 2000 lines is a week or two of work. If your junior SWE is writing 2000 lines of code before anyone looks at it, you're basically letting them be raised by wolves.
tomerbd 4y ago
Wait until he hears about LEFT JOIN
im3w1l 4y ago
I'm gonna be the contrarian and say this is mostly fine. We can research the proper way to do things, or use code review to teach about the proper ways. But this can lead to code shaming and a fearful environment where people second guess themselves and spend a lot of time chasing a perfection that doesn't move the business metrics. In this case, doing the join manually isn't a huge deal, chunking isn't a huge deal, parallel requests isn't a huge deal. But "concurrent limit reached" is the point in this story where Bob should have put on the thinking cap and reasoned that "this shouldn't be hard, other people do things like this with bigger datasets all the time, I wonder how". Before that point it's literally just a matter of changing a couple lines to solve the issue. So what? After that point however, it's starting to affect the overall design around it in harmful ways, and turning the issue into a bigger one.
- mgaunard 4y ago
  That just means he did the parallelization incorrectly, there should be a fixed maximum number of concurrent requests.
- int_19h 4y ago
  And that's how we end up with chat apps that consume gigabytes of RAM, and blinking cursors using one whole CPU core.
jmull 4y ago
This doesn't really add up to me. The article explains how the original bad code gets checked in which seems plausible enough. But that doesn't explain why the first fix wasn't to just start using a JOIN? Or the second fix. I guess it's a made up story, to make a point? Anyway, I found the plot holes distracting.
acdha 4y ago
Back in the 90s, I remember getting a project from a local F500 company. Our design team had been doing some work for them and they'd been happy with the results so when they had problems on a backend project which was over a year behind schedule they asked if we could help & I was pulled in. The project was a fairly straight forward product selector for industrial equipment but the team from a large consulting firm which had been working on it was struggling with performance & hadn't completed most of the features. The client was saying it was unacceptable that pages would take 5 or more minutes to load and they weren't going to drop $500K on bigger servers like the developers were swearing were necessary to run the site. I knew something was off performance-wise since the entire product catalog was only on the order of tens of thousands of records. As soon as I looked at the source code, the mystery was explained: they had allegedly experienced 3 developers working on it but none of them knew about SQL WHERE constraints! Instead, they were doing nested for loops to repeatedly retrieve every row of every table and doing the equality checks in VBScript. Finishing the rest of the project backlog took me a couple of days and the customer was quite happy that the slowest pages were now measured in hundreds of milliseconds rather than tens of minutes. I was proud of how quickly we were able to turn that project around but the PM & I were discussing how even our rush rate wasn't enough to get us anywhere close to the amount of money the previous contractors had charged.
phendrenad2 4y ago
Ragequitting a company and calling it a "trainwreck" because one developer didn't know about JOIN seems... extreme.
Arwill 4y ago
The principle is that you should be using the underlying API if something is already solved on a lower level, and not replicate the functionality on a higher level, because it will perform poorly. There was a reason why the lower level API exists in the first place. This applies to graphics programming very well, its not a question that you wouldn't be making your own pixel rasterizer instead of using DX, OpenGL or Vulkan, for example. The big recognition is that when doing business apps, SQL database functionality is the underlying API, and you should prefer using that.
- nonethewiser 4y ago
  > The principle is that you should be using the underlying API if something is already solved on a lower level I think you're making a great point but I want to consider what this suggests about ORM's. Using an ORM means you're not directly using the underlying API. In theory an ORM should be a very small "distance" from the underlying API. When that's the case, they are a no-brainer. But no ORM has 100% feature parity and for more complex queries this distance from the underlying API can grow considerably. And if you insist on ONLY using the ORM API then you're going to find yourself doing some pretty dumb shit in the application layer. Personally I think ORM's are great, but there is this common problem of over-insisting on their API and treating raw SQL as the devil.
icedchai 4y ago
I've seen systems where people are doing manual JOINs with CSV, JSON, and the results of DynamoDB scans on relatively tiny datasets (<10 megs.) Everything could fit in sqlite on a single machine. Instead, they build a Rube Goldberg contraption that uses "modern cloud architecture."
ivanhoe 4y ago
Back in the days of Mysql w/ MyISAM engine it was sometimes way faster on big data-sets and underpowered DB servers to do the query exactly this way. Even with all the correct indices in place the JOINs (especially if more than one table was in game) would often just freeze the server for 15-20 minutes, while joining data at the app level in the for loop and with the lookup tables for id-s would typically take only a few seconds. Obviously this is an obsolete hack for long time now...
- butlerm 4y ago
  I can imagine that, but MySQL didn't really start to become a competitive relational database until the 3.23 timeframe (around 2000), and it is hard to imagine MyISAM (with no actual transaction support) being used for a production database except under very carefully controlled circumstances. To some degree that was true of a lot of earlier competing databases as well, which tended to take escalating locks on everything from the page level on up just to implement basic read consistency. So any transaction of any type could easily lock up a random set of unrelated rows if not entire tables until completion.
brightball 4y ago
That's an excellent description of Contagion caused by tech debt. How much does this problem grow and spread the longer it goes unfixed?
tantaman 4y ago
This is an incredibly common thing. The worst I've seen it is when people drop their SQL DB for a No-SQL thing (for no good reason) and then end up implementing all the joins they lost in the application :(
tmp60beb0ed 4y ago
> Don’t let junior SWEs get 2000 lines into a change before submitting a pull request. Why junior SWEs and not all SWEs?
jmartrican 4y ago
We've all been Bob at least once in our life. By 'we' I really mean me.
mgaunard 4y ago
how is CSV not "wrap the fields in double quotes and join them with commas and newlines"?
- jasonhansel 4y ago
  Escaping.
  - mgaunard 4y ago
    isn't that covered by wrapping in double quotes?
    - zerocrates 4y ago
      Not if the content contains a double quote
      - mgaunard 4y ago
        well it's not really wrapping it if it's not escaped. Regardless it is all trivial. Not sure what the point of that comment was.
LAC-Tech 4y ago
My key take away here is that not spending an hour reviewing code probably man-days worth of work. The technical capabilities are all there on the team, from description. What was probably missing is someone both technical and assertive, who could politely say to the deadline setters "This is fucking stupid and it's not going to work".
Nihilartikel 4y ago
I sling a lot of SQL, and, mirroring a lot of peoples sentiment here, wish it had better syntax and composability. DuckDB and Apache spark expose nice apis that almost completely remove the need to faff around with textual strings. Each projection returns a view that can be treated like another table, so composition and reuse is simple.. It would be nice if such a thing we're more standard and available on the other dbms that I have to work with. I feel like, in the continuum of abstraction, SQL is like opengl 3.. high level and a bit inflexible. Taking the analogy further, an ORM would be like the game engine on top of opengl.. What doesn't exist, as far as I know, is the Vulkan equivalent. A low level, api that exposes the relational algebra and exactly how to execute it. There are cases where I would have saved a lot of effort if I could just write the damned physical plan for a query execution myself rather than rearranging table join orders and sending hints that the query optimizer is just going to passive aggressively ignore anyway.
- aoeusnth1 4y ago
  Don’t TVFs accomplish the level of composability you’re describing? They give exactly a “view that can be treated like another table”. https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions https://cloud.google.com/bigquery/docs/reference/standard-sq...
gsvclass 4y ago
This is more common that you would believe other issues i've seen are no `limit` on the query, fetching all the results and then sorting in your own app code, using wrong joins. Many of these happen while using ORMs as well. SQL is a context switch for more devs and very few understand it and even those that do might not be familiar with the capabilites of your startups db choice. Shameless plug but this was my motivation behind building GraphJin a GraphQL to SQL compiler and it's my single goto force multipler for most projects. https://github.com/dosco/graphjin https://github.com/dosco/graphjin

268 points by subset 4y ago

Jupe 4y ago

Firstly, I'd suggest the author look at this differently; perhaps "For Want of a Code Review". Especially code from a relatively recent graduate, on a piece of code for which the engineer in question has little experience. With that said, the JOIN is a very powerful concept which, unfortunately, has been given a terrible reputation by the NoSQL community. Moving such logic out of the database and into to DB's client is just a waste of IO and computing bandwidth. SQL has been the ONLY technology/language that has stuck with me for > 25 years. The fact that it is (apparently) not being taught by institutions of higher learning is just a shame.

leononame 4y ago
I agree. It took me 3 years or so to actually land in a project and learn SQL for the first time. Before it was all with ORMs. I didn't know what a join was for the first couple of years of my career. Understanding SQL and being able to work with data interactively has made me a better software engineer. This tech is important enough that it should be taught in university/coding camps.
- miiiiiike 4y ago
  You DIDN’T learn SQL in school? Probably my most useful class. I hated it at the time, I was a desktop and embedded dev, and this was before SQLlite roamed the earth.
  - rjbwork 4y ago
    >Probably my most useful class. Ditto. My teacher was hardcore. He was a graybeard who was around before Codd's now famous paper. He worked with some of the old pre-relational hierarchical databases. We had to take SQL queries, turn them into relational calculus and algebra, turn that into a query plan, then come up with an estimate for the time the query would take to run given various hardware speed numbers and the size of the data. We had to implement our own (primitive!) database engines, including various join algorithms. To date it's one of the hardest, yet most rewarding, learning experiences I've had.
    - miiiiiike 4y ago
      That.. Sounds graduate-level. How's your PhD?
      - avidphantasm 4y ago
        That sort of course was very much par for undergrad courses at CMU when I was taking CS classes there 25 years ago. The OS course was very intense. I wasn’t a major and didn’t have time to take it, but my networks course was of similar rigor (i.e., implement a toy TCP/IP stack).
      - rjbwork 4y ago
        I took it during summer, and there were some masters and PhD students in there, but I took it as an undergrad course. It was very intense, 3 hours per day, 3 days per week, and 1.5 hours per day the other two.
        miiiiiike 4y ago
        Man, I got downvoted into oblivion for my comment, but seriously, yeah, graduate level. That sounds fun. My most intense undergrad class series was one where we soldered together a M68HC11 computer and learned assembly one class, built an OS for it the next, and then turned it into a robot with control software running on our OS-es.
      - miiiiiike 4y ago
        This was obviously was a joke. Sounds like there's a range in SQL training that goes from "I've heard of SQL" to "I implemented a PostgreSQL compatible db my Sophomore year."
      - Izkata 4y ago
        Not necessarily. One of my undergrad courses (~2008) had us implementing a basic inverted index (think Solr or Elasticsearch), which gave me some insight into Solr that my co-workers didn't have that helped with performance issues. If we had a database course that went that in-depth, I'd've definitely taken it, too.
  - pineconewarrior 4y ago
    I took several classes on it and I still didn't really 'get' it until I had to work on challenging problems in the real world. Granted, my education was not great quality overall.
  - cfeduke 4y ago
    I just enrolled in an online CS degree course. The database class is an elective. Crazy, I know.
  - TheCapn 4y ago
    For me the first time we dabbled with SQL was in a 3rd year Software Engineering course where the focus of the class was a single group project that we managed among ourselves by splitting tasks, conducting code reviews and handling the build and release in teams. I recall one group doing the project login which went much along the lines of what the OP's article touched on. Their code was esseentially var success = false var query = SELECT * FROM users while query.read { if query(user) == input_user && query(password) == input_pass { success = true } } Yes. They selected the entire user table. Yes. They iterated over the entire result (even if first returned result was valid) Yes. That was "shipped" for the project No. My complaints notion they should be leveraging the database for all the things they're doing wrong were ignored. It was performant! Look! It logs in instantly! YEah, because there's 8 users on the database for this project, what about when it ""ships"" and there's 100,000? More? --- My first real job dealing with a database wasn't much better. We were using a MS Access database with no normalized data. Our client's primary transaction data was across a table with 70 some columns, many of which were often duplicated values in some form or utilizing very bad practices. Since joining this company I've sped up queries in almost immeasurable ways and done things my older coworkers initially derided because they couldn't understand the syntax. TL;DR SQL, for some stupid reason, is still treated as second class to core langauges and it is a god damn shame
    - marcus_holmes 4y ago
      > SQL is still treated as second class Agree so much. And if you've ever seen a real SQL wizard in action, you realise how much can be done with it. Like most of the business logic of a system can be in the database, with an interface that's a set of stored procs/functions. And fast.
      - toyg 4y ago
        > most of the business logic of a system can be in the database The problem of this approach is the tooling and lock-in. If databases had first-class versioning support for their code objects (which could easily interoperate with git), testing automation, and a parvence of standardization across the industry, then a lot of people would be very happy to work with that model. But they don't.
  - stephenhuey 4y ago
    When I was at Rice a couple decades ago, the database class was a 400-level class in which we learned relational algebra and relational calculus before SQL. The professor must have been good at teaching because I loved learning the formal underpinnings even though my memory of them has faded, but I do recall that I went from zero SQL knowledge to being very excited by its power. So many of my CS classes were very theoretical, and even though we learned some theory in the database class, it was definitely one of the single most (the single most?) pragmatic & practical of all the CS classes I had. I was so zealous about normal forms that I complained loudly at one job where they used an old D3 database with multivalue fields. It was so glaring to me because we actually used all hand-rolled SQL instead of an ORM in those days. Years later, after growing less tech-centric and more thoughtful of business needs, I realized that sparingly using multivalue fields was not a hill to die on. :) Fast forward many years to my first startup in Boston. Google App Engine was new and I wasted precious time trying to figure out how to shoehorn a typical relational data model into the early NoSQL data store available for App Engine at the time. This was just after the financial crisis and I hadn't yet heard the mantra to pick boring technologies, and I learned through sheer pain that unless you really really really need to, don't waste effort by walking away from relational databases. And also, most apps can get by with whatever the ORM does and if there's a performance issue, optimize that one query instead of trying to optimize all your SQL from the beginning. There's a lot I still don't know about pushing heavily complex queries down to the db level, but for expensive problems I'd reach for expensive assistance, because it's worth it (after trying to play with the SQL myself).
- tracker1 4y ago
  Agreed... ORMs can be nice, but one should understand how it works. I'm a pretty big proponent of simple mappers (Dapper for .Net, template literals for JS/TS) with straight SQL over ORMs at this point.
maratc 4y ago
I was at a place that used sharding, so the data was scattered across 128 database servers. SELECT works there but JOIN doesn’t, as your right side may reside at another shard.
- Jupe 4y ago
  IMO... If the query is for OLAP the data may need to be extracted to another data store. If the query is for OLTP, then the design is wrong. I don't know your problem space, but pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea.
  - paulmd 4y ago
    > If the query is for OLTP, then the design is wrong. I don't know your problem space, but pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea. well, that's the basic idea of microservices lol. forget living on a different shard, lots of times your data is going to round-trip to JSON and back a couple times and then be manually joined in some backend/service layer, or in graphql! one bad abstraction I see a lot from microservice teams (that don't really understand it past the high-level concept) is "every table is a service", or "every minimal set of tables and its codeset is a service" and that's exactly how that ends up. Microservices really ought to be chunky enough to do their business without ending up calling 27 different services under the hood just to do simple operations. Obviously there is a point where it's too chunky, but too micro is also bad too.
  - maratc 4y ago
    > pulling data from 128 shards to resolve queries while a user is waiting is just a really bad idea. To the contrary, pulling data from 128 shards can be done in parallel, and about 127 of them don't have any data to return.
- higeorge13 4y ago
  If you had to do joins on different shards, then you have implemented sharding wrong.
- 4y ago
jjice 4y ago
Our SQL course in uni left a lot to be desired. Very little time spent on join, much more on subqueries, oddly. My first job our of school there was a SQL portion and they were impressed by my overuse of subqueries. Best SQL I learned was on the first few months in a real database with real information, instead of a student-courses mock DB with 15 rows that seems to be the academic standard for teaching.
- tracker1 4y ago
  I'm frankly surprised that some of the larger MS based data sets aren't more standard for learning. MS SQL Server isn't generally my first choice (preferring PostgreSQL for standards and portability), but it's got some pretty great example data out there.
Beltiras 4y ago
Oh it's taught. I have a bone to pick with how. I'd rather have spent a lot of time on the practical application of SQL than the theoretical background of column and table operations.
lultimouomo 4y ago
> Firstly, I'd suggest the author look at this differently; perhaps "For Want of a Code Review". Especially code from a relatively recent graduate, on a piece of code for which the engineer in question has little experience. I assume the story is made up, but if we were to take it at face value the title would be "For Want of Basic Human Decency"; the author is saying that they saw this whole easily preventable train wreck happen in slow motion and did not lift a finger to prevent it, instead laughing, taking notes and thinking of the fabulous snarky blog bost that would have come out of it.
- axus 4y ago
  The way I read it, they accepted the decisions of those higher in the hierarchy, after providing their feedback. It wasn't clear if lots of money was lost, just lots of time. I didn't think it was made up.
- nisegami 4y ago
  Eventually you learn it's not always a great idea to stop your employer from getting burned. Some people don't learn until it hurts.
- Epskampie 4y ago
  From the article: > I’d definitely commented on the JOIN statements during the initial code review
  - boxed 4y ago
    How about when it became a problem? Or the second time? The third?

yyyk 4y ago

The worst part is not the missing JOIN. This happens, especially with juniors. It's the 'all signup errors warranted paging the on-call even on 4am' bureaucratic decision followed by being unable to apply any fix quickly. No surprise the author did not stay.

weego 4y ago
The worst part is more senior devs being happy to watch it happen and even accept the commits, and then write a long winded story of how this 'car crash unfolded' apparently unaware that they're a tacitly active contributor to it happening and then escalating out of control. If you're going to be aper of throwing junior devs under the bus, at least have the self awareness not to brag about it on the itnernet.

Thaxll 4y ago

The other problem with the double join is that it's not an atomic operation so between the two select data could have changed.

deleted 4y ago
[deleted]
ttfkam 4y ago
I really wish more people understood this. Thank you. ACID is not just a good time on a Saturday night.

deleted 4y ago

[deleted]

funstuff007 4y ago

> With the exception of NPM modules, most tools are designed to solve problems, possibly the ones you have Upvoted just because of the chuckle this gave me.

btown 4y ago

As someone who’s primarily worked with monoliths, I often wonder how often this exact problem happens, but where A and B are [micro]services owned by two different teams, one is required by company policy to use their APIs not their raw databases, and escalation of each of these issues e.g. query size/rate limiting runs the risk of burning political capital on top of everything else. How does one JOIN across not just tables but opaque services, in the general case? Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly?

pwg 4y ago
> Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly? What tends to be by far more common is that each team fails to envision that someone, somewhere, sometime in the not so distant future will want or be required to retrieve more than one "element" at a time via their APIs. And so panic ensues when "other entity" begins feeding 20 API retrievals per second at their "one-at-a-time API" and their performance goes off the cliff it was always sitting near.
- NegativeLatency 4y ago
  Extra points if you manage to do it to your own team’s APIs because of bad design and planning
  - tracker1 4y ago
    You mean like 8+ joins to get that single record and then having it bottleneck when you get a few hundred requests a minute? (not bitter at all here)
thatwasunusual 4y ago
> How does one JOIN across not just tables but opaque services, in the general case? You (should) never do that. It's as simple as that. If you create microservices that are atomically depending on each other, you are doing something _extremely_ wrong.

Ayesh 4y ago

That was a fun read, and I loved that little joke with NPM packages. I find SQL, Regular Expressions, DNS, Client-side caching, CORS, TLS, and a few other things to be a MUST when hiring people, because most of the over-engineered crap can be avoided with a little bit of expertise with these. I spend most of my semi-leisure time with some good Regex books and golfing too. Modern databases are amazing. Every few months, I take pleasure and not shy away in refactoring some complex and frequent queries into SQL views, carefully replace data logic (but not business logic) into stored procedures, and replace certain batch scripts with one-off queries.

jjice 4y ago
Favorite regex books? Only one I've read is Mastering Regular Expressions by Jeffrey Fridel and I loved it. If you have any more recs, I'm all ears.
- ttfkam 4y ago
  Excellent book! I was only about 25-30 pages into the first edition when it all finally clicked for me. I've never feared a regex since.
masto 4y ago
I was recently discussing something that involved knowing whether a string consisted of only a single repeated character. Having spent many years in the trenches with Perl, my first thought was /^(.)\1*$|^$/, which is the kind of thing people dismiss as "line noise" because they haven't spent a few minutes learning a language that can easily express what you want. We have this trend now from people who like languages like Go where answering the question "does this string consist of a single repeated character" begins with "I would now like to reserve space for a 64-bit integer which I shall henceforth refer to as 'i'...", and that's considered a virtue.
- cbm-vic-20 4y ago
  Let's see if I remember Perl regexps: '/': this is a regular expression. '^' at the beginning of a line, '(.)' match any character, and remember it for later. '\1' match the same character that you just remembered, '*' zero or more times. '$' then match the end of the line. '|' Or, '^$' match the beginning and end of the line with nothing in between.

rubyist5eva 4y ago

This article speaks to me. So many times I have needed to go back and fix queries that were naively written this way like it was some kind of "optimization". There is no difference in effort between writing a join or doing the ORM-double-round-trip in the vast majority of cases. People are so afraid of doing joins I see people doing subqueries with the id in a subselect because "joins are slow". The worst is usually some kind of pseudo-join and then an aggregate or filtering in the application code. It drives me up the wall when I see it in code review, usually because I get into some argument about "joins are slow" (with no evidence) and then I have to go and rewrite the query and maybe add an index to show that, yes - an aggregate that takes seconds and a ton of memory in the application code can in fact take milliseconds in the database. The NoSQL people have really done a lot of brain-damage to this industry. It's so pervasive that I've starting using this kind of question in our technical interviews, doing a double round-trip ends the interview for anyone higher than a junior.

giovannibonetti 4y ago
Here is some actionable advice for speeding up a join with indexes. You'll need one in each table with the join columns. For example: SELECT ... FROM table_A JOIN table_B ON table_A.column_A1 = table_B.column_B1 AND table_A.column_A2 = table_B.column_B2 You can add indexes like this: - table_A(column_A1, column_A2) - table_B(column_B1, column_B2) If both tables are large enough, this query can probably take advantage of those indexes to perform a merge join. Postgres tip: you can also add columns to the include part of the index to speed up filters in the WHERE conditions. You might even get an index-only scan! Look into covering indexes to learn more about it.
- boredemployee 4y ago
  Not related, but here in my job our query just stopped working because the data got too big. we use left joins everywhere because we don't want to lose the main table data. do you think the trick you just mentioned could optimize our left join as well?
  - 4y ago

bjornsing 4y ago

Shipping it the first time may have made sense. Second time? Not so much.

LudwigNagasena 4y ago

I would call it “for want of reasonable hiring and onboarding processes”. How does someone get into a data engineering job without any knowledge of SQL and doesn’t even get basic onsite training?

brocha 4y ago
Largely due to the fact that "Data Engineer" is defined differently at every company. Some want a SRE, some want a Database Architect, some want a software engineer that knows some SQL, others want only SQL junkies. As a result, I have picked up a variety of skills to fit into whatever my company dictated what a Data Engineer should handle

phamilton 4y ago

I've learned that "a month in the lab saves an hour in the library" usually can be distilled to "A shallow understanding produces complex solutions. A deeper understanding is usually required to create simple solutions." While the original example of not understanding JOIN might just be a lack of of general knowledge, the later steps are great examples of this, especially if someone else comes along and is told to fix the error. Making something execute slow code in parallel is pretty easy to do generically. It doesn't require understanding much about the slow code. It's fairly low risk, you probably won't have to tweak tests, there won't be additional side effects. The major risks will be around error handling and it's easy to turn a blind eye to partial success/failure and leave that as a problem for a future team. You can confidently build the parallel for loop, call the task done and move on. Striving for a deeper understanding requires a lot more effort and a lot more risk. Re-writing the slow code is a lot more risk. All side effects must be accounted for. Tests might have to be re-written. The new implementation might be slower. The new index might confuse the query planner and make unrelated queries slower somehow. It's not just a matter of investing time, it's investing energy/focus and taking on risk. But the result will have comparatively fewer failure modes, it'll be cheaper to operate and less likely to have security implications. I've been in both spots and while I wish I could say we always went with the deeper understanding that wouldn't be an honest statement. But the framing has been really helpful, especially as I work with other execs in the company to prioritize our limited resources.

isoprophlex 4y ago
"Simplicity is the highest form of sophistication"
- hoosieree 4y ago
  "less is more"
  - boffinism 4y ago
    <>>

outsidetheparty 4y ago

I got a chuckle out of "With the exception of NPM modules, most tools are designed to solve problems, possibly the ones you have," but have to agree with some other commenters that a better title for this would have been "for want of a mature code review process"

deltarholamda 4y ago
I have to wonder if the code review process wouldn't be as big a deal if they hired a proper DBA. This problem would not have been an issue if the DBA had been told "we need to do X," and the DBA would craft a stored procedure for X. I get that stored procedures aren't a cure-all, and sure, they can get out of hand, but doing this stuff in code is often worse than letting the DB do its job.

data-ottawa 4y ago

In the spirit of HN I should confess I’ve done exactly this, not the using Python to feed data back to SQL, but writing terrible hacks to get around resource limits on deadlines. On a recent project I needed to process a couple years of data for a hard deadline of Monday, and it was Friday. Our DB had a query timeout and a resource memory limit which blocked doing the full analysis without building new data models which would take days to get shipped and to build the new data models. The deadline couldn’t be moved so hacks were needed. The solution: write some Python code to generate one query per week of data going back two years (over 100 queries), save the results to individual scratch tables, and then use a second query to union all the results together in our BI tool. Of course the first time I ran it serially it was too slow, so I parallelized it. That was too many queries so I added a limit. Then one query failure broke the whole thing so I added retries… by the end of the day it looked exactly like this article. It worked though! I got all the data we needed processed for Monday, I presented it to our execs and our project was approved. We only needed to manually run that script once more before I built the real solution and deleted the script.

mijoharas 4y ago
This reminds me of some log parsing code I wrote that had to batch download sets of logs from our log provider, and then used some gnu parallel, jq, and some general shell tools to spit things out (may have thrown the data into some format and used textql for the end of it.) (I can't recall why the general log searching tools we had didn't work in this situation, I think it was because I needed to get data from a lot of disparate logs at once, or it was driven by having to make lots of separate downloads of the logs.)

jeffreygoesto 4y ago

"If you encounter an unusually round system limit, you’re probably using the system in a way its designers never imagined." Haha, so true. We triggered a static code analyzer error "Cyclomatic Complexity bigger than 1.000.000.000!". The vendor was very interested in that code snippet (generated classifier code) and we shared a good laugh.

Ensorceled 4y ago

We hired a data engineering consulting company and none of their team of SQL experts had heard of upsert or merge. I find it weird that people don't spend a bit of time searching for a better way of doing stuff before just jumping into a long, hard way of doing things.

higeorge13 4y ago
Unfortunately many data engineers don’t know basic stuff about sql snd databases, but are experts in etl tools and data warehouses where such features are not relevant or don’t exist. You were probably looking for some dba who are something different.

bayesian_horse 4y ago

An SQL query goes into a bar, walks up to two tables and asks: May I join you?

dagss 4y ago

It is one thing when a junior does this because they haven't learned better. It's quite another when experienced seniors ban the use of SQL features because it's not "modern" or there is an architectural principle to ban "business logic" in SQL. In our team we use SQL quite heavily: Process millions of input events, sum them together, produce some output events, repeat -- perfect cases for pushing compute to where the data is, instead of writing a loop in a backend that fetches events and updates projections. Almost every time we interact with other programmers or architects it's an uphill battle to explain this -- "why can't just just put your millions of events into a service bus and write some backend to react to them to update your aggregate". Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute -- instead of a new microservice or whatever and some minutes of compute. People bend over backwards and basically re-implement what the databases does for you in their service mesh. And with events and business logic in SQL we can do simulations, debugging, inspect state at every point with very low effort and without relying on getting logging right in our services (because you know -- doing JOIN in SQL is not modern, but pushing the data to your service logs and joining those to do some debugging is just fine...) I think a lot of blame is with the database vendors. They only targeted some domains and not others, so writing SQL is something of an acquired taste. I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation).

joeblubaugh 4y ago
How easy is it to verify that the configuration of your database matches a checked-in configuration or source file these days? My beef with a lot of installed procedures in a SQL database comes down to deployment and rollback difficulty.
- mh- 4y ago
  Depends on the DBMS, but pt-config-diff works well for MySQL. edit: This will only help for configuration, not procedures. https://docs.percona.com/percona-toolkit/pt-config-diff.html https://docs.percona.com/percona-toolkit/pt-config-diff.html
- Wicher 4y ago

darepublic 4y ago

Yes I remember inheriting a project where in a similar fashion people were allergic to join. So we got js code selecting entire table, looping over the rows and then doing inner loops with further selects. I eventually had to switch everything around to using joins. There seemed to be a huge disdain for SQL.. like if you ever endeavoured to try some raw SQL you were playing with matches. Sure OK but code that is handling db operations that inefficiently is 100x worse tho...

tracker1 4y ago
I tend to push for the other direction, especially if using JS/TS... template literals are so useful here... const foo: MyType[] = await db.query` SELECT ... FROM ... WHERE bar = ${baz} `; And simply understanding how the queries work... very similar with Dapper in C#... I'm kind of all out against ORMs at this point.
- ryanbrunner 4y ago
  Uhhhh, you should be extremely careful with string interpolation around DB statements. The code sample you posted is pretty much a textbook case of a SQL injection vulnerability if the value of ${baz} is ever provided by a user.
  - tracker1 4y ago
    No, it isn't... db.query method recieves the parameters separately from the string parts and will turn it into a parameterized query. You're confusing/conflating db.query`...` with db.query(``); https://www.javascript.christmas/2020/11 https://www.javascript.christmas/2020/11
    - ryanbrunner 4y ago
      Ah, sorry! I haven't used templates much and an interpolation inside a string triggered my "oh shit security problem" sense.

nightpool 4y ago

I'm not sure I understand how a JOIN would have fixed this problem. That is, if each chunk is fetching 1k rows, and you're doing 50 simultaneous chunks, then you're doing a 50,000 row query, and that's ALSO going to be extremely slow, in terms of exclusive database contention (less of an issue with bigquery) and result set memory usage (definitely still a huge issue for python). In fact, one of my most frequent pieces of feedback to junior engineers who are just working on a larger backend for the first time is "this query tries to fetch too much data at once, it will take too long and use way more memory then it needs to, please use find_each to automatically batch the query so that we balance memory usage and database contention". Indeed, Rails by default will use the exact same batching strategy the junior engineer chose in this case: fetch 1k items, process those items, and move on to the next 1k items. I understand that the author rankles about things not being done the "right way" with JOIN, but I question whether their focus on "best practices" is preventing them from seeing the optimization forest (split things up into parallel background tasks, don't try to keep the entire dataset in memory at once) for the "doing things right" trees (use JOIN)

civilized 4y ago
Two clear inefficiencies in the junior's code: 1. It is pushing the entire id table back and forth through the network connection, bit by bit. Replacing with a join completely eliminates this. 2. A query with an IN clause is (probably) doing a hash join under the hood to calculate the result of the IN clause. So the junior's code is effectively submitting a join query over and over, each time with a slightly different tiny chunk of data, rather than asking for the joined data once and processing the result in batches. It is also worth considering if the entire data processing pipeline can be in SQL, but I can't tell if that's the case from the blog post.
HelloNurse 4y ago
Do you know what's slower than fetching 50000 rows with a big join? Fetching them with the overhead of thousands of tiny queries instead of one, and repeating disk reads thousands of times because you cannot consolidate the thousands of query executions.
- 4y ago

pier25 4y ago

The aversion to SQL by younger devs is pretty amazing. Yes it has a weird cognitive model and a learning curve but it's a cornerstone of web dev. Instead they resort to convoluted and technically inferior solutions like Prisma just because of a superficial DX advantage. I'm certain Mongo only became popular because of this even though for many years it was crap. That said I do think we need a better SQL. It's still not there but EdgeDB looks very promising.

CodesInChaos 4y ago
Mongodb is very convenient for CRUD operations, while relational databases need a complex ORM to handle that sanely. Consider how much CRUD a typical application contains, I certainly get the appeal. However aggregatipn framework is horrible and thr 16MB limitation very annoying. Im general I find SQL/relational models easy to understand conceptually, but maps badly to both the rest of the application and the problem domain. I also hope that edgedb will help with that. When I modeled one of my applications in its SDL it was a very clean match. I don't have much experience with its query languange. But so far it looks much nicer than SQL, but still uglier than functional programming.
- ttfkam 4y ago
  Those who fail to learn the lessons of SQL are doomed to re-implement it… poorly. SQL-92 is no longer the baseline. Now we have CTEs, laterals, graph queries, JSON storage, system versioning, and more within a reasonably concise DSL for set theory. And even more showing up all the time like function pipelines. https://docs.timescale.com/timescaledb/latest/how-to-guides/hyperfunctions/function-pipelines/ https://docs.timescale.com/timescaledb/latest/how-to-guides/... SQL is the Rodney Dangerfield of programming languages. It don't get no respect!
  - setr 4y ago
    My main issue is: 1. The ORMs are really not updated to utilize these useful features, largely because they try to support all the databases, and end up supporting really just the ANSI standard set of features, with maybe a few extensions. 2. Without the ORM, you're writing SQL-as-string, and its as demented and awful as one would expect of writing all your code inside a random string. It doesn't help that all SQL features are made inconsistent language-wise, so you're basically guaranteed to have at least one syntax error using anything novel (with an utterly useless error message from your favorite SQL compiler), which can only be caught at runtime (because your IDE's DB linting and language support is also limited to mostly ANSI SQL, because they also try to support all the databases). 3. If you make it a stored proc/function/view, the DB IDE tooling is shit across the board. They're all miles behind any decent app-lang IDE in terms of features/tooling they provide. It's actually impressive how pathetic an environment DBA's put up with. You're really not going to get much more support than an autocomplete on table/column names, and maybe datatypes. So ultimately, the act of writing SQL is terrible compared to doing your normal app-logic. The only reason I'm willing to put up with it is because the positives of using an RDBMS properly dramatically outweighs the negatives. But that tradeoff isn't immediately visible to the novice, so this absurdity of reimplementing SQL with not-SQL becomes reasonable. The engine is beautiful. The relational algebra -- glorious. The language, tooling and ecosystem? It's all stuck in the 80s.

johnthuss 4y ago

"Don’t let junior SWEs get 2000 lines into a change before submitting a pull request." This is good advice. Share your code early and often so you can get feedback before you're fully committed to one approach.

feoren 4y ago
Yeah, this is insane to me. 2000 lines written well in an expressive programming language is a small but complete library. It's 15 to 30 code files. 2000 lines is just about enough to write a complete spelling and word-use checker with a persistence layer. It's enough to write a generic mass-balance process simulator, or a web security library, or a moderately complex workflow-management library, or a toy game. 2000 lines is a week or two of work. If your junior SWE is writing 2000 lines of code before anyone looks at it, you're basically letting them be raised by wolves.

tomerbd 4y ago

Wait until he hears about LEFT JOIN

im3w1l 4y ago

I'm gonna be the contrarian and say this is mostly fine. We can research the proper way to do things, or use code review to teach about the proper ways. But this can lead to code shaming and a fearful environment where people second guess themselves and spend a lot of time chasing a perfection that doesn't move the business metrics. In this case, doing the join manually isn't a huge deal, chunking isn't a huge deal, parallel requests isn't a huge deal. But "concurrent limit reached" is the point in this story where Bob should have put on the thinking cap and reasoned that "this shouldn't be hard, other people do things like this with bigger datasets all the time, I wonder how". Before that point it's literally just a matter of changing a couple lines to solve the issue. So what? After that point however, it's starting to affect the overall design around it in harmful ways, and turning the issue into a bigger one.

mgaunard 4y ago
That just means he did the parallelization incorrectly, there should be a fixed maximum number of concurrent requests.
int_19h 4y ago
And that's how we end up with chat apps that consume gigabytes of RAM, and blinking cursors using one whole CPU core.

jmull 4y ago

This doesn't really add up to me. The article explains how the original bad code gets checked in which seems plausible enough. But that doesn't explain why the first fix wasn't to just start using a JOIN? Or the second fix. I guess it's a made up story, to make a point? Anyway, I found the plot holes distracting.

acdha 4y ago

Back in the 90s, I remember getting a project from a local F500 company. Our design team had been doing some work for them and they'd been happy with the results so when they had problems on a backend project which was over a year behind schedule they asked if we could help & I was pulled in. The project was a fairly straight forward product selector for industrial equipment but the team from a large consulting firm which had been working on it was struggling with performance & hadn't completed most of the features. The client was saying it was unacceptable that pages would take 5 or more minutes to load and they weren't going to drop $500K on bigger servers like the developers were swearing were necessary to run the site. I knew something was off performance-wise since the entire product catalog was only on the order of tens of thousands of records. As soon as I looked at the source code, the mystery was explained: they had allegedly experienced 3 developers working on it but none of them knew about SQL WHERE constraints! Instead, they were doing nested for loops to repeatedly retrieve every row of every table and doing the equality checks in VBScript. Finishing the rest of the project backlog took me a couple of days and the customer was quite happy that the slowest pages were now measured in hundreds of milliseconds rather than tens of minutes. I was proud of how quickly we were able to turn that project around but the PM & I were discussing how even our rush rate wasn't enough to get us anywhere close to the amount of money the previous contractors had charged.

phendrenad2 4y ago

Ragequitting a company and calling it a "trainwreck" because one developer didn't know about JOIN seems... extreme.

Arwill 4y ago

The principle is that you should be using the underlying API if something is already solved on a lower level, and not replicate the functionality on a higher level, because it will perform poorly. There was a reason why the lower level API exists in the first place. This applies to graphics programming very well, its not a question that you wouldn't be making your own pixel rasterizer instead of using DX, OpenGL or Vulkan, for example. The big recognition is that when doing business apps, SQL database functionality is the underlying API, and you should prefer using that.

nonethewiser 4y ago
> The principle is that you should be using the underlying API if something is already solved on a lower level I think you're making a great point but I want to consider what this suggests about ORM's. Using an ORM means you're not directly using the underlying API. In theory an ORM should be a very small "distance" from the underlying API. When that's the case, they are a no-brainer. But no ORM has 100% feature parity and for more complex queries this distance from the underlying API can grow considerably. And if you insist on ONLY using the ORM API then you're going to find yourself doing some pretty dumb shit in the application layer. Personally I think ORM's are great, but there is this common problem of over-insisting on their API and treating raw SQL as the devil.

icedchai 4y ago

I've seen systems where people are doing manual JOINs with CSV, JSON, and the results of DynamoDB scans on relatively tiny datasets (<10 megs.) Everything could fit in sqlite on a single machine. Instead, they build a Rube Goldberg contraption that uses "modern cloud architecture."

ivanhoe 4y ago

Back in the days of Mysql w/ MyISAM engine it was sometimes way faster on big data-sets and underpowered DB servers to do the query exactly this way. Even with all the correct indices in place the JOINs (especially if more than one table was in game) would often just freeze the server for 15-20 minutes, while joining data at the app level in the for loop and with the lookup tables for id-s would typically take only a few seconds. Obviously this is an obsolete hack for long time now...

butlerm 4y ago
I can imagine that, but MySQL didn't really start to become a competitive relational database until the 3.23 timeframe (around 2000), and it is hard to imagine MyISAM (with no actual transaction support) being used for a production database except under very carefully controlled circumstances. To some degree that was true of a lot of earlier competing databases as well, which tended to take escalating locks on everything from the page level on up just to implement basic read consistency. So any transaction of any type could easily lock up a random set of unrelated rows if not entire tables until completion.

brightball 4y ago

That's an excellent description of Contagion caused by tech debt. How much does this problem grow and spread the longer it goes unfixed?

tantaman 4y ago

This is an incredibly common thing. The worst I've seen it is when people drop their SQL DB for a No-SQL thing (for no good reason) and then end up implementing all the joins they lost in the application :(

tmp60beb0ed 4y ago

> Don’t let junior SWEs get 2000 lines into a change before submitting a pull request. Why junior SWEs and not all SWEs?

jmartrican 4y ago

We've all been Bob at least once in our life. By 'we' I really mean me.

mgaunard 4y ago

how is CSV not "wrap the fields in double quotes and join them with commas and newlines"?

jasonhansel 4y ago
Escaping.
- mgaunard 4y ago
  isn't that covered by wrapping in double quotes?
  - zerocrates 4y ago
    Not if the content contains a double quote
    - mgaunard 4y ago
      well it's not really wrapping it if it's not escaped. Regardless it is all trivial. Not sure what the point of that comment was.

LAC-Tech 4y ago

My key take away here is that not spending an hour reviewing code probably man-days worth of work. The technical capabilities are all there on the team, from description. What was probably missing is someone both technical and assertive, who could politely say to the deadline setters "This is fucking stupid and it's not going to work".

Nihilartikel 4y ago

I sling a lot of SQL, and, mirroring a lot of peoples sentiment here, wish it had better syntax and composability. DuckDB and Apache spark expose nice apis that almost completely remove the need to faff around with textual strings. Each projection returns a view that can be treated like another table, so composition and reuse is simple.. It would be nice if such a thing we're more standard and available on the other dbms that I have to work with. I feel like, in the continuum of abstraction, SQL is like opengl 3.. high level and a bit inflexible. Taking the analogy further, an ORM would be like the game engine on top of opengl.. What doesn't exist, as far as I know, is the Vulkan equivalent. A low level, api that exposes the relational algebra and exactly how to execute it. There are cases where I would have saved a lot of effort if I could just write the damned physical plan for a query execution myself rather than rearranging table join orders and sending hints that the query optimizer is just going to passive aggressively ignore anyway.

aoeusnth1 4y ago
Don’t TVFs accomplish the level of composability you’re describing? They give exactly a “view that can be treated like another table”. https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions https://cloud.google.com/bigquery/docs/reference/standard-sq...

gsvclass 4y ago

This is more common that you would believe other issues i've seen are no `limit` on the query, fetching all the results and then sorting in your own app code, using wrong joins. Many of these happen while using ORMs as well. SQL is a context switch for more devs and very few understand it and even those that do might not be familiar with the capabilites of your startups db choice. Shameless plug but this was my motivation behind building GraphJin a GraphQL to SQL compiler and it's my single goto force multipler for most projects. https://github.com/dosco/graphjin https://github.com/dosco/graphjin