DB Tabelle beschleunigen

[Ykcim]

Hallo Zusammen,

ich habe eine Tabelle mit Auftragsdaten, in der ich historische Daten speichere. In dieser Tabelle sind über 2,5 Mio Datensätze. AUs diesem Grund sind die Abfragen sehr langsam (1,6-2,1 sek). Ich habe keine Idee, ob / wie ich die Tabelle schneller bekomme.
So ist die Tabelle aufgebaut:

Delphi-Quellcode:

CREATE TABLE  `nedcom`.`as400archiev` (
  `WAAUNR` varchar(20) NOT NULL COMMENT 'FA-Nr',
  `WAAUPO` varchar(20) NOT NULL COMMENT 'FA-Zusatz',
  `WATENR` varchar(13) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL COMMENT 'Artikel-Nr',
  `TEBEZ1` varchar(26) NOT NULL COMMENT 'Artikelbezeichnung 1',
  `TEBEZ2` varchar(26) NOT NULL COMMENT 'Artikelbezeichnung 2',
  `WAFEMG` int(11) DEFAULT NULL COMMENT 'FA-Sollmenge',
  `WAGFMG` int(10) unsigned zerofill DEFAULT NULL COMMENT 'FA-Istmenge',
  `WAWEDA` int(11) DEFAULT NULL COMMENT 'FA-Wareneingangsmenge',
  `WASTAT` int(10) unsigned DEFAULT NULL COMMENT 'FA-Status',
  `OAAGNR` int(11) NOT NULL COMMENT 'AG-Nr',
  `OAAGBZ` char(30) DEFAULT NULL COMMENT 'AG-Bezeichnung',
  `OARMMG` int(11) DEFAULT NULL COMMENT 'AG-Istmenge',
  `OARMDA` date DEFAULT '0000-00-00' COMMENT 'AG-Rückmeldedatum',
  `OATLKZ` varchar(1) DEFAULT NULL COMMENT 'AG-Rückmeldung',
  `OAMANR` varchar(8) DEFAULT NULL COMMENT 'Arbeitsplatz',
  `MAMABZ` varchar(26) DEFAULT NULL COMMENT 'Arbeitsplatzbezeichnung',
  `TGDATE` varchar(10) DEFAULT NULL COMMENT 'Tagesdatum - 6Tage',
  `OASTTE` int(11) DEFAULT NULL COMMENT 'AG-Datum',
  `lgzgda` datetime DEFAULT '0000-00-00 00:00:00' COMMENT 'Lagerzugangsdatum',
  `kritfa` double DEFAULT '0' COMMENT 'Kritischer Auftrag',
  `thslda` date DEFAULT '0000-00-00' COMMENT 'Theoretisches Soll-Datum Rückwärtsberechnung',
  `aglfzt` varchar(20) DEFAULT '00:00:00' COMMENT 'Laufzeit des AG''s',
  `WASIBS` int(11) DEFAULT '0' COMMENT 'Sicherheitsbestand',
  `WAGEWI` double DEFAULT '0' COMMENT 'Gewicht Netto',
  `WAGWMB` varchar(5) DEFAULT '0' COMMENT 'Gewichtsbasis',
  `WATLKZ` varchar(2) DEFAULT '0' COMMENT 'Auftragskennzeichnung',
  `WARSMG` int(11) DEFAULT '0' COMMENT 'Restmenge',
  `OAUEZT` double DEFAULT NULL,
  `OAMAZT` double DEFAULT NULL,
  `TETART` varchar(45) DEFAULT NULL,
  `TETEFA` varchar(45) DEFAULT NULL,
  `TEPRKA` varchar(45) DEFAULT NULL,
  `OARUZT` double DEFAULT NULL,
  `OASTZT` double NOT NULL,
  `ZBASIS` varchar(5) NOT NULL COMMENT 'Kennzeichen, wie die Dauer mit der Stückzeit und der Menge berechnet werden muss',
  `RestDauer` varchar(20) NOT NULL DEFAULT '00:00:00' COMMENT 'Wie lange der FA von diesem AG noch benötigt, bis FA fertig',
  `EPress` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Einpressterminierung',
  `EPressProz` double NOT NULL DEFAULT '0' COMMENT 'Wieviel Prozent der benötigten AGDauer ist noch verfügbar',
  `SollMenge` int(11) DEFAULT '0' COMMENT 'Geplante Soll-Menge des AG''s',
  `plan_date` date DEFAULT NULL COMMENT 'Für wann ist der FA geplant (korreliert mit dem Datum Start aus EilFa)',
  `frozen` varchar(5) DEFAULT '0' COMMENT 'Ist der Auftrag in dem eingefrorenenen Bereich',
  `sequence` int(10) unsigned DEFAULT '999' COMMENT 'Reihenfolgeposition',
  `rfehler` varchar(5) DEFAULT '0' COMMENT 'Rückmeldefehler in der AS400 ',
  `mchange` varchar(5) DEFAULT '0' COMMENT 'Maschine anders als in AS400 ',
  `emuster` varchar(5) DEFAULT '0' COMMENT 'Ist ein Erstmusterauftrag ',
  `draht` varchar(5) DEFAULT '0' COMMENT 'Ist Draht verfügbar. Ja,Nein ',
  `werkz` varchar(5) DEFAULT '0' COMMENT 'Ist Werkzeug verfügbar. Ja,Nein ',
  `scheiben` varchar(5) DEFAULT '0' COMMENT 'Sind Scheiben verfügbar. Ja,Nein ',
  PRIMARY KEY (`WAAUNR`,`WAAUPO`,`OAAGNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Hat jemand eine Idee?

Vielen Dank
Patrick

[jfheins]

Moin,
Tabelle schön und gut, aber:
- welche Abfragen sind langsam?
- und Welche Indices sind auf der Tabelle?

Im einfachsten Fall sollte ein Index auf das Feld helfen, das in Where-Klauseln steht. Je nach Fall kann es aber schnell sehr viel komplexer werden.

[Schokohase]

Auch Partitioning könnte hier zusätzlich zum Index helfen, aber auch dazu benötigt man die Information wie auf diese Tabelle im Allgemeinen zugegriffen wird und wo man primär Performance braucht.

Wenn es allgemein um Performance geht, dann ist es ganz einfach (aber auch teuer):

Neue schnelle Hardware mit ganz viel schnellem RAM (die gesamte DB sollte da rein passen + X) und ganz viel schnellen Platten (SSD)

[Medium]

:shock: Wer auch immer für die ersten 2/3 der Spaltennamen verantwortlich ist, gehört sehr fest an den Marterpfahl gezurrt!


Das nur so am Rande. Ohne die Abfragen genau zu sehen, kann man dir leider nicht wirklich helfen.

[Ykcim]

Also ich bin Keys etc nicht sehr vertraut...

Ich habe einen Primary gesetzt, der aber aus 3 Spalten besteht (waaunr, waaupo, oaagnr). Aber außer das ich den in der Tabelle gesetzt habe, mache ich sonst nichts damit...
Die Query ist fast egal, denn auch eine ganz einfache dauert sehr lange (1,7 sek).

Delphi-Quellcode:

SELECT * FROM as400archiev
where waaunr=291482

Was die Hardware betrifft, so liegt der Server auf eine Virtualisierung und hat einiges an Power....

Vielen Dank
Patrick

Zitat:

Wer auch immer für die ersten 2/3 der Spaltennamen verantwortlich ist, gehört sehr fest an den Marterpfahl gezurrt!

:-D Die kommen aus XPPS... WAAUNR=WarenauftragsNr, WAAUPO=Warenauftragsposition, WATENR=WarenauftragsTeileNr...:lol:

[Rolf Frei]

Dein Query auf waaunr sollte hier eigentlich den Primary Key verwenden und in diesem Fall ein schnelles Resultat bringen. Was für eine DB nutzt du? Gibt es da eine Art Execution Plan den du anschauhen kannst? Dort würdest du sehen, ob für das abgesetzte SQL ein Index verwendet wird oder nicht. Wieviele Records liefert dir dein Besipiel SQL?

Wenn du nicht recht weisst wie man einen Index nutzt, solltest du dich unbeding erst mal zu diesem Thema auseinandersetzen. Das ist das A und O bei der DB Entwicklung.

[Ykcim]

Ich benutze einen MySQL Server 5

Kannst Du mir ein Beispiel aufschreiben, wie ich den Primary in mein SQL-Statement einbauen muss?

Danke
Patrick

[Medium]

Ich bin mir grad nicht 100% sicher, aber ist der primary key so in dieser Form nicht ein kombinierter Schlüssel, und bringt auch wirklich nur etwas, wenn man über alle 3 Felder filtert? Man könnte testhalbar mal einen Index über die Spalte WAAUNR erzeugen, und dann schauen ob die konkrete Abfrage hier dann schneller ist.

Das ist übrigens auch, warum die ganz konkreten Abfragen wichtig sind: Man muss immer spezifisch für die optimieren, die man tatsächlich auch einsetzt. Allgemeine Maßnahmen gibt es, von Hardware abgesehen, praktisch kaum.

[Ykcim]

Zitat:

Das ist übrigens auch, warum die ganz konkreten Abfragen wichtig sind: Man muss immer spezifisch für die optimieren, die man tatsächlich auch einsetzt. Allgemeine Maßnahmen gibt es, von Hardware abgesehen, praktisch kaum.

Ich weiß, wenn ich eine Frage mit einer Abfrage habe, dann stelle ich die auch online. Aber hier ist nicht die Abfrage das Problem, sondern der Umgang mit den vielen Datensätzen.

Zitat:

Man könnte testhalbar mal einen Index über die Spalte WAAUNR

Die WAAUNR Spalte ist nicht unique. Die Werte wiederholen sich hier. Geht das dann trotzdem?

Vielen Dank
Patrick

EDIT: Ich habe einen Index auf WAAUNR gelegt - keine Veränderung...

[hoika]

Hallo,
nehmen wir mal eine andere Abfrage

SELECT TEBEZ1 FROM as400archiev
where waaunr=291482


1. Wie schnell ist die?

2. Und was passiert, wenn du die Query 2mal hintereinander ausführst?

3. Was läuft noch auf dem Datenbank-Server, vielleicht ein Domain-Controller?

[Delphi.Narium]

Tabellendefinition haben wir.

Es fehlen: Die Indexdefinitionen!
Die auszuführenden SQLs!

Ohne diese Angaben kann man nicht helfen, sondern nur rumspekulieren.

Index erstellen: https://www.w3schools.com/sql/sql_create_index.asp

Grundlagen für SQL aneignen: Dringend. https://www.google.com/search?q=grun...t=firefox-b-ab

[Rolf Frei]

Zitat:

Zitat von Ykcim (Beitrag 1436600)

Ich benutze einen MySQL Server 5

Kannst Du mir ein Beispiel aufschreiben, wie ich den Primary in mein SQL-Statement einbauen muss?

Danke
Patrick

Das kannst du nicht im SQL bestimmen. Es kommt darauf an was du bei der DB-Erstellung für Keys definiert hast. Diese werden soweit es eben geht, vom SQL Optimizer benutzt. Wie gesagt müsste der Optimizer für dein SQL so den Primary Key nutzen, sofern das Feld waaunr auch wirklich das erste Feld im Key ist. Ansonsten könnte er den Key nicht nutzen und er würde einen Scan über alle Records machen. Dann müsstest du einen eigenen Index für das Feld "waaunr" erstellen. Der Optimizer schaut dann selber, ob da ein Index passt, den er nutzen kann.

[Rolf Frei]

Zitat:

Zitat von Medium (Beitrag 1436601)

Ich bin mir grad nicht 100% sicher, aber ist der primary key so in dieser Form nicht ein kombinierter Schlüssel, und bringt auch wirklich nur etwas, wenn man über alle 3 Felder filtert?

Das hängt vom verwendeten DBMS ab. Die DB die ich selber nutze, würde in diesem Fall den PK benutzen, da es das erste Feld im Index ist. Würde nach den 2. Feld im Key gesucht, ginge das natürlich nicht. Was MySQL hier macht weiss ich leider auch nicht.

[Ykcim]

Zitat:

1. Wie schnell ist die?

1,7951 s

Zitat:

2. Und was passiert, wenn du die Query 2mal hintereinander ausführst?

0,0008 s

Zitat:

3. Was läuft noch auf dem Datenbank-Server, vielleicht ein Domain-Controller?

Nichts, auf dem Server läuft ausschließlich der MySQL-Server

Zitat:

Es fehlen: Die Indexdefinitionen!

Leider weiß ich nicht, was damit gemeint ist.

Zitat:

Die auszuführenden SQLs!

Ich glaube zwar nicht, dass das weiterhilft, wenn schon die einfachsten Abfragen Probleme bereiten, aber nachfolgend die Abfragen...

Delphi-Quellcode:

procedure TMySQLDB.Get_Alternativen(ArtikelNr, Bereich: string);
var   I: integer;
      where1: string;
      Cols: TCols;
      Rows: TRows;
begin
   where1:='';
   FMySelectQuery.SQL.Clear;
   FMySelectQuery.SQL.Add('select max(a.waaunr) from as400archiev as a '+
                          'where a.watenr=:artikel '+
                          'and  left(a.oamanr,2) in (SELECT arbeitsplatzkz from bereiche '+
                          'where bereiche_text= :bereich ) '+
                          'group by a.oamanr ');
   FMySelectQuery.ParamByName('artikel').AsString:=ArtikelNr;
   FMySelectQuery.ParamByName('bereich').AsString:=Bereich;
   ExecQuery(FMySelectQuery, Cols, Rows,0);
   if FMySelectQuery.RecordCount=0 then begin
      SetLength(FRows_Alternativen, Length(FCols_Alternativen),0);
      Exit;
   end;
   for I := 0 to Length(Rows[0]) -1 do begin
      if I=0 then
         where1:=where1+QuotedStr(Rows[0,I])
      else
         where1:=where1+', '+QuotedStr(Rows[0,I])
   end;
   FMySelectQuery.SQL.Clear;
   FMySelectQuery.SQL.Add('select oamanr, mamabz, oaagbz from as400archiev '+
                          'where waaunr in ('+where1+') '+
                          'and left(oamanr,2) in (SELECT arbeitsplatzkz FROM bereiche '+
                          'where bereiche_text=:bereich) '+
                          'group by oamanr' );
   FMySelectQuery.ParamByName('bereich').AsString:=Bereich;
   ExecQuery(FMySelectQuery, FCols_Alternativen, FRows_Alternativen,0);
end;

Mit der ersten Abfrage hole ich mir den letzten Auftrag des Artikels, der beendet wurde und in der zweiten hole ich mir die Maschineninformationen. Die erste Query mache ich, weil sich Angaben zur Geschwindigkeit (in oaagbz) der Maschine von Zeit zur Zeit ändern. Also hole ich mir damit den letzten Stand.

Zitat:

Grundlagen für SQL aneignen: Dringend.

Werde mir die Links am WE vornehmen...

Vielen Dank
Patrick

[hoika]

Hallo,
MySQL multi-column indices

https://dev.mysql.com/doc/refman/8.0...n-indexes.html

Laut der Tabellen-Beschreibung ist der Index in der richtigen Reihenfolge.
Und einen separaten Index hatte der TE auch schon probiert, hatte aber auch nichts geholfen.

[hoika]

Hallo,
Indexdefinition = Welche Indizes gibt es.

select max(a.waaunr) from as400archiev as a
where a.watenr=:artikel
and left(a.oamanr,2) in (SELECT arbeitsplatzkz from bereiche
where bereiche_text= :bereich )
group by a.oamanr

Das in (also das SubSelect) könnte bei MySQL zu Problemen führen,
wenn der SQL-Server das für jeden einzelnen Datensatz ausführt.
Mach mal 2 Queries draus, hole dir mit Query1 die arbeitsplatzkz
und benutze das Ergebnis Query2 (Select max).

SubSelects
https://stackoverflow.com/questions/...low-workaround

MySQL müsste auch einen Planalyzer haben, der einen Query-Plan erzeugt,
also sagt, wie der DB-Server intern eine Query abarbeitet.

Wenn dann z.B. bei
SELECT arbeitsplatzkz from bereiche
where bereiche_text= :bereich
steht natural scan, muss die komplette Tabelle durchlaufen werden, um das Ergebnis zu bekommen.
Liegt ein Index auf bereiche_text, würde der die Suche beschleunigen.



Zu meinen Fragen:
1.9 Sekunden bei Select* und Select ein_Feld.

Tja, ein Planalyzer könnte dir jetzt sagen, ob die Query-Ausführung (Execute) langsam war,
oder das Übermitteln der Daten über das Netz (Fetch).

Ohne einen Planalyzer könntest Du deine Anwendung auch auf dem SQL-Server mal selber laufen lassen.
Dann ist der Netzeinfluss erst mal weg.

[Delphi.Narium]

SQL-Code:

select max(a.waaunr) from as400archiev as a
where a.watenr = :artikel
and left(a.oamanr,2) in 
(SELECT arbeitsplatzkz from bereiche where bereiche_text = :bereich)
group by a.oamanr

Zuerst: wielange dauert das?

SQL-Code:

SELECT arbeitsplatzkz from bereiche where bereiche_text = :bereich

Wie lange dauert es, wenn Du einen Index auf bereiche_text erstellst?

Funktioniert das? Wird es schneller?

SQL-Code:

select max(a.waaunr) from as400archiev a, bereiche b
where a.watenr = :artikel
and left(a.oamanr,2) = b.arbeitsplatzkz
and bereiche_text = :bereich
group by a.oamanr

Alternativ:

SQL-Code:

select max(a.waaunr) from as400archiev a
inner join bereiche b on left(a.oamanr,2) = b.arbeitsplatzkz
where a.watenr = :artikel
and b.bereiche_text = :bereich
group by a.oamanr

Alles nur ungetestet hingedaddelt.

Sowas

SQL-Code:

left(a.oamanr,2)

in 'ner Wherebedingung ist nicht gerade dafür bekannt, dass entsprechende Abfragen schnell werden, in der Regel schließt sowas eine Indexnutzung aus.

Auch ein

SQL-Code:

in (select spalte from tabelle)

in einer Wherebedingung ist nicht zwingend zur Beschleunigung einer Abfrage geeignet, eher das Gegenteil.

Achso: Und einfache Abfragen sind das ganz und gar nicht, die Schwierigkeit einer Abfrage ergibt sich nicht daraus, ob es schwierig ist sie zu schreiben, sie zu formulieren, sondern daraus, wie schwierig ihre Ausführung für die Datenbank wird.

Schlimmste Laufzeitannahme:

Wenn Deine Tabelle 2.5 Mio Datensätze hat und Du per left(a.oamanr,2) für jeden Datensatz eine Einschränkung baust, so muss die Datenbank 2,5 Mio mal eben diesen Substring bilden. Und dann muss sie 2.5 Mio mal dieses SQL

SQL-Code:

SELECT arbeitsplatzkz from bereiche where bereiche_text = :bereich

ausführen, obwohl es eigentlich immer das gleiche Ergebnis liefert.
Dann muss sie sich alle die Sätze merken, bei der die Bedingung zutrifft und anschließend davon den Satz mit der größten waaunr je oamanr auswählen.

Für meine Begriffe sind bei dem Aufwand die Antwortzeiten ja eigentlich garnicht mal so übel ;-)

[mkinzler]

Zitat:

Sowas left(a.oamanr,2) in 'ner Wherebedingung ist nicht gerade dafür bekannt, dass entsprechende Abfragen schnell werden, in der Regel schließt sowas eine Indexnutzung aus.

Oder man erstellt ein "Schattenfeld" oder einen expression index

[Delphi.Narium]

Zitat:

Zitat von mkinzler (Beitrag 1436623)

Zitat:

Sowas left(a.oamanr,2) in 'ner Wherebedingung ist nicht gerade dafür bekannt, dass entsprechende Abfragen schnell werden, in der Regel schließt sowas eine Indexnutzung aus.

Oder man erstellt ein "Schattenfeld" oder einen expression index

Ja durchaus, aber hier kommen wir dann in den Bereich der Optimierung für Fortgeschrittene ;-)

Momentan ist aber noch unklar, was ein Index überhaupt ist.

[peterbelow]

Zitat:

Zitat von Ykcim (Beitrag 1436587)

Hallo Zusammen,

ich habe eine Tabelle mit Auftragsdaten, in der ich historische Daten speichere. In dieser Tabelle sind über 2,5 Mio Datensätze. AUs diesem Grund sind die Abfragen sehr langsam (1,6-2,1 sek). Ich habe keine Idee, ob / wie ich die Tabelle schneller bekomme.
So ist die Tabelle aufgebaut:

Delphi-Quellcode:

CREATE TABLE  `nedcom`.`as400archiev` (
  `WAAUNR` varchar(20) NOT NULL COMMENT 'FA-Nr',
  `WAAUPO` varchar(20) NOT NULL COMMENT 'FA-Zusatz',
  ...snip...
  PRIMARY KEY (`WAAUNR`,`WAAUPO`,`OAAGNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Hat jemand eine Idee?

Vielen Dank
Patrick

Was der Server braucht is memory, je mehr desdo besser. Je mehr der Indexfiles und der Tabellen der Server im Speicher halten kann desdo schneller kann er Abfragen bearbeiten, denn der Zeitfresser sind die Diskzugriffe. Bei den meisten DBs kann man auch in der DB Konfiguration festlegen, wieviel Memory der Server für die diversen Caches verwenden darf (teilweise sind das per session Einstellungen, e.g. für temporäre Daten, die bei der Bearbeitung einer Abfrage anfallen). Wenn der DB Admin hier zu knauserig ist kann das drastische Auswirkungen auf die Performance haben.

Ansonsten ist vieles, wie schon in anderen Antworten gesagt, von der spezifischen Query abhängig. Die besseren Datenbanken bieten eine query plan analyzer an, mit dem man sich mal ansehen kann, wie der query optimizer sich das Vorgehen vorstellt und was er da Zeitbedarf abschätzt. Da kann man normalerweise sofort sehen, ob vorhandene Indizes verwendet werden oder der Optimizer auf einen full table scan ausweicht (schlecht, sehr schlecht!).

Es hängt auch von der Art der Query ab, ob die Datenbank schon Records zurückliefern kann, bevor der volle Resultset verfügbar ist. Sowas wie "distinct", "group by" oder "order by" in der query machen das unmöglich (es sei denn, ein order by kann direkt über einen Index realisiert werden). Verwendung von Funktionen in einer WHERE-Klausel (wie concat, upper, cast, etc.) machen es unmöglich, für den Zugriff einen Index auf dem betroffenen Feld zu verwenden, selbst wenn einer existiert.

Außerdem sind select * Abfragen Gift für die Performance, wenn man eigentlich nur einen Teil der Spalten braucht. Sowas erhöht einfach das Datenvolumen, was den Speicher des Servers belastet und die Datenübertragung zum Client bremst.

Außerdem sollte man immer im Hinterkopf behalten, dass auch Abfragen bei den meisten DB Servern in Transaktionen laufen und damit Serverresourcen belegen, bis die Abfragen geschlossen und die zugehörige Transaktion committed wurde (glücklicherweise macht das Client framework das normalerweise, wenn man eine Query schließt). Viele Abfragen offenzuhalten ist daher etwas, mit dem man sich weder bei DB Admins noch bei den Benutzern beliebt macht :wink:. Leider macht das typische Delphi RAD Design genau das...

[Ykcim]

Hallo Zusammen,

vielen Dank für die Anregungen. Ich muss das Thema aber erst einmal zurückstellen und mich um andere Sachen kümmern.

Wenn es wieder aktuell wird und ich weitere Erkenntnisse habe, würde ich mich gerne wieder menlden.

Vielen Dank
Patrick

[freimatz]

Zitat:

Zitat von peterbelow (Beitrag 1436631)

Ansonsten ist vieles, wie schon in anderen Antworten gesagt, von der spezifischen Query abhängig. Die besseren Datenbanken bieten eine query plan analyzer an, mit dem man sich mal ansehen kann, wie der query optimizer sich das Vorgehen vorstellt und was er da Zeitbedarf abschätzt.

Es ist schon viele Jahre her, dass ich mit MySQL gearbeitet habe. Aber ich kann sagen, dass MySQL so etwas hat. Ohne das Tool zu verwenden halte ich bei dem Fall eine Optimierung für Zeitverschwendung.