5 ms·

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 bi

by 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.
  - dragonwriter 13y ago
    > 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 want all friends out to some specified distance n where n > 1, recursive CTEs are a much more sane solution that anything involving n levels of OUTER JOINs. And if you just want specifically friends-of-friends-of-friends by joins, you want three levels of INNER JOINS. > 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. More accurately, graph DBs store addresses (not ids/addresses) while RDBMS store ids (Foreign Keys) of the records you want to join with ahead of time. This means that to get from one record to a related record takes an additional level of indirection in an RDBMS compared to a graph database, but that doing so from many similar structured records to their equivalent linked records is more efficient in an RDBMS. > 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. With a Graph DB instead of an RDBMS, you obviously wouldn't haven a ORM (Object-Relational Mapping) layer in any case. At more than one level out
    - RyanZAG 13y ago
      Recursive CTEs are not what one would traditionally call 'relational' and many relational databases certainly don't have them. Some distributed graph databases do store ids and not addresses - there is no actual requirement that they be addresses, only that they be efficient to retrieve. So not actually more accurate. People still call object-db mappers 'ORMs' even when dealing with document databases such as Google's bigtable or MongoDB. It's just an acronym at this point. [1][2] [1] - http://mongomapper.com/ http://mongomapper.com/ [2] - https://code.google.com/p/mongo-java-orm/ https://code.google.com/p/mongo-java-orm/ EDIT: By 'id' I'm referring to some unique hash or key that can be used to locate a document. By 'address' I'm referring to an actual on-disk location that can be directly loaded. Maybe my definitions clash with yours, I don't think this stuff is formally defined.
mtdewcmu 13y ago
There isn't much you can't do with a modern RDBMS. If you want to denormalize relationships, you can do that. If the queries are too repetitive or hard to write, you can use views or stored procedures. One thing that I find peculiar about RDBMSs is that foreign key relationships are tracked for integrity, but they are not used to make joins easier by automatically inferring the kind of join implied by a foreign key field. But that isn't a limitation on power.
- dragonwriter 13y ago
  > One thing that I find peculiar about RDBMSs is that foreign key relationships are tracked for integrity, but they are not used to make joins easier by automatically inferring the kind of join implied by a foreign key field. Huh? Could you expand on this?
  - mtdewcmu 13y ago
    A foreign key is frequently just an integer. You can create a foreign key constraint that checks that the integer exists in a field in another table, but that information isn't used by the database for anything other than checking that constraint (that I know of). In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. To make the join use the foreign key information, you'd have to invent some new syntactic sugar, so that's a potential valid criticism.
    - dragonwriter 13y ago
      > In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. Okay, I see what you are saying. So, what you'd like is something like NATURAL JOIN that instead of using matching column names to infer join conditions it would use declared FK relations. That would be convenient -- but possible complicated by the possibility of multiple different FKs from one table to another (though the syntax could involve specifying the FK rather than the target table, which would solve that.) e.g., if you had CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS manager, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS hr_rep, name VARCHAR(50), ... ); You could do something like: SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager;

by RyanZAG 13y ago

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.
- dragonwriter 13y ago
  > 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 want all friends out to some specified distance n where n > 1, recursive CTEs are a much more sane solution that anything involving n levels of OUTER JOINs. And if you just want specifically friends-of-friends-of-friends by joins, you want three levels of INNER JOINS. > 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. More accurately, graph DBs store addresses (not ids/addresses) while RDBMS store ids (Foreign Keys) of the records you want to join with ahead of time. This means that to get from one record to a related record takes an additional level of indirection in an RDBMS compared to a graph database, but that doing so from many similar structured records to their equivalent linked records is more efficient in an RDBMS. > 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. With a Graph DB instead of an RDBMS, you obviously wouldn't haven a ORM (Object-Relational Mapping) layer in any case. At more than one level out
  - RyanZAG 13y ago
    Recursive CTEs are not what one would traditionally call 'relational' and many relational databases certainly don't have them. Some distributed graph databases do store ids and not addresses - there is no actual requirement that they be addresses, only that they be efficient to retrieve. So not actually more accurate. People still call object-db mappers 'ORMs' even when dealing with document databases such as Google's bigtable or MongoDB. It's just an acronym at this point. [1][2] [1] - http://mongomapper.com/ http://mongomapper.com/ [2] - https://code.google.com/p/mongo-java-orm/ https://code.google.com/p/mongo-java-orm/ EDIT: By 'id' I'm referring to some unique hash or key that can be used to locate a document. By 'address' I'm referring to an actual on-disk location that can be directly loaded. Maybe my definitions clash with yours, I don't think this stuff is formally defined.

mtdewcmu 13y ago

There isn't much you can't do with a modern RDBMS. If you want to denormalize relationships, you can do that. If the queries are too repetitive or hard to write, you can use views or stored procedures. One thing that I find peculiar about RDBMSs is that foreign key relationships are tracked for integrity, but they are not used to make joins easier by automatically inferring the kind of join implied by a foreign key field. But that isn't a limitation on power.

dragonwriter 13y ago
> One thing that I find peculiar about RDBMSs is that foreign key relationships are tracked for integrity, but they are not used to make joins easier by automatically inferring the kind of join implied by a foreign key field. Huh? Could you expand on this?
- mtdewcmu 13y ago
  A foreign key is frequently just an integer. You can create a foreign key constraint that checks that the integer exists in a field in another table, but that information isn't used by the database for anything other than checking that constraint (that I know of). In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. To make the join use the foreign key information, you'd have to invent some new syntactic sugar, so that's a potential valid criticism.
  - dragonwriter 13y ago
    > In a select query, if you want to resolve that relationship in a join, you have to restate it as if the database knows nothing about it. Okay, I see what you are saying. So, what you'd like is something like NATURAL JOIN that instead of using matching column names to infer join conditions it would use declared FK relations. That would be convenient -- but possible complicated by the possibility of multiple different FKs from one table to another (though the syntax could involve specifying the FK rather than the target table, which would solve that.) e.g., if you had CREATE TABLE emp ( emp_id INTEGER PRIMARY KEY, manager_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS manager, hr_rep_id INTEGER FOREIGN KEY REFERENCES emp JOIN AS hr_rep, name VARCHAR(50), ... ); You could do something like: SELECT emp.name AS "Employee", manager.name AS "Manager" FROM emp NAMED JOIN emp.manager;