Do global temporary tables have performance advantages over usual tables?

tom.rhodesw wrote:

I am trying to evaluate what are the best options for performing some calculations and data queries using Firebird tables.

At present I can see two options:

• use usual tables with dedicated field for storing some session id
• use global temporary tables

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?

Huan Ruan answers:

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.

Vlad Khorsun adds:

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.