At 05:49 PM 12/6/00 +0100, Ivan Prenosil wrote:

>>
>> It is generally (i.e. always) faster to make a sequential pass
>> through selected records and sort the results than to bounce
>> between the index and the data pages -- a quicksort, even with
>> a merge, is faster than a page read.
>
>Unfortunately "generally" does not always mean "always".
>
>E.g. you have wide rows and ony one row fit on page
>(perhaps not typical table, but good for this example);
>then ordering by index means that you will have to read
>index (only small amount of data), and then each data page
>only once. On the other hand using sort files means (approximately)
>that each row is read from datapage, written to sort file,
>read from sort file, i.e. 3 times more i/o operations.
>

There are two problems with this analysis. First, all IO
is not the same. Large sequential operations are much faster
per byte than successive ordered reads and successive ordered
read are a great deal faster than random access. The index
retrieval scheme through an intermediate bitmap guarentees
that records are fetched as close to successive ordered reads
possible. Sort runs are written as single writes 64KB (or
whatever -- should be a MB). The cost of writing the sort
run is probably less than reading two random pages. Second,
if the sort buffer doesn't overflow, nothing gets written at
all. Also keep in mind that sort doesn't copy the engine
record, only fields referenced.

>Retrieving only part of result set is also important,
>especially with internet applications.
>

Could you explain this, please?


Jim Starkey