MySQL PreparedStatement mit INSERTs langsam, Batch-Statement auch

[ThePianoman]

Hallo,

ich wollte ein PreparedStatement nutzen, um mehr Geschwindigkeit
bei meinen INSERTs zu haben. Also:

String sqlInsertWord = "INSERT INTO wordsp (germanWord, foreignWord, lessonId) VALUES (?,?,?)";
PreparedStatement prepStmt = conn.prepareStatement(sqlInsertWord); // dauert 0.5s !!!

Das Anmelden des PreparedStatement per conn.prepareStatement
dauert eine halbe Sekunde! Das kann doch nicht so lange dauern?!

Bei 400 INSERTs dauert connection.prepareStatement(...) 0,5s,
das Einfügen der Werte in einer Schleife selbst dann nochmal etwa 0,25s.

Das Gleiche mit ganz normalen einzelnen Statements dauert - aufgepasst -
0,75s, also genauso lang. Meine PreparedStatements wären also nur dann
schneller, wenn ich hier >400 Zeilen einfüge.

Ich habe gerade einen Statement-Batch getestet, also

String sqlInsert = "INSERT INTO wordsp (germanWord, foreignWord, lessonId) VALUES ('%s','%s',%s)";
while ((line = bis.readLine()) != null) { // 400 Zeilen im BufferedInputStream
  String sql = String.format(sqlInsert, values[0], values[1], lessonId);
  stmt.addBatch(sql);
}
stmt.executeBatch();.

Das dauert jetzt 0,8s; also länger als die einzelnen Statements
bzw. PreparedStatement. Ich bin verwirrt.

Sind 0,5s für das Parsen des PreparedStatement normal?
Warum ist ein Statement.executeBatch nicht schneller als
das Verschicken von einzelnen Statements?
Wo kann ich noch optimieren?

Danke und Gruß
Bernd

PS: Ich nutze MySQL 5.1, den MySQL-Connector v5.1.6, Java 6 Update 22,
all das in einer Web-Anwendung unter Tomcat6.

 

[planetenkiller]

Füge bei der JDBC Verbindungs-Url folgendes hinzu: ?rewriteBatchedStatements=true
Neuer Import Code:

String sqlInsertWord = "INSERT INTO wordsp (germanWord, foreignWord, lessonId) VALUES (?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sqlInsertWord);
while ((line = bis.readLine()) != null) { // 400 Zeilen im BufferedInputStream
  stmt.setString(1, values[0]);
  stmt.setString(2, values[1]);
  stmt.setInt(3, lessonId);
  stmt.addBatch();
}
stmt.executeBatch();

Sollte folgenden Query an die DB schicken:
INSERT INTO wordsp (germanWord, foreignWord, lessonId) VALUES ('a','b',1),('a','b',1),('a','b',1),....

Effizienter geht es kaum noch.

 

[Bartleby]

Bei 400 INSERTs dauert connection.prepareStatement(...) 0,5s,
das Einfügen der Werte in einer Schleife selbst dann nochmal etwa 0,25s.

Du rufst das conn.prepareStatement() aber nicht für jeden INSERT neu auf, oder?

 

[ThePianoman]

Füge bei der JDBC Verbindungs-Url folgendes hinzu: ?rewriteBatchedStatements=true
Effizienter geht es kaum noch.

Tatsächlich, mit dem Parameter "rewriteBatchedStatements" ist es etwas
schneller. In meinem Fall waren es statt vorher 0,75s jetzt 0,6s für meine
400 INSERTs. Die PreparedStatements im Batch habe iche dann auch
übernommen. Danke.

Edit: Ich habe gerade nochmal meinen Importer mit den INSERTs
aufgerufen und bin jetzt bei ca. 0,12s, also VIEL besser. Das war wohl
ein Problem mit Cache oder unvollständiger Kompilierung.
[Ende Edit]

Du rufst das conn.prepareStatement() aber nicht für jeden INSERT neu auf, oder?

Nein.

Edit:
Nächste Absätze sind dann weniger relevant, da die INSERTs jetzt
ausreichend schnell sind. Danke nochmal.[Ende Edit]

Als Nicht-DB-Experte finde ich diese Zahl von INSERTs (sagen wir mal
700 INSERTs pro Sekunde) extrem wenig. Das Ganze läuft auf einem
recht aktuellen und sauberen MySQL 5.1.41, Typ der DB ist MyISAM,
Datenträger ist eine SSD, BS ist Win7.

Sind das normale Werte (~700 INSERTs pro Sekunde)?
Ich gehe mal davon aus, dass in meiner Konfiguration wohl noch etwas
zu optimieren ist (xampp). Ein Aktualisierung auf MySQL 5.5 wird wohl
nicht substanziell etwas bringen, oder?

Meine Tabelle sieht so aus:

CREATE TABLE `wordsp` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  
`germanWord` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_general_ci',  
`gwAudio` BLOB NULL,  
`gwAudioLink` VARCHAR(80) NULL DEFAULT NULL COLLATE 'utf8_general_ci',  
`foreignWord` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_general_ci',  
`fwAudio` BLOB NULL,  
`fwAudioLink` VARCHAR(80) NULL DEFAULT NULL COLLATE 'utf8_general_ci',  
`lessonId` INT(11) NOT NULL,  `score` INT(1) NULL DEFAULT '3' 
`comment` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',  
UNIQUE INDEX `id` (`id`) )  
COLLATE 'latin1_swedish_ci' 
ENGINE=MyISAM ROW_FORMAT=Dynamic AUTO_INCREMENT=1986 COMMENT='';

Gruß
Bernd

 

[student86]

Moin Moin,
ich benutze die selbe methode um Daten aus einer Datei in eine DB zu bekommen.
Doch bei schafft er ca 10 einträge in der Sekunde..... das ist viel zu langsam.
kann aber keinen unterschied zum hier verwendetet code finden ???

       String line;	
	        String[]values;
	        PreparedStatement ps = connection.prepareStatement("INSERT INTO Symboltabelle VALUES (?, ?, ?,?);");
	        while ((line=br.readLine())!=null){
	        	values=line.split(";");
	            ps.setString(1, values[0]);
	            ps.setString(2, values[1]);
	            ps.setString(3, values[2]);
	            ps.setString(4, values[3]);  
	            ps.addBatch();
	         
	            i++;
	        
	        }   
	        System.out.println(i+" Datensätze wurden in die Batch geschrieben");  
	        ps.executeBatch();
	        System.out.println(i+" Datensätze wurden in die DB geschrieben");
	        br.close();
	        stmt.close();

jemand eine Idee wieso es so langsam ist ?

Genauer : das ps.executeBatch() brauch die 15 min ^^
MFG