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
[firebird-support] Query optimization on FB3
Does anybody has a idea to optimization my follow query? SELECT ELEMENTE.BEZEICHNUNG, DATENLOGGING.DATUMZEIT, DATENLOGGING.WERT, DATENLOGGINGTYPEN.MASSEINHEIT, DATENLOGGINGTYPEN.DATA_TYPE, DATENLOGGINGTYPEN.FAKTOR FROM DATENLOGGING INNER JOIN ELEMENTE ON DATENLOGGING.SPS_NODE = ELEMENTE.SPS_NODE AND DATENLOGGING.TYP = ELEMENTE.TYP AND DATENLOGGING.ELEMENT = ELEMENTE.ELEMENT AND DATENLOGGING.JOBID = ELEMENTE.JOBID INNER JOIN DATENLOGGINGTYPEN ON DATENLOGGING.SPS_NODE = DATENLOGGINGTYPEN.SPS_NODE AND DATENLOGGING.TYP = DATENLOGGINGTYPEN.TYP AND DATENLOGGING.ELEMENT = DATENLOGGINGTYPEN.ELEMENT AND DATENLOGGING.JOBID = DATENLOGGINGTYPEN.JOBID WHERE ( (DATENLOGGING.DATUMZEIT >= '11.01.2017 12:51:26') AND (DATENLOGGING.DATUMZEIT <= '11.01.2017 13:51:37') OR (DATENLOGGING.DATUMZEIT >= '11.01.2017 13:50:13') AND (DATENLOGGING.DATUMZEIT <= '11.01.2017 22:07:10') OR (DATENLOGGING.DATUMZEIT >= '11.01.2017 22:07:38') AND (DATENLOGGING.DATUMZEIT <= '12.01.2017 22:04:20') OR her between are about 200 Lines!! (DATENLOGGING.DATUMZEIT >= '12.09.2017 14:03:20') AND (DATENLOGGING.DATUMZEIT <= '12.09.2017 16:59:18') OR (DATENLOGGING.DATUMZEIT >= '14.09.2017 10:54:03') AND (DATENLOGGING.DATUMZEIT <= '14.09.2017 16:46:48') OR (DATENLOGGING.DATUMZEIT >= '15.09.2017 14:29:02') AND (DATENLOGGING.DATUMZEIT <= '15.09.2017 14:59:27') OR (DATENLOGGING.DATUMZEIT >= '18.09.2017 09:58:56') AND (DATENLOGGING.DATUMZEIT <= '18.09.2017 10:59:10') OR (DATENLOGGING.DATUMZEIT >= '18.09.2017 13:58:56') AND (DATENLOGGING.DATUMZEIT <= '18.09.2017 14:59:10') ) AND DATENLOGGING.SPS_NODE = 100 AND DATENLOGGING.TYP = 20 AND DATENLOGGING.JOBID = 4 AND ((DATENLOGGING.ELEMENT = 32) OR (DATENLOGGING.ELEMENT = 38) OR (DATENLOGGING.ELEMENT = 20) OR (DATENLOGGING.ELEMENT = 35) OR (DATENLOGGING.ELEMENT = 41) OR (DATENLOGGING.ELEMENT = 23)) AND DATENLOGGING.WERT IS NOT NULL ORDER BY ELEMENTE.BEZEICHNUNG, DATENLOGGING.DATUMZEIT The follow statement in the where clause (DATENLOGGING.DATUMZEIT >= '12.09.2017 14:03:20') AND (DATENLOGGING.DATUMZEIT <= '12.09.2017 16:59:18') OR are a result of a separate query like the follow: SELECT bm.KOMMT, bm.GEHT FROM bm WHERE bm.SPS_NODE=20 and bm.TYP=25 and bm.ELEMENT=1 and bm.KOMMT > '01.01.2017 00:00:00' and bm.KOMMT < '31.12.2017 23:59:59' Are there are a better solution to combine the two querys? May thanks for any hints Best Regards Gregor
