--- In firebird-support@yahoogroups.com, Huan Ruan wrote:

>
> >
> > The question is - do global temporary tables have performance advantages
> > over usual tables? And whether global temporary tables were designed with
> > optimization goal in mind at all? Maybe they have different goals?
> >
> I don't think global temporary tables (GTT) is designed purely for
> optimisation, but there can be some performance gains with using GTT.
>
> 1. Large deletion on usual tables can be very slow especially when there
> are lots of non unique indexes, generates lots of garbage, and sometimes
> can cause index corruptions as well. GTTs, especially transaction based
> GTTs, don't have this problem because typically you don't need to do
> deletion, The database engine just drops the whole instance of the GTT when
> you finish your work.
> 2. GTT data (table data, index data and the related old versions and
> garbage), is stored outside of the normal database file so doesn't bloat
> the database size.
> 3. In Firebird 2.5, you can do writings to GTT within a read-only
> transaction. This certainly improves performance. Read-only read committed
> transactions do not interfere with transaction housekeeping, i.e. OIT and
> OAT.
> 4. Similar to the above point, I can imagine the database engine can
> utilise the fact that GTT data has limited visibilities for more
> optimisation.

Good points. I can add:
5. FW is always OFF for temp file with GTT's data
6. There is no need to keep writes correctly ordered for GTT, therefore in some cases it require less writes then persistent tables.

Regards,
Vlad