AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Delphi Select über 2 Tables dauert mehere Minuten

Select über 2 Tables dauert mehere Minuten

Ein Thema von DataCool · begonnen am 30. Mai 2006 · letzter Beitrag vom 30. Mai 2006
Antwort Antwort
Seite 1 von 2  1 2   
Benutzerbild von DataCool
DataCool

Registriert seit: 10. Feb 2003
Ort: Lingen
909 Beiträge
 
Delphi 10.3 Rio
 
#1

Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 12:30
Datenbank: Firebird • Version: WI-V1.0.2.908 • Zugriff über: BDE
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
Der Horizont vieler Menschen ist ein Kreis mit Radius Null, und das nennen sie ihren Standpunkt.
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.851 Beiträge
 
Delphi 11 Alexandria
 
#2

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 12:49
Wie sieht es mit den Idices aus?
Markus Kinzler
  Mit Zitat antworten Zitat
alzaimar
(Moderator)

Registriert seit: 6. Mai 2005
Ort: Berlin
4.956 Beiträge
 
Delphi 2007 Enterprise
 
#3

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 12:50
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.
"Wenn ist das Nunstruck git und Slotermeyer? Ja! Beiherhund das Oder die Flipperwaldt gersput!"
(Monty Python "Joke Warefare")
  Mit Zitat antworten Zitat
Benutzerbild von DataCool
DataCool

Registriert seit: 10. Feb 2003
Ort: Lingen
909 Beiträge
 
Delphi 10.3 Rio
 
#4

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 12:56
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
Der Horizont vieler Menschen ist ein Kreis mit Radius Null, und das nennen sie ihren Standpunkt.
  Mit Zitat antworten Zitat
mquadrat

Registriert seit: 13. Feb 2004
1.113 Beiträge
 
Delphi XE2 Professional
 
#5

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:00
CREATE [UNIQUE] [ASC[ENDING] | DESCENDING]] INDEX index-name ON table-name (col [, col...]);
  Mit Zitat antworten Zitat
Benutzerbild von DataCool
DataCool

Registriert seit: 10. Feb 2003
Ort: Lingen
909 Beiträge
 
Delphi 10.3 Rio
 
#6

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:13
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
Der Horizont vieler Menschen ist ein Kreis mit Radius Null, und das nennen sie ihren Standpunkt.
  Mit Zitat antworten Zitat
shmia

Registriert seit: 2. Mär 2004
5.508 Beiträge
 
Delphi 5 Professional
 
#7

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:18
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)
Andreas
  Mit Zitat antworten Zitat
mquadrat

Registriert seit: 13. Feb 2004
1.113 Beiträge
 
Delphi XE2 Professional
 
#8

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:25
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.
  Mit Zitat antworten Zitat
Benutzerbild von DataCool
DataCool

Registriert seit: 10. Feb 2003
Ort: Lingen
909 Beiträge
 
Delphi 10.3 Rio
 
#9

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:34
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
Der Horizont vieler Menschen ist ein Kreis mit Radius Null, und das nennen sie ihren Standpunkt.
  Mit Zitat antworten Zitat
Benutzerbild von DataCool
DataCool

Registriert seit: 10. Feb 2003
Ort: Lingen
909 Beiträge
 
Delphi 10.3 Rio
 
#10

Re: Select über 2 Tables dauert mehere Minuten

  Alt 30. Mai 2006, 13:37
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
Der Horizont vieler Menschen ist ein Kreis mit Radius Null, und das nennen sie ihren Standpunkt.
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 2  1 2   

Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 17:16 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz