What is faster in inserting data into database: PreparedStatement using batch or standard PreparedStatement in Java application?
What is better to use PreparedStatement
with batch or without batch. I test both ways to connect by JDBC to database and get data from one table and insert into other.
The first case will be select query. I use for it Microsoft example database: AdventureWorks2012
. I create New Project in Netbeans and add to Library node in project, JDBC driver to MSSQL Server.
INSERT query for PreparedStatement object without batch function.
I get all records from Person( schema Person )
table from AdventureWorks2012
database. This table contains data in 19 972 rows. And these data will be insert into new table. In last post I describe how fast create this table. I either delete four column in new table.
alter table [AdventureWorks2012].[Person].[NewPerson] drop column [Suffix]; alter table [AdventureWorks2012].[Person].[NewPerson] drop column [AdditionalContactInfo]; alter table [AdventureWorks2012].[Person].[NewPerson] drop column [Demographics]; alter table [AdventureWorks2012].[Person].[NewPerson] drop column [rowguid];
I check how much time take using PreparedStatement
object without using batch.
package doraprojects; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Date; public class TestInsert { public static void main(String[] args) { long time_in = System.currentTimeMillis(); System.out.println(new Date(time_in)); new DataFromMSSQL(); long time_out = System.currentTimeMillis(); System.out.println(new Date(time_out)); System.out.println("start:"+time_in+",stop:"+time_out+",diffrence:"+(time_out-time_in)+" ms"); } } class DataFromMSSQL { private String url; private Driver driver; private Connection connection; private PreparedStatement ps; private ResultSet rs; public DataFromMSSQL() { url = "jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2012;user=sa;password=YourPassToDb"; try { driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver(); DriverManager.registerDriver(driver ); } catch (Exception e) { System.out.println("Problem with download driver for MSSQL: " + e.getMessage()); } try { connection= DriverManager.getConnection(url); } catch (Exception e) { System.out.println("Problem with connection to MSSQL: " + e.getMessage() ); } try { ps = connection.prepareStatement("select * from Person.Person"); rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt(1); String type = rs.getString(2); byte nameStyle = rs.getByte(3); String title = rs.getString(4); String fName = rs.getString(5); String mName = rs.getString(6); String lName = rs.getString(7); int emailP = rs.getInt(9); String dateModified = rs.getString(13); PreparedStatement psI = connection.prepareStatement("insert into Person.NewPerson values(?,?,?,?,?,?,?,?,?)"); psI.setInt(1, id); psI.setString(2, type); psI.setByte(3, nameStyle); psI.setString(4, title); psI.setString(5, fName); psI.setString(6, mName); psI.setString(7, lName); psI.setInt(8, emailP); psI.setString(9, dateModified); psI.executeUpdate(); psI.close(); } rs.close(); ps.close(); } catch (Exception e) { System.out.println("Problem with ResultSet of MSSQL" + e.getMessage() ); } try{ if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." ); } } catch (Exception e) { System.out.println("Problem with close connection of MSSQL" ); } } }
Result time:
start:1470434126881, stop:1470434140999, diffrence: 14 118 ms
After run application You see this time. And what is with PreparedStatement with batch ?
INSERT query for PreparedStatement object with batch.
As in above example I get all records from Person
table from AdventureWorks2012
database and insert data into another empty table. I check how much time take using PreparedStatement
object use batch function.
package doraprojects; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Date; public class TestInsert { public static void main(String[] args) { long time_inp = System.currentTimeMillis(); System.out.println(new Date(time_in)); new DataFromMSSQL(); long time_out = System.currentTimeMillis(); System.out.println(new Date(time_out)); System.out.println("start:"+time_in+", stop:"+time_out+", diffrence:"+(time_out-time_in)+" ms"); } } class DataFromMSSQL { private String url; private Driver driver; private Connection connection; private PreparedStatement ps,psI; private ResultSet rs; public DataFromMSSQL() { url = "jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2012;user=sa;password=YourPassToDb"; try { driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver(); DriverManager.registerDriver(driver ); } catch (Exception e) { System.out.println("Problem with download driver for MSSQL: " + e.getMessage()); } try { connection= DriverManager.getConnection(url); } catch (Exception e) { System.out.println("Problem with connection to MSSQL: " + e.getMessage() ); } try { ps = connection.prepareStatement("select * from Person.Person"); rs = ps.executeQuery(); psI = connection.prepareStatement("insert into Person.NewPerson values(?,?,?,?,?,?,?,?,?)"); connection.setAutoCommit(false); while (rs.next()) { int id = rs.getInt(1); String type = rs.getString(2); byte nameStyle = rs.getByte(3); String title = rs.getString(4); String fName = rs.getString(5); String mName = rs.getString(6); String lName = rs.getString(7); int emailP = rs.getInt(9); String dateModified = rs.getString(13); psI.setInt(1, id); psI.setString(2, type); psI.setByte(3, nameStyle); psI.setString(4, title); psI.setString(5, fName); psI.setString(6, mName); psI.setString(7, lName); psI.setInt(8, emailP); psI.setString(9, dateModified); psI.addBatch(); } int[] ar = psI.executeBatch(); psI.close(); connection.commit(); rs.close(); ps.close(); } catch (Exception e) { System.out.println("Problem with ResultSet of MSSQL" + e.getMessage() ); } try{ if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." ); } } catch (Exception e) { System.out.println("Problem with close connection of MSSQL" ); } } }
Result:
start:1470470183176, stop:1470470190040, diffrence: 6 864 ms
In this test better findings achives PreparedStatement object using batch script. Difference between them is 7 254 miliseconds.