Re: ODP: [firebird-support] Query optimization on FB3
Steve Naidamast wrote:
> However, could you clarify what you mean between a session or a transaction?
Session == connection
I guess you know what a transaction is.
You can define a GTT with a life that lasts as long as the transaction
in which it is instantiated, i.e.,
CREATE GLOBAL TEMPORARY TABLE name
( [, { | } ...])
[ON COMMIT {DELETE | PRESERVE} ROWS]
So, ON COMMIT DELETE ROWS empties the GTT instance when the
trasnaction commits (the default), while ON COMMIT PRESERVE ROWS keeps
the data until the session (connection) ends.
For more info, see
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-gtt
Helen
---
This email has been checked for viruses by AVG.
https://www.avg.com
Re: ODP: [firebird-support] Query optimization on FB3
Hi Helen... Thank you for clearing this up for me. However, could you clarify what you mean between a session or a transaction? Steve Naidamast Sr. Software Engineer
Re: ODP: [firebird-support] Query optimization on FB3
Steve Naidamast wrote: > By using a global table in Firebird, could not multiple users cause > a conflict if two such users were to issue the same query against the global > table? No. A GTT definition is persistent, of course, but persistent data are not stored in it. A GTT instance is created for use within a single session or transaction and dies when the session or transaction ends. One instance has no knowledge of another - other than multiple GTTs within the same session or transaction - depending on the life defined for that GTT. > Coming from a SQL Server background, I am used to using local > temporary tables, which are isolated on a query by query basis... Not the same thing. The life of a GTT instance can be transaction or session. (Life depends on a property in the definition.) HB --- This email has been checked for viruses by AVG. https://www.avg.com
Re: ODP: [firebird-support] Query optimization on FB3
By using a global table in Firebird, could not multiple users cause a conflict if two such users were to issue the same query against the global table? Coming from a SQL Server background, I am used to using local temporary tables, which are isolated on a query by query basis... Steve Naidamast Sr. Software Engineer
ODP: [firebird-support] Query optimization on FB3
Hi. There are 2 common ways 1. Create global temporary table Do INSERT INTO T(DSTART, DEND) SELECT … and then do simple join with this table 2. Use derived table e.g. SELECT DATENLOGGING.* … FROM (SELECT D.DSTART, D.DEND FROM TABLED) X LEFT JOIN DATENLOGGING ON DATENLOGGING.DATUMZEIT>=X. DSTART AND DATENLOGGING.DATUMZEIT<=X.DEND WHERE DATENLOGGING.KEY IS NOT NULL /* do hidden inner join by providing some not nullable field check */ Regards, Karol Bieniaszewski
