Subject Re: [firebird-support] Re: sort table depending on the select list instead of depending on the order-by
Author Ann W. Harrison
woodsmailbox wrote:
>> Generally, it is much faster to retrieve records in storage order
>> than to access them randomly. Unless you happen to store records
>> in the order that you're sorting them, selecting after the sort
>> will produce much more I/O on the database than you're currently
>> seeing on the sort file.
> Not sure how your answer is related to the problem described. Firebird
> builds a 1.2GB sort table for ~40 seconds while CPU = 100%.

I tried to answer you're question which was, if I understand correctly,
why doesn't firebird retrieve only the sort keys on the first pass,
then sort them, then go back and retrieve all the other items in
the select list. It doesn't because generally, I/O to the database
is the most expensive operation it does, and doing it twice, once
in storage order and once in random order relative to storage is
likely to be slower than retrieving all the rows in the first pass.

> This only happens when I also include a 4k utf8 varchar column in the
> _select list_.

Yup. A 4k utf8 varchar field generated a buffer of 12 or 16k -
depending on whether the current code is prepared to handle the
absolute worst case. In this case, a blob would be a lot faster.

> If I don't include that column in the select list, fb
> makes a sort table of 300K. The respective column is not part of the
> ORDER BY clause, so it shouldn't affect performance that much. This is
> quite a show stopper for me since the query is very simple and the
> select is a simple join of two tables of 6000 x 20 rows. Also, the
> execution plan shows no NATURAL joins, and SORT is at the root of the
> plan.

The first element of the plan is the last one executed - which would
be the sort. Firebird normally executes indexed access in two stages,
first getting all qualifying values from the index and setting bits
in a storage-order bitmap, then second retrieving the rows in bitmap
order. So you get storage order access even when an index is used.

> The performance is so lousy for such small tables that I incline
> to think it's a bug. Besides, how can it build a 1.2GB sort table from
> a 45MB database on a simple join + order by? Is this normal
> performance?

I'd guess you were getting very good compression on your utf8

Good luck,