MySQL Prepared Statement batch langsamer als bulk insert?

[Laures]

Hallo,

ich vergleiche gerade mehrere methoden viele zeilen in eine tabelle einzufügen.

Zuerst ist das das bisher im code (nicht meiner^^) verwendete Verfahren, ein laaaanges insert query mit vielen einträgen zu basteln.

StringBuffer insertStart, insert = new StringBuffer();
String insertValues;
insertStart = new StringBuffer("INSERT INTO " + tn + "(id, ts, uid, eid) VALUES");
insert.append(insertStart);
stmt = c.createStatement();
stmt = c.createStatement();
for(uid = 1; uid <= COUNT; uid++) {
	id = uid - COUNT;
	insertValues = "(" + id + ", '" + new Timestamp(System.currentTimeMillis()) + "', " + uid + ", " + 42 + ")";
	insert.append(insertValues);
	if(uid % BATCH == 0 || uid == COUNT) {
		stmt.execute(insert.toString());
		insert = new StringBuffer(insertStart);
	} else {
		insert.append(", ");
	}
}

Dann die methode über Prepared statements:

String sql = "INSERT INTO " + tn + "(id, ts, uid, eid) VALUES (?, ?, ?, ?)";
stmt = c.prepareStatement(sql);
for(uid = 1; uid <= COUNT; uid++) {
	id = uid - COUNT;
	stmt.setInt(1, id);
	stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
	stmt.setInt(3, uid);
	stmt.setInt(4, 35);
	stmt.addBatch();
	if(uid % BATCH == 0 || uid == COUNT) {
		stmt.executeBatch();
		stmt.clearBatch();
	}
}

Vergleiche ich nun die Zeit, die beide methoden brauchen um 100 000 Zeilen in 1000er Paketen hinzuzufügen ist die prepared statement methode um üetwa 40% langsamer. Es will mir nicht ganz in den kopf das die saubere Art das zu machen so viel langsamer ist.

Was mache ich falsch.

Laures

 

[Gast2]

Ist relativ einfach - im ersten Fall wird genau ein Commando an die Datenbank geschickt im zweiten Fall eine Menge von Commandos.

Soweit ich weiß ist glaube ich auch Lösung 1 ein Sonderfall von MySQL, das geht soweit ich weiß nicht bei jedem RDBMS. Ich würde auf jeden Fall Lösung 2 bevorzugen - da kann man dann wenigstens auch vernünftig Fehler aufarbeiten. Und über weiter Vorteile wie die Typensicherheit und Sicherheit gegen SQL Injections brauch man nun nicht weiter reden.

 

[Laures]

das heißt obwohl die statement batches genausoviele inserts enthalten wie ein bulk insert statement, sind sie langsamer weil es im batch mehrere querys sind?

 

[maki]

Ist relativ einfach - im ersten Fall wird genau ein Commando an die Datenbank geschickt im zweiten Fall eine Menge von Commandos.

Sorry, ist aber nicht richtig.

"Commandos zur Datenbank" schicken dauert an sich sehr lange, weil das Netzwerk involviert ist.
NOrmalerweise sind Bulkstatement gleich um Größenordnungen schneller als ihre einzelnen Pendants, vorrausgesetzt, man hat die richtige Batch- und Transaktionsgröße gefunden, hängt immer von der DB Konfiguration ab.

Laures,

würde mal versuchen nicht 1000 Statements aufeinmal zu schicken, spiel doch mal ein bisschen mit der Batchgröße herum.
Bei Transaktionen wird oft eine temp. Tabelle verwendet, wenn es zuviele Transaktionen werden, kann das bremsen.

 

[Laures]

ich hab jetz mal bissel mit der batch size rumprobiert und alle debug outputs rausgenommen.

bulk insert immer etwa 9-10 mal so schnell wie die prepared statement batch.
die "optimale" bulk größe scheint bei ca 2000 zu liegen.

die prepared statements brauche (relativ unabhängig von der bulk größe immer etwa 10,xx sekunden.

scheinbar ist die häßliche methode die deutlich schnellere.

 

[Gast2]

Sorry, ist aber nicht richtig.

"Commandos zur Datenbank" schicken dauert an sich sehr lange, weil das Netzwerk involviert ist.
NOrmalerweise sind Bulkstatement gleich um Größenordnungen schneller als ihre einzelnen Pendants, vorrausgesetzt, man hat die richtige Batch- und Transaktionsgröße gefunden, hängt immer von der DB Konfiguration ab.

Ich habe doch nichts anderes geschrieben? Das MySQL Bulk Insert ist genau ein Statement das komplett an die DB geschickt wird und dann intern auseinander genommen wird. PreparedStatements (auch als Batch) werden afaik einzeln hintereinander abgearbeitet (Im Treiber gequeued, nicht in der DB) und brauchen deswegen grundsätzlich länger.

 

[SlaterB]

bulk insert immer etwa 9-10 mal so schnell wie die prepared statement batch.

<->

Vergleiche ich nun die Zeit, die beide methoden brauchen [..] ist die prepared statement methode um etwa 40% langsamer.

?

 

[Laures]

beim ersten versuch hatte ich debug ausgaben drinn. die haben dem bulk insert wohl deutlich mehr geschadet als den prepared statements. Jedenfalls lag da das verhältnis bei 6 sekunden gegen 11 sekunden.

ohne dass alles (und mit verschiedenen batch größen getestet) braucht ein großes insert etwa eine sekunde, die prepared statements über 10.

 

[ARadauer]

. Es will mir nicht ganz in den kopf das die saubere Art das zu machen so viel langsamer ist.

Was mache ich falsch.

machst nix falsch... ist leider so...

 

[maki]

Ich habe doch nichts anderes geschrieben?
...

Oops, dann nehme ich alles zurück und behaupte das Gegenteil, nix für ungut 🙂

 

[Gast2]

Dann will ich dir mal verzeihen 😉