Schnell viel Einfügen (INSERT)

wulfskin · Verfasst: Do 22.03.07 10:48

Hallo,

mir geht es hier eher prinzipiell um Varianten wie ich größere Datenmengen in eine MySQL-Datenbank schnell einfügen kann.
Dabei geht es um Daten die sich grundsätzlich für jede Zeile ändern, als auch um Daten, die Zeilenweise bis auf eine (aufsteigende) Nummer gleich sind.
Sind normale Inserts (in Schleifen) schnell genug oder wie lässt sich das optimieren?

Gruß Hape!

ene · Verfasst: Do 22.03.07 11:18

Hi,

was heißt viel und was heißt schnell? Generell: Je weniger Indexe auf der Tabelle sind, desto schneller das Schreiben und desto langsamer das Suchen. Ggf kann man sich auch die Werte vorhalten und dann nach und nach abarbeiten?

wulfskin · Verfasst: Do 22.03.07 11:45

Hallo,

mir geht es eigentlich nur darum, was die schnellste Möglichkeit ist viele statische Daten in eine Tabelle einzutragen.
Oder noch allgemeiner: Welche verschiedenen Möglichkeiten gibt es zum Einfügen und wie schnell sind diese, bzw. für welchen Zweck sind diese optimiert?

Ok, damit du einen Überblick hast: Ich rechne so mit 500 Datensätzen auf einmal. Das wird wohl nicht viel sein, aber ich bin trotzdem interessiert, wie ich so etwas effizient gestalte. Schnell bedeutet für mich in weniger als einer Sekunde, aber ich hab da überhaupt kein Gefühl dafür.

Viele Grüße,
Hape

ene · Verfasst: Do 22.03.07 11:59

500 sollten in weniger als 1 Sekunde laufen. Wobei der Umfang und die Connection auch noch mitspielen. Der gängige Weg ist es sich eine Abfragesyntax in Delphi zu schreiben und sie an MySQL zu übergeben, also in deinem Falle ein INSERT INTO ... und dass dann als Abfrage ausführen zu lassen.

Unter MySQL > 5 kannst du nun auch endlich Stored Procedures verwenden. Diese könntest du dann auf MySQL-Seite anlegen und mit Übergabeparametern füttern. Die Auswertung der Parameter geschieht dann auf dem Server, wobei der Aufruf zum Ausführen immer noch von Delphi kommt. Vorteil kann hierbei der Ablaufplan sein. Denn eine Datenbank optimiert den Ablauf einer Abfrage selbstständig. Sprich, es kann Unterschiede geben, wenn du sie das 1. und das 3. mal aufrufst. Beim 3. mal wäre sie schneller. Allerdings bei einem einfachen Insert sollte sich dort nicht viel tun.

Und dann kommt immer noch die Art der Connection und der verwendete CursorType zum Tragen, hier habe ich aber nur Erfahrungen unter VB(A).

HTH

Niko S. · Verfasst: Do 22.03.07 12:03

Also Insert ist die mir einzig bekannnte Funktion im Moment.
Das würde denn so aussehen:

markieren

Quelltext

1:	INSERT INTO `name` VALUES ('text',int,int,'text');

Und das immer hintereinander oder halt wenns überschrieben werden soll:

markieren

Quelltext

1:	REPLACE INTO `name` VALUES ('text',int,int,'text');

MySQL Arbeitet eigendlich sehr schnell von Hause aus.

wulfskin · Verfasst: Do 22.03.07 12:16

Hallo,

danke für eure Antworten!

Simak hat folgendes geschrieben:

Unter MySQL > 5 kannst du nun auch endlich Stored Procedures verwenden.

Klingt nach einem guten Tipp, ich muss mich mal erkundigen ob ich schon so eine neue Datenbank auf dem Webserver habe.
Allerdings ist die Lösung halt nur auf sehr wenigen Datenbanken im Internet umsetzbar und deswegen für mein PHP-Projekt wohl eher ungeeignet.

Simak hat folgendes geschrieben:

Also Insert ist die mir einzig bekannnte Funktion im Moment. [...]

Sei mir nicht böse, aber dein Beitrag ist mir ingesamt etwas zu einfach gestrickt:

Dein Beispiel ist stark vereinfacht, es fehlen sogar die Feldmann, die man üblicherweise benutzt, z.B um Daten automatisch ausfüllen zu lassen.
Es gibt auch INSERT SELECTs.

Replace scheint eine sehr langsame Methode zu sein und nur in ganz wenigen Fällen brauchbar:

Eric Stevens hat folgendes geschrieben:

Please note that REPLACE INTO is a much slower performer than an UPDATE statement. Keep in mind that a REPLACE INTO requires a test on the keys, and if a matching unique key is found on any or all columns, a DELETE FROM is executed, then an INSERT is executed. There's a lot of management of rows involved in this, and if you're doing it frequently, you'll hurt your performance unless you simply cannot do with any other syntax.

The only time when I can see where you'd actually need a REPLACE INTO is when you have multiple unique constraints on a table, and need to drop any rows that would match any of the constraints. Then REPLACE INTO becomes more efficient from DELETE FROM... INSERT INTO...

Außerdem möchte ich etwas Einfügen und nicht Ersetzen.

Viele Grüße,
Hape

ene · Verfasst: Do 22.03.07 12:39

Keine Ahung, ob Replace überhaupt noch dazu gehört, sonst nimmt man Update.

Wichtig bei der ganzen Geschichte, um es noch einmal zu unterstreichen, ist der Zugriff. zB sollte man die Connection nur einmal öffnen und sich die Bearbeitungsschritte überlegen. Unter VB-ADO gibt es zB ein BatchUpdate, mit dem man sehr viele Querys in einen Queue stellen kann und dann erst die Bearbeitung anstößt, aber so etwas geht nicht immer.

Auch kann man mit Textdateien arbeiten (Stichwort SQL-Dump), die dann von MySQL geparst werden und damit kann man nicht nur Strukturen erstellen, sondern auch Daten kopieren. Vielleicht könntest du näher auf dein Vorhaben eingehen, evtl kommt einem ja noch die eine oder andere Idee.

Falls du mit ADO arbeitest, kannst du dir hier mal die CursorTypes ansehen und hier die ConnectionProperties.

Ansonsten wären weitere Informationen nötig.

wulfskin · Verfasst: Do 22.03.07 12:55

Hallo Ene,

ich möchte für eine zusätzlich Newsletterplugin für WordPress (PHP, MySQL) dem Nutzer die Möglichkeit geben, mehrere E-Mail-Adressen in einer Liste auf einmal einzufügen.
Zusätzlich kann der Nutzer noch zu diesen Adressen Einstellungen vorgeben die für alle Adressen gleich sind, in einer weiteren Tabelle gespeichert sind und mit den Adressen über den Index zugeordnet werden.
Das ganze soll schnell gehen und elegant gelöst sein.
Ein Beispiel:

markieren

Quelltext

1:
2:
3:
4:
5:
6:
7:
8:

Tabelle email
Id        int
email     varchar

Tabelle options
Id        int
email_id  int
option    int

Die E-Mail-Adressen selbst einzutragen, geht wohl nur über eine Schleife mit INSERTs. Für die Optionen allerdings würde ich mir eine elegante Methode wünschen, denn grundsätzlich soll nur für die email_ids der eben eingefügten Zeilen die Optionen (die immer gleich sind) gesetzt werden.
Beispiel:

markieren

Quelltext

1:	INSERT INTO options (email_id, option) VALUES (:zahl, 1)

Es ändert sich also nur der Parameter :zahl, der für jede eingefügte Id stehen soll.
War das verständlich?

Gruß Hape!

ene · Verfasst: Do 22.03.07 13:17

Achja...leidige MySQL Probleme...unter anderem DBMS wäre das kein Problem. Das Stichwort heißt Trigger, die gibts IMHO auch erst ab MySQL 5 und damit wieder nur eingeschränkt. Ein Trigger ist ein TabellenEvent. Sprich du schreibst einen Wert in eine Tabelle und dein Trigger führt einen Befehl danach aus. Somit könntest du gleich schon mal die ID in die andere Tabelle schreiben und dann mit einem UPDATE Tabelle SET Feld = 1 WHERE Feld Is Null alle neuen EMails aktualisieren.

Ansonsten fällt mir bei MySQL dort leider keine andere Lösung ein, als mit 2 Abfragen zu arbeiten, die in einer Schleife verarbeitet werden. Wobei die Performance hier auch noch als schnell zu bezeichnen sein muss.

wulfskin · Verfasst: Do 22.03.07 13:38

ene hat folgendes geschrieben:

Ansonsten fällt mir bei MySQL dort leider keine andere Lösung ein, als mit 2 Abfragen zu arbeiten, die in einer Schleife verarbeitet werden. Wobei die Performance hier auch noch als schnell zu bezeichnen sein muss.

Klar Aussage, danke für die Tipps!

Gruß Hape!

ene · Verfasst: Do 22.03.07 13:58

Ein INSERT INTO Tabelle () INNER JOIN Tabelle2 () VALUES () gibts leider nicht. Wobei ich mich gerade frage, warum du so einen Aufbau verwendest oder gibt es zu jeder EMail mehrere Optionen?

wulfskin · Verfasst: Do 22.03.07 14:04

ene hat folgendes geschrieben:

Wobei ich mich gerade frage, warum du so einen Aufbau verwendest oder gibt es zu jeder EMail mehrere Optionen?

Ja, prinzipiell sind es keine Optionen sondern Kategorien, für jene sie festlegen können, ob sie für diese Benachrichtigungen erhalten möchten oder nicht.

ene · Verfasst: Do 22.03.07 14:24

Ok, dann hab ich nichts gesagt

BenBE · Verfasst: Do 22.03.07 14:36

Ich denke, im Allgemeinen sollte die Performance des Servers bei Insert kein Problem sein:
Wer's kennt: Die GeoIP-Datenbank lässt sich in weniger als 5 Sekunden einspielen (~200000 Datensätze) ... Alle on the Fly mit PHP aus CSV nach SQL-Statements konvertiert und über ne lokale DB-Connection geschrieben ...

Ansonsten kann ich auch nur den Tipp mit ADO und den BatchUpdates, sowie die Empfehlung zur Nutzung des ConnectionCache geben. Diesen hab ich mit Access einmal genutzt (Einfügen Wortliste, 380000 Einträge) und dabei von 35 Sekunden auf ~1 Sekunde optimieren können.

Was also auf jeden Fall vorteilhaft ist, ist daten vorgeparst\vorbereitet an MySQL zu übergeben und ggf. dem Server eine Canche zu geben, eigene Optimierungen in der Verarbeitung so gut wie möglich auszuführen (also z.B. Stored Procedures) auszuführen.

Was man in diesem Zusammenhang vermeiden sollte, ist die Nutzung einzelner Transaktionen für jeden Eintrag, da sie unverhältnismäßig viel Load auf dem Server provozieren (man macht dem Server doppelte bis dreifache Arbeit ... für jede TXN).

Ein weiterer Tipp ist z.B. auch die Datentyp-Optimierung an sich ... D.h. Anstatt die Tabelle von Anfang an mit VarCHars zu nutzen kannst Du eine zweiter Temporärtabelle erzeugen, die von den anforderungen deiner zweiten Tabelle entspricht und dann einfach über zwei einzelne Queries die Daten in eigentlichen Tabellen übernimmt ...

Also:

markieren

SQL-Anweisung

1:
2:

INSERT INTO user_tbl SELECT email FROM tmp_tbl GROUP BY email;
INSERT INTO notify_tbl SELECT email, cat FROM tmp_tbl;

Damit gibst Du dem MySQL-Server die Chance, ein großes Update auszuführen, anstatt viele kleine Operationen vornehmen zu müssen ... Außerdem reduziert sich damit der Aufwand für Index-Operationen, da diese kleine Tabelle ohne die umfangreichen Indizes der Haupttabelle aufgebaut werden kann.

Allerdings bringt diese Methode nur vorteile, wenn sich die einzutragenden Daten relativ leicht in einer Tabelle zusammenfassen lassen, ohne übermäßig Redundanz zu erzeugen ...

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme