Benötigte Zeit für einen Abfrage

[Dumpfbacke]

Hallo Leute,
ich habe hier eine Frage zur benötigten Zeit für eine Abfrage. Es wird in der Where Abfrage zwei Felder abgefragt, auf beiden Feldern liegt jewals ein extra Index.

Fall 1

Delphi-Quellcode:

Where Feld1 = '4EE' and Feld2 = 'Frankfurt'

Fall 2

Delphi-Quellcode:

Where Feld1 = '4EE' and (Feld2 = 'Frankfurt' or Feld2 = 'München'

Nun zu meiner Frage.Warum dauert es denn bei dem zweiten Fall erheblich länger als bei den ersten Fall ? Ich habe es mit IBExpert überprüft und dort ist es genau so.

Wie kann denn so etwas sein und wie kann ich es denn Ändern ?

Tanja

[Sir Rufo]

Weil für die zweite Abfrage kein passender Index verfügbar ist ;)

Erstelle dir einen Index der beide Felder beinhaltet, dann sollte das fixer gehen

[Furtbichler]

Die frage ist doch: Wieso dauert es wesentlich länger, wenn man auf dem nicht indizierten Feld2 auf zwei Werte statt auf einen Wert prüft? Gegenfrage:
Ist ein Unterschied zwischen

Code:

where Feld1='Foobar' and (Feld2 = 'Frankfurt' or Feld2='München')

und

Code:

where Feld1='Foobar' and Feld2 in ('Frankfurt','München')

Ich vermute, beides ist gleich langsam.

Falls das wirklich wesentlich langsamer ist als

Code:

where Feld1='Foobar' and Feld2='Frankfurt'

, ist das ein Bug in FB bzw. eine Schlamperei.

[himitsu]

In MySQL/Postgres gibt es sowas wie

Delphi-Quellcode:

EXPLAIN ANALYSE SELECT * FROM xyz WHERE ...

.

Gibt es das in Firebird auch?
Dann siehst du ja wie/ob welcher Index verwendet wird.

[mjustin]

Die Ursache (fehlender oder ungünstiger Index) läßt sich über den "Query Plan" ermitteln, mit etwas Glück unterstützt IBX den SQL Monitor auch für Firebird: "Use the SQL Monitor" aus dem Artikel "InterBaseExpress: Tips and Tricks".

Eine weitere Möglichkeit ist IBExpert, damit ist (in der professionellen Version) für jede SQL Abfrage auch der Plan ablesbar.

Die dritte Möglichkeit an den Query Plan zu gelangen ist, die Property TIBSQL.Plan auszulesen.

[dataspider]

Wenn auf beiden Feldern ein Index liegt, sollte der PLAN auch beide Indexe zeigen.

In der Anlage ein Screenshot von IBExpert.
Wie man da ersehen kann, arbeitet der Optimizer von Firebird (2.52) hier absolut korrekt.


Frank

[jobo]

Ein OR Kriterium bedeutet -unter Verwendung von Indizierung- immer, dass die DB >nacheinander< den Surchvorgang für die OR Kriterien durchführen muss.
Hier also:
Suche alle Sätze ~"mit München"
dann
Suche alle Sätze ~"mit Frankfurt"

Angenommen, solche Suchvorgänge dauern immer gleich lang, dann dauert die Suche nach 'München or Frankfurt' doppelt so lange wie die Suche nach nur einem der Orte.
Ist ein weiteres Kriterium im Spiel, kann diese doppelte Suche auf der Teilmenge vorgenommen werden, die sich durch das konstante 2. Kriterium ergibt und die ist mglw so klein, dass es gar nicht auffällt. Das Verhalten hängt dann von der Implementierung des Optimizers ab.

Das oben Genannte gilt nur bedingt oder gar nicht für teil- oder gar nicht indizierte Kriterienfelder bzw. Kriterien, die nicht mittels Index untersucht werden können. Siehe Antwort von Sir Rufo.

Ist bspw. das erste gemeinsame Suchkriterium- hier 4EE- von geringer >Selektivität<, dann ist die sich daraus ergebende Einschränkung so gering, dass ein fehlender Index auf dem 2 Kriterium praktisch einem Full Table Scan gleich kommt.

[Furtbichler]

Zitat:

Zitat von jobo (Beitrag 1241228)

Ein OR Kriterium bedeutet -unter Verwendung von Indizierung- immer, dass die DB >nacheinander< den Surchvorgang für die OR Kriterien durchführen muss.

Das ist zu pauschal und stimmt wegen dem 'immer' schon mal nicht immer ;-). Man kann es parallelisieren oder optimierte Suchen nach mehreren Schlüsseln verwenden, die das in einem Durchlauf erledigen. Je nach Anzahl der zu suchenden Werte können unterschiedliche Strategien verwendet werden.

Zitat:

Zitat von dataspider (Beitrag 1241227)

Wie man da ersehen kann, arbeitet der Optimizer von Firebird (2.52) hier absolut korrekt.

Finde ich nicht. Andere RDBMS benötigen hier nicht viel länger. Ich habe es gerade mit SQL-Server ausprobiert. Der verwendet unterschiedliche Strategien, je nachdem, ob 1,2 oder mehr Werte per OR verknüpft sind.

[Jasocul]

Gründe für dieses Verhalten kann es mehrere geben. Ich bin kein Firebird-Spezi, aber ein paar Dinge sollten vielleicht mal geprüft werden. Meistens ist ein "OR" für eine DB nicht optimal. Das gilt auch für andere Bedingungen. ">=" ist z.B. besser zu verarbeiten als ">", wenn ein Index auf dem Feld liegt.

Vergleiche die Geschwindigkeit für beide Städte getrennt. Falls dort ein auffälliger Unterschied ist, kann auch eine unterschiedliche Spracheinstellung zwischen Client und Server die Ursache sein (München enthält deutsches Sonderzeichen).

Die Reihenfolge der Bedingungen kann eine Rolle spielen. Einfach mal ausprobieren, ob es was in diesem Fall bringt, diese zu verändern.

Eine Abfrage mit "IN", wie weiter oben schon beschrieben ist manchmal besser zu verarbeiten, als ein "OR".

Manchmal sind 2 Abfragen mit einem "UNION" schneller.

Auf jeden Fall sollte man den Analyse-Plan anschauen. Der gibt schon eine Menge Hinweise, wo die Abfrage ausgebremst wird.

[jobo]

Zitat:

Zitat von Furtbichler (Beitrag 1241234)

Das ist zu pauschal und stimmt wegen dem 'immer' schon mal nicht immer ;-).

Furtbichler, danke für Deine Korrektur, zumal Du selbst zuvor von Bug und Schlamperei geschrieben hast. ;)

Sobald ein "intelligenter" Optimizer unter Berücksichtigung von Statistiken arbeitet, ist das Verhalten aus der Ferne schwer zu beurteilen, bzw. vorherzusagen. Ich hab pauschal mal das grundsätzliche Verhalten beschrieben.
Erfahrungsgemäß interessiert es doch keine Sau, wie so etwas im Detail funktioniert, solange es funktioniert. Die Standardlösung ist immer ;), den fehlenden Index einzubauen und fertig.

[Furtbichler]

Zitat:

Zitat von jobo (Beitrag 1241254)

Erfahrungsgemäß interessiert es doch keine Sau, wie so etwas im Detail funktioniert, solange es funktioniert. Die Standardlösung ist immer ;), den fehlenden Index einzubauen und fertig.

Rischtisch. Es interessiert die meisten Säue nicht. Mich als Klugscheißersau schon. Dank deiner Erklärung habe ich mir mal das Verhalten vom SQL-Server genauer angeschaut und mal wieder dazugelernt, wie unterschiedlich MSSQL und FB sind (und zwar nur, was den Optimizer anbelangt).

[IBExpert]

Zitat:

Zitat von Sir Rufo (Beitrag 1241209)

Weil für die zweite Abfrage kein passender Index verfügbar ist ;)
Erstelle dir einen Index der beide Felder beinhaltet, dann sollte das fixer gehen

nein, Firebird kombiniert in einem SQL pro Tabelle auch mehrere Einzelindizes, wenn das sinnvoll
erscheint. Die beiden Einzelindizes sind also durchaus korrekt.

Über welche Zeitunterschiede und welche Datenmengen reden wir denn eigentlich.

Der IN Operator ist nicht schneller als die die OR Implementation, das sieht man am Plan, der für beide
Varainten meistens identisch ist.

Wenn solche Abfragen bei dir Standard sind, dann kann es ggf sogar sinnvoll sein, das du auf
Feld 2 gar keinen Index hast, dann muß Firebird in der Ergebnismenge auf Feld1 einen Tablescan
machen. Solche Probleme treten aber erst bei sehr großen Datenmengen wirklich relevant auf.

[Dumpfbacke]

Zitat:

Zitat von IBExpert (Beitrag 1241271)

nein, Firebird kombiniert in einem SQL pro Tabelle auch mehrere Einzelindizes, wenn das sinnvoll
erscheint. Die beiden Einzelindizes sind also durchaus korrekt.

Über welche Zeitunterschiede und welche Datenmengen reden wir denn eigentlich.

Der IN Operator ist nicht schneller als die die OR Implementation, das sieht man am Plan, der für beide
Varainten meistens identisch ist.

Wenn solche Abfragen bei dir Standard sind, dann kann es ggf sogar sinnvoll sein, das du auf
Feld 2 gar keinen Index hast, dann muß Firebird in der Ergebnismenge auf Feld1 einen Tablescan
machen. Solche Probleme treten aber erst bei sehr großen Datenmengen wirklich relevant auf.

Die Datenmenge ist leider etwas größer. Das Ergebnis beinhaltet 19 Treffer und die Tabelle enhält leider 192585 Datensätze.

EIn in geht auch nicht schneller dieses habe ich bereits geprüft. Das ein Or jeodch so lange dauert hätte ich nicht erwartet.

Tanja

[Union]

Also unter 200k Sätze ist ja nicht wirklich groß. Ist das die komplette Query oder nur der "Kern"? Hängen da in der Realität noch Joins dran? Und von welchen Laufzeiten (in ms) sprechen wir hier? Wird das einmalig oder in einer Schleife aufgerufen?

[Dumpfbacke]

Zitat:

Zitat von Union (Beitrag 1241273)

Also unter 200k Sätze ist ja nicht wirklich groß. Ist das die komplette Query oder nur der "Kern"? Hängen da in der Realität noch Joins dran? Und von welchen Laufzeiten (in ms) sprechen wir hier? Wird das einmalig oder in einer Schleife aufgerufen?

Leider hast du hier recht. Ich war mir sicher das ich die Join auskomentiert hatte und es wurde nicht schneller. Es kommt also von dem leider sehr komplexen Join. Oh man ab und zu steht man einfach auf dem Schlauch und sucht am falschen Ende. :oops: :evil:

Ich versuchen hier mal alles Darzustellen:

Tabelle 1:

Delphi-Quellcode:

Feld1 VarChar(3)
Feld2 VarChar(40)
Feld3 VarChar(40)
BNummer VarChar(12)
und noch viele weitere Felder

Es ist auf jedem Feld von oben ein Index

Tabelle2:

Delphi-Quellcode:

AAZaehler Numeric (9,0)
AAStatus CarChar(13)
BNummer VarChar(12)
Kriterium VarChar(35)
und noch viele weitere Felder

Es ist auf jedem Feld ein Index und auf dem AAZaehler noch ein DESC Index wegen dem Maxwert

So sieht nun mein Select Aus

Delphi-Quellcode:

....
From Tebelle1
Left Outer join Tabelle2 on (Tabelle1.BNummer = Tabelle2.BNummer and
                            Tabelle2.aazaehler = (Select Max(AAZaehler) from Tabelle2 AA where AA.BNummer = Tabelle1.BAnummer and
                            AA.Kriterium = 'ID' and AA.AAStatus <> 'Erledigt' ))
Where Tabelle1.Feld1 = '4EE' and (Tabelle1.Feld2 = 'Frankfurt' or Tabelle1.Feld2 = 'München'

Ich muss leider der Join so machen auf jeden Fall aus meiner Sicht. Eventuell kann es ja jemand besser und gibt mir einen Tip.

Ich beschreibe es mal in Worten
Ich möchte alle Datensätze aus der Tabelle1 haben bei dem im Feld1 4EE und im Feld2 Frankfurt oder München steht. Des weiteren soll noch ein Spalte (AAZaehler) aus der Tabelle2 kommen. Hier ist nun mein Problem. Es können in der Tabelle mehrere Datensätze mit den Kriteren sein. Ich möcht den mit dem größten AAZaheler.

Inhalt der Tabelle2

Delphi-Quellcode:

AAZaehler  AAStatus   Kriterium BNummer
1          Erledigt   ID         1
2          Offen      ID         1
3          Neu        ID         1
4          Erledigt   ID         1
5          Neu        AA         2

Es sollte also bei dem Select aus der Tabelle2 der AAZaehler 3 und nur diser herauskommen.

Danke schon einmal und Entschuldigung das ich Euch zuerst in die falsche Richtung geschickt habe.

Tanja

[Union]

Also ich würde entweder den Join als View anlegen (mit einem GROUP BY) oder mit einer Subquery arbeiten.

[nahpets]

In Ermangelung einer entsprechenden Datenbank (und daher ungetestet) hier ein Vorschlag, wie er unter Oracle funktionieren könnte:

Delphi-Quellcode:

  select *
  from Tabelle1,
       Tabelle2,
       (Select Max(AAZaehler) As AAZaehler
        from  Tabelle2
        where Kriterium = 'ID'
        and   AAStatus <> 'Erledigt'
       ) MaxAAZaehler
  where Tabelle1.BNummer   = Tabelle2.BNummer (+)
  and   Tabelle1.Feld1     = '4EE'
  and   Tabelle1.Feld2     = 'Frankfurt'
  and   Tabelle2.aazaehler = MaxAAZaehler.aazaehler
  union
  select *
  from Tabelle1,
       Tabelle2,
       (Select Max(AAZaehler) As AAZaehler
        from  Tabelle2
        where Kriterium = 'ID'
        and   AAStatus <> 'Erledigt'
       ) MaxAAZaehler
  where Tabelle1.BNummer   = Tabelle2.BNummer (+)
  and   Tabelle1.Feld1     = '4EE'
  and   Tabelle1.Feld2     = 'München'
  and   Tabelle2.aazaehler = MaxAAZaehler.aazaehler;

Sofern die "Oraclevariante" nicht funktioniert eine View anlegen:

Delphi-Quellcode:

create view V_MaxAAZaehler as
Select Max(AAZaehler) As AAZaehler
from  Tabelle2
where Kriterium = 'ID'
and   AAStatus <> 'Erledigt'

und dieses SQL probieren:

Delphi-Quellcode:

select *
  from Tabelle1,
       Tabelle2,
       V_MaxAAZaehler
  where Tabelle1.BNummer   = Tabelle2.BNummer
  and   Tabelle1.Feld1     = '4EE'
  and   Tabelle1.Feld2     = 'Frankfurt'
  and   Tabelle2.aazaehler = V_MaxAAZaehler.aazaehler
  union
  select *
  from Tabelle1,
       Tabelle2,
       V_MaxAAZaehler
  where Tabelle1.BNummer   = Tabelle2.BNummer
  and   Tabelle1.Feld1     = '4EE'
  and   Tabelle1.Feld2     = 'München'
  and   Tabelle2.aazaehler = V_MaxAAZaehler.aazaehler

oder eventuell auch

Delphi-Quellcode:

select *
  from Tabelle1,
       Tabelle2,
       V_MaxAAZaehler
  where Tabelle1.BNummer = Tabelle2.BNummer
  and Tabelle1.Feld1 = '4EE'
  and (Tabelle1.Feld2 = 'Frankfurt' or Tabelle1.Feld2 = 'München')
  -- bzw.
  -- and Tabelle1.Feld2 in ('Frankfurt','München')
  and Tabelle2.aazaehler = V_MaxAAZaehler.aazaehler

Konstrukte dieser Art:

Delphi-Quellcode:

... and Tabelle2.aazaehler = (Select Max(AAZaehler) from Tabelle2 AA where AA.BNummer = Tabelle1.BNummer...

haben sich in der Vergangenheit bei meinen Implementierungen häufig als arge Flaschenhälse oder Perfomancekiller herausgestellt, so dass ich es tunlichst vermeide, derartige Konstrukte zu verwenden.
Je nach Datenbank und Cleverness des Optimierers kann es sein, dass das Subselect in der Wherebedingung je Datensatz ausgeführt wird. Hier kommen dann auch bei wenigen Millisekunden für die Ausführung des Subselectes für einen Datensatz, doch recht schnell sehr hohe Laufzeiten für ein paar tausend Datensätze heraus.

[jobo]

Zitat:

Zitat von Furtbichler (Beitrag 1241262)

Dank deiner Erklärung habe ich mir mal das Verhalten vom SQL-Server genauer angeschaut und mal wieder dazugelernt, wie unterschiedlich MSSQL und FB sind (und zwar nur, was den Optimizer anbelangt).

Ich würde bei einem kommerziellen Produkt eines Marktführers auch ganz andere Maßstäbe anlegen, als bei einem kostenlosen OpenSource Produkt. Selbst oder gerade bei guten Optimizern bleibt es dank ihrer Komplexität, mangelnder Kenntnis des Statistik Zustandes und der Grenzwert Gewichtungen im Extremfall immer wieder eine Überraschung, was der Optimizer macht.

Zitat:

Zitat von Dumpfbacke (Beitrag 1241279)

..

So sieht nun mein Select Aus

Delphi-Quellcode:

....
From Tebelle1
Left Outer join Tabelle2 on (Tabelle1.BNummer = Tabelle2.BNummer and
                            Tabelle2.aazaehler = (Select Max(AAZaehler) from Tabelle2 AA where AA.BNummer = Tabelle1.BAnummer and
                            AA.Kriterium = 'ID' and AA.AAStatus <> 'Erledigt' ))
Where Tabelle1.Feld1 = '4EE' and (Tabelle1.Feld2 = 'Frankfurt' or Tabelle1.Feld2 = 'München'

Ich muss leider der Join so machen auf jeden Fall aus meiner Sicht. Eventuell kann es ja jemand besser und gibt mir einen Tip.

Ich habe in meiner ersten Antwort etwas von Selektivität geschrieben- kannste ja mal nachschlagen. Du kannst versuchen, Dir das hier zu nutze zu machen und dem Optimizer etwas auf die Sprünge zu helfen. Wenn es also tatsächlich 200k Sätze sind- nicht viel, aber auch nicht zu vernachlässigen-, die Dank der Where Bedingung 4EE+Frankfurt|München auf 19 zusammenschmilzen, ist das eine sehr brauchbare Reduktion, mit der ich experimentieren würde.
Z.B.:
Das oben zitierte Statement so umbauen, dass Tabelle1 Select mit Where Bedingung ganz allein in einem inneren Selekt liegt und der Join auf Tabelle2 mit Group By "darüber".

Code:

Select * from (Select * from Tabelle1 where 4EE and Franktfurt|München)
Left Outer join Tabelle2 on (siehe oben)