Record Oracle Bind Variables and Java

http://asktom.oracle.com/pls/asktom/f?p=100:11:2253849508741645::::P11_QUESTION_ID:10128287191505

Bind Variables and Java

One of the recent questions on the website asktom.Oracle.com recently was this interesting one. It
was about programming in Java with JDBC.  This discussion applies equally to Visual Basic
programmers using VB with ODBC as the concept of "Statements" and "PreparedStatements" exists in
ODBC in more or less the same fashion.  It questioned the use of Statements versus a
PreparedStatement. When using Statements in JDBC - you must use the "string concatenation
approach". Using PreparedStatements allows you to use bind variables. The question was:
Tom -- Please briefly skim this link (link omitted for obvious reasons) which gives an excerpt for
JDBC performance.  It says always use statements (no bind variables allowed) instead of
preparedstatements because they perform better without discussing the impact on the database, only
in terms of a single apps metrics.  Is this accurate or is this information just extremely short
sighted with regards to overall db impact?

Well, that was easy - I gave them the proof from above - case closed. PreparedStatements with bind
variables are absolutely without question the only way to do it. Of course, later on, I got a
followup:

For a moment keep aside shared pool, hard parse and soft parse and talk about PreparedStatement and
Statement as they are the only way to execute statements from java.  I wrote this benchmark code
that shows a Statement performs better than a PreparedStatement unless you execute the same
statement a whole lot of times.  So, I reproduced the findings of the above link and prove that
Statements are better than PreparedStatements.


I had some issues with this one - they missed the point. They start with "for a moment keep aside
shared pool, hard parse and soft parse". Well, if we were to ignore those - we totally miss the
boat on this topic as they are the only things to consider. The facts are:

o Hard Parsing incurs many latches
o Latches are serialization devices
o Serialization is not a scalable thing
o Therefore as you add users, the system that uses Statements instead of PreparedStatements with
bind variables will fail.

I quite simply could not observe their request to put aside the shared pool and hard/soft parse.
They are the relevant topics - they must be considered. That was my initial response - but you
know, this bothered me so much, I had to explore it further. So, starting with their benchmark code
which simply inserted into a database table, I made it a multi-user benchmark to demonstrate the
fact that if you expand this simple, single user benchmark out to a real world example with
multiple users - you will see clearly what the issue is and why you need to avoid statements.


But, an interesting thing happened. I could not reproduce their findings that a Statement in JDBC
without bind variables versus a PreparedStatement using bind variables. When I ran their code - I
could, using my code - I found that a single statement executed using either of a Statement or
PreparedStatement took the same amount of time initially and if we executed the SQL over and over -
the PreparedStatement was always much faster. This conflicted with their observations totally.

So, I set out to find out why. We'll walk through this process here because it does cover two
interesting things:

o If for some reason your test isn't meeting your hypothesis - either your hypothesis is wrong or
your test is flawed
o The seemingly simple, convincing test can be the most misleading thing in the world


We'll walk through this simple benchmark now, starting with their original test case and working up
to the "real thing". It used a single table TESTXXXPERF which was created using the script
perftest.sql as follows:


scott@ORA920> drop table testxxxperf;
Table dropped.

scott@ORA920> create table testxxxperf
  2  ( id number,
  3    code varchar2(25),
  4    descr varchar2(25),
  5    insert_user varchar2(30),
  6    insert_date date );
Table created.

scott@ORA920> exit


Then, the main java code was supplied. It consisted of three subroutines basically - a main that
connected to the database and then called a routine to insert into that table using statement and
then called a routine to do the same with prepared statements. The code piece by piece is:

import java.sql.*;
import oracle.jdbc.OracleDriver;
import java.util.Date;
public class perftest
{
  public static void main (String arr[]) throws Exception
  {
    Connection con = null;
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection
    ("jdbc:oracle:thin:@aria-dev:1521:ora920", "scott", "tiger");
    con.setAutoCommit(false);
    Integer iters = new Integer(arr[0]);
    doStatement (con, iters.intValue() );
    doPreparedStatement(con, iters.intValue() );
    con.commit();
    con.close();
  }


That is the main routine which simply connects to my Oracle 9iR2 instance as scott/tiger - disables
the autocommit JDBC uses by default and then invokes the subroutine to execute a Statement N times
and then a PreparedStatement N times. I set it up to allow us to pass "N" into the Java routine so
we can run multiple simulations. Next, we'll look at the doStatement routine:


  static void doStatement(Connection con, int count)
  throws Exception
  {
    long start = new Date().getTime();
    Statement st = con.createStatement();

    for (int i = 0; i < count; i++)
    {
      st.executeUpdate
      ("insert into testxxxperf " +
       "(id, code, descr, insert_user, insert_date)" +
       " values (" + i  + ", 'ST - code" + i + "'" +
       ", 'St - descr" + i + "'" + ", user, sysdate ) ");
    }
    long end = new Date().getTime();
    st.close();
    con.commit();
    System.out.println
    ("statement " + count + " times in " +
      (end - start) + " milli seconds");
  }


Very straight forward - it simply creates a statement object and then loops "count" times and
builds a unique - never before seen INSERT statement and executes it. It is somewhat scaled back
from reality in that it is not checking for quotes in strings and fixing them up - but we'll let
that go for now. Also note that it retrieves the time before and after executing the statement and
prints out the results.  Next, we look at the prepared statement:


  static void doPreparedStatement (Connection con, int count)
  throws Exception
  {
    long start = new Date().getTime();
    PreparedStatement ps =
       con.prepareStatement
       ("insert into testxxxperf " +
        "(id, code, descr, insert_user, insert_date)"
        + " values (?,?,?, user, sysdate)");

    for (int i = 0; i < count; i++)
    {
      ps.setInt(1,i);
      ps.setString(2,"PS - code" + i);
      ps.setString(3,"PS - desc" + i);
      ps.executeUpdate();
    }
    long end = new Date().getTime();
    con.commit();
    System.out.println
    ("pstatement " + count + " times in " +
     (end - start) + " milli seconds");
  }
}


Basically the same code but this uses a PreparedStatement to insert "count" rows. It accomplishes
the same exact task as the doStatement routine - just using a PreparedStatement. Lastly, I set up a
shell script to execute this:


!#/bin/csh -f
sqlplus scott/tiger @perftest
java perftest $1


A CMD file for Windows might look like:


sqlplus scott/tiger @perftest
java perftest %1


Now, I ran this with inputs of 1 (do one statement/prepared statement), 10, 100 and 1,000 and the
results were:


Rows to Insert    Statement    PrepareStatement
1            0.05 seconds    0.92 seconds
10            0.34 seconds    1.03 seconds
100            2.69 seconds    2.35 seconds
1000            26.68 seconds   15.74 seconds


So, at first glance - it looks like they might have something here. If you were to ignore the
database (which I'm not inclined to do personally). If I just look at this test - I might conclude
that if I'm not going to execute the same statement over and over - about 100 times - I would best
be served by using a Statement. The problem is there is a FLAW in our test! I discovered this flaw
when I rewrote the code a little to go "multi-user". I knew in a multi-user test, using
System.out.println would not be a very "scalable" testing tool. It would be hard to collect and
analyze the results. So, I did what I always do when benchmarking and setup a database table to
hold the timing results. The slightly modified Java code had an extra subroutine "saveTimes" to
save the timing information into the database. That routine you can add to the test program above
is:


  static PreparedStatement saveTimesPs;
  static void saveTimes( Connection con,
                         String which,
                         long elap ) throws Exception
  {
    if ( saveTimesPs == null )
        saveTimesPs = con.prepareStatement
                      ("insert into timings " +
                       "( which, elap ) values "+
                       "( ?, ? )" );

    saveTimesPs.setString(1,which);
    saveTimesPs.setLong(2,elap);
    saveTimesPs.executeUpdate();
  }

Then, I modified the doStatement and doPreparedStatement routines like this:

  static void doStatement (Connection con,
                           int count) throws Exception
  {
    long start = new Date().getTime();
    Statement st = con.createStatement();
    for (int i = 0; i < count; i++)
    {
      st.executeUpdate
      ("insert into testxxxperf " +
       "(id, code, descr, insert_user, insert_date)" +
       " values (" + i  +
       ", 'ST - code" + i + "'" +
       ", 'St - descr" + i + "'" +
       ", user, sysdate ) ");
    }
    st.close();
    con.commit();
    long end = new Date().getTime();
    //System.out.println( "STMT" + " (" + (end-start) + ")" );
    saveTimes( con, "STMT", end-start );
  }

And I did likewise for the PreparedStatement routine. This would simply save the
times in a database table:

create table timings ( which varchar2(10), elap number );


so we could run a query to get average/min/max timings from multiple users. So, remembering that
the only thing I changed was to comment out the System.out.printlns and add a routine to record the
time - I ran this in single user mode to test. I found:



Rows to Insert    Statement    PrepareStatement
1               0.05 seconds    0.05 seconds
10              0.30 seconds    0.18 seconds
100             2.69 seconds    1.44 seconds
1000           28.25 seconds    15.25 seconds

That's different - very different. Surprisingly different. All of a sudden - there is not only no
penalty ever for using a PreparedStatement - but it quickly benefits us in single user mode even to
use it. What could be the cause.
The code being timed was no different. Not a single byte of code was changed. Sure, we commented
out a System.out.println and added a call to saveTimes - but that code was never timed before. So,
what did change then? Well, it turns out the saveTimes routine was the culprit here. If you look at
that code - it uses a PreparedStatement. It "warmed up" the PreparedStatement class. It paid a one
time penalty to load that class - java dynamically loads classes as you use them. The simple act of
connecting did that for the Statement class (it is used during the connection to Oracle). Once the
timing of the initial load of the PreparedStatement class was factored out - it turns out that a
PreparedStatement is no more expensive to execute than a Statement is in JDBC. The entire premise
of a Statement being "lighter weight", "more efficient" for small numbers of statements was flawed
- wrong. If you used a single PreparedStatement anywhere in your code - you would have paid this
"load" penalty (which is pretty small when you look at it over all) for ALL PreparedStatements.


That was the interesting part of this example - that the basic test itself was flawed, we were
timing an unrelated "thing". Since most non-trivial Java JDBC programs are going to have to use a
PreparedStatement somewhere - they all pay this "load" penalty. Not only that but this "load
penalty" isn't a penalty at all - but simply the price of admission to building a scalable
application on Oracle. If you don't use Prepared statements - if you insist on using Statements and
"gluing the values in" - opening yourself up to the SQL Injection security flaw and buggy code -
your application will not scale as you add users. There is no "maybe" here, there is no "might not
scale", your application will not scale - period.