Subject Re: Plan not what I expected
Author Adam
--- In, "chrisacron" <chris@...> wrote:
> Thanx Dmitry
> It does wonders for my perception of the Firebird project when the
> main developer takes time to answer my mundane questions. Thank you
> very much! I'm an even bigger disciple now.
> I notice that if I drop the ImportBatchId from the key that the plan
> changes to:
> My main reason for wanting the ID with the key is to use it to drive a
> header click sort app that is limited by ID. I am not however seeing a
> major increase in performance for this applicaation if I get the above
> plan.
> I'm conviced that a index that can act as a both a selector and
> sequence must be a lot faster (no sort to do) - I will test when this
> functionality has been included and report back here.

Generally speaking, an in memory sort of a set of records read from
the disk in storage order will return the entire set faster than an
ordered read from the disk, because hard drive seek times are measured
in milliseconds. Unless the data happens to be stored in more or less
the correct order, you need to wait for a spindle to physically
position itself across the piece of data of interest.

An ordered read can return the initial records much faster though, so
it depends on what you are going to do. If you are just getting the
first 20 odd, then an indexed read will almost certainly be faster. If
you are grabbing all the records, then you don't want an ordered read.