On 17-5-2013 16:27, Ann Harrison wrote:

> On Thu, May 16, 2013 at 12:35 PM, haris_p_sw <haris_p_sw@...> wrote:
>> Why Firebird sorts datasets loading whole rows into memory when the plan
>> is NATURAL? Couldn't it sort only primary keys for example? There might be
>> something I 'm missing...
>>
> Firebird actually doesn't sort whole rows, it sorts only the rows requested
> in the query - so select * is your enemy here as well. If it were to sort
> carrying only the columns in the ORDER BY clause and the RDB$DB_KEY, it
> would then have to make random reads for each row in the result set.
> Random reads all over the database are even more expensive than the block
> reads used to retrieve sorted subsets of the query.
>
> Allocate as much memory as feasible for sorts and use a fast disk (or disk
> equivalent) for temp space.

Although you specifically answer his question about why Firebird loads
rows into memory, the problem described could also be a bug or a
suboptimal implementation.

Mark
--
Mark Rotteveel