4 ms·
The site is down for me. Anyway, I don't think database joins are all that important. They make sense when structuring your schema - only store a unique piece
by Swizec 16y ago
The site is down for me.
Anyway, I don't think database joins are all that important. They make sense when structuring your schema - only store a unique piece of data once and then connect other data to it. This is a very sound design choice.
But in light of modern key/value stores, I don't think one needs to perform joins on the database anymore. Sure it's theoretically a bit faster than performing the join in your app, but considering all the other implications and quickly you realise you don't need those.
Consider these two options:
Pull data from a db, it's neatly tied together with all its meta-data. But the query takes almost a second and when the DB is always worried about consistency and stuff so writes are kind of slow too.
Pull data from a db. If you need some related data, you make another query based on the key stored in the first piece of data. Each query takes a fraction of a second, together they don't take as long as the full join query ... and you're only joining stuff when you actually need all of the data.
The added benefit is that in this situation the DB doesn't have to worry about almost anything and is thus much faster.
If that made any sense, I'm preparing a seminar on NoSQL so I'd love to hear some input on my understanding of things :)
- Devilboy 16y agoThe problem is that updates becomes a monster problem if you have lots of connected records, since the same information is duplicated in many places over your data store. Failed updates can easily lead to inconsistencies.
- Swizec 16y agoIt depends on the data of course, but sometimes it might be easier to just create a new entry rather than update an old one :)
- tbrownaw 16y ago> The site is down for me. http://pastebin.com/Dmpyyv8Q http://pastebin.com/Dmpyyv8Q
- nerfhammer 16y ago> But the query takes almost a second I don't think the existence of a join is sufficient to slowness like this > and when the DB is always worried about consistency and stuff so writes are kind of slow too. Has nothing to do with relational data per se. Unless you're using foreign keys and calling that a join (which is actually fair). NoSQL stores typically do lots of work to ensure consistency and many support multi-statement transactions on top of this. Unless we're talking about only keeping data in memory but you can make a relational db do that. > If you need some related data, you make another query based on the key stored in the first piece of data. Each query takes a fraction of a second, together they don't take as long as the full join query That is exactly how a join is executed under the hood. Loop over rows in one table, look up a value in another table. However, the db won't force you to join based on a key. If all you're doing is looking up unique keys there's no way multiple calls will be faster unless all the data won't fit on one machine.
- dstorrs 16y agoDown for me too, so I'm only responding to your comment. >[using a DB join means that] the query takes almost a second This is, at best, an overgeneralization and at worst outright FUD. I don't know what sort of data or database engine you're working with that might make this true, but it's definitely not a requirement that it be this way. The RDBMS backing my site is trivially able to return all needed data in fractions of a second for most cases regardless of the presence of joins. If it can't, that generally just means that I forgot to index something properly. Moving past this, going with a simple key/value store instead of an RDBMS means giving up at least three of the ACID properties in favor of nothing more than a speed gain. - Atomic: No transactions. If the system dies between two related commits...oops, your dataset is inconsistent. - Isolated: No transactions, again. You cannot make guarantees about what information an object will have -- e.g., you cannot say "Every user will always have at least one entry in Pages, because we automatically create a non-deletable Home page for every user at signup." If you rely on that, you will eventually hit a user who is halfway through the signup process and has a user entry but no Page entry. - Durable. If your backing store has not actually flushed your write to disk and the system crashes...oops, you're hosed. I hope you weren't storing your Accounts Receivable data in that NoDB.
- Groxx 16y ago>Moving past this, going with a simple key/value store instead of an RDBMS means giving up at least three of the ACID properties in favor of nothing more than a speed gain. This is, at best, an overgeneralization and at worst outright FUD. >The CouchDB file layout and commitment system features all Atomic Consistent Isolated Durable (ACID) properties. http://couchdb.apache.org/docs/overview.html http://couchdb.apache.org/docs/overview.html (to pick a single counterexample) I have no idea what anti-NoSQL things you've been reading, but they've been wildly incorrect. And as to guarantees of what info an object has, schema-based NoSQL databases exist, just as schema-free RDBMS databases exist. Triggers frequently exist - you can automatically create a non-deletable (permissions exist too) home page for every user at signup. You're entirely correct that DB joins taking a long time is similarly wildly incorrect, though I'm not certain that's what they're claiming. It sounds to me more like they're saying pulling all related info on primary object X in your system takes a long time in a highly-normalized RDBMS compared to a document-based DB where it's all in the same location. Which is frequently correct.