Re: [agileDatabases] Database Eye for the Application Guy
- On Sun, 20 May 2007, Jason Bennett wrote:
> Enter my new job: very transactionally intense, with a mix of OLTP and"Fewer joins is better" is a rule of thumb that can bite you as often as
> reporting requirements. This means lots of PL/SQL, denormalization, and
> query crafting. What I need is some books that can take me from where I
> am to where I want to be. What queries are safe (few joins), which will
> kill you (date ranges, lots of joins), and how to strike a good balance
> when denormalizing without it coming back to hose you.
it can help you. The big performance killer on large databases (where
the size is more than a couple of times that of the machine's RAM) are
random reads from disk. Lots of these are about the worst thing that can
ever happen to you.
Large sequential reads can hurt, too, but in most situations, I'll take
a couple of 100-block sequential reads over 20 1-block random reads,
even though the former is reading ten times as much data.
The joins only hurt when they're hitting disk; getting rid of relatively
small lookup tables to avoid joins can actually slow down queries
overall (due to reducing the amount of effective cache) just to save you
a little bit of CPU.
Anyway, a great book to learn all about this is Dan Tow's _SQL Tuning_
(O'Reilly). You should come out of this one with a pretty good idea of
how database optimizers work and the tools to do real optimization,
rather than the phony "just denormalize and avoid joins" approach that
that so many alleged DBAs take. (This is an area where programmers have
a big advantage; they're generally a lot better at thinking about how
a DBMS might do query optimization.)
Also, a general familiarity with the low-level details of how disk IO
works is fairly essential; i.e., you want a fairly good idea of where
the disk heads will be moving and why in various configurations and
with various queries. The book above will help with that, if you don't
already know it.
Beyond that, a good pile of relational theory is a big help. I'm a
big fan of C.J. Date's books. _Introduction to Database Systems_ is
great if you can find the time to get through it; failing that, _The
Database Relational Model_ is a slim volume that will give you the
essentials in a slightly more beginner-friendly form. Moving on from
there, _Foundation for Future Database Systems: The Third Manifesto_
is fantastic (you may want to skip straight to this if you're already
reasonably familiar with relational theory), and Date, Darwen and
Lorentzos' _Temporal Data and the Relational Model_ is quite useful.
A warning: if you actually learn and understand any substantial portion
of the books above, your life will start to feel rather frustrating, as
you'll be in a situation akin to that of an experienced and productive
Lisp or Smalltalk programer forced to program in COBOL or BASIC. Yes,
the products out there really are that bad. I have been for several
years now sorely tempted to write a good RDBMS. I'm not sure such a
thing would ever be a commercial success (at least not in the next
decade or two), so it's going to have to wait until I'm rich through
Curt Sampson +81 90 7737 2974
The power of accurate observation is commonly called cynicism
by those who have not got it. --George Bernard Shaw
- I've been a data/database professional for over 12 years. I've worked
with a lot of developers on tuning SQL and the over all application.
One book that I just recently discovered and started going through is
"SQL Design Patterns" by Vadim Tropashko. It's definitely not a
beginner's book, but once you get more experienced, there's a lot of
great information in there.
BoldTech Systems, Inc