Improve speed of SQL Inserts into XML column from JDBC (SQL Server)

koRnAlone

New Member
I am currently writing a Java program which loops through a folder of around 4000 XML files.Using a for loop, it extracts the XML from each file, assigns it to a String 'xmlContent', and uses the PreparedStatement method setString(2,xmlContent) to insert the String into a table stored in my SQL Server. The column '2' is a column called 'Data' of type XML.The process works, but it is slow. It inserts about 50 rows into the table every 7 seconds.Does anyone have any ideas as to how I could speed up this process?Code:\[code\]{ ...declaration, connection etc etc PreparedStatement ps = con.prepareStatement("INSERT INTO Table(ID,Data) VALUES(?,?)"); for (File current : folder.listFiles()){ if (current.isFile()){ xmlContent = fileRead(current.getAbsoluteFile()); ps.setString(1, current.getAbsoluteFile()); ps.setString(2, xmlContent); ps.addBatch(); if (++count % batchSize == 0){ ps.executeBatch(); } } } ps.executeBatch(); // performs insertion of leftover rows ps.close();}\[/code\]\[code\]private static String fileRead(File file){ StringBuilder xmlContent = new StringBuilder(); FileReader fr = new FileReader(file); BufferedReader br = new BufferedReader(fr); String strLine = ""; br.readLine(); //removes encoding line, don't need it and causes problems while ( (strLine = br.readLine() ) != null){ xmlContent.append(strLine); } fr.close(); return xmlContent.toString(); }\[/code\]
 
Back
Top