Re: Book Review: "SQL Tuning" by Dan Tow

From: Ryan <rgaffuri_at_cox.net>
Date: Thu, 1 Jan 2004 22:20:04 -0500
Message-ID: <Dr5Jb.59057$hf1.31218@lakeread06>

"Mikito Harakiri" <mikharakiri_nospaum_at_yahoo.com> wrote in message

news:8a529bb.0401011544.ed69d30_at_posting.google.com...


> > "Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message

> > news:JSEIb.1$Mf4.30_at_news.oracle.com...
> > Let me get this straight are you saying that NL almost always
outperforms HJ
> > and MJ? Yes I know NL outperforms the other two types of queries in
OLTPs...
> > how about stating WHY! This is misleading because it makes people think
they
> > should automatically avoid HJs and MJs.
>
> It is not the percentage of cases where NL outperforms others. It is
> the skew in performance difference. Once again, if the best join
> method is, say, HJ but you use indexed nested loops, then, I routinely
> see 2, 3, and sometimes even 5 times elapsed time difference. Big
> deal! When NL outperforms HJ we often speak seconds versus hours.
>
> > The reason is simple. NL is optimized for returning a relatively small
> > number of rows. MJs and HJs are optimized for returning a relatively
large
> > number of rows. In OLTPs most of the time you only need a relatively
small
> > number of rows. Its batch processing where you use MJs and HJs most of
the
> > time.
>
> Not necessarily. If all the required columns of the inner table are in
> the index, then we don't incur overhead of table access by row id
> (random io) and NL would outperform the others on the big relations as
> well.
>

I flipped through this book. I like his 'method'. Ill buy it eventually. However, this statement above(which Im assuming is from the book) is wrong. HJ with full scans of both tables routinely outperforms NJs by 1000% or more when a large number of rows are involved. Its not even close.

Bench mark it. Dont just repeat what you read.

> > Does Dan Tow discuss this?
>
> There is a section discussing NL vs other methods, but his arguments
> aren't necessarily identical to mine.
>
> > I have never heard about the 'leverage' of HJ over MJ. Who said that?
Who
> > are the often 'sited'? This sounds inaccurate.
>
> <quote>
> the often cited argument in favour of HJ or MJ is the advantage of
> leveraging disk sequential io vs. random io
> </quote>
>
> There is no "'leverage' of HJ over MJ" in the above quote. I meant
> that NL is associated with random IO, while HJ and MJ - with
> sequential io.
>
> > The book intrigues me. It sounds different from the other SQL Tuning
books
> > which just give you 'tips' on things to try without an approach.
However,
> > your review about join types is not correct.

Ive never heard the 'random io' argument vs. the 'sequential io' argument before. Not 100% sure what you mean.Could mean alot of things.
>
> AFAIK this is the first book solely dedicated to tuning individual SQL
> statements, as opposed to competitors where authors compensate their
> lack of SQL tuning expertise with dull chapters how to tune overall
> system performace. Once again, SQL optimization is a very nontrivial
> subject.

That isnt true. Stop marketing and give a quality review. There are others. Guy Harrison has one. It doesnt have a 'method' like this one appears to. There is also a pretty good pocket reference.

Ill still buy a copy eventually. No time now. There seem to be several inaccuracies in the book. I flipped through it at the store today, but they are minor. Received on Thu Jan 01 2004 - 21:20:04 CST