SQL-Abfrage dauert zu lange

[davar]

Hallo Leute,

bei folgende SQL-Abfrage dreht mein Rechner durch. PHPMyAdmin benötigt für das Ergebnis 7 Sekunden, Delphi mit Zeos kommt sogar auf Minuten.

Hab ich nun einen Denkfehler in die Abfrage eingebaut oder ist das normal?

Folgende Sachen sind gegeben:

Tabellen: kasse_vorgaenge, kasse_rechnungen, kasse_gutschriften, kasse_lieferscheine

In jedem einzelnen Datensatz von "kasse_vorgaenge" steht in der Spalte "vorgangsart", ob es sich um eine Rechnung, Lieferschein oder Gutschrift handelt. Mit

Delphi-Quellcode:

SELECT * FROM kasse_vorgaenge WHERE kundennummer = 1 WHERE datum BETWEEN 2006-05-01 AND 2006-11-28

komme ich ganz normal weiter. Nun möchte ich jedoch auch die jeweilige Rechnungs-, Gutschrifts- bzw. Lieferscheinnummer dabei haben, die in den anderen drei Tabellen stehen.

Also nehme ich folgende Abfrage:

Delphi-Quellcode:

SELECT
   kasse_vorgaenge.*, kasse_rechnungen.ID, kasse_gutschriften.ID, kasse_lieferscheine.ID 
FROM
   kasse_vorgaenge, kasse_rechnungen, kasse_gutschriften, kasse_lieferscheine 
WHERE 
   kasse_vorgaenge.kundennummer = 1 
  AND 
   kasse_vorgaenge.datum BETWEEN 2006-05-01 AND 2006-11-28 
  AND
   kasse_rechnungen.vorgangsnummer = kasse_vorgaenge.ID 
  OR
   kasse_gutschriften.vorgangsnummer = kasse_vorgaenge.ID 
  OR 
   kasse_lieferscheine.vorgangsnummer = kasse_vorgaenge.ID 
GROUP BY kasse_vorgaenge.ID

Hat jemand eine Idee, wie ich das Ergebnis schneller bekomme?


MfG

davar

[MarcoWarm]

Das ist ein ziemlich komplexes Thema (optimierung von Queries)

Was du zuerst prüfen solltest:
Sind die Indizes (von manchen auch Indexe genannt) korrekt gesetzt.

In deinem Fall bei Tabelle kasse_vorgaenge z.B. auf kundennummer und datum
und in den anderen Tabellen die jeweiligen Vorgangsnummern.

Wenn immer möglich benutze InnerJoin In deinem Fall würde ich Sub-Selects verwenden

[mkinzler]

Es scheinen auch Klammern zu fehlen.

[hoika]

Hallo,

Outer Join ?
Das hier sind doch auch inner joins, nur halt die alte Syntax.

Ich würde hier entweder 3 Queries machen,
und dass dann am Client zusammenbauen,
oder eine Stored Procedure, die intern ebenfalls auf 3 Queries aufbaut,
(+ der Query für den kasse_vorgangs_id Cursor).


Heiko

[Phoenix]

Durch die BETWEEN - Abfrage wird die Datenbank auf jeden Fall einen Full Table Scan machen. Und das leider zwangsläufig auf allen Tabellen. Und ein FTS ist so ziemlich das Schlimmste was Dir bei der Performance passieren kann.

Versuch mal, die Between-Abfrage rauszulassen und schau, wie das Laufzeitverhaltend es Queries dann ist. Setze zudem noch Indizes auf die abgefragten Spalten.

Die Auswahl nach dem Datum machst Du dann im Client. Das dürfte performanter sein.

Ausnahme: Du setzt vorher einen Index auf das Datum und machst eine materialized View die Dir die Tabelle nach dem Datum eingrenzt. Das Problem wird hier aber sein, dass das Datum ja eher dynamischer Natur ist und damit fällt die View an der Stelle wohl weg.

[alzaimar]

Zitat:

Zitat von Phoenix

Durch die BETWEEN - Abfrage wird die Datenbank auf jeden Fall einen Full Table Scan machen. Und das leider zwangsläufig auf allen Tabellen. Und ein FTS ist so ziemlich das Schlimmste was Dir bei der Performance passieren kann.

:gruebel:
Ein "x BETWEEN a AND b"ist -zumindest bei MSSQL- genau das Gleiche wie "x>=a AND x<=b". Wieso sollte er einen FTS machen? Sofern X indiziert ist, kann der Server doch toll optimieren.

[Mumpe]

Kannst Du mal sagen, wie viele Datensätze in den einzelnen Tabellen sind?
Ich tippe auch eher auf die Richtung, die mkinzler ansprach.

Deine Abfrage sucht Dir nebenbei mal alle Datensätze aus "kasse_gutschriften" und "kasse_lieferscheine" deren Vorgangsnummer = kasse_vorgaenge.ID ist.

Dazu kommen dann noch die Datensätze aus kasse_rechnungen die zu den aus kasse_vorgaenge selektierten passen.

Wenn Du die OR-Bedingungen in Klammern setztest, würdest Du eher das Ergebnis bekommen, das Du haben möchtest und wahrscheinlich geht die Abfrage dann auch schneller.

[omata]

Hallo davar,

vielleicht so...

SQL-Code:

SELECT kv.*, 
       kr.ID, 
       kg.ID, 
       kl.ID
FROM kasse_vorgaenge kv
LEFT JOIN kasse_rechnungen kr
  ON kv.ID = kr.vorgangsnummer
LEFT JOIN kasse_gutschriften kg
  ON kv.ID = kg.vorgangsnummer
LEFT JOIN kasse_lieferscheine kl
  ON kv.ID = kl.vorgangsnummer
WHERE kv.kundennummer = 1
  AND kv.datum BETWEEN '2006-05-01' AND '2006-11-28'

Gruss
Thorsten

[GuenterS]

Wie erkennt man denn, dass die Datenbank einen Full Table Scan macht?

Sieht man das im Profiler? Wenn ja woran?

[alzaimar]

Du musst doch nur einen Index über die Datumsspalte machen, dann macht kein normales DBMS einen 'full table scan'. Oder Ist MySQL etwa kein normales DBMS?

[Phoenix]

Sogar Oracle setzt bei sowas einen Full Table Scan an, wenn das Ergebnis noch in der Art wie oben angegeben verjoint wird. Egal, ob da ein Index sitzt oder nicht. Bei so einem geschachtelten Statement setzt früher oder später jede Ablaufplanoptimierung aus, egal wie gut sie ist.

[Masterof]

hallo

ich denke es kommt auf die Anzahl der Datensätze an.
du kannst es ja mal mit den ODBC Treiber für Mysql probieren, vielleicht sind die schneller

[Hoshy]

"OR" Verknüpfungen in Abfragen sind immer schlecht...

Arbeite lieber mir UNION ALL, das dürfte schonmal schneller sein:

SQL-Code:

SELECT kv.*, kr.ID as id_rechnungen, null as id_gutschriften, null as id_lieferscheine
FROM kasse_vorgaenge kv
LEFT JOIN kasse_rechnungen kr ON kv.ID = kr.vorgangsnummer 
WHERE kv.kundennummer = 1 AND kv.datum BETWEEN '2006-05-01' AND '2006-11-28'

UNION ALL

SELECT kv.*, null as id_rechnungen, kg.ID as id_gutschriften, null as id_lieferscheine
FROM kasse_vorgaenge kv
LEFT JOIN kasse_gutschriften kg ON kv.ID = kg.vorgangsnummer 
WHERE kv.kundennummer = 1 AND kv.datum BETWEEN '2006-05-01' AND '2006-11-28'

UNION ALL

SELECT kv.*, null as id_rechnungen, null as id_gutschriften, kl.ID as id_lieferscheine
FROM kasse_vorgaenge kv
LEFT JOIN kasse_lieferscheine kl ON kv.ID = kl.vorgangsnummer 
WHERE kv.kundennummer = 1 AND kv.datum BETWEEN '2006-05-01' AND '2006-11-28'

Leg einen Index über (kv.kundennummer, kv.datum) und jeweils einen über die Vorgangsnummer der anderen Tabellen.

Gruß
Andi

[alzaimar]

Zitat:

Zitat von Phoenix

Sogar Oracle setzt bei sowas einen Full Table Scan an, wenn das Ergebnis noch in der Art wie oben angegeben verjoint wird. Egal, ob da ein Index sitzt oder nicht. Bei so einem geschachtelten Statement setzt früher oder später jede Ablaufplanoptimierung aus, egal wie gut sie ist.

Nee, das kann nicht sein, dann wäre Oracle ja schlechter als mein Frickel-DBMS. Es gibt kein Konstrukt, bei dem ein Optimizer aussetzt und dann nur einen FTS duchführt. Wenn der Optimizer entscheidet, das ein FTS das Billigste ist, dann nur, weil ein Index nicht zur Verfügung steht, oder die Tabellengröße relativ klein ist.

Phoenix, wie kommst Du darauf, das 'selbst Oracle' einen FTS macht... :gruebel:

[Hansa]

Zitat:

Zitat von alzaimar

...Es gibt kein Konstrukt, bei dem ein Optimizer aussetzt und dann nur einen FTS duchführt...

Wie wärs denn hiermit :

SQL-Code:

select * from table where name like '%test%'

? Erkläre mal wie man das optimieren kann. :mrgreen:

[alzaimar]

Hansa, da 'setzt' der Optimierer nicht aus, sondern macht das Beste draus. Kleiner, feiner Unterschied. :mrgreen: Und falls eine Volltextsuche eingeschaltet ist, kann hier auch der dümmste Optimierer optimieren.

[marabu]

Moin Hansa,

das ist einfach: Einen Index auf das Feld legen und aus dem table scan wird ein index scan. Da geht die Post ab - besonders, wenn die index pages auch noch komplett im memory pool des DBMS gehalten werden. Und wenn du das richtige RDBMS verwendest, dann musst du den Index noch nicht mal selbst erzeugen.

Freundliche Grüße

[alzaimar]

Zitat:

Zitat von marabu

Und wenn du das richtige RDBMS verwendest, dann musst du den Index noch nicht mal selbst erzeugen.

Moin marabu,

:shock: Welches RDBMS kann das denn? Welches RDBMS wagt es, meine Indexstrategie zu unterminieren? :zwinker: Her damit!

Grüße.

[Hansa]

Zitat:

Zitat von alzaimar

Welches RDBMS wagt es, meine Indexstrategie zu unterminieren? :zwinker: Her damit!

Wird eine Strategie gebraucht, zusammengesetze Indizes usw. dann wird das wohl nirgends automatisch gehen. Aber das wichtigste müßte die DB schon selber erkennen. Siehe hier :

http://www.ibexpert.info/documentati...dices/419.html

Und das war auch ungefähr das, was ich gemeint hatte. Man könnte ja nun einige Spalten manuell indizieren. Aber die Vorteile werden eben durch Konstrukte wie LIKE %BLUBB% mehr oder weniger zunichte gemacht.

[Union]

Zitat:

Zitat von Hansa

Zitat:

Zitat von alzaimar

...Es gibt kein Konstrukt, bei dem ein Optimizer aussetzt und dann nur einen FTS duchführt...

Wie wärs denn hiermit :

SQL-Code:

select * from table where name like '%test%'

? Erkläre mal wie man das optimieren kann. :mrgreen:

Indem man CONTAINS und FTI einsetzt?

[marabu]

Hallo alzaimar,

Zitat:

Zitat von alzaimar

Welches RDBMS wagt es, meine Indexstrategie zu unterminieren? Her damit!

momentan machen das MS, Oracle, IBM u.a. - allerdings noch mit virtuellen Indexen. Wenn man die Veröffentlichungen im VLDB-Sektor beobachtet, dann sind Agent-Module, wie sie bei ADS (Active Database System) Einsatz finden, der logische nächste Schritt für unsere RDBMS. Heute glänzen hier noch Tuning Assistant und Index Wizard, aber ein autonomer Agent würde bereits heute alle notwendigen Informationen für eine automatische Index-Erstellung im statistics catalog vorfinden. Daneben gibt es noch Proxy-Ansätze, bei denen die Anfragen von einem speziellen externen optimizer ausgewertet werden, bevor sie an den eigentlichen Anfrage-Prozessor weitergeleitet werden (Stichwort query-driven index tuning). Für deine laufenden Projekte also eher Fehlanzeige.

Gute Nacht

[davar]

Edit: hab nur Mist geschrieben (und wieder entfernt), die Variante von omata ist das was ich gesucht hab. Dankeschön.