5 ms·

> Seven-table joins. Ugh. What? That's what relationship databases are for. And seven is nothing. Properly indexed, that's probably super-super-fast. This is

by gmjoe 13y ago

> Seven-table joins. Ugh. What? That's what relationship databases are for. And seven is nothing. Properly indexed, that's probably super-super-fast. This is the equivalent of a C programmer saying "dereferencing a pointer, ugh". Or a PHP programmer saying "associative arrays, ugh". I think this attitude comes from a similar place as JavaScript-hate. A lot of people have to write JavaScript, but aren't good at JavaScript, so they don't take time to learn the language, and then when it doesn't do what they expect or fit their preconceived notions, they blame it for being a crappy language, when it's really just their own lack of investment. Likewise, I'm amazed at people who hate relational databases or joins, because they never bothered to learn SQL and how indexes work and how joins work, discover that their badly-written query is slow and CPU-hogging, and then blame relational databases, when it's really just their own lack of experience. Joins are good, people. They're the whole point of relational databases. But they're like pointers -- very powerful, but you need to use them properly. (Their only negative is that they don't scale beyond a single database server, but given database server capabilities these days, you'll be very lucky to ever run into this limitation, for most products.)

pekk 13y ago
When program errors pass silently, that is a legitimate problem in the toolchain.
achy 13y ago
I agree with you to a point. Joins are your friend. But trying to pull out all of the information about a graph of 'objects' using a single query with multiple one-to-many and many-to-many joins is just as foolish in SQL as in Mongo.
mkoryak 13y ago
This article ends up agreeing with you at the end, by the way.
_pferreir_ 13y ago
> A lot of people have to write JavaScript, but aren't good at JavaScript, [...] they blame it for being a crappy language, when it's really just their own lack of investment. I think it's pretty much an accepted fact that JS has its problems. Even Brendan Eich has been quoted as admitting it. (Note: I am a JS developer myself)
- rictic 13y ago
  This is true, but the "wtf js is such a fucked up language" meme is outsized compared to the actual problems of javascript. Having worked full time in python for a couple years I could easily show you just as many weird python semantics that will inevitably bite you[1]. I think the grandparent's point has merit, that people expect to invest in their primary language for a project, but when circumstances dictate that they need to use a bit of javascript they find it annoying. 1] What does this program do? print object() > object()
  - raverbashing 13y ago
    About your code snippet, it prints a "random" boolean value You're creating two objects (with random addresses, which affect the __str__ method result, which in turn result in a string comparison that returns False or True)
  - aduitsis 13y ago
    That's nothing compared to the "Perl is Satan!!1" meme that us Perl programmers usually have to put up with ;)
    - beat 13y ago
      Actually, Perl is many different Satans, depending on the particular stylistic quirks of the programmer in question.
      - berntb 13y ago
        Not so much with the usual coding standards (see "Best Practices" book etc).
      - clem 13y ago
        TMTOWTDI: There's more than one way to damn it!
    - merfakos 13y ago
      IMHO, it's well deserved. Start by stop having those $, @, % identifiers for variables and then we 'll talk again about how many more daemons you need to impale.
bowlofpetunias 13y ago
People hate joins because at some point they get in the way of scaling, and getting past that is a huge pain. Or at least, that's where the original join-hate comes from. In reality of course, most of us don't have that problem, never had and never will, and it's just being parroted as an excuse for not bothering to understand RDMS's. Relational database design is a highly undervalued skill outside the enterprise IT world. Many of the best programmers I've worked with couldn't design a proper database if their lives depended on it.
- rosser 13y ago
  People hate joins because at some point they get in the way of scaling... No, in fact, they don't. Poor relational modeling gets in the way of scaling, and that can be geometrically exacerbated by JOINs. A JOIN, in and of itself, is neither good nor bad. It's just a tool, and like all tools, how you use it is what makes it "good" or "bad" — just like you can build a house or bash in a skull with a hammer.
  - parasubvert 13y ago
    In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site. A time series data warehouse could go into the billions of rows with scalable joins with partitioning and bitmap indices ... but is also only applicable in the unlikely case you could afford oracle at $60-90k/CPU list price Also, most databases that aren't Oracle don't have high performance materialized views to "preprocess" joins at upsert time, therefore people resort to demoralized tables and their own custom approach to materializing those views. Then even denormalized tables begin to stop scaling at around 250 million to 500 million rows. So people resort to sharding managed in a custom way. I haven't even begun to express the scalability impacts of millions of users on a LRU buffer cache used in most RDBMS - that usually is resolved through an in-memory cache (Memcached, Redis) whose coherency is also managed in a custom manner. Or you could spend $$$ for Coherence, Gigaspaces, Gemfire, etc. but that's also unlikely in most web companies. At the end of all this, even if you bought a cache, you wonder why you're using an RDBMS at all since you're so constrained in your administrative approaches. Cue NoSQL. of course in practice many devs ignore all of this history and "design by resume" assuming their new social-mobile-chat-photo-conbobulator will be at Facebook scale tomorrow.
chao- 13y ago
Do you have any resources you would recommend to understand or at least give an overview of indexes? I learned basic SQL once-upon-a-time and understand the relational algebra side of things, but only truly picked up the finer details and specific engines in piecemeal manner, as needed in various projects.
- daigoba66 13y ago
  http://use-the-index-luke.com/ http://use-the-index-luke.com/ is a great online book (free) targeted at programmers and developers. It's practically required reading in my opinion.
- jmulho 13y ago
  http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721 http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexi...
- Negitivefrags 13y ago
  http://use-the-index-luke.com/ http://use-the-index-luke.com/ This is a really good resource for understanding how the queries you do relate to the actual actions that the database engine takes.
Keyframe 13y ago
Star, constellation, snowflake, flat.. Developers (not the author) would benefit from database introductory course even if they are not using databases. I think Stanford did one that was open to everyone.
schrodinger 13y ago
7 isn't necessarily nothing. Each join is O(log(n)), so I believe you're stuck with O(log(n)^7) as a worst case, although in practice it will probably not be so bad since one of the joins will probably limit the result set significantly. The other problem is that with 7 joins, that's 7! permutations of possible orders in which the database can perform the join. That's a lot of combinations, and often you can run into the optimizer picking a poor plan. Sometimes it picks a good plan initially, and then as your data set changes it can choose a different, suboptimal plan. This leads to unpredictable performance. I think that in practice, you're best off sticking with only a few joins...
- jmelloy 13y ago
  If you're regularly doing 7 joins it's a good sign of an over normalized databased.
  - jacques_chester 13y ago
    Nonsense. It very much depends on the problem domain.

by gmjoe 13y ago

pekk 13y ago

When program errors pass silently, that is a legitimate problem in the toolchain.

achy 13y ago

I agree with you to a point. Joins are your friend. But trying to pull out all of the information about a graph of 'objects' using a single query with multiple one-to-many and many-to-many joins is just as foolish in SQL as in Mongo.

mkoryak 13y ago

This article ends up agreeing with you at the end, by the way.

_pferreir_ 13y ago

> A lot of people have to write JavaScript, but aren't good at JavaScript, [...] they blame it for being a crappy language, when it's really just their own lack of investment. I think it's pretty much an accepted fact that JS has its problems. Even Brendan Eich has been quoted as admitting it. (Note: I am a JS developer myself)

rictic 13y ago
This is true, but the "wtf js is such a fucked up language" meme is outsized compared to the actual problems of javascript. Having worked full time in python for a couple years I could easily show you just as many weird python semantics that will inevitably bite you[1]. I think the grandparent's point has merit, that people expect to invest in their primary language for a project, but when circumstances dictate that they need to use a bit of javascript they find it annoying. 1] What does this program do? print object() > object()
- raverbashing 13y ago
  About your code snippet, it prints a "random" boolean value You're creating two objects (with random addresses, which affect the __str__ method result, which in turn result in a string comparison that returns False or True)
- aduitsis 13y ago
  That's nothing compared to the "Perl is Satan!!1" meme that us Perl programmers usually have to put up with ;)
  - beat 13y ago
    Actually, Perl is many different Satans, depending on the particular stylistic quirks of the programmer in question.
    - berntb 13y ago
      Not so much with the usual coding standards (see "Best Practices" book etc).
    - clem 13y ago
      TMTOWTDI: There's more than one way to damn it!
  - merfakos 13y ago
    IMHO, it's well deserved. Start by stop having those $, @, % identifiers for variables and then we 'll talk again about how many more daemons you need to impale.

bowlofpetunias 13y ago

People hate joins because at some point they get in the way of scaling, and getting past that is a huge pain. Or at least, that's where the original join-hate comes from. In reality of course, most of us don't have that problem, never had and never will, and it's just being parroted as an excuse for not bothering to understand RDMS's. Relational database design is a highly undervalued skill outside the enterprise IT world. Many of the best programmers I've worked with couldn't design a proper database if their lives depended on it.

rosser 13y ago
People hate joins because at some point they get in the way of scaling... No, in fact, they don't. Poor relational modeling gets in the way of scaling, and that can be geometrically exacerbated by JOINs. A JOIN, in and of itself, is neither good nor bad. It's just a tool, and like all tools, how you use it is what makes it "good" or "bad" — just like you can build a house or bash in a skull with a hammer.
- parasubvert 13y ago
  In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site. A time series data warehouse could go into the billions of rows with scalable joins with partitioning and bitmap indices ... but is also only applicable in the unlikely case you could afford oracle at $60-90k/CPU list price Also, most databases that aren't Oracle don't have high performance materialized views to "preprocess" joins at upsert time, therefore people resort to demoralized tables and their own custom approach to materializing those views. Then even denormalized tables begin to stop scaling at around 250 million to 500 million rows. So people resort to sharding managed in a custom way. I haven't even begun to express the scalability impacts of millions of users on a LRU buffer cache used in most RDBMS - that usually is resolved through an in-memory cache (Memcached, Redis) whose coherency is also managed in a custom manner. Or you could spend $$$ for Coherence, Gigaspaces, Gemfire, etc. but that's also unlikely in most web companies. At the end of all this, even if you bought a cache, you wonder why you're using an RDBMS at all since you're so constrained in your administrative approaches. Cue NoSQL. of course in practice many devs ignore all of this history and "design by resume" assuming their new social-mobile-chat-photo-conbobulator will be at Facebook scale tomorrow.

chao- 13y ago

Do you have any resources you would recommend to understand or at least give an overview of indexes? I learned basic SQL once-upon-a-time and understand the relational algebra side of things, but only truly picked up the finer details and specific engines in piecemeal manner, as needed in various projects.

daigoba66 13y ago
http://use-the-index-luke.com/ http://use-the-index-luke.com/ is a great online book (free) targeted at programmers and developers. It's practically required reading in my opinion.
jmulho 13y ago
http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721 http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexi...
Negitivefrags 13y ago
http://use-the-index-luke.com/ http://use-the-index-luke.com/ This is a really good resource for understanding how the queries you do relate to the actual actions that the database engine takes.

Keyframe 13y ago

Star, constellation, snowflake, flat.. Developers (not the author) would benefit from database introductory course even if they are not using databases. I think Stanford did one that was open to everyone.

schrodinger 13y ago

7 isn't necessarily nothing. Each join is O(log(n)), so I believe you're stuck with O(log(n)^7) as a worst case, although in practice it will probably not be so bad since one of the joins will probably limit the result set significantly. The other problem is that with 7 joins, that's 7! permutations of possible orders in which the database can perform the join. That's a lot of combinations, and often you can run into the optimizer picking a poor plan. Sometimes it picks a good plan initially, and then as your data set changes it can choose a different, suboptimal plan. This leads to unpredictable performance. I think that in practice, you're best off sticking with only a few joins...

jmelloy 13y ago
If you're regularly doing 7 joins it's a good sign of an over normalized databased.
- jacques_chester 13y ago
  Nonsense. It very much depends on the problem domain.