4 ms·

The title of the post is throwing people off, because it's actually the opposite. Graph databases like OrientDB and Neo4j "Pre-Join" everything. Using nodes c

The title of the post is throwing people off, because it's actually the opposite. Graph databases like OrientDB and Neo4j "Pre-Join" everything. Using nodes connected by relationships, every single "record" in your database knows what is connected to it without having to do a table or index scan/lookup. It's all pre-joined, so you avoid the pain of having to JOIN dynamically when you run your queries. After years of MS SQL Server, switching to a graph database (I use Neo4j but it's the same concept) requires a mental mode change, but once you get past the "graph epiphany" you'll never want to go back.

bilbo0s 13y ago
This. Graph DBs don't do away with JOINs... they just do the JOIN on INSERT.
- cwyers 13y ago
  Two questions about that. 1) Wouldn't that have roughly the same performance impact on insert/update/etc. as building indexes? Possibly even more, as building an index (without foreign key constraints) only affects one table, whereas this would imply updating all tables with which this table has relationships? 2) Doesn't this require you to know all of the relationships your data has at insert/update, rather than at select?
  - RyanZAG 13y ago
    1) No. It stores the id/address of the 'row' you are linking too, so there is no performance impact on insert/update/etc. 2) Yes, very much so. EDIT: For a bit more clarity on how/why you'd use it. Graph DBs are very good for something like a social network. You would store each person as an object/document and then you could have a simple array of ids for all that person's friends. Then when you retrieve the person from the database, the database can automatically retrieve all of the friends very quickly as there is no need to do further searching - it already knows where all the friends are stored as their id/address is already in the document. In a relational DB, you would have a table of friend->friend and would need to do a one-many join across that table.
    - cwyers 13y ago
      Okay. I'm not sure I understand why that form of linking would be significantly faster than JOINs when you are properly indexing everything (or significantly faster on INSERT/UPDATE, for that matter). To the second, it sounds like if you're willing to give up a substantial amount of the flexibility that SQL gives you, you can achieve performance gains. Isn't that true of any RDBMS, though? If you go through and denormalize heavily, you can make similar tradeoffs (maybe not the exact same ones, so there could be cases where a graph DB would be better, but it's hardly the blanket "joins are bad" that seems to be implied by the original post).
      - gregwebs 13y ago
        Yes, it is true that the major performance gains are for a (commonly used) subset of what is possible in SQL. But you need to keep in mind that you don't have to de-normalize or adopt some sort of design pattern in OrientDB for some use cases you would in SQL, instead you can just model your data.
      - RyanZAG 13y ago
        It's faster, but it's not remarkably faster, no. The main place it becomes faster is deep joins - if you want the friend of the friend of the friend. If using joins that looks like LEFT OUTER JOIN bla a1 LEFT OUTER JOIN bla a2 LEFT OUTER JOIN bla a3 etc. If you've got much experience with SQL you should realize the performance impact of that. So graph dbs are best suited to highly networked problems. Denormalize is something different, you're misunderstanding something I've said. With a graph DB, you still only have 1 copy of each person - you are just storing the 'joins' or ids/addresses of the 'rows' you want to join with ahead of time. I certainly did not say that joins are bad, I was just answering your questions as to how a graph db differs and why it would perform better in certain specific circumstances. If you're in those circumstances then a graph DB is very useful. Another nice part of a graph db is if you have a large collection of different objects in your programming language that all interact with each other. Those interactions can be stored directly in-line and allow for an ORM layer to be more efficient when retrieving the whole collection.
        13y ago
  - gregwebs 13y ago
    1) I think you want to compare apples to apples. Linking up validated primary key relationships should be faster in OrientDB. I am not sure how you handle other types of relaitonships. 2) exactly. The tradeoff is that SQL lets you easily do ad-hoc analysis (which isn't an important use case for a lot of applications). But also in SQL you do have to know about foreign key relationships at insert/update time to validate those.
    - cwyers 13y ago
      That's true for keys, but that really only covers a subset of what JOINs can do -- if I want to use things like dates, times, quantities of things... and frankly, if I want to do something other than JOIN on equal values, say join on greater or less than or between, then that's an entirely different thing altogether.

bilbo0s 13y ago

This. Graph DBs don't do away with JOINs... they just do the JOIN on INSERT.

cwyers 13y ago
Two questions about that. 1) Wouldn't that have roughly the same performance impact on insert/update/etc. as building indexes? Possibly even more, as building an index (without foreign key constraints) only affects one table, whereas this would imply updating all tables with which this table has relationships? 2) Doesn't this require you to know all of the relationships your data has at insert/update, rather than at select?
- RyanZAG 13y ago
  1) No. It stores the id/address of the 'row' you are linking too, so there is no performance impact on insert/update/etc. 2) Yes, very much so. EDIT: For a bit more clarity on how/why you'd use it. Graph DBs are very good for something like a social network. You would store each person as an object/document and then you could have a simple array of ids for all that person's friends. Then when you retrieve the person from the database, the database can automatically retrieve all of the friends very quickly as there is no need to do further searching - it already knows where all the friends are stored as their id/address is already in the document. In a relational DB, you would have a table of friend->friend and would need to do a one-many join across that table.
  - cwyers 13y ago
    Okay. I'm not sure I understand why that form of linking would be significantly faster than JOINs when you are properly indexing everything (or significantly faster on INSERT/UPDATE, for that matter). To the second, it sounds like if you're willing to give up a substantial amount of the flexibility that SQL gives you, you can achieve performance gains. Isn't that true of any RDBMS, though? If you go through and denormalize heavily, you can make similar tradeoffs (maybe not the exact same ones, so there could be cases where a graph DB would be better, but it's hardly the blanket "joins are bad" that seems to be implied by the original post).
    - gregwebs 13y ago
      Yes, it is true that the major performance gains are for a (commonly used) subset of what is possible in SQL. But you need to keep in mind that you don't have to de-normalize or adopt some sort of design pattern in OrientDB for some use cases you would in SQL, instead you can just model your data.
    - RyanZAG 13y ago
      It's faster, but it's not remarkably faster, no. The main place it becomes faster is deep joins - if you want the friend of the friend of the friend. If using joins that looks like LEFT OUTER JOIN bla a1 LEFT OUTER JOIN bla a2 LEFT OUTER JOIN bla a3 etc. If you've got much experience with SQL you should realize the performance impact of that. So graph dbs are best suited to highly networked problems. Denormalize is something different, you're misunderstanding something I've said. With a graph DB, you still only have 1 copy of each person - you are just storing the 'joins' or ids/addresses of the 'rows' you want to join with ahead of time. I certainly did not say that joins are bad, I was just answering your questions as to how a graph db differs and why it would perform better in certain specific circumstances. If you're in those circumstances then a graph DB is very useful. Another nice part of a graph db is if you have a large collection of different objects in your programming language that all interact with each other. Those interactions can be stored directly in-line and allow for an ORM layer to be more efficient when retrieving the whole collection.
      - 13y ago
- gregwebs 13y ago
  1) I think you want to compare apples to apples. Linking up validated primary key relationships should be faster in OrientDB. I am not sure how you handle other types of relaitonships. 2) exactly. The tradeoff is that SQL lets you easily do ad-hoc analysis (which isn't an important use case for a lot of applications). But also in SQL you do have to know about foreign key relationships at insert/update time to validate those.
  - cwyers 13y ago
    That's true for keys, but that really only covers a subset of what JOINs can do -- if I want to use things like dates, times, quantities of things... and frankly, if I want to do something other than JOIN on equal values, say join on greater or less than or between, then that's an entirely different thing altogether.