>
> 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.

Cheers
Huan

> Reply via web post<http://groups.yahoo.com/group/firebird-support/post;_ylc=X3oDMTJyY2JhOGNzBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyMjQyNwRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzEzNzU4MTU5ODM-?act=reply&messageNum=122427> Reply
> to sender
> <tom.rhodesw@...?subject=Re%3A%20Do%20global%20temporary%20tables%20have%20performance%20advantages%20over%20usual%20tables> Reply
> to group
> <firebird-support@yahoogroups.com?subject=Re%3A%20Do%20global%20temporary%20tables%20have%20performance%20advantages%20over%20usual%20tables> Start
> a New Topic<http://groups.yahoo.com/group/firebird-support/post;_ylc=X3oDMTJlcGQxOGpoBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM3NTgxNTk4Mw--> Messages
> in this topic<http://groups.yahoo.com/group/firebird-support/message/122427;_ylc=X3oDMTM4ODVqbHJ0BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyMjQyNwRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzNzU4MTU5ODMEdHBjSWQDMTIyNDI3>(1)
> Recent Activity:
>
> - New Members<http://groups.yahoo.com/group/firebird-support/members;_ylc=X3oDMTJmMDRhMDZhBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzNzU4MTU5ODM-?o=6>
> 7
>
> Visit Your Group<http://groups.yahoo.com/group/firebird-support;_ylc=X3oDMTJlYXJ0NW5hBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM3NTgxNTk4Mw-->
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> [image: Yahoo! Groups]<http://groups.yahoo.com/;_ylc=X3oDMTJkZG51djg2BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzc1ODE1OTgz>
> Switch to: Text-Only<firebird-support-traditional@yahoogroups.com?subject=Change+Delivery+Format:+Traditional>,
> Daily Digest<firebird-support-digest@yahoogroups.com?subject=Email+Delivery:+Digest>�
> Unsubscribe<firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>� Terms
> of Use <http://docs.yahoo.com/info/terms/> � Send us Feedback
> <ygroupsnotifications@yahoogroups.com?subject=Feedback+on+the+redesigned+individual+mail+v1>
> .
>
>
>

[Non-text portions of this message have been removed]