Select über 2 Tables dauert mehere Minuten

[DataCool]

Hi Leute,

ich bin gerade am verzweifeln, ich habe eine Datenbank die im Moment ca. 20 MB gross ist, jeden Tag wächst diese DB um ca. 2 -4 MB(es handelt sich um Bewegungsdaten aus einer Kasse, wie Ihr sie aus vielen Cafes kennt).

Auf Basis dieser Daten möchte ich diverse Auswertungen machen.
Aber erstmal die Table Struktur :

Delphi-Quellcode:

CREATE TABLE "TRANSAKTION" 
(
  "SORT_ID"   INTEGER NOT NULL,
  "IMPORT_ID"   INTEGER NOT NULL,
  "ID"   INTEGER NOT NULL,
  "JOURNAL_NR"   INTEGER,
  "START_DT"   DOUBLE PRECISION,
  "ENDE_DT"   DOUBLE PRECISION,
  "KASSEN_NR"   INTEGER,
  "TISCH_NR"   INTEGER,
  "BEDIENER_START"   INTEGER,
  "BEDIENER_ENDE"   INTEGER,
  "BON_NR"   INTEGER,
  "BON_TYP"   INTEGER,
  "TOTAL"   DOUBLE PRECISION,
  "TOTAL_TYP"   VARCHAR(10),
  "TOTAL_NETTO"   DOUBLE PRECISION,
  "FINANZWEG"   INTEGER,
  "ZAHLGELD"   DOUBLE PRECISION,
  "AUSLAGEN"   DOUBLE PRECISION,
  "INFOREC_TYPE"   INTEGER,
  "INFOREC_VALUE"   INTEGER,
  "EXPORT_DT"   DOUBLE PRECISION,
CONSTRAINT "RDB$PRIMARY7" PRIMARY KEY ("SORT_ID", "IMPORT_ID", "ID")
);

CREATE TABLE "TRANSAKTIONDETAILS" 
(
  "SORT_ID"   INTEGER NOT NULL,
  "TRANSAKTION_ID"   INTEGER NOT NULL,
  "ID"   INTEGER NOT NULL,
  "PARENT_JOURNALNR"   INTEGER,
  "JOURNALNR"   INTEGER,
  "GEBUCHT_DT"   DOUBLE PRECISION,
  "ABGERECHNET_DT"   DOUBLE PRECISION,
  "ARTIKEL_NR"   INTEGER,
  "ANZAHL"   INTEGER,
  "PREIS"   DOUBLE PRECISION,
  "ILEVEL"   INTEGER,
  "MODIFYER"   INTEGER,
  "SOFORTSTORNO"   DOUBLE PRECISION,
  "RUECKNAHME"   SMALLINT,
  "STORNO"   SMALLINT,
  "MOD_NR"   INTEGER,
  "MOD_TYPE"   INTEGER,
  "MOD_FACTOR"   INTEGER,
  "ISTAUSLAGE"   SMALLINT,
CONSTRAINT "RDB$PRIMARY8" PRIMARY KEY ("SORT_ID", "TRANSAKTION_ID", "ID")

Der Table TransaktionDetails enthält wirklich jeden Schei**, jede Cola jeden Cafe alles was ein Kellner verkauft, storniert und gemacht haben.

Eine SQL Abfrage die den Vogel wirklich abschiesst(dauerd 15-20 Minuten und der Process IBserver zieht sich alles an CPU was er bekommen kann) :

Delphi-Quellcode:

SELECT ARTIKEL_NR, Sum(ANZAHL) AS ANZ, Sum(PREIS * ANZAHL) as GPreis 
FROM TRANSAKTION T INNER JOIN TRANSAKTIONDETAILS TD ON(T.ID = TD.TRANSAKTION_ID)
WHERE T.ENDE_DT >= 38864.25 And T.ENDE_DT < 38865.25 
And (T.BON_TYP=1 Or T.BON_TYP=2) And ARTIKEL_NR IN('+strL_KuechenArtikel.commatext+')'+
T.SORT_ID = 6 GROUP BY ARTIKEL_NR;

Wie Ihr im SQL seht arbeite ich mit ARTIKEL_NR IN, dort kommen ca. 200-300 Artikel-Nummern die zur Warengruppe Küche gehören. In diesem Fall will ich den Umsatz der Küche ermitteln.
Ich habe das ganze auch schon ohne IN versucht hat aber keinen Effekt die Query dauerd immer Ewig.

In dem Table Transaktion sind insgesamt 34232 Datensätze und 2931 Datensätze die in den Zeitraum passen.

In dem Table TransaktionDetails sind insgesamt 97758 Datensätze.

Hat jemand ne Ahnung warum das Select So lange dauerd und wie manche das ganze beschleunigen könnte ?

Vorher hatte ich das ganze zum Testen auf der Basis von Access und die Abfragen waren wesentlich schneller(ein paar Sekunden).

Danke und Gruß Data

[mkinzler]

Wie sieht es mit den Idices aus?

[alzaimar]

Indiziere die Tabellen entsprechend den Join- und Where Klauseln

SELECT ARTIKEL_NR, Sum(ANZAHL) AS ANZ, Sum(PREIS * ANZAHL) as GPreis
FROM TRANSAKTION T
INNER JOIN TRANSAKTIONDETAILS TD ON(T.ID = TD.TRANSAKTION_ID)
WHERE T.ENDE_DT >= 38864.25 And T.ENDE_DT < 38865.25
And (T.BON_TYP=1 Or T.BON_TYP=2) And ARTIKEL_NR IN('+strL_KuechenArtikel.commatext+')'+
T.SORT_ID = 6 GROUP BY ARTIKEL_NR;

Ich würde erstmal Ende_DT. Bringt sehr viel, weil er dann schon mal nicht mehr alle Daten durchgeht, sondern nur noch die von diesem Tag.
Dann vielleicht das '>=' and '<' durch ein Between A and B ersetzen. Bringt nix, sieht aber höbscher aus.

Du kannst vielleicht einen zusammengesetzen Index erstellen: ENDE_DT + BON_TYP, hängt aber von weiteren Queries ab.

Dann wird noch nach ARTIKEL_NR gefiltert, also auch einen Index rauf (wenn es viele verschiedene sind).

Ich glaube aber, der Datums-Index ist der Wichtigste.

Probier mal, wenns nicht reicht, dann musst du die Artikelnummern normalisieren, Artikelgruppen einführen und über bestimmte Artikelgruppen filtern. Das geht in jedem Fall schneller.

[DataCool]

Hi,

danke erstmal für die Tipps,
könnt Ihr mir mal eben auf die Sprünge helfen, ich finde in der IBConsole keine Option um einen Index nachträglich hinzuzufügen ?!

Wahrscheinlich muss ich das wohl per SQL machen, aber wie lautet der Befehl bei Firebird dazu ?

Gruß Data

[mquadrat]

CREATE [UNIQUE] [ASC[ENDING] | DESCENDING]] INDEX index-name ON table-name (col [, col...]);

[DataCool]

Hi,

habe jetzt die Idices erzeugt auf (ENDE_DT und BON_TYP) in Transaktion und auf
ARTIKEL_NR in TransaktionDetails.

Habe jetzt nochmal die Abfrage gestart und ich warte noch wie lange es dauerd bis die Abfrage fertig ist

Vielleicht dauerd es ja jetzt nicht mehr 15 Minuten sondern 7 oder 8 aber das kann doch nicht der Sinn der Sache seien, eine Query darf doch nicht solange dauern.

Gruß Data

[shmia]

Die Primärschlüssel beider Tabellen schliesen das Feld "SORT_ID" mit ein.
Eigentlich düften nur folgende Felder zum Primärschlüssel gemacht werden:
TRANSAKTION.ID und TRANSAKTIONDETAILS.ID
Was passiert nun bei deinem Join ?
FROM TRANSAKTION T INNER JOIN TRANSAKTIONDETAILS TD ON(T.ID = TD.TRANSAKTION_ID) Da die Werte für das Feld "SORT_ID" hier überhaupt nicht festgelegt wurden, kann
der Primärschlüsselindex nicht für den Join herangezogen werden!!
Die Folge: der Join muss völlig ohne Indexunterstützung vom Server hergestellt werden
==> extrem lange Verarbeitungszeit.

meine Empfehlung:
TRANSAKTION.ID => Primärschlüssel
TRANSAKTION.SORT_ID => Index drauf
TRANSAKTION.IMPORT_ID => Index drauf

TRANSAKTIONDETAILS.ID => Primärschlüssel
TRANSAKTIONDETAILS.TRANSAKTION_ID => Index drauf (besonders wichtig für den JOIN)

[mquadrat]

Wäre es nicht einfacher, wenn du eine dritte Tabelle erstellst mit der Zuordnung Artikel <-> Kategorie? Dann könntest du dir dieses riesige in () Konstrukt sparen.

[DataCool]

Hi,

bei dem Table Transaktion ist der Primary Key ein Verbundschlüssel("SORT_ID", "IMPORT_ID", "ID").

Beim Table TransaktionDetails ist der Primary Key ebenfalls ein Verbund("SORT_ID", "TRANSAKTION_ID", "ID")

Zum Verständnis :

"SORT_ID" ist die ID des Standorts, die im Moment überall die gleiche ist. Diese wurde nur angelegt um beim Zusammenfügen von Datenbanken aus mehreren Standorten keine PK Verletzung zu bekommen.

"IMPORT_ID" ist die Nummer des Imports, wenn z.B. in einem Standort 4 Kassen stehen gibt es an einem Tag 4 Import_IDs.

"ID" ist eine laufende eindeutige Nummer über die auch die Verknüpfung zu TransaktionDetails geschiet.

Aber anstatt weitere lange Erklärung abzugeben habe ich die Datenbank als Zip-File angehängt.

"EDIT" : Leider lässt der Upload nur Max 3MB zu und die gezippte DB ist 4,5 MB gross

Gruß Data

[DataCool]

Hi,

ich habe weitere Tables z.B. den Table Artikel :

Delphi-Quellcode:

CREATE TABLE "ARTIKEL" 
(
  "SORT_ID"   INTEGER NOT NULL,
  "NR"   INTEGER NOT NULL,
  "WGNR"   INTEGER,
  "BEZEICHNUNG"   VARCHAR(30),
  "PREIS1"   DOUBLE PRECISION,
  "PREIS2"   DOUBLE PRECISION,
  "PREIS3"   DOUBLE PRECISION,
  "HGNR"   INTEGER,
CONSTRAINT "RDB$PRIMARY1" PRIMARY KEY ("SORT_ID", "NR")
);

Vor dem Select was solange dauerd lese ich in einem Select alle Artikel-Nummern aus, wo die HGNR = xyz ist.

Ich habe aber auch andere Selects die Lange dauern und wo nicht dieses "In Konstruct" verwendet wird.

Gruß Data