Subject Re: sort table depending on the select list instead of depending on the order-by
Author woodsmailbox
> 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%.

This only happens when I also include a 4k utf8 varchar column in the
_select list_. 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 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

> >
> > Btw, in fb 2.1.2 or 2.5, does the optimizer try to inline stacked
> > views (views that select from views)?
> Yes, when possible, which it is if the inner views are simple joins,
> meaning that they don't includes group by or aggregation. That's
> been the case since Firebird was InterBase.
> Good luck,
> Ann