Performance Problem bei SQL-Abfrage

[Hansi]

Hey,

Ich habe ein größeres Performance-Problem bei meinen Abfragen; Ich schreib mal kurz was ich mache:

Es handelt sich um einen Datenimport von einer Txt-Datei in eine MySQL DB.

1. Zu erst wird die txt-Datei geöffnet und die komplette Datei zeilenweise eingelesen und die Spalten (die txt-Datei ist Tab-getrennt) bzw die Werte in ein Array gelesen.
2. Nun wird das erste Array genommen und überprüft ob es diesen Datensatz schon gibt. Mit SQL(Select...ID=ID; wenn recordcount > 0 dann update(SQL), ansonsten insert(SQL).
3. bei update und insert wird jeweils per ParamByName(ca. 7-12 Stück) die Werte aus dem Array zugewiesen.

Das klappt alles wunderbar, nur dass ich damit ca. bei localhost 3 Datensätze pro Sekunde einlese und auf einem Webserver nur 1 Datensatz in 3 Sekunden.

Woran könnte ich "schrauben", damit der Speed deutlich schneller wird?

MfG
Hansi

[mkinzler]

-Ich würde das Einlesen und verarbeiten in einem Schritt durchführen.
-Auch den Insert würde ich mit dem Select kombinieren insert into ... where not exists ...
-Da die Reihenfolge der Parameter sich ja nicht ändert, kannst du diese ja per Index zuweisen.

[Gollum]

Hallo,

wie sieht es mit Indexen aus?

Gebe allen Feldern, die in der WHERE-Klausel Deiner SELECT- und UPDATE-Anweisung stehen, einen Index. Dies sollte Deinen Import evtl. beschleunigen.

Beschränke Dich im SELECT nur auf die Felder, die auch tatsächlich benötigt werden:

SQL-Code:

  SELECT ID
  FROM MeineTabele 
  WHERE (ID=10)

[Jelly]

Wie gross ist die bestehende MySQL Tabelle?

Inserts laufen wesentlich schneller ab, wenn kein Index auf der Tabelle existiert. Selects (und updates mit where Bedingung über Indexspalte) dafür langsamer. Vielleicht hilft es ja, den Index erst zu löschen, dann alle Datensätze einzufügen, und anschliessend den index neu zu erstellen.

In MySQL existiert auch die erweiterte Insert Anweisung, die auch einen Performanceschub geben wird:

SQL-Code:

insert into table (feld1,feld2) values (1,2),(2,3)(3,4)...

[Hansi]

Danke euch beiden.

- Einlesen und Verarbeiten in einem Schritt machen: Was hat das für einen Speed-Vorteil? Oder meinst du das nur vom Ablauf her?
- Insert mit Select kombinieren; Das ist glaube ich gar nicht schlecht; Könnte was bringen. Nur wie mache ich das mit der Trennung von insert und update?
- Das mit dem Select beschränken habe ich schon gemacht.
- Das mit den Indexes muss ich mal testen; habe ich bis jetzt noch nicht verwendet. Mal schaun wie viel das bringt.

Gibt es sonst nochwas? Ich dachte an z.B. alle Insert bzw update in einem riesen String zusammenfassen und auf einmal ausführen. Bringt das was, anstatt jeden Datensatz einzeln einfügen?

- Insert ohne Index; dafür select und update mit Index; mal schaun...
- Insert mit Values muss ich auch mal testen. Bringt das viel? Bisher habe ich mit Set gearbeitet

[Jelly]

Du hast mir immer noch nicht verraten, wieviel bestehende Datensätze in der MySQL Tabelle stehen, und wieviele in der zu importierenden Textdatei.

[mkinzler]

Zitat:

alle Insert bzw update in einem riesen String zusammenfassen und auf einmal ausführen. Bringt das was, anstatt jeden Datensatz einzeln einfügen?

Zitat:

- Insert mit Values muss ich auch mal testen. Bringt das viel? Bisher habe ich mit Set gearbeitet

Dies ist ja daselbe.

[Hansi]

upps...
in der DB sind am Anfang 0; in der txt-Datei (beim test ca. 100 Zeilen mit 5 Werten) In "Echt" werden es ca. 5000-10000 Zeilen

[Gollum]

Hallo,

der schnellste Weg um in MySQL Daten einzufügen ist

SQL-Code:

LOAD DATA INFILE

.
Schau Dir einmal die Syntax im MYSQL-Referanz-Manual an. Evtl. kannst Du damit etwas anfangen.

[Hansi]

Zitat:

Zitat von mkinzler

Zitat:

alle Insert bzw update in einem riesen String zusammenfassen und auf einmal ausführen. Bringt das was, anstatt jeden Datensatz einzeln einfügen?

Zitat:

- Insert mit Values muss ich auch mal testen. Bringt das viel? Bisher habe ich mit Set gearbeitet

Dies ist ja daselbe.

nein; Ich meinte alle Inserts "zusammensammeln" und mit einem ExecSQL ausführen. Das mit den Values war doch angedacht für jede Zeile der txt-Datei einem Insert, oder?

[mkinzler]

Zitat:

nein; Ich meinte alle Inserts "zusammensammeln" und mit einem ExecSQL ausführen. Das mit den Values war doch angedacht für jede Zeile der txt-Datei einem Insert, oder?

Nein alle in einem jeweils ein (..) ist ein Werte-Tuppel

[Bernhard Geyer]

1, (Wie schon angemerkt) sollten passende Indize auf der Tabelle vorhanden sein
2, Alle SELECT + Inserts/Updates werden ja (mit geänderten Werten) sollten als prepared Statements ablaufen (also nur die parameterwerte geändert werden
3, Du solltest halbwegs aktuelle Zeos-Version einsetzen. Ältere Versionen waren krotten-langsam. Falls du das letzte Prozent brauchst entweder ohne komponenten Arbeiten oder MyDAC verwenden.

Damit solltest Du mit lokaler DB überschlagsmäßig alle 10ms ein SQL-Statement abschicken können. Auf ferner DB kommt noch die verzögerungszeiten der Netzwerkkommunikation dazu. Wie hoch sind die Ping-Zeiten zum Server?

[Jelly]

Wenn es nur 10000 Werte in der Textdatei sind, würd ich den ganzen Bestand erstmal einfach in eine Stringlist laden, oder in eine vielleicht besser geeignete Speicherstruktur, wie auch immer.

Dann wie folgt vorgehen:
Alle IDs (nenn ich jetzt mal die Spalten über die Du testen willst, ob der Record schon in der MySQL Tabelle existiert) suchen, die bereits in der DB vorhanden sind. Dazu baust du dir ein kommaseparated String zusammen, über alle Datensätze im Speicher. Das wird dann sowas:
1,2,3,4,5,...,10000
und führst anschliessend ein select aus, in dem du bestehende Datensätze suchst:

SQL-Code:

select id from deinetable where ID in (1,2,3,4,4,...,10000)

Jetzt durchläufst du deine Stringlist:
ist ID bereits vorhanden -> update Statement für den Record formulieren und zum Server schicken. Updates musst Du leider einzeln abarbeiten.
ist ID noch nicht in der Tabelle drin, dann den Values Tupel, als kommasarated bilden:
(1,'blu'),(2,'bla')...
also neue Datensätze einfach hin anhängen.

Zum Schluss noch ein letztes Insert in die Tabelle:

SQL-Code:

insert into deinetable (ID,Spalte2) values (1,'blu'),(2,'bla')...

Das ist imho die schnellste Möglichkeit, neben dem direkten Laden über den Load Befehl von MySQL, falls es denn damit geht.

[Hansi]

Danke Tom,

So wie Du das schreibst klingt es einleuchtend; so werde ich das auch machen; Mit dem Befehl Load Data infile kann ich dann immer noch testen.

Das mit der Select Abfrage nach allen update Datensätzen ist eine super idee.

ich teste mal...

[hoika]

Hallo,

kann die 4.1 schon stored procedures (SP) ?
Wenn ja, alle Einträge in eine eigene Tabelle packen (Name: Import ?)

Dann eine SP, die das Eintragen erledigt (also das select -> update/insert).
Falls die Tabelle während des inserts nicht benötigt wird,
könnte man auch die Indizes deaktivieren (alter index idx_bla inactive)
und nach dem insert wieder neuaufbauen (alter index idx_bla active)

Hauptvorteil ist die geringe Netzlast,
es werden nicht 10000 inserts/updates übers Netz gemacht.

Ah ja,
die Tabelle hat natürlich keine Indizes,
und wird mit 10000 inserts gefüttert.
Oder über das load data.

Heiko

[Hansi]

Jo Baby ... Jo Baby ... Jooo :)

So nachdem ich sämtliche SQL-Abfragen optimiert habe und vorallem auf das "Insert Into ... Values"-Tupel umgestellt habe. Liege ich derzeit nicht mehr bei 3 Inserts pro Sekunde sondern bei ca. bei 250-300 pro Sekunde.

Nochmal Danke Tom!