Langsame Abfrage

[hartmutfesting]

Hallo,

ich versuche folgende Abfrage auszuführen (Firebird 2.1.3):

select SUM(ANZAHL_A*TAGESPREIS) GESPREIS from WAEBEWEGUNGEN where WID=:WID and AUSGANG='T' and ADATUM<='31.12.2010' and ADATUM>='01.01.2010';

Das dauert ewig... Also: die Anzahl der zu durchlaufenden WID's is ca. 5.500 - in der abzufragenden Datenbank WAEBEWEGUNGEN sind ca. 300.000 Einträge und sie hat einen PK. Mein SQL-Tool sagt mir folgendes (und bestätigt damit die Langsamkeit):

Prepare: 0 ms Execute: 344 ms Fetch: 344 ms

-> denn daraus ergibt sich: 5500*0,35=1925 sek. -> 1925/60=32,1 Minuten für diese Abfrage. Als StoredProcedure gehen die Zeiten unwesentlich auf ca. 318 ms herunter...immer noch viel zu lang.

Hat da jemand eine Idee, wo das Haken kann?

Hartmut

[Markus Kinzler]

Wie sieht es mit Indizes aus?
[highlight=sql]
select
SUM(ANZAHL_A*TAGESPREIS) GESPREIS
from
WAEBEWEGUNGEN
where
WID=:WID and AUSGANG='T' and
and ADATUM between '01.01.2010' and '31.12.2010';
[/highlight]
oder
[highlight=sql]
... Extract( Year from ADATUM) = 2010
[/highlight]

[hartmutfesting]

Hallo Markus,

ja, die Tabelle hat einen Index/Primary Key.

Dein erster SQL-Code mit dem Between ergibt bei direkter Abfrage:

Prepare: 0 ms Execute: 328 ms Fetch: 328 ms

und der zweite Code mit Extract:

Prepare: 0 ms Execute: 343 ms Fetch: 343 ms

Leider also keine wirkliche Verbesserung. Ich habe das auch mal ohne die Abfrage des Datums probiert und ebenso ohne die SUM-Funktion...es bleibt aber immer noch über 300 ms (und hilft natürlich nicht wirklich, da ich ja den Datumsbereich genauso wie die Aufsummierung benötige).

Hartmut

[Markus Kinzler]

Es muss ein Index über alle Fragen der Where-Clause vorhanden sein

[hartmutfesting]

Ok, ich glaube zwar, ich verstehe den Sinn dahinter...aber wie setze ich das um??? Ich dachte der Hauptindex der Tabelle genügt, da ich auch nicht über mehrere Tabellen abfrage...

Kannst Du dazu bitte noch etwas mehr schreiben oder hast Du ein Beispiel?

Hartmut

[Markus Kinzler]

Nein, der Haptindex ist wichtig, wenn nach der ID gefiltert wird und für FK-Bezüge anderer Tabellen auf diesen Schlüssel.

Für jede (Abfrage)Filterung muss ein passender Index existieren; in deinem Fall WID, AUSGANG und ADATUM

[Thomas Steinmaurer]

Hallo,

um nicht spekulieren zu müssen:

- Wie sieht der Ausführungsplan aus?
- Welche Indizes/Constraints gibt es auf der Tabelle?
- Was ist der Zweck, dass dieses Statement in einer Schleife durchlaufen werden muss? Wenn man je WID für die gegebene WHERE Klausel eine Aufsummierung benötigt, dann könnte man das auch mit einem Statement und einem GROUP BY machen.

lg,
Thomas

[hartmutfesting]

Hallo Thomas,

sorry für die Verzögerung meiner Antwort, aber die Grippe war zu heftig um arbeiten zu können...

Also:
- Der Ausführungsplan: ich hole mir aus einer Tabelle alle angelegten WID's und muss dann besagte Tabelle abfragen um festzustellen, mit welcher Anzahl and welchem Tagespreis diese in dem Abfragezeitraum gespeichert wurden. Von "Aussen" wird also lediglich die WID als Parameter übergeben - andere Tabellen müssen nicht in die Abfrage einbezogen werden.
- Es gibt nur einen Index auf der abzufragenden Tabelle (WBID), den ich hier aber nicht benötige bzw abfrage.
- Zweck: siehe Erläuterung des Ausführungsplans. Statement? Group By? Ich glaub, da benötige ich "Nachhilfe"... wie könnte denn sowas aussehen?

Hartmut

[Thomas Steinmaurer]

Hallo,

der Ausführungsplan ist etwas mit dem man Analysieren kann, welchen Zugriffspfad der Optimizer genommen hat, um an die Daten zu kommen. Du kannst dir den Ausführungsplan für ein Statement ausgeben lassen. Z.b.: Database Workbench, IBExpert etc. zeigen dir den anschaulich an. Das ist ein String der Form "PLAN ...".

Naja, da wirst schon einen Index benötigen, damit das Ganze bei ca. 300.000 Datensätzen abgeht. Z.B. einer auf WID und ADATUM. Vielleicht auch auf AUSGANG, allerdings kommt es da drauf an, wieviele unterschiedliche Ausprägungen da in der Tabelle drinnen sind. Der Ausführungsplan würd uns zeigen, ob irgendwelche Indizes für den performanten Zugriff verwendet werden.

Bzgl. GROUP BY: Vielleicht kommt ja mit folgenden Statements das selbe Ergebnis raus, was du jetzt in einer Schleife versuchst zu lösen:

[highlight=sql]
SELECT
WID
, SUM(ANZAHL_A*TAGESPREIS) GESPREIS
FROM
WAEBEWEGUNGEN
WHERE
AUSGANG='T'
AND ADATUM BETWEEN '01.01.2010' AND '31.12.2010';
GROUP BY
WID
[/highlight]

Thomas

[hartmutfesting]

Hallo Thomas,

das mit dem GROUP BY hat's definitiv gebracht: ich liege jetzt zusammen mit den "Rahmenaktivitäten" bei ca. 15 Sekunden.

Ok, nachdem der Versuch positiv war, werd' ich jetzt mal ein bisschen Lesen zu GROUP BY.

Danke!

Hartmut

[Markus Kinzler]

GROUP BY fasst alle Datensätze die in den angegeben Feldern übereinstimmen zusammen. Die abgefragten Felder m+ssen dan Aggegate ( SUM, MIN, MAX, AVG, ...) sein