28
Part I: Laying the Foundations
code and maintenance jobs, and signifi cantly lowers both the initial and the total cost of
the database system.A Performance Framework
By describing the dependencies between the schema, queries, indexing, transactions, and
scalability, Smart Database Design is a framework for performance.The key to mastering Smart Database Design is to understand the interaction, or cause-
and-effect relationship, between these hierarchical layers (schema, queries, indexing, and
concurrency). Each layer enables the next layer; conversely, no layer can overcome defi cien-
cies in lower layers. The practical application of Smart Database Design takes advantage of
these dependencies when developing or optimizing a database by employing the right best
practices within each layer to support the next layer.Reducing the aggregate workload of the database component has a positive effect on the
rest of the database system. An effi cient database component reduces the performance
requirements of the server platform, increasing capacity. Maintenance jobs are easier to
plan and also execute faster when the database component is designed well. There is less
client access code to write and the code that needs to be written is easier to write and
maintain. The result is an overall database system that’s simpler to maintain, cheaper to
run, easier to connect to from the data access layer, and that scales beautifully.Although it’s not a perfect analogy, picturing a water fountain on a hot summer day can
help demonstrate how shorter transactions improve overall database performance. If
everyone takes a small, quick sip from the fountain, then no queue forms; but as soon
as someone fi lls up a liter-sized bottle, others begin to wait. Regardless of the amount of
hardware resources available to a database, time is fi nite, and the greatest performance
gain is obtained by eliminating the excess work of wastefully long transactions. Striving
for database design excellence is a smart business move with an excellent estimated return
on investment (ROI). Further, early investment in thoughtful database design can pay huge
dividends in saved development and maintenance time. In the long term, it’s far cheaper to
design the database correctly than to throw money or labor at project overruns or hardware
upgrades.The cause-and-effect relationship between the layers helps diagnose performance problems
as well. When a system is experiencing locking and blocking problems, the cause is likely
found in the indexing or query layers. These issues can be caused by poorly written code.
However, the root cause isn’t always the code; it is often the overly complex, antinormal-
ized database design that is driving the developers to write horrid code.The bottom line? Designing an elegant database schema is the fi rst step to maximize the
performance of the overall database system while reducing costs.c02.indd 28c02.indd 28 7/30/2012 4:07:54 PM7/30/2012 4:07:54 PM
http://www.it-ebooks.info