Abfrage dauert zu lange unter Delphi

[Dumpfbacke]

Zitat von tsteinmaurer:

Jegliche Ungleichheitsoperatoren (<>, IS NOT, NOT IN etc.) können in der Regel nicht durch einen Index bedient werden, d.h. der einzige Index der ev. greift ist auf dem Feld "Eingang".

Mich würde primär mal der Ausführungsplan interessieren.

Also das in ist hier natürlich falsch und unlogisch. Es muss hier nur eine Wert ausgeschlossen werden und deshalb wird hier auch ein <>. Ich hatte es mal geändert im zu sehen ob sich was ändert und dann ganz vergessen es hier zu ändern.
Also : and MaterialTyp <> 'Rohre'

Nun zum Ausführungsplan. Leider ist es richtig das hier wirklich nur ein Index greift und zwar der vom Feld Eingang. Mir war nicht bewusst das beim negieren der Index nicht mehr greift. Man lernt immer dazu.
Also Dauert es nun wirklich so lange bis die wenigen Datensätze ausgelesen sind ? Gibt es noch eine Möglichkeit für mich wo ich etwas ändern kömnnte ?

Schon einmal Danke alle hier Ihr seit einfach die Experten.

[mkinzler]

Zitat:

Also Dauert es nun wirklich so lange bis die wenigen Datensätze ausgelesen sind ?

Das hat nichts mit der Anzahl der zurückgegebenen Datensätze zu tun, sondern mit der Tatsache, dass kein Index greift.

Zitat:

Gibt es noch eine Möglichkeit für mich wo ich etwas ändern kömnnte ?

Ja, Normalisierung ( Materialtyp, Statusraus, usw.)

[jobo]

Wenn Du von 3,8 mio auf 20 Datensätze kommst, muss irgendetwas in den Parametern ja diese Einschränkung bringen.
Das Kriterium, das die größte Einschränkung bringt, sollte idealerweise per Index laufen.
Neben den genannten Vorschlägen, würde ich mal probieren, wieviel die eine Spalte mit Index bringt, also wie groß /klein das Ergebnis bereits ist und das in einer Kapselung weiter einschränken, wenn es bereits klein genug ist.
Also bspw. im ersten Schritt per Kriterium 1 von 3,8 Mio auf 10 T und im 2. die restlichen Kriterien anwenden, ist dann quasi nur noch etwas Kopfrechnen.
Im Grunde ist das Spielerei mit dem Optimizer, geht vermutlich auch per Hints, weiß nicht genau wie das bei Firebird ist.
Konkret wäre noch der Vorschlag, das schwache
Referenz is not null gegen ein kräftiges
Referenz> ' '
oder anderes auszutauschen, je genauer, desto besser. Dann könnte hier auch der Index greifen. Muss natürlich sichergestellt sein, dass das Kriterum auch die gleiche Menge ergibt.

[hoika]

Hallo,
ich warte immer noch auf den Query-Plan ...

Heiko

[tsteinmaurer]

Wir alle warten auf den Ausführungsplan , aber im Wesentlichen hat der Thread-Ersteller die Indexverwendung schon bestätigt. Ich würde jetzt sogar noch etwas weiter ausholen, nämlich:

• Welche Firebird Version wird genau eingesetzt?
• Welche Firebird Architektur?
• Was ergibt gstat -h, da ich mal stark davon ausgehe, dass bzgl. Page Cache die Default-Einstellungen verwendet werden
• Wie oft wird diese Abfrage ausgeführt, sprich welches Optimierungspotential hat man hier eigentlich?
• Wie viele Datensätze befinden sich in der Tabelle und wieviele Datensätze werden ca. von der Abfrage zurückgeliefert?

Wie gesagt, jegliche Negation unterbindet die Verwendung eines Index. D.h. man könnte den Spieß umdrehen und z.b. zusätzlich eine Art "Status-Schattenfeld" via Trigger mitwarten, um darüber mit einem nicht-negierten indexierten Zugriff einen Großteil der Datenmenge auszufiltern. Hängt halt stark davon ab wieviele Kombinationsstati es gibt. Ob sich dieses Vorgehen lohnt ist halt sehr spezifisch (bzgl. Datenvolumen, Selektivität der neuen Schattenstati etc.), aber durchaus in der Praxis anzutreffen.

LG

[Dejan Vu]

Zitat von tsteinmaurer:

Wie gesagt, jegliche Negation unterbindet die Verwendung eines Index.

Das verstehe ich nicht. Wenn das so implementiert ist, geht reichlich Potential flöten. Meistens ist es zu teuer, einen Index zu verwenden, aber möglich wäre es schon.
Beispiel: 'Field <> 'b'
Der Index sieht so aus: 'AAAbbbbbbbbCCC', d.h. wir haben 14 records, 3 mit 'A' und 8 mit 'b' und 3 mit 'C'.
Ohne Verwendung des Index müssen wir einen Scan durchführen, mit Verwendung können wir die Sequenz (darum geht es doch beim Abarbeiten der Query) für diese Bedingung einfach durch Konkatenation der beiden Teilindexe 'AAA' und 'CCC' bilden. Das ist (hier) wesentlich schneller als der Scan.

Die Verwendung des Index ist aber teuer, sodaß er selten dieser Fall seltener Einsatz kommt, aber in Extremfällen (z.B. 90% der Records haben im Feld 'b' zu stehen), bringt das durchaus etwas, einen Index zu verwenden.

[tsteinmaurer]

Ist technisch erst möglich wenn zum Zeitpunkt der Planerstellung der Optimizer Kenntnis über die Verteilung der Daten hat. Aktuell gibt es diese Information nicht, sondern es wird banal gesagt derzeit alles über den Selektivitäts-Kamm eines Index geschert. Mit der Einführung der Speicherung von Histogrammdaten für Indizes schauts dann anders aus. Hier hat dann der Optimizer zur Prepare-Time viel mehr Möglichkeiten.

Ein extremeres Beispiel ist mit einem Boolean-Feld. Angenommen wir haben eine große Tabelle (> 1 Mio Datensätze) wo nur ein Datensatz den Wert FALSE beinhaltet. Die Verwendung eines Index für die Suche nach TRUE wird in der Regel immer langsamer sein als ein Full-Table Scan, weil für den Zugriff zum eigentlichen Datensatz immer doppelt gemoppelt wird, sprich Index Page laden, Lookup des Datensatzes = Data Page laden. Abfrage des einen Datensatzes mit FALSE siehts natürlich ganz anders aus. Bei der Mitführung eines Histogramms würde der Optimizer wissen, dass 999999 Einträge TRUE speichern und 1er FALSE. Dieses Wissen kann sich dann der Optimizer zu nutze machen, dass je nach Abfrageprofil ein Index verwendet wird oder auch nicht.

Siehe auch: http://tracker.firebirdsql.org/browse/CORE-1686

Ursprünglich für Firebird 3 geplant, aber aktuell für V3 rausgenommen.

[Dejan Vu]

Stimmt. Ohne Statistiken ist hier nichts zu holen. Danke für die Aufklärung.

[Dumpfbacke]

Zitat von tsteinmaurer:

Wir alle warten auf den Ausführungsplan , aber im Wesentlichen hat der Thread-Ersteller die Indexverwendung schon bestätigt. Ich würde jetzt sogar noch etwas weiter ausholen, nämlich:

• Welche Firebird Version wird genau eingesetzt?
• Welche Firebird Architektur?
• Was ergibt gstat -h, da ich mal stark davon ausgehe, dass bzgl. Page Cache die Default-Einstellungen verwendet werden
• Wie oft wird diese Abfrage ausgeführt, sprich welches Optimierungspotential hat man hier eigentlich?
• Wie viele Datensätze befinden sich in der Tabelle und wieviele Datensätze werden ca. von der Abfrage zurückgeliefert?

Wie gesagt, jegliche Negation unterbindet die Verwendung eines Index. D.h. man könnte den Spieß umdrehen und z.b. zusätzlich eine Art "Status-Schattenfeld" via Trigger mitwarten, um darüber mit einem nicht-negierten indexierten Zugriff einen Großteil der Datenmenge auszufiltern. Hängt halt stark davon ab wieviele Kombinationsstati es gibt. Ob sich dieses Vorgehen lohnt ist halt sehr spezifisch (bzgl. Datenvolumen, Selektivität der neuen Schattenstati etc.), aber durchaus in der Praxis anzutreffen.

LG

Hier nun meine Antworten.
Das Probelm liegt hier eindeutig am Index. Ich habe es wie hier Geschrieben mit einen Zusätzlichen Feld gelöst. Für weitere Anregungen bin ich immer offen. Wenn mir geholfen wird und ich vor allen noch etwas lernen kann bin ich immer dabei.

• Welche Firebird Version wird genau eingesetzt? Antwort :2.5
• Welche Firebird Architektur? Antwort Classic oder Super Classic
• Was ergibt gstat -h, da ich mal stark davon ausgehe, dass bzgl. Page Cache die Default-Einstellungen verwendet werden Siehe Unten
• Wie oft wird diese Abfrage ausgeführt, sprich welches Optimierungspotential hat man hier eigentlich? alle 15 Minuten. Das Problem war da es mehrer Abfragen / Aufgaben gab und es so in Summe länger als 15 Minten dauerte. Dorch die Optimierung bin ich nun unter 10 Minuten in Summe für alles
• Wie viele Datensätze befinden sich in der Tabelle und wieviele Datensätze werden ca. von der Abfrage zurückgeliefert?Anazhl > 3,7 Millionen und Rurückgeliefert werden zwischen 0 und 50

Inhalt von gstat

Database header page information:
Flags 0
Checksum 12345
Generation 299054
Page size 16384
ODS version 11.2
Oldest transaction 288592
Oldest active 295032
Oldest snapshot 295032
Next transaction 295513
Bumped transaction 1
Sequence number 0
Next attachment ID 3538
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Feb 11, 2012 13:40:10
Attributes force write

Variable header data:
*END*


Ups hier wirde eine alte Transaction anscheinend nicht geschlossen. Muss ich doch mal im Code prüfen wo der Fehler liegt.

Was ist ein Ausführungsplan ?

[Sir Rufo]

Ich werfe es mal in die Runde:

Eine zusätzliche Zugriffs-Schicht zwischen der Anwendung und der Datenbank kann dieses Problem sehr elegant lösen, denn Änderungen laufen durch diese Schicht, und ohne Änderungen ändert sich das angeforderte Ergebnis auch nicht und kann also aus dem Cache beantwortet werden. Erst nach einer Änderung wird die Abfrage wirklich erneut ausgeführt.

Das bedingt natürlich, dass man sich in der Anwendung vom SQL-Gedanken trennt und dort mehr auf den echten Kontext konzentriert.