Loading ...
Sorry, an error occurred while loading the content.
 

Re: [agileDatabases] Database Eye for the Application Guy

Expand Messages
  • Curt Sampson
    ... Fewer joins is better is a rule of thumb that can bite you as often as it can help you. The big performance killer on large databases (where the size is
    Message 1 of 33 , May 20, 2007
      On Sun, 20 May 2007, Jason Bennett wrote:

      > Enter my new job: very transactionally intense, with a mix of OLTP and
      > 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.

      "Fewer joins is better" is a rule of thumb that can bite you as often as
      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
      other means....

      cjs
      --
      Curt Sampson +81 90 7737 2974
      <cjs@...> http://www.starling-software.com
      The power of accurate observation is commonly called cynicism
      by those who have not got it. --George Bernard Shaw
    • khopsicker
      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
      Message 33 of 33 , Aug 10, 2007
        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.

        Ken Hopsicker
        BoldTech Systems, Inc
      Your message has been successfully submitted and would be delivered to recipients shortly.