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.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.