4 ms·

I find zero explanation of how to solve performance with a relational model. As I understand the article, it seems to say...just because all the existing datab

I find zero explanation of how to solve performance with a relational model. As I understand the article, it seems to say...just because all the existing databases you have seen suck at performance when normalized doesn't mean normalization can't be fast.

sgeneris 10y ago
If you are looking to solve performance "with a relational model" then you do not understand physical independence and the relational model. This is exactly what the article explains you should not do. You mean DBMSs, not databases. Yes, that's the argument, but it's precisely this kind of lack of understanding that prevents better RDBMSs.
- catnaroek 10y ago
  Exactly. The idea that you have to sacrifice the right abstraction for the sake of performance is preposterous, and can only be explained by lack of imagination. Just because most SQL DBMSes happen to implement relations, foreign keys, aggregates, etc. in a specific way, it doesn't automatically mean that the specifics of these implementations must be elevated to the status of laws of nature.
  - srean 10y ago
    Indeed, but it helps a great deal to give examples that show better ways, or if not working examples, even sketches of credible implementation ideas.
  - sgeneris 10y ago
    SQL DBMS are not relational, so they don't really implement relations (they support bags and NULLs), not all their operations preserve closure, have weak support of relational constraints, of physical and logical independence, I could go on and on. Problem is practitioners confuse RDBMSs with SQL DBMSs and are incapable of seeing what they're missing in terms of practical benefits of the latter.
    - dragonwriter 10y ago
      NULL is part of Codd's articulation of the relational model, even if later theorists have proposed cleaner variants of the model without it.
    - catnaroek 10y ago
      Yeah, these things you mention have always annoyed me. In particular, every relation should have a primary key, possibly consisting of zero attributes. If the primary key consists of zero attributes, the relation must have at most one element, because the primary key (the empty tuple!) determines every other attribute.
deleted 10y ago
[deleted]
julochrobak 10y ago
There are several basic concepts you can apply to improve performance in the RDBMS and still avoid denormalization. For example: * use as many constraints as possible (this helps the query optimizer) * use indexes which bring better performance in your use case (e.g. bitmap join index or even index-organized tables) * apply table/index partitioning * use materialized views as a query result cache
- LordHeini 10y ago
  Or you just avoid all that hassle and have some duplicates. I have never seen a properly denormalized table. In practice you will get a "historically grown" system way to often and doing anything like that will break things. The whole article seems to be quite academic from my personal experience a textbook normalized database is slow beyond belief (i did exactly that once and we had to revert it back).
  - MustardTiger 10y ago
    Or better yet, since you don't care about your data anyways, just don't bother storing it. Infinitely scalable and always blazingly fast. >I have never seen a properly denormalized table Do you mean normalized? There's no such thing as "properly" denormalized, anything that is not normal is denormalized. >The whole article seems to be quite academic from my personal experience a textbook normalized database is slow beyond belief (i did exactly that once and we had to revert it back). I've seen lots of people say that, but then consistently found those same people don't actually know what the normalization rules are, and all they did was create a different denormalized database that happened to have poor performance for the queries they were using.
  - julochrobak 10y ago
    For an existing application I personally prefer changing the index type, partitioning the table or tuning DB parameters first. It's far less risky because you don't need to change a single query and it's transaprent to the application. Sure if you cannot get the desired perfomance by tuning the RDBMS than you need to consider changing the way how the tables are modelled. From my experience, usually normalizing it one step furhter improves the performance, at least for OLTP use cases.

sgeneris 10y ago

If you are looking to solve performance "with a relational model" then you do not understand physical independence and the relational model. This is exactly what the article explains you should not do. You mean DBMSs, not databases. Yes, that's the argument, but it's precisely this kind of lack of understanding that prevents better RDBMSs.

catnaroek 10y ago
Exactly. The idea that you have to sacrifice the right abstraction for the sake of performance is preposterous, and can only be explained by lack of imagination. Just because most SQL DBMSes happen to implement relations, foreign keys, aggregates, etc. in a specific way, it doesn't automatically mean that the specifics of these implementations must be elevated to the status of laws of nature.
- srean 10y ago
  Indeed, but it helps a great deal to give examples that show better ways, or if not working examples, even sketches of credible implementation ideas.
- sgeneris 10y ago
  SQL DBMS are not relational, so they don't really implement relations (they support bags and NULLs), not all their operations preserve closure, have weak support of relational constraints, of physical and logical independence, I could go on and on. Problem is practitioners confuse RDBMSs with SQL DBMSs and are incapable of seeing what they're missing in terms of practical benefits of the latter.
  - dragonwriter 10y ago
    NULL is part of Codd's articulation of the relational model, even if later theorists have proposed cleaner variants of the model without it.
  - catnaroek 10y ago
    Yeah, these things you mention have always annoyed me. In particular, every relation should have a primary key, possibly consisting of zero attributes. If the primary key consists of zero attributes, the relation must have at most one element, because the primary key (the empty tuple!) determines every other attribute.

deleted 10y ago

[deleted]

julochrobak 10y ago

There are several basic concepts you can apply to improve performance in the RDBMS and still avoid denormalization. For example: * use as many constraints as possible (this helps the query optimizer) * use indexes which bring better performance in your use case (e.g. bitmap join index or even index-organized tables) * apply table/index partitioning * use materialized views as a query result cache

LordHeini 10y ago
Or you just avoid all that hassle and have some duplicates. I have never seen a properly denormalized table. In practice you will get a "historically grown" system way to often and doing anything like that will break things. The whole article seems to be quite academic from my personal experience a textbook normalized database is slow beyond belief (i did exactly that once and we had to revert it back).
- MustardTiger 10y ago
  Or better yet, since you don't care about your data anyways, just don't bother storing it. Infinitely scalable and always blazingly fast. >I have never seen a properly denormalized table Do you mean normalized? There's no such thing as "properly" denormalized, anything that is not normal is denormalized. >The whole article seems to be quite academic from my personal experience a textbook normalized database is slow beyond belief (i did exactly that once and we had to revert it back). I've seen lots of people say that, but then consistently found those same people don't actually know what the normalization rules are, and all they did was create a different denormalized database that happened to have poor performance for the queries they were using.
- julochrobak 10y ago
  For an existing application I personally prefer changing the index type, partitioning the table or tuning DB parameters first. It's far less risky because you don't need to change a single query and it's transaprent to the application. Sure if you cannot get the desired perfomance by tuning the RDBMS than you need to consider changing the way how the tables are modelled. From my experience, usually normalizing it one step furhter improves the performance, at least for OLTP use cases.