SQL Sauert zu lange

[Dumpfbacke]

Hallo Delphianer,
ich habe ein Problem mit einem Select. Deshalb habe ich hier unten mal einen Auszug als dem Select eingestellt. Das Problem liegt hier bei or (UrMaster.referenznr = 'ABT_25' ).

Auf allen Feldern in der Where liegt ein Index auch auf der RefernzNr.

Wenn ich das ganze ohne die letzte Zeile mache mache dauert es ca. 2 Sekunden. Mit dieser Bedingung dauert es ca. 22 Sekunden. Wenn ich das danze richtig deute (bei IBExpert) wird hier der Index nicht benutzt.

Ich muss die Joins so machen da ich daten aus den entsprechenden Tabellen benötige.

Kann mit jemand von Euch hier helfen ?

Danke schon einmal Tanja

Delphi-Quellcode:

from MASTER
Left Outer JOIN SLAVE on MASTER.Vorgangsnummer = SLAVE.Vorgangsnummer and (SLAVE.ZusatzStatus = 'EZ' or SLAVE.ZusatzStatus = 'EV' or
                                                                    SLAVE.ZusatzStatus = 'EF' or SLAVE.ZusatzStatus = 'EA' or
                                                                    SLAVE.ZusatzStatus = 'EQ' or SLAVE.ZusatzStatus = 'EP')
Left Outer Join MASTER UrMaster on MASTER.Vorgangursprung = UrMaster.Vorgangsnummer
where MASTER.Status = 'In Bearbeitung'  and SLAVE.SlaveNummerr is not NULL
and ((SLAVE.SlaveStatus = 'Warten' or SLAVE.SlaveStatus = 'Unterbrochen' or
      SLAVE.SlaveStatus = 'In Arbeit' and SLAVE.Dzusatz is NULL) or
     (SLAVE.SlaveStatus = 'In Abreit' and SLAVE.Dzusatz = 'Gesperrt' and
      SLAVE.Sollende < Current_Date)
    )

    and
    MASTER.FATW = 'Aktive'
and (((MASTER.AltesSystem like 'Start%' or MASTER.AltesSystem like 'Vorgang%' )
       and MASTER.AltesSystem not like '%Abbruch%' and MASTER.Storno is null
     )
    or (UrMaster.referenznr = 'ABT_25' )   )

[jobo]

Wie groß ist die Basismenge der einzelnen Tabellen und wie groß ca das Ergebnis?
Auf die Schnelle schon mal:
Feste Kriterien wenn mgl. z.B. bei Slave mit in den Join reinpacken (auch wenn es nicht zum Join gehört)

Zitat:

MASTER.AltesSystem not like '%Abbruch%

Möglich kein % am Anfang, bester alle '%Abbruch' Varianten analysieren und aufführen (falls das statisch ist bzw. bleibt)
Bei dem Or von URMASTER sehe ich auf den ersten Blick keinen Ausweg. Muss es wirklich ein Left Outer Join zu Master sein?

[nahpets]

Habe das Statement einmal strukturell verändert, um es für mich lesbarer zu machen.

Dabei hab' ich (nach meinem Verständnis) Vereinfachungen eingebaut und Verständnisprobleme mit Kommentaren versehen.

Allerdings habe ich keine Ahnung, ob das irgendwas am Geschwindigkeitsproblem ändert.

SQL-Code:

from MASTER
Left Outer JOIN SLAVE
on   MASTER.Vorgangsnummer = SLAVE.Vorgangsnummer
and  SLAVE.ZusatzStatus in ('EZ','EV','EF','EA','EQ','EP')
Left Outer Join MASTER UrMaster
on    MASTER.Vorgangursprung = UrMaster.Vorgangsnummer
where MASTER.Status      = 'In Bearbeitung'
and   SLAVE.SlaveNummerr is not NULL
and MASTER.FATW = 'Aktive'
and 
    (
     -- Beginn Verständnisproblem
     -- Hier verstehe ich nicht genau, was gemeint ist und weiß nicht, wie FireBird das handhabt.
     -- Das Is Null bezieht sich nur auf Status = 'In Arbeit'?
     (   SLAVE.SlaveStatus in ('Warten','Unterbrochen')
      or SLAVE.SlaveStatus = 'In Arbeit' and SLAVE.Dzusatz is NULL
      -- Bei Is Null-Bezug nur auf 'In Arbeit' würd' ich das in Klammern setzen.
      or (SLAVE.SlaveStatus = 'In Arbeit' and SLAVE.Dzusatz is NULL)
     )
     -- oder ist dieses gemeint?
     (
          SLAVE.SlaveStatus in ('Warten','Unterbrochen','In Arbeit')
      and SLAVE.Dzusatz is NULL
     )
     -- Ende Verständnisproblem
     
     or (    SLAVE.SlaveStatus = 'In Abreit'  <-- Schreibfehler? 'In Arbeit'
         and SLAVE.Dzusatz = 'Gesperrt'
         and SLAVE.Sollende < Current_Date
        )
    )
and 
    (
     (
      (
          MASTER.AltesSystem like 'Start%'
       or MASTER.AltesSystem like 'Vorgang%'
      )
      and MASTER.AltesSystem not like '%Abbruch%'
      and MASTER.Storno      is null
     )
     or (UrMaster.referenznr = 'ABT_25')
    )

Dann hätte ich noch ein fachliches Problem:
Worauf bezieht sich das UrMaster.referenznr = 'ABT_25' im zweiten Left outer Join.
Irgendwie kann ich an der Stelle, an der die Oder-Bedingung für die 'ABT_25' steht, nicht so recht den Sinn erkennen.

Es findet eine Grundsätzliche Verbindung über MASTER.Vorgangursprung = UrMaster.Vorgangsnummer statt.
Es gibt ein paar Einschränkungen und die letzte Einschränkung gilt für AltesSystem like 'Start%' oder like 'Vorgang%', sofern nicht AltesSystem like '%Abbruch% und Storno is null, wobei diese Einschränkung nicht für die 'Abt_25' gilt.
D. h.: Für 'Abt_25' sollen die Daten auch geliefert werden, wenn AltesSystem like 'Start%' oder like 'Vorgang%' ...?

[sh17]

wer lässt denn auch eine SQL-Abfrage versauern :-D

[ibp]

Zitat:

Zitat von sh17 (Beitrag 1340844)

wer lässt denn auch eine SQL-Abfrage versauern :-D

das ganze ist unausgegoren :-D

[jobo]

Zitat:

Zitat von nahpets (Beitrag 1340843)

SQL-Code:

and  SLAVE.ZusatzStatus in ('EZ','EV','EF','EA','EQ','EP')

Ich bin kein Firebird Spezialist, aber "in ()" oder "or" wird doch sehr wahrscheinlich intern gleich abgearbeitet? Wäre also nur besser für die Augen.

Ja und "sauer macht lustig", wollte ich schon immer mal loswerden.
Nicht dass wir die Sache am Ende mit zuviel unnötigem Ernst angehen. ;)

[nahpets]

Zitat:

Zitat von jobo (Beitrag 1340849)

Zitat:

Zitat von nahpets (Beitrag 1340843)

SQL-Code:

and  SLAVE.ZusatzStatus in ('EZ','EV','EF','EA','EQ','EP')

Ich bin kein Firebird Spezialist, aber "in ()" oder "or" wird doch sehr wahrscheinlich intern gleich abgearbeitet? Wäre also nur besser für die Augen.

Das mag durchaus so sein, aber durch erhöhte Lesbarkeit hab' ich in der Vergangenheit schon des Öftern fachliche Fehler finden können, die ansonsten nicht auffielen. Gerade bei vielen and und or kann es schonmal schnell passieren, das eine Klammerung knapp daneben ging, auch wenn das Statement syntaktisch weiterhin korrekt war.
Und so knapp daneben liegende Klammern können schonmal schnell zu einer exorbitant steigenden Laufzeit führen.

Und letztlich resultieren meine Fragen zum "threadauslösenden" SQL ja auch daher.

[ibp]

Zitat:

Zitat von Dumpfbacke (Beitrag 1340836)

...
Wenn ich das ganze ohne die letzte Zeile mache mache dauert es ca. 2 Sekunden. Mit dieser Bedingung dauert es ca. 22 Sekunden.

Wie lange dauert es denn, wenn du die Abfrage nur mit der letzten Bedingung (Zeile) absendest?

SQL-Code:

from MASTER
Left Outer JOIN SLAVE on MASTER.Vorgangsnummer = SLAVE.Vorgangsnummer and (SLAVE.ZusatzStatus = 'EZ' or SLAVE.ZusatzStatus = 'EV' or
                                                                    SLAVE.ZusatzStatus = 'EF' or SLAVE.ZusatzStatus = 'EA' or
                                                                    SLAVE.ZusatzStatus = 'EQ' or SLAVE.ZusatzStatus = 'EP')
Left Outer Join MASTER UrMaster on MASTER.Vorgangursprung = UrMaster.Vorgangsnummer
where (UrMaster.referenznr = 'ABT_25' )

[nahpets]

Zitat:

Zitat von ibp (Beitrag 1340852)

Zitat:

Zitat von Dumpfbacke (Beitrag 1340836)

...
Wenn ich das ganze ohne die letzte Zeile mache mache dauert es ca. 2 Sekunden. Mit dieser Bedingung dauert es ca. 22 Sekunden.

Wie lange dauert es denn, wenn du die Abfrage nur mit der letzten Bedingung (Zeile) absendest?

SQL-Code:

from MASTER
Left Outer JOIN SLAVE on MASTER.Vorgangsnummer = SLAVE.Vorgangsnummer and (SLAVE.ZusatzStatus = 'EZ' or SLAVE.ZusatzStatus = 'EV' or
                                                                    SLAVE.ZusatzStatus = 'EF' or SLAVE.ZusatzStatus = 'EA' or
                                                                    SLAVE.ZusatzStatus = 'EQ' or SLAVE.ZusatzStatus = 'EP')
Left Outer Join MASTER UrMaster on MASTER.Vorgangursprung = UrMaster.Vorgangsnummer
where (UrMaster.referenznr = 'ABT_25' )

Das dürfte aber zu einem vollständig anderen Ergebnis führen, da die Abfrage auf die ReferenzNr ja nur ein Teil innerhalb der letzten AND-Bedingung ist. Diese Bedingung wird ja nur auf eine bereits erstellte Teilmenge angewandt und nicht auf die Ursprungstabelle. Von daher könnte ich mir vorstellen, dass dort die Nutzung des Index für die Datenbank nicht mehr möglich ist.

Eventuell könnte es aber hilfreich sein, im zweiten Left-Join grundsätzlich auf UrMaster.referenznr <> 'ABT_25' einzuschränken und in einem dritten Left-Join nur die Besonderheiten von UrMaster.referenznr = 'ABT_25' "abzuarbeiten".

[p80286]

Wenn man die Datenbank kennen würde, könnte man viel mehr dazu sagen. z.b. diese

SQL-Code:

Slave.ZusatzStatus= or Slave.Zusatzstatus=

Orgie, könnte die nicht durch

SQL-Code:

Slave.ZusatzStatus<>x and Slave.Zusatzstatus<>y

ersetzt werden?
U.U. kann man 'ABT_25' auch durch eine liste von IDs ersetzen (anderer Index)?
Und dieses

Code:

 SLAVE.SlaveStatus = 'In Arbeit' and SLAVE.Dzusatz is NULL) or
      (SLAVE.SlaveStatus = 'In Abreit' and SLAVE.Dzusatz = 'Gesperrt'

würde ich durch

SQL-Code:

SLAVE.SlaveStatus = 'In Arbeit' and (SLAVE.Dzusatz is NULL or  SLAVE.Dzusatz = 'Gesperrt')

ersetzen.

Gruß
K-H

[Dumpfbacke]

Zitat:

Zitat von jobo (Beitrag 1340840)

Wie groß ist die Basismenge der einzelnen Tabellen und wie groß ca das Ergebnis?
Auf die Schnelle schon mal:
Feste Kriterien wenn mgl. z.B. bei Slave mit in den Join reinpacken (auch wenn es nicht zum Join gehört)

Zitat:

MASTER.AltesSystem not like '%Abbruch%

Möglich kein % am Anfang, bester alle '%Abbruch' Varianten analysieren und aufführen (falls das statisch ist bzw. bleibt)
Bei dem Or von URMASTER sehe ich auf den ersten Blick keinen Ausweg. Muss es wirklich ein Left Outer Join zu Master sein?

Master ca. 414.201 Datensätze
Slave ca. 1.381.415 Datersätze
Ergebnis ca. 48 Datensätze

[hstreicher]

Bei IBExpert kann man einfach ein(e)/paar Registerkarte weiterklicken und kriegt den Execpution Plan angezeigt sowie die Abruf Statistiken nach Index Reads und nonindexed Read
da sieht mah schnell wo die Zeit verbaucht wird


ggf mal den Plan hier posten

mfg Hannes

[Dumpfbacke]

Zitat:

Zitat von ibp (Beitrag 1340852)

Zitat:

Zitat von Dumpfbacke (Beitrag 1340836)

...
Wenn ich das ganze ohne die letzte Zeile mache mache dauert es ca. 2 Sekunden. Mit dieser Bedingung dauert es ca. 22 Sekunden.

Wie lange dauert es denn, wenn du die Abfrage nur mit der letzten Bedingung (Zeile) absendest?

[/CODE]

Das führt natürlich zum falschen Ergebnis. Es hat in Summe ganze 15 Minuten benötigt

[jobo]

Zu Ergebnis = 48 Stück.

Ich weiß nicht, wie der Optimizer von FB tickt. Aber ich würde versuchen, ihm zu helfen und folgende Strategie anwenden:
Die Kriterien, die die heftigste Einschränkung liefern für ein Kern-Select-Statement verwenden. (Das sind vielleicht bereits die bloßen Joins) Sodass ein grobes Ergebnis auf weniger als 5000 Datensätze kommt, darf auch weniger sein.
Auf dem Rest dann den ganzen Schnickschnack mit like '%' Or / in () usw. Mglw. geht das, wenn man das Kernselect klammert und außen dann die restlichen Kriterien abfragt.

So ungefähr (vorschlag):

Delphi-Quellcode:

select *
  from ( 
        select *
          from MASTER
          Left Outer JOIN SLAVE
            on MASTER.Vorgangsnummer = SLAVE.Vorgangsnummer
           and (SLAVE.ZusatzStatus = 'EZ' or SLAVE.ZusatzStatus = 'EV' or
               SLAVE.ZusatzStatus = 'EF' or SLAVE.ZusatzStatus = 'EA' or
               SLAVE.ZusatzStatus = 'EQ' or SLAVE.ZusatzStatus = 'EP')
          Left Outer Join MASTER UrMaster
            on MASTER.Vorgangursprung = UrMaster.Vorgangsnummer
         where MASTER.Status = 'In Bearbeitung'
           and SLAVE.SlaveNummerr is not NULL
           and ((SLAVE.SlaveStatus = 'Warten' or
               SLAVE.SlaveStatus = 'Unterbrochen' or
               SLAVE.SlaveStatus = 'In Arbeit' and SLAVE.Dzusatz is NULL) or
               (SLAVE.SlaveStatus = 'In Abreit' and
               SLAVE.Dzusatz = 'Gesperrt' and
               SLAVE.Sollende < Current_Date))
           and MASTER.FATW = 'Aktive'
       ) as Kernselect
 where ((    (Kernselect.AltesSystem like 'Start%' or Kernselect.AltesSystem like 'Vorgang%') 
         and  Kernselect.AltesSystem not like '%Abbruch%' 
         and  Kernselect.Storno is null
        ) 
       or 
        (Kernselect.referenznr = 'ABT_25')
       )

Wie man genau die Kriterien nach innen oder außen legt, muss anhand des Ausführungsplans oder Try/Error bestimmt werden. Ach und nochmal die Frage: Sind es wirklich alles Outer Joins oder vielleicht doch feste Joins? Ändert sich die Ergebnismenge, wenn Du feste Joins draus machst?

[Dumpfbacke]

Zitat:

Zitat von hstreicher (Beitrag 1340884)

Bei IBExpert kann man einfach ein(e)/paar Registerkarte weiterklicken und kriegt den Execpution Plan angezeigt sowie die Abruf Statistiken nach Index Reads und nonindexed Read
da sieht mah schnell wo die Zeit verbaucht wird


ggf mal den Plan hier posten

mfg Hannes

Hier die gewünschten Daten

------ Performance info ------
Prepare time = 32ms
Execute time = 12m 34s 826ms
Avg fetch time = 41.934,78 ms
Current memory = 17.287.612
Max memory = 17.312.136
Memory buffers = 800
Reads from disk to cache = 414.449
Writes from cache to disk = 19.315
Fetches from cache = 14.407.183

Performace Analysis
Slave 34.444
Master 436.807

[hstreicher]

nein , das sind sie nicht:
Die Gewünschten Daten sind der Execution Plan

in IBExpert

im SQL Editor die 4. Karteikarte Plananalyse
(hier sieht man wo ein Index benutzt wird)

und die 5 Leistungsanalyse
hier die DAten aus dem 2 Karte Additional
(hier sieht an wieviel Daten über den Index gefunden werden und wieviel davon ohne)


mfg Hannes