| Subject | Re: [firebird-support] Re: sort table depending on the select list instead of depending on the order-by |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2009-03-02T21:18:31Z |
woodsmailbox wrote:
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.
depending on whether the current code is prepared to handle the
absolute worst case. In this case, a blob would be a lot faster.
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.
varchars.
Good luck,
Ann
>> Generally, it is much faster to retrieve records in storage orderI tried to answer you're question which was, if I understand correctly,
>> 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%.
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.
>Yup. A 4k utf8 varchar field generated a buffer of 12 or 16k -
> This only happens when I also include a 4k utf8 varchar column in the
> _select list_.
>
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, fbThe first element of the plan is the last one executed - which would
> 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.
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 inclineI'd guess you were getting very good compression on your utf8
> 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?
>
varchars.
Good luck,
Ann