Abfrage dauert zu lange unter Delphi

[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.

[tsteinmaurer]

@Dumpfbacke:

Zitat:

Was ist ein Ausführungsplan ?

Hmm, wenn du fragst was ein Ausführungsplan ist, dann stellt sich für mich die frage, ab welchem KnowHow-Level sollen wir jetzt Firebird-seitig ansetzen? Der Ausführungsplan ist quasi das Navi für den Optimizer wie die Abfrage zu den angeforderten Daten kommt. Du hast sicher irgendein Tool zur DB-Entwicklung im Einsatz. Mach in einem SQL Editor einfach mal ein Prepare deiner Abfrage da sollte das Tool dann in der Regel irgendwo den PLAN auspucken.

Wenn du die Abfrage auch tatsächlich ausführst, dann sollte auch die Information wieviele Indexed/Non-Indexed Reads etc. durchgeführt wurden.

Zitat:

Page buffers 0 in gstat

D.h. bei (Super)Classic default 75 pages fürn Cache, sofern in firebird.conf der Defaultwert nicht erhöht wurde, von dem ich mal nicht ausgehe. Ich würd sonst mal Tests für dieses Setting mit 1024 bzw. 2048 machen. Gesetzt kann das mit dem Tool deiner Wahl oder gfix werden. Wieviele max. Connections hast du auf die DB und wieviel RAM die Maschine mit dem Firebird Server?

Zitat:

Anazhl > 3,7 Millionen und Rurückgeliefert werden zwischen 0 und 50

Eigentlich ein perfektes Szenario für einen Index, da die Ergebnismenge nur einen Bruchteil der Ausgangsmenge darstellt.

@Sir Rufo:
Grundsätzlich ja, aber hier meiner Meinung totaler Overkill. Da ist dann die Frage der Aktualität des Caches, der Invalidierung etc. Der Firebird Page Cache + OS File Cache reicht hier in der Regel aus.

[Dumpfbacke]

Zitat:

Hmm, wenn du fragst was ein Ausführungsplan ist, dann stellt sich für mich die frage, ab welchem KnowHow-Level sollen wir jetzt Firebird-seitig ansetzen? Der Ausführungsplan ist quasi das Navi für den Optimizer wie die Abfrage zu den angeforderten Daten kommt. Du hast sicher irgendein Tool zur DB-Entwicklung im Einsatz. Mach in einem SQL Editor einfach mal ein Prepare deiner Abfrage da sollte das Tool dann in der Regel irgendwo den PLAN auspucken.

Wenn du die Abfrage auch tatsächlich ausführst, dann sollte auch die Information wieviele Indexed/Non-Indexed Reads etc. durchgeführt wurden.

Nein alles klar ich habe nur den Begriff nicht gekannt.

Es sieht jetzt anders aus da ich ja ein Index auf ein neues Feld gesetzt habe damit ich die Auswahl schon mal sehr weit veringern konnte.

Plan
PLAN (Material INDEX (IDX_Material_STANDARDARBEITSAUFT))

------ Performance info ------
Prepare time = 21ms
Execute time = 2s 553ms
Avg fetch time = 150,18 ms
Current memory = 173.919.496
Max memory = 181.610.688
Memory buffers = 10.240
Reads from disk to cache = 26.893
Writes from cache to disk = 0
Fetches from cache = 146.993

Zitat:

D.h. bei (Super)Classic default 75 pages fürn Cache, sofern in firebird.conf der Defaultwert nicht erhöht wurde, von dem ich mal nicht ausgehe. Ich würd sonst mal Tests für dieses Setting mit 1024 bzw. 2048 machen. Gesetzt kann das mit dem Tool deiner Wahl oder gfix werden. Wieviele max. Connections hast du auf die DB und wieviel RAM die Maschine mit dem Firebird Server?

Sollte eigentlich gesetzt sein.

Daten aus der Conf
# Number of cached database pages
#
# This sets the number of pages from any one database that can be held
# in cache at once. If you increase this value, the engine will
# allocate more pages to the cache for every database. By default, the
# SuperServer allocates 2048 pages for each database and the classic
# allocates 75 pages per client connection per database.
#
# Type: integer
#

DefaultDbCachePages = 1024

Auf diese DB greifen nicht viele zu. Es sind 3 Stück und Ram hat die Kiste so 6 GB

[tsteinmaurer]

Naja, eigentlich wäre der PLAN von deinem Originalstatement interessant gewesen. Das Statement unten dauert jetzt < 3 Sekunden d.h. was passiert jetzt mit dem Rest der von dir zuletzt genannten 10 Minuten?

[Dumpfbacke]

Zitat von tsteinmaurer:

Naja, eigentlich wäre der PLAN von deinem Originalstatement interessant gewesen. Das Statement unten dauert jetzt < 3 Sekunden d.h. was passiert jetzt mit dem Rest der von dir zuletzt genannten 10 Minuten?

Kein Problem habe ich schnell mal erzeugt.

Plan
PLAN (Material INDEX (IDX_Material_Eingang))

------ Performance info ------
Prepare time = 10ms
Execute time = 6m 26s 316ms
Current memory = 174.484.452
Max memory = 181.610.688
Memory buffers = 10.240
Reads from disk to cache = 104.184
Writes from cache to disk = 0
Fetches from cache = 1.544.588

Das Programm steuert ein anderes Programm da ich handarbeit nicht mag. Hierzu wird die Zeit benötigt.