Recent Posts

Wednesday, 14 November 2018

Batch Update

Need of Batch Updates
     When we submit multiple SQL Queries to the database one by one then lot of time will be wasted in request and response. In the case of simple Statement.
      In the case of normal Statement,
Total Time per Query = Request Time + Compile Time + Execution Time+Response Time
                                    = 2 ms + 1 ms + 1 ms + 3 ms = 7 ms
per 1000 Queries = 7 * 1000ms = 7000ms


     In the case of PreparedStatement,
Total Time per Query = Request Time + Response Time + Execution Time
                                   = 2 ms + 3 ms + 1 ms = 6 ms
1000 Queries = 6 * 1000 = 6000 ms

     In the above 2 cases, we are trying to submit 1000 queries to the database one by one. For submitting 1000 queries we need to communicate with the database 1000 times. It increases network traffic between Java application and database and even creates performance problems also.

     To overcome these problems, we should go for Batch updates. We can group all related SQL Queries into a single batch and we can send that batch at a time to the database.

With Simple Statement Batch Updates
Per 1000 Queries = Request Time+1000*Compile Time+1000*Execution Time+Response Time
                              = 2ms+1000*1ms+1000*1ms+3ms 
                              = 2005ms 

With PreparedStatement Batch Updates
Per 1000 Queries = Request Time+ Response Time + 1000 * Execution Time
                              = 2ms+3ms+1000*1ms 
                              = 1005ms

     We can implement batch updates by using the following two methods.
1. public void addBatch(String sqlQuery)
     To add query to batch

2. int[] executeBatch()
     To execute a batch of SQL queries.
     We can implement batch updates either by simple Statement or by PreparedStatement.
Batch Updates with Statement
import java.sql.*; 
/**
 * 
 * @author ashok.mariyala
 *
 */
public class BatchUpdatesDemo { 
   public static void main(String[] args) throws Exception {
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement(); 
      st.addBatch("insert into emp values(600,'Keerthi',35000,'Chennai')"); 
      st.addBatch("update emp set esal = esal+1000 where esal < 15000");
      st.addBatch("delete from emp where esal > 10000"); 
      int[] count=st.executeBatch(); 
      int updateCount=0; 
      for(int x: count) { 
         updateCount=updateCount+x; 
      } 
      System.out.println("The number of rows updated :"+updateCount); 
      con.close(); 
   } 
}
Batch Updates with PreparedStatement
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class BatchUpdatesDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "insert into emp values(?,?,?,?"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      PreparedStatement pst = con.prepareStatement(sqlQuery);
      Scanner sc = new Scanner(System.in);
      while(true) {
         System.out.println("Employee Number:");
         int eno=sc.nextInt();
         System.out.println("Employee Name:"); 
         String ename=sc.next(); 
         System.out.println("Employee Sal:"); 
         double esal=sc.nextDouble();
         System.out.println("Employee Address:");
         String eaddr=sc.next(); 
         pst.setInt(1,eno);
         pst.setString(2,ename);
         pst.setDouble(3,esal); 
         pst.setString(4,eaddr);
         pst.addBatch();
         System.out.println("Do U want to Insert one more record[Yes/No]:"); 
         String option = sc.next(); 
         if(option.equalsIgnoreCase("No")) {
            break;
         }
      }
      pst.executeBatch();
      System.out.println("Records inserted Successfully");
      con.close();
   }
}
Advantages
1. Network traffic will be reduced
2. Performance will be improved

Limitations
1. We can use Batch Updates concept only for non-select queries. If we are trying to use for select queries then we will get RE saying BatchUpdateException.
2. In batch if one sql query execution fails then remaining sql queries wont be executed.

Note
     In JDBC total there are 4 execute methods are available
1. executeQuery() ➔ For select queries
2. executeUpdate() ➔ For non-select queries(insert|delete|update)
3. execute()
    ➔ For both select and non-select queries
    ➔ For calling Stored Procedures
4. executeBatch() ➔ For Batch Updates

Next Tutorial : Inserting Date Values into Database

Previous Tutorial : Functions

No comments:

Post a Comment