Subject Re: [IB-Architect] 'Order By' Issue
Author Dmitry Yemanov

> > > Thanks a lot for your explanation. But it seems to me, that in
> > > when tables contain large character fields, buffer I/O will be the most
> > > part of a query execution. This will be extremely sad when most of those
> > > character fields are nulls or empty.
> >
> > Right. It would not involve a great deal more processing to
> > compress the intermediate output and address it through an
> > array... Part of the answer is that returning lots of long
> > fields, mostly empty, wasn't part of the design center when
> > that code was written.
> Agreed.

A few fresh thoughts.

As far as I understand, the sorting mechanism should use an index to
accomplish this task. The engine creates intermediate buffer irregardless
of indices created for a table to be sorted. So when are indices used? If
at the phase of the buffer creation, then why this buffer is needed at all?
It seems to be simpler to read data pages directly from a database file(s)
via these indices. Or am I wrong? The only reason I can find for this
buffer is to sort records by non-indexed fields, but in this case I would
store in the buffer and sort just a set of FIELD1, ..., FIELDn (from ORDER
BY list) with appropriate DB_KEY or raw pointer.

Anyway, what should the engine do to sort records now? Read them from their
data pages (sequentially or via indices), write them to the buffer, sort
the buffer, read it and finally transfer to the client side. I do think
there is too many I/O operations. I can easily imagine tens of records with
blobs which are being sorted for a whole life. To the best of my
understanding, a time of sorting must depend on key fields size only (and,
of course, record count), but not the entire record size. Is it *really*
necessary to store in the buffer fields which are not used in sorting?

Or maybe have I got any leaks in my mind?

Best regards,