Firebird - where Bedingung mit Bedingung

[lxo]

Hallo,

ich habe einen Fall den ich nicht ganz verstehe und hoffe es gibt da eine Lösung zu.
In einer where-Bedingung möchte ich anhand eines Booleans entscheiden welche Bedingung genutzt werden soll.

Code:

-- 314 indizierte reads auf die Tabelle
T.ID = 1

-- 290 indizierte reads auf die Tabelle
T.ID= 20

-- 604 indizierte reads auf die Tabelle 
            ( ( TRUE and
              ( T.ID = 1)) or
              ( not TRUE and
              ( T.ID= 20)))

Warum macht Firebird bei der verschachtelten Bedingung 604 reads und nicht 314?
Kann ich das evtl. mit einem vorgegebenen PLAN beeinflussen?
Hab ich probiert aber nicht hinbekommen mit dem PLAN bisher.

[Blup]

Code:

select * from tabelle where ((:condition and (id = 1)) or (not :condition and (id = 20))

Der Plan wird aufgestellt, bevor dein Parameter zugewiesen wird (True, False, NULL).
So, als wenn da zwei unterschiedliche Parameter sind und deshalb beide Teilbedingungen wahr sein könnten.

Code:

select * from tabelle where id = iif(:condition, 1, 20)

[lxo]

Zitat:

Zitat von Blup (Beitrag 1540478)

Code:

select * from tabelle where id = ((:condition and (id = 1)) or (not :condition and (id = 20))

Der Plan wird aufgestellt, bevor dein Parameter zugewiesen wird (True, False, NULL).
So, als wenn da zwei unterschiedliche Parameter sind und deshalb beide Teilbedingungen wahr sein könnten.

Code:

select * from tabelle where id = iif(:condition, 1, 20)

Ja sowas habe ich mir auch schon gedacht, aber gibt es da denn eine Möglichkeit das zu beeinflussen?

[Blup]

Deshalb doch die Variante mit iif() gepostet.

[lxo]

Achso, ne das hilft auch nicht.
Funktioniert nur bei Konstanten-Werten mit iif.
Wenn ich dort Felder angebe macht der sogar 5264 reads.

Setze ich die selben Felder in mein oben genanntes Konstrukt wo 604 reads gemacht werden.
Macht der auch mit den Feldern nur 604 reads.


** Mit Case gleiches Ergebnis wie mit iif.

[lxo]

Hier nochmal runtergebrochen aufs wesentliche wie es bei mir aussieht.
Hab auch schon statt iif mit einer Function probiert aber auch so viele Ergebnisse.
Mit Plan auch schon hin und her probiert aber find da auch keine Lösung.


** Hintergrund warum ich das mache.
Durch die Eigenschaft "Global" in der Tabelle1 sollen die Werte aus Tabelle3 immer aus dem Main aus Tabelle2 (Es kann immer nur ein Main in Tabelle2 geben) geladen werden.
Ansonsten wenn nicht "Global" lies wie gehabt.

Dieses Statement habe ich in eine View gepackt um den Zugriff auf Tabelle3 im Programm zu manipulieren. (Wollte mir ersparen in Delphi alles anpassen zu müssen)

Das funktioniert auch wie gewünscht, jedoch bisschen langsam durch die mehrfachen reads die meiner Meinung nach unnötig gemacht werden.

Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from TABELLE1 T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID
-- 604 reads
( ( T1."GLOBAL" and
  ( T3.T2_ID = ( select T2_TEMP.ID
                 from TABELLE2 T2_TEMP
                 where T2_TEMP.MAIN))) or
  ( not T1."GLOBAL" and
  ( T3.T2_ID = T2.ID)))

-- 314 reads
( T3.T2_ID = ( select T2_TEMP.ID
                 from TABELLE2 T2_TEMP
                 where T2_TEMP.MAIN))
-- 290 reads
( T3.T2_ID = T2.ID)


-- iif() 5264 reads
      T3.T2_ID = iif( T1."GLOBAL", ( select T2_TEMP.ID
                                     from TABELLE2 T2_TEMP
                                     where T2_TEMP.MAIN), T2.ID)

where T1.ID = 111 and
      T2.ID = 20

[Frickler]

Statt nem View ne GTT als "poor man's materialized view"? Muss natürlich auch gefüllt werden (alles zusammen in EXECUTE BLOCK, oder möglicherweise in einem Trigger).

[lxo]

Zitat:

Zitat von Frickler (Beitrag 1540491)

Statt nem View ne GTT als "poor man's materialized view"? Muss natürlich auch gefüllt werden (alles zusammen in EXECUTE BLOCK, oder möglicherweise in einem Trigger).

Wie meinst du das, alles in einem Execute Block?
GTT sind ja Transaktions- oder Verbindungs-gebunden.
Hab ich da denn die Möglichkeit in einem trigger einer normalen Tabelle die GTT für jede Transaktion bzw. Verbindung zu aktualisieren?
Ich muss ja sicherstellen das der Inhalt bei jeder Verbindung aktuell ist, weil bei jedem select das GTT neu aufbauen ist bestimmt noch langsamer als meine bisherige Lösung mit doppelten reads oder?

[Delphi.Narium]

Ginge sowas?

SQL-Code:

select * from tabelle where id = ((:condition and (id = 1)) 
union all
select * from tabelle where id = (not :condition and (id = 20))

Zitat:

Zitat von lxo

Warum macht Firebird bei der verschachtelten Bedingung 604 reads und nicht 314?

Meiner Meinung nach ist das korrekt so wie es ist:

Code:

1. Wherebedingung
-- 314 indizierte reads auf die Tabelle
T.ID = 1

Code:

2. Wherebedingung
-- 290 indizierte reads auf die Tabelle
T.ID= 20

314 Reads + 290 Reads = 604 Reads

Code:

3. Wherebedingung -> Reads = Summe der Reads aus Wherebedingung 1 und Wherebedingung 2.
-- 604 indizierte reads auf die Tabelle
            ( ( TRUE and
              ( T.ID = 1)) or
              ( not TRUE and
              ( T.ID= 20)))

Firebird arbeitet hier praktisch die beiden Wherebedingungen ab und fügt deren Ergebnisse zusammen. Der Aufwand für die Datenbank für das Ausführen der kombinierten Wherebedingung ist genauso hoch wie der Aufwand für das Ausführen der einzelnen Bedingungen. Die Reads sind wohl eher abhängig von der Datenmenge, vermutlich gibt es mehr Sätze mit ID = 1 als mit ID = 20 und deshalb unterscheiden sich die Reads. Bei geänderten Datenverhältnissen kann sich das dann auch mal verschieben.

[lxo]

Zitat:

Zitat von Delphi.Narium (Beitrag 1540501)

Ginge sowas?

SQL-Code:

select * from tabelle where id = ((:condition and (id = 1)) 
union all
select * from tabelle where id = (not :condition and (id = 20))

Zitat:

Zitat von lxo

Warum macht Firebird bei der verschachtelten Bedingung 604 reads und nicht 314?

Meiner Meinung nach ist das korrekt so wie es ist:

Code:

1. Wherebedingung
-- 314 indizierte reads auf die Tabelle
T.ID = 1

Code:

2. Wherebedingung
-- 290 indizierte reads auf die Tabelle
T.ID= 20

314 Reads + 290 Reads = 604 Reads

Code:

3. Wherebedingung -> Reads = Summe der Reads aus Wherebedingung 1 und Wherebedingung 2.
-- 604 indizierte reads auf die Tabelle
            ( ( TRUE and
              ( T.ID = 1)) or
              ( not TRUE and
              ( T.ID= 20)))

Firebird arbeitet hier praktisch die beiden Wherebedingungen ab und fügt deren Ergebnisse zusammen. Der Aufwand für die Datenbank für das Ausführen der kombinierten Wherebedingung ist genauso hoch wie der Aufwand für das Ausführen der einzelnen Bedingungen. Die Reads sind wohl eher abhängig von der Datenmenge, vermutlich gibt es mehr Sätze mit ID = 1 als mit ID = 20 und deshalb unterscheiden sich die Reads. Bei geänderten Datenverhältnissen kann sich das dann auch mal verschieben.

Mein Gedanke war aber wenn die zweite Bedingung eh schon false ist, wozu wird da noch was gelesen. Die könnte er ja eigentlich ignorieren.
Macht der ja auch wenn ich iif mache, aber nur mit konstanten Werten.

[Delphi.Narium]

Zitat:

Zitat von lxo (Beitrag 1540504)

Mein Gedanke war aber wenn die zweite Bedingung eh schon false ist, wozu wird da noch was gelesen. Die könnte er ja eigentlich ignorieren.
Macht der ja auch wenn ich iif mache, aber nur mit konstanten Werten.

Das könnte in Deiner Vermutung könnte aber genau der Knackpunkt sein.

Delphi hat 'nen Kompilerschalter mit dem man die vollständige Auswertung von boolschen Vergleichen ein- oder ausschalten kann. Hat Firebird auch die Fähigkeit bei der Auswertung eines Teiles einer Bedingung zu erkennen, dass sie nur noch nicht erfüllt sein kann und es daher die weitere Arbeit unterlassen kann?

Sieht bei Deinem Beispiel eher nicht so aus.

[lxo]

Schade schade, könnte so eine schöne Lösung werden ohne alles in Delphi anpassen zu müssen.
Dann muss ich mal weiter überlegen was man machen kann.
Ich könnte mir vorstellen das man es mit einem angepassten PLAN und IIF hinbekommt. Da klappt es ja mit konstanten Werten wie gewünscht nur mit Feldern nicht. Aber krieg es nicht so hingebogen wie gewünscht.
Falls jemand noch weitere Tipps oder Ideen hat, gerne her damit.

[lxo]

Zitat:

Zitat von Delphi.Narium (Beitrag 1540501)

Ginge sowas?

SQL-Code:

select * from tabelle where id = ((:condition and (id = 1)) 
union all
select * from tabelle where id = (not :condition and (id = 20))

Ich hab meine optimale Lösung mit UNION hinbekommen, mit der nur die erforderlichen Reads gemacht werden.
Danke für den Hinweis mit UNION, hatte das irgendwie außer acht gelassen da ich irgendwie davon ausgegangen bin das es mit dem UNION noch umständlicher für Firebird wäre.

Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from TABELLE1 T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID  = T1.ID and
                T1."GLOBAL" and
            T3.T2_ID = ( select T2_TEMP.ID
                      from TABELLE2 T2_TEMP
                             where T2_TEMP.MAIN)            
union
select T1.ID as T1_ID,
       T3.T2_ID,
       T3.WERT
from TABELLE1 T1
inner join TABELLE3 T3 on T3.T1_ID = T1.ID and
      not T1."GLOBAL"

[Delphi.Narium]

Nimm bitte mal nur die zweite Hälfte des Union und schau, ob sich hierdurch was verbessert / verändert:

SQL-Code:

select T1.ID as T1_ID,
       T3.T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where not "GLOBAL" 
) T1
inner join TABELLE3 T3 on T3.T1_ID = T1.ID

In der ersten Hälfte des Union lässt sich bestimmt auch noch was optimieren, weiß aber gerade keinen sinnvollen Ansatz dazu.

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from TABELLE1 T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID and
                T1."GLOBAL" and
            T3.T2_ID = ( select T2_TEMP.ID -- sowas ist nicht unbedingt performant
                      from TABELLE2 T2_TEMP
                             where T2_TEMP.MAIN)

Leider ist es nicht unbedingt einfach die SQL-Syntax zu prüfen, wenn man keine entsprechenden Tabellen und Daten hat, daher ein Versuch für die erste Hälfte des Union als Idee, ohne Anspruch auf korrekte Syntax:

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where "GLOBAL"
) T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID 
inner join (select ID from TABELLE2 where MAIN) T4 on T3.T2_ID = T4.ID

Wenn das funktionieren sollte, ergäbe sich daraus folgendes SQL:

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where "GLOBAL"
) T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID 
inner join (select ID from TABELLE2 where MAIN) T4 on T3.T2_ID = T4.ID
union
select T1.ID as T1_ID,
       T3.T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where not "GLOBAL" 
) T1
inner join TABELLE3 T3 on T3.T1_ID = T1.ID

Kann es passieren, dass Datensätze sowohl in der ersten Hälfte des Union selektiert werden, als auch in der zweiten Hälfte des Union? Wenn nein, ändere das Union bitte in Union All. Dadurch muss die Datenbank das Ergebnis nicht sortieren und von Dubletten bereinigen, was die Abfrage (je nach Datenmenge) durchaus deutlich beschleunigen kann.

[lxo]

Zitat:

Zitat von Delphi.Narium (Beitrag 1540535)

Nimm bitte mal nur die zweite Hälfte des Union und schau, ob sich hierdurch was verbessert / verändert:

SQL-Code:

select T1.ID as T1_ID,
       T3.T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where not "GLOBAL" 
) T1
inner join TABELLE3 T3 on T3.T1_ID = T1.ID

In der ersten Hälfte des Union lässt sich bestimmt auch noch was optimieren, weiß aber gerade keinen sinnvollen Ansatz dazu.

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from TABELLE1 T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID and
                T1."GLOBAL" and
            T3.T2_ID = ( select T2_TEMP.ID -- sowas ist nicht unbedingt performant
                      from TABELLE2 T2_TEMP
                             where T2_TEMP.MAIN)

Leider ist es nicht unbedingt einfach die SQL-Syntax zu prüfen, wenn man keine entsprechenden Tabellen und Daten hat, daher ein Versuch für die erste Hälfte des Union als Idee, ohne Anspruch auf korrekte Syntax:

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where "GLOBAL"
) T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID 
inner join (select ID from TABELLE2 where MAIN) T4 on T3.T2_ID = T4.ID

Wenn das funktionieren sollte, ergäbe sich daraus folgendes SQL:

SQL-Code:

select T1.ID as T1_ID,
       T2.ID as T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where "GLOBAL"
) T1
cross join TABELLE2 T2
inner join TABELLE3 T3 on T3.T1_ID = T1.ID 
inner join (select ID from TABELLE2 where MAIN) T4 on T3.T2_ID = T4.ID
union
select T1.ID as T1_ID,
       T3.T2_ID,
       T3.WERT
from 
(
  select ID from TABELLE1 where not "GLOBAL" 
) T1
inner join TABELLE3 T3 on T3.T1_ID = T1.ID

Kann es passieren, dass Datensätze sowohl in der ersten Hälfte des Union selektiert werden, als auch in der zweiten Hälfte des Union? Wenn nein, ändere das Union bitte in Union All. Dadurch muss die Datenbank das Ergebnis nicht sortieren und von Dubletten bereinigen, was die Abfrage (je nach Datenmenge) durchaus deutlich beschleunigen kann.

1) Macht keinen Unterschied, Anzahl der reads und Ausführungszeit ist die selbe.

2) Hatte ich auch gedacht, aber Firebird macht das schon auf irgendeine weise effizient.
Habe auf die Tabelle2 durch den Subselect nur ein zusätzliches reads, egal wieviele Datensätze ich habe.

3) Das macht es langsamer, deutlich mehr Zugriffe auf Tabelle3 dadurch.

4) UNION ALL hat nochmal die Ausführungszeit verbessert. Besonders bei vielen Datensätzen merkbar. (Danke für den Tipp :thumb:)
Mithilfe von UNION ALL ist der SQL schon so gut wie so schnell wie der direkte Zugriff auf Tabelle3.

Direkter Zugriff auf die original Tabelle3 mit ca. 3.800.000 Datensätzen: Select * from Tabelle3 (fetch all) = (Durchschnittszeit bei 10x ausführen 12s 875ms)
Mit meiner View (Durchschnittszeit bei 10x ausführen 13s 050ms)

Wenn ich als Bedingung die T2_ID mitgebe ist der unterschied zwischen original Tabelle und View eigentlich gar nicht mehr festzustellen von der Zeit.