Re: Database development
Zlatko Duric wrote:
But there is some information about all those objects I'd like to
store in a single table or maybe two of them, that'd be super-fast to
reach, without having to look for all those
parent/children/node/parameters/other links ...
Wrong approach.
Now, how common is this approach (combination)? Is there something
Common doesn't mean correct.
really important I should read about this, before starting with the
implementation?
Yes. Read about why normalization is important in the first place. Read
about the things others have mentioned, like (materialized) views. Read about
why "premature optimization is the root of all evil."
Whatever you do to "optimize" won't, at least not unless you actually
*measure* performance before and after your so-called "optimizations" under
realistic loads and field conditions.
Don't forget to take into account the cost of the increased code complexity
for denormalized structures, and compare that to the cost of keeping data
normalized. Don't forget to take into account the actuarial cost of the risk
to your data from the denormalization.
Better yet, stick with best practices.
markspace wrote:
As far as I know, de-normalizing a database for faster access is very
common, as long as you started with a good normalized design, and you
document carefully what you denormalize, and you measure carefully the
performance boost and can justify the extra maintenance.
I don't have any links handy, but if you Google for "database
denormalization optimization" there seems to be plenty of info. I'd try
some standard techniques for denormalization first, rather than try to
improvise something.
Arne Vajh??j wrote:
It is very common to denormalise databases for "performance".
OP: Notice how some of us put "performance" in quotation marks? There's a
good reason for that.
I have a strong suspicion that in more than 90% of cases it
is unwarranted.
You are being kind.
Databases are extremely optimized to do joins efficiently.
If the logical and physical design is good then joins is
usually not the problem.
Even if it is a problem, then the specific databases may
offer the possibility of materialized views to solve the
problem.
When one denormalizes a database for "performance", one usually winds up with
none of the expected performance gains and all of the expected increase in
risk to the data.
At least, one ought to expect that risk. The purpose of normalizing a
database is to prevent data anomalies and enforce data constraints.
Denormalize and you screw that up.
As for ORM efficiency, as Arne pointed out:
Hibernate [or any other ORM framework] can be slow
and Hibernate can be fast.
It all depends on the guy writing the code.
Properly written, JPA code is no slower than raw SQL coding, takes less time
to develop and maintain (part of the cost-benefit equation, folks!), and is a
much more natural fit to the object model of the application.
Furthermore, JPA frameworks offload much of the management effort for
persistent storage connections and O-R mapping. This is similar to how
managed-memory environments like the JVM and .Net offload the effort, expense
and risk of memory management from the programmer. Don't give that up lightly.
Beyond that, Hibernate and other JPA frameworks lend themselves well to
inbuilt and outboard cache approaches. Out of the box, JPA gives you a "level
one" cache (a.k.a. a "session") that will help optimize interaction with the
database.
If you're looking to optimize database access, it is by far much more
productive to pay attention to things like client- and server-side statement
preparation, scope and lifecycle of 'EntityManager' instances, database tuning
parameters (such as work memory or whatever the DBMS calls it), connection
pooling, disk speed (use high-rotational-speed drives in a RAID array with a
battery-backed RAID controller), scalability, concurrency, indexes,
partitioning (database, not disk), and other adjustments that will improve
performance WITHOUT TOTALLY HOSING YOUR DATA MODEL.
A good object model that caches well without concurrency bottlenecks will
scale well to additional hardware and provide much more performance than a
messed-up data model, without the risks of the latter.
I've been part of performance optimization efforts for databases a number of
times. Denormalization usually has hurt, not helped. In one case that I
recall fondly, the denormalized structure caused a quadratic increase in
processing time with data quantity, rather than the linear increase a
normalized database would have provided (and did, when they finally accepted
my recommendation to normalize, but not before causing a major problem with
their customer that got the project manager replaced and nearly cost the
contract).
Program run time is rarely the dominant cost in a project.
Code correctly and well first. That will almost always give sufficient
performance. If not, MEASURE and optimize and MEASURE again, focusing first
and foremost on things that don't mess you up by harming data integrity,
maintainability or scalability.
--
Lew