4 ms·
I'm not sure what you mean by "handle the tables internally", but as a really rough exercise, you can think of just raw table reads. If you have 6 tables that
by michaelbuckbee 18y ago
I'm not sure what you mean by "handle the tables internally", but as a really rough exercise, you can think of just raw table reads.
If you have 6 tables that need joined, you're going to need to "look through" each to find the ones with a matching id. If it's all one table it's essentially just one read.
Now, there are many many caveats to all of this (how indexes are setup, relative sizes of tables, etc. but in general it is more computationally expensive to do more joins).
- Tichy 18y agoIn theory the db could keep the separate tables in one table internally. But granted, probably it won't always do so.
- olavk 18y agoYeah, this is called a materialized view.
- newt0311 18y agoThis depends heavily on implementation details. If you are using Oracle (rare, I know for any company not in the Fortune 500), then just make a materialized view and you are set. Oracle will pre-compute most of the data in the view and ensure consistency (<<-- Not an oracle DBA so some confirmation here would be nice). Furthermore, some database engines handle joins and the kind a lot more efficiently than others. Furthermore, some kind of query caching mechanism will always be a better choice than de-normalization and most decent databases have that inbuilt (and if you really want, you are welcome to add memcached on top of that though that is not guaranteed to be correct). Either way, there are a lot more options than straight out de-normalization.
- olavk 18y agoSQL Server supports "indexed views" which is basically the same as materialized views.