DB Tabelle beschleunigen

[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...