Subject Re: [Firebird-Architect] Re: Some benchmarks about 'Order by' - temporary indexes?...
Author Ann W. Harrison
Jim Starkey wrote:
> If the only overhead were the seek and rotational latency of the hard
> disk, I wouldn't quibble at all. But a Firebird record fetch requires
> the following: ...

When the set to be sorted is larger than can be handled in a memory
only sort, does it make sense to write the data to a temporary file
and perform a tag sort on the key values using the offset into that
file of the required data as the tag? That would eliminate all the
page cache issues - and avoid churning the cache for other threads -
while reducing the size of the data to be sorted. The temporary file
would be written and read serially - relatively fast for a hard disk

Pavel Cisar wrote:

> In the project I was involved in the past that had to sort records of
> various size in sets of various size we had a great success with
> building B-tree index on the fly + read in index order instead sorting
> the set. ...

Err, your experience with that project runs counter to my experience
with Firebird/InterBase over the years. First, reading in sorted order
is not generally faster than reading in storage order and sorting -
unless you count only the time to get the first record or happen to
store the data in sorted order. Second, building a b-tree by adding
records in storage order is slower than sorting the records and
building the b-tree from the sorted record stream. The latter is
what we call "index fast load" and is used when reactivating an index
or creating an index on existing data. When we recommend that users
disable indexes before bulk loads, we're showing them how to take
advantage of that speed difference.

Of course, if everything fits in memory, then it's going to be fast...