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'

Leave a Reply

Your email address will not be published. Required fields are marked *