> > > in order to retrieve all the information about them. Replacing a
> > > theoretically ugly null flag with a 10 way join strikes me, as an
> > > rather than a theoretician, the wrong side of the
> > > trade-off.
> > Using NULLs as well as de-normalization brings the risk of
> > integrity problems to your storage, storing what is right is only
> > a good thing.
> > And when it comes to having to writing JOINs for all your queries,
> > lo and behold, I bring you the wonder of the VIEW.
> > ;-)
> The VIEW eases the syntax, but does it do anything for performance? Surely
> it must be much slower to read 11 different tables (Master record
> containing all NOT NULL fields, and 10 slave records which may or may not
> contain relevant fields)? Ignoring caching, you are going to have at least
> one disk access for every NULL field (index lookup which fails) and two
> for every non NULL field (index lookup, data lookup) for every null field.
> This means that you have multiplied your number of disk accesses (ignoring
> caching, again) by 6-11 times (assuming the master record takes two disk
> accesses). That again seems a very high price to pay for theoretical
Keeping your design clean is hardly sacrificing anything.
The most important feature of your database and the database
design is the integrity of your data. Period.
De-normalization, NULLs et all make this task much harder,
as well as understanding your design for the guy that takes
over your job ...
Performance comes next, first thing to do there is getting
your indices straight.
Next thing is optimizing the slow stuff - if you can go around
by keeping, for example, (these much smaller slave) tables
in memory, and this works out fine, then do so.
If all your queries are optimized, the next thing to do is
to throw hardware at it. This is very cheap comparing to
your hourly rate. ;-)
Obviously, if your queries and design are valid and the
database system is still slow, you can also complain with
the creators of the database system -> performance, after
all, is a physical attribute, not a database design issue perse.
During the years, many database systems have seen many
improvements to bring better performance to its users.
Only recently, for example, the Firebird DBMS implemented
a new on-disk structure for it's indices greatly enhancing
performance for indices with a large number of duplicates. In
the past, people said: you have to modify your design such
and so, with all risks included. Now, you can keep your design
as clear as possible and enjoy greater performance, just because
someone thought of something clever. If no-one ever complained,
this particular piece of code wouldn't have changed.
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!