Executing Multiple Update / Insert SQL queries using JDBC on Oracle – Performance improvements

Sometimes we need to execute multiple update/insert queries on our DB. The performance of these queries can be critical for our application. We will see how we can improve performance of multiple update/insert queries on oracle.

1. Create Index on the column/columns used in the WHERE clause for update queries. This will avoid the full table scan for every query. Full table scan is a time-consuming operation.
2. Transaction commit – do once, set auto-commit to false.  If auto-commit is true, which it is by default, then every SQL statement is committed to the database upon its completion.
3. Do batch update/insert using PreparedStatement.

   Connection conn;             //database connection
   conn.setAutoCommit(false);   //Set auto-commit to false
   
   PrepareStatement ps;         //create the  PreparedStatement for your query
   for(each update/insert){
      //Set parameters on the PreparedStatement
      ps.addBatch();            //Add to the batch
   }
   ps.execute();                //Execute the queries as a batch
   
   conn.commit();               //Commit the transaction here.

4. Avoid Mixed-Type Expressions in where clause. If your column emp_id is VARCHAR2  and you want to use an index on the column emp_id, but the WHERE clause looks like this:-

   AND emp_id = 10010

   Where 10010 is of number type, Oracle translates that expression into:-

   AND TO_NUMBER(emp_id) = 10010

   Any expression using a column, such as a function having the column as its argument, causes the optimizer to ignore the possibility of using an index on that column, even a unique index. If the system processes even a single row having emp_id as a string of characters that does not translate to a number, then an error is returned.

   You can avoid this problem by replacing the above expression with the explicit conversion:-

   AND emp_id= '10010'