11 May, 2012

Java JDBC VS Java in Database

We can use Java code directly in PLSQL to manipulate database (insert data , update , do transaction .... etc) , and also we can write java code to manipulate database through JDBC connection.

When you have two solutions to do the same task, you should wait and choose the best solution.
So in my post today I will illustrate the difference between embedded java in PLSQL and Java run through JDBC connection.

For our demo I will create java code to delete and insert record in SCOTT.EMP table.
I will write code to run from PLSQL and write the same code to run through JDBC connection.

I created below Java class contains dmlOperation() method which do delete and insert record in SCOTT.EMP table and track time used for finishing transaction.


 import java.sql.*;  
 public class JdbcVsPlsql {  
   public static void dmlOperation() throws SQLException {  
     int empNo = 999;  
     String empName = "Mahmoud";  
     String empJob = "DEV";  
     long startTime, endTime;  
     Connection conn = null;  
     Statement stmt = null;  
     if (System.getProperty("oracle.jserver.version") != null) {  
       // You are in the database, already connected,then use the default  
       conn = DriverManager.getConnection("jdbc:default:connection:");  
       System.out.println("Running in OracleJVM,in the database!");  
     } else {  
       // You are not in the database,then you need to connect to the database  
       DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
       conn = DriverManager.getConnection("jdbc:oracle:thin:", "SCOTT", "TIGER");  
       System.out.println("Running in JDK VM, outside the database;");  
       conn.setAutoCommit(false);  
     }  
     // Start timing  
     startTime = System.currentTimeMillis();  
     stmt = conn.createStatement();  
     String deleteStatement = "DELETE FROM SCOTT.EMP WHERE EMPNO= " + empNo;  
     String insertStatement =  
       "INSERT INTO SCOTT.EMP(EMPNO, ENAME, JOB)VALUES(" + empNo + ",'" + empName + "','" + empJob + "')";  
     stmt.executeUpdate(deleteStatement);  
     stmt.executeUpdate(insertStatement);  
     // Close the Statement  
     stmt.close();  
     // Stop timing  
     endTime = System.currentTimeMillis();  
     System.out.println("====> Duration of Delete and Insert Operation: " + (int)(endTime - startTime) +  
               " Milliseconds");  
     // Close the connection  
     conn.close();  
   }  
   public static void main(String[] args) throws SQLException {  
     dmlOperation();  
   }  
 }  


I will create java source for JdbcVsPlsql class as below
 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JdbcVsPlsql"   
   AS   
 import java.sql.*;  
 public class JdbcVsPlsql {  
   public static void dmlOperation() throws SQLException {  
     int empNo = 999;  
     String empName = "Mahmoud";  
     String empJob = "DEV";  
     long startTime, endTime;  
     Connection conn = null;  
     Statement stmt = null;  
     if (System.getProperty("oracle.jserver.version") != null) {  
       // You are in the database, already connected,then use the default  
       conn = DriverManager.getConnection("jdbc:default:connection:");  
       System.out.println("Running in OracleJVM,in the database!");  
     } else {  
       // You are not in the database,then you need to connect to the database  
       DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  
       conn = DriverManager.getConnection("jdbc:oracle:thin:", "SCOTT", "TIGER");  
       System.out.println("Running in JDK VM, outside the database;");  
       conn.setAutoCommit(false);  
     }  
     // Start timing  
     startTime = System.currentTimeMillis();  
     stmt = conn.createStatement();  
     String deleteStatement = "DELETE FROM SCOTT.EMP WHERE EMPNO= " + empNo;  
     String insertStatement =  
       "INSERT INTO SCOTT.EMP(EMPNO, ENAME, JOB)VALUES(" + empNo + ",'" + empName + "','" + empJob + "')";  
     stmt.executeUpdate(deleteStatement);  
     stmt.executeUpdate(insertStatement);  
     // Close the Statement  
     stmt.close();  
     // Stop timing  
     endTime = System.currentTimeMillis();  
     System.out.println("====> Duration of Delete and Insert Operation: " + (int)(endTime - startTime) +  
               " Milliseconds");  
     // Close the connection  
     conn.close();  
   }  
   public static void main(String[] args) throws SQLException {  
     dmlOperation();  
   }  
 }  

Now I will create PLSQl wrapper procedure for dmlOperation() method in JdbcVsPlsql class
 CREATE OR REPLACE PROCEDURE DMLOPERATION  
 AS  
   LANGUAGE JAVA  
   NAME 'JdbcVsPlsql.dmlOperation () ';  

After creating PLSQL procedure and Java Class
let's now run every one 5 times and record the estimated time to execute and get the final average for each one.

to test from PLSQL you can use below code
 BEGIN  
   DBMS_JAVA.SET_OUTPUT (100000);  
   FOR I IN 1 .. 5  
   LOOP  
    DMLOPERATION;  
   END LOOP;  
 END;  

to test from JDBC connection you can run java class from Jdeveloper



Java Class (JDBC) Java from PLSQL
1st 62 Milliseconds  8 Milliseconds
2nd 61 Milliseconds    4 Milliseconds
3rd 64 Milliseconds  4 Milliseconds
4th 61 Milliseconds  4 Milliseconds
5th 60 Milliseconds     5 Milliseconds
Average 61.6 Milliseconds  5 Milliseconds

Regarding average result, using java from PLSQL is faster than using JDBC connection.
The time to execute may differ at your machine because of hardware difference specification.

Thanks
Mahmoud A. El-Sayed

12 comments:

  1. You can indicate from where you connect to database from System.getProperty("oracle.jserver.version")
    if it has null value then you connected through JDBC connection otherwise you connected from database using Oracle JVM

    I posted before post about Java system Properties
    http://mahmoudoracle.blogspot.com/2012/04/print-java-system-properties-in-plsql.html

    ReplyDelete
  2. Ok, java code manipulating the database is faster when run on the server, that's quite normal because you do not waste time in for the communication between your client and you server which is what costs the most (btw are talking of a JDBC connection over a network or is it a loopback ?). If you need to call your java proc in JDBC and pass parameters then you'll lose a great part of the time you gained. What is really important to take in account when you make your choice between JDBC or a server proc (java or plain plsql) is the kind of operation you'll do and what will be your inputs/outputs. Creating a java proc that just build a statement and execute it with parameters passed by a JDBC connection is useless.

    ReplyDelete
    Replies
    1. I am agree with you in your proof why it takes more time in JDBC connection.
      I am also use the approach that build PLSQL code and call it from Java.
      But what I want is to approve that the transaction time in database id fast whatever you run it from PLSQL or Oracle JVM in database

      Delete
    2. Sure, and you made your point, it is ~12 times faster :)

      An interesting question you might answer in a next article is : Do PLSQL and Java has the same performances, maybe java is a bit slower because of the embedded VM induced overhead ?

      Delete
    3. PLSQL is faster than Java also so the best is creating packages, functions and procedures and call it from Java

      Delete
  3. +1 for Twister.

    And take into account scalability. It's easier to run a jdbc program twice than to duplicate your database. License wise and so on.

    ReplyDelete
  4. And I forgot: replace the comic sans, it is not comical at all ;)

    ReplyDelete
  5. I always create PLSQL functions, procedures and packages in database and call it from Java.
    But what I intend to illustrate is that running everything in database is faster than others

    ReplyDelete
  6. What about server side Java vs PLSQL -- which is faster?

    ReplyDelete
    Replies
    1. PLSQL is faster so create your packages and functions in Plsql and call it from Java

      Delete
  7. Nice post but, what's the performance benefit of using a java based stored procedure over the one one written entirely in PL/SQL?

    ReplyDelete
    Replies
    1. I want to approve that running everything in database is faster than others

      Delete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...