> This is an interesting subject area.
> In a data warehousing environment, one tends to adopt table structures
> as snowflake layouts which lead to improved performance.
> Createing a perfect normalised database design may well lead to
If this is the case, go bug the database vendors :-) ... they should give
us systems that work properly ...
>The more joins you have, by far the worse the performance. You may
That's a pretty bold statement...
> need to consider horizontal or vertical table splits. You may need to
> consider replicating certain data in child tables to avoid joins.
> I am not saying you do not need to carry out data analysis and gain a full
> and first hand understanding of the data structures. It is just that when
> comes to online performance, sometimes you have to break the rules.
But still: logical first, performance later... If at all.
I once joined a team that had a running Oracle database and an application
on top of it. We were having performance problems and there was the
"2 seconds of max response time" requirement in the contract.
We tweaked Oracle (not particularly the fastest beast on the block), we
threw hardware at it. Both options worked... for a while.
Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching
data - the response time was alright. But it complicated our application,
the database design and the stored procedures using it... Not a particular
Then again... years later, I realized that the design should have been
different (better logical structure) and these problems would have been
avoided... Pity we couldn't do that part again...
Learned a lot though.
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!