Solved

Java-JDBC Statement Vs PreparedStatement

Posted on 2004-03-31
21
11,406 Views
Last Modified: 2007-12-19
Hi,
 I'm using Java JDBC Statement to do select/insert/update data.
 All my stmt.executeQuery()/stmt.executeUpdate() are executed
 only once. (not in a loop) I have around 2000 of these Statements
 in my application.Its a web based project and deployed into IBM WebSphere.

 From the readbility point of view I hate my half page SQL string statement
 and also lot of String concatenation or stringbuffer.append().
 I understand that using PreparedStatement will decrease performance
 since all my SQL statements are executed only once. But it gives the
 elegant look and no string/stringbuffer operations. ok

 Can I solve the issue of readability and avoiding String concatenation by
 using PreparedStatement but without loosing the performance.
 Is there anywhere (jdbc or driver or database) I can set something (some flag or variable)
 so that I can use preparedstatement (thus good readbility, no more string operations)
 and still maintaining same performance as Statement ???
 
Thanks in advance.
0
Comment
Question by:rameshaa
  • 6
  • 4
  • 3
  • +5
21 Comments
 
LVL 92

Accepted Solution

by:
objects earned 50 total points
ID: 10728452
>  I understand that using PreparedStatement will decrease performance

actually it improves performance
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 50 total points
ID: 10728457
You assessment is quite correct. There's no easy way, but if i were you i'd maybe use the java.text.MessageFormat class
0
 
LVL 92

Expert Comment

by:objects
ID: 10728461
i would not expect any significant decrease in performance in your case.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10728480
Certainly i'd do some testing. It may not be worth losing the PS
0
 
LVL 10

Assisted Solution

by:rajesh_bala
rajesh_bala earned 50 total points
ID: 10729742
Hi Objects,
  If the statement is executed only once, its better to go for Statement rather than PreparedStatement. When you create PreparedStatement, it actually makes almost 9 round trip calls to the database internally to cache the statement.

Rameshaa,
  When you say almost 2000 sql statements which are executed only once, I guess its some kinda batch operation or something of that sort.
1. When you talk about performance, I don't see a point in PreparedStatement reducing the performance compared to the volume of queries you have got (2000 is pretty huge).
2. You may not find significant improvement in performance, just by using Statement.
3. Run your program a couple of times and identify the top 10% of the queries to be tuned and start tuning them. That should give great boost to your performance rather than Statement Vs PreparedStatement.

So for readability purpose, PreparedStatement would be a better bet than Statement. (At the cost of neglible % of performance). Note that I am comparing the performance you gain from "Statement Vs PreparedStatement" to "Tuning SQL queries"

~Rajesh.B
0
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 50 total points
ID: 10729980
If you really want to hard-code your SQL in your application, then I would recommend using the PreparedStatement over the Statement every time if you are specifying any parameters (i.e. updating, or the where clause of a query).  Better still, if your database supports it, put the commands into stored procedures, so that the database experts can fully tune the queries independent of the application (how many programmers are expert database developers?).

When you write your query such as "SELECT * FROM <table> WHERE <column1> = 1", using a Statement, you will force the database to parse the query every time, which will seriously reduce your ability to scale up.  If however, you use PreparedStatements, the database will see "SELECT * FROM <table> WHERE <column1> = :1", which will be using bind variables whereby your database can use the pre-cached execution plan, and will not have to work out the optimal execution plan every time you run your query.

As an additional note, the readability of your code is very important, you will spend significanly less time developing a system, than it will be in production/supported, and in a couple of months time when you have to go back and change the query, you will have to take out all of your concatenation just to understand what the query was trying to do in the first place!
0
 
LVL 30

Assisted Solution

by:mayankeagle
mayankeagle earned 50 total points
ID: 10730296
A lot has been already said - don't know if you people have mentioned this so far or not. But just a note - all drivers DON'T allow you to update/ insert rows using the Statement class (no exceptions will be thrown either, but execute () will return 0, meaning 0 rows added or updated) - in such cases, you are bound to use the PreparedStatement.
0
 
LVL 1

Expert Comment

by:Mirinda
ID: 10731292
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10731620
Mirinda, there are several flaws with that link.  Firstly, it is dated, the book from which it is taken from was written in 2001, and does not specify which JDK is being used, but there is a hint that Oracle 8.1.6 is used as the database, which is very dated (no offence if anyone is still using it! ;) ).

But the most fundamental problem with their benchmarks, is that they state they are testing certain functionality, so they change a flag (for example stmt.setEscapeProcessing(false)), and then proceed to change other parts of the method.  Here's their downloaded code snippets:

Escape processing enabled:

  Statement stmt = conn.createStatement();
  SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
  conn.setAutoCommit(false);
  startTime = System.currentTimeMillis();
  for (i=1;i<1001;i++) {
   stmt.executeUpdate("insert into testxxxperf ( id, code, descr, insert_user, insert_date ) " +
    "values ( " + Integer.toString( i ) + ", '123456789012345678901234567890', " +
    "'12345678901234567890123456789012345678901234567890123456789012345678901234567890', " +
    "USER, {ts '" + sdf.format(new java.util.Date(System.currentTimeMillis())) + "'} )");
  }
  conn.commit();

Escape processing disabled:

  Statement stmt = conn.createStatement();
  stmt.setEscapeProcessing(false);
  SimpleDateFormat sdf = new SimpleDateFormat( "yyyyMMddHHmmss" );
  conn.setAutoCommit(false);
  startTime = System.currentTimeMillis();
  for (i=1;i<1001;i++) {
   stmt.executeUpdate("insert into testxxxperf ( id, code, descr, insert_user, insert_date ) " +
    "values ( " + Integer.toString( i ) + ", '123456789012345678901234567890', " +
    "'12345678901234567890123456789012345678901234567890123456789012345678901234567890', " +
    "USER, to_date('" + sdf.format(new java.util.Date(System.currentTimeMillis())) + "', 'YYYYMMDDHH24MISS'))");
  }
  conn.commit();


Note how the date format is different.  I've run these same tests and changing the test makes the version the author is trying to recommend appear better than it is!  This is no true benchmark!

One other major fault is that the database schemas change between each script, one test that just calls testxxxperf, and another uses oehr.testxxxperf, quite blatently a different schema!  Who knows how differently that has been set up?  Or how much data already exists in the table?

If you run all of the tests, using the correct set up, and the same schema, and using JDK1.4.2_04 and Oracle 9i (I admit that I cannot test this on older versions), then what the author has stated as fact is completely wrong!  The performance would have undoubtedly improved with each release since then though!

If it wasn't such an old entry I'd kick up a fuss!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:rameshaa
ID: 10733441
Hi ' rajesh_bala':
 Its not a batch process. Depends upon the user operation 1 or more queries are executed.
 So its not batch of 2000 queries executed at the same time.
 Whenever user does some operation I'll get connection from pool, create statement,
 do execute(), close statement, release connection.

 Read this, http://www.ideas2work.com/jdbc1.html
 Time taken to create Statement, PreparedStatement are quite different.

Thanks for the input.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10733485
>>But the most fundamental problem with their benchmarks...

Well done grim! Would be interesting to get some stats on your  particular sql rameshaa
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10733870
Here are my $0.02.

If a query/update statement is to be executed rarely, a regular Statement will be fine. If a query/update statement is to be used more than once, a PreparedStatement is to be preferred over a Statement as a PreparedStatement is compiled and cached on the db server. The actual SQL code is compiled and chached (separate from the instance of your PreparedStatement).

The first execution of a particular PreparedStatement statement is a little bit slower than using a Statement, as it gets compiled and cached, but all subsequent calls are faster than using a Statement. Keep in mind that the caching is done on the server. Even when you create a new PreparedStatement with the same query/update statement it will run faster than with Statement.

If you're really looking to get a performance boost, why not use callable statements? You create all your queries/updates on the server, and just call them with the appropriate parameters. Main advantage to callable statements is that when doing performance tuning (even after release), you won't have to change your Java code.

Take the example from the link you provided and try to eceute two Statements, and then two PreparedStatements (with the same query) and then compare the times.
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10738750
--> Read this, http://www.ideas2work.com/jdbc1.html
Again, several flaws with the "benchmarking".  Each query is only executed once, and taken as the result.  However, things can quite easily change, so it is an absolute must to execute the queries a number of times and then take the average.  For example, other applications running on the machine can take more resources, or the database may have a backup job running - we really cannot know.

When dealing with results of 20ms or 10ms, if you ran the exact same queries again, this may swap around, or change drastically.  Again, take an average of several runs.  Also, how many of your queries are "select * from <table>", most queries that are used in applications require some sort of criteria in the where clause to prevent massive amounts of data being returned to the client, in which case the PreparedStatement offers several advantages.  As stated in my previous post, the PreparedStatement will use bind variables, and the Statement does not.  Provided you are using Oracle, here's the proof:

1) Start off with a clean database cache:
ALTER SYSTEM FLUSH SHARED_POOL;

2) Get all of the parsed SQL, and note that the number should be pretty low on a dedicated server, without any applications running (you may need to be given access to the v$ tables from your dba).
SELECT * FROM v$sqlarea

3) Run the following statement query from your java code:
for (int i = 0; i < 1000; i++) {
    stmt = con.createStatement();
    stmt.executeQuery("SELECT * FROM emp WHERE empno = " + i);
}

4) Now re-query the parsed SQL
SELECT * FROM v$sqlarea
And you will notice that there are several (1000) parsed queries like:
SELECT * FROM emp WHERE empno = 1
SELECT * FROM emp WHERE empno = 2
...

5) Now reset the database:
ALTER SYSTEM FLUSH SHARED_POOL;

6) And run the same query, but using a PreparedStatement object:
for (int i = 0; i < 1000; i++) {
    stmt = con.prepareStatement("SELECT * FROM emp WHERE empno = ?");
    stmt.setInt(1, i);
    stmt.executeQuery();
}

7) Now re-query the parsed SQL
SELECT * FROM v$sqlarea
And you will see this time that there is only a single entry for our run queries
SELECT * FROM emp WHERE empno = :1

What this means is the database has a single piece of SQL already parsed that will be used, instead of filling up the shared pool with SQL that will probably only ever be called once.  This will significantly reduce the resources required on the database side (a very good thing!).


Oh, and as a final note, on the link they state:
--> So it is always advisable to initialize database connection and prepared statement in some initialization code
I would totally disagree.  There is never a "you should always do this" when it comes to these sorts of situations (or any for that matter).  A database connection is a scarce resource, and if you get a connection and initialise a prepared statement, that code must hold onto that Connection until the end (the de-initialisation section), preventing anything else using it, and if you have many users running that same initialisation of a larger body of code, you could get serious out-of resource errors!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10739505
Since this seems to be turning into something of a workshop rameshaa, why not post your code? There are plenty of other ways of optimization too, even if it's something as trivial as not using SELECT *
0
 

Author Comment

by:rameshaa
ID: 10745133
OK,
 The code is nothing but regular JDBC stuff. Mostly my queries are atleast half page. (where clause is big)
 anyway,

                static final String DRIVER_NAME = "COM.ibm.db2.jdbc.app.DB2Driver";
      static final String URL = "jdbc:db2:MyDB";
      static final String USER_NAME = "x";
      static final String USER_PWD = "x";
      
      public static void execStatement()  {
            Connection con = null;Statement st = null;ResultSet rs = null;
            try {
                  Class.forName(DRIVER_NAME);
                  con = DriverManager.getConnection(URL, USER_NAME, USER_PWD);
                  long time1 = System.currentTimeMillis();
                  st = con.createStatement();
                  rs = st.executeQuery("select * from employe_tab where emp_id='1323' " +
                                                                                 "and last_name='karry' and sal > 8000 and comp_id=222 " +
                                                                                  "and dept='A' and cat='mgr' ");
                  long time2 = System.currentTimeMillis() - time1;
                  System.out.println("Time taken by Statement query : "+time2);
      
                  } catch(Exception ex){
                        ex.printStackTrace();
                  } finally {
                        try { rs.close(); } catch (Exception e) { rs = null; }
                        try { st.close(); } catch (Exception e) { st = null; }
                        try { con.close(); } catch (Exception e) { con = null; }
                  }
      }

Similary I have test code using preparedstatement.
I tested several times (30+ times) and 99% statements executed faster than prepared statement.
The difference is 1 ms to 100 ms between stat and preparedstat.
one more time, as I said, I don't have the same statement executing in a loop.
For each user operation the queries were executed mostly once. So I don't reuse the same statement/preparedstatement.
I agree that If I do it loop the preparedstatement is faster.
So for executing the query once, the statement seems to be better in performance.
I still didn't tested the callablestatement.
for above select query what/how do I write the stored procedure ?

Thanks.


0
 

Author Comment

by:rameshaa
ID: 10786580
Hi
 I did the bench mark using Statement, PreparedStatement,CallableStatement.
 If the query is simple select with 2 or 3 where clause the Statement is the best choice !
 If the query consists of more than 10 where clauses, group by, order by the Callable works really good.
 But the preparedstatement mostly doing bad than statement !!! But the same PreparedStatement
 works really awesome If I call inside the loop. (I mean using the same preparedstatement instance)

 I ran the test code with varying where clause to avoid database caching.

 Also I changed the driver from Type2 (app) to Type 4 (jcc) and I got the same result as above.
 The performance factor for type 2 was better than type 4.
 But I heard that type 4 (puer java)is generally better than type 2 since it avoid JNI layer, native client.

 anyway, thats all.

Thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10793917
In that case, i'd refer you to my very first comment - that should make coding a little easier
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 10820925
--> one more time, as I said, I don't have the same statement executing in a loop.
I understand this, the loop only represents multple requests for the same query.  Think of it as 1000 users performing the query at the same time.  Also, it allows for the java optimizer to kick in and optimize the java code as much as possible so that the tests are fairer.

Sorry about the Oracle info, didn't help you at all.  I presumed from the link you provided that you were also using Oracle (each database would implement how it uses statements/prepared statements differently, so the best thing to do - which you have - is to benchmark yourself).

-->In that case, i'd refer you to my very first comment - that should make coding a little easier
Using the MessageFormat class would over complicate things a bit too much!  Plus you would have to handle Dates in a database specific manner (although that is probably already being done with the current method).

-->But I heard that type 4 (puer java)is generally better than type 2 since it avoid JNI layer, native client.
There's no rule that says one is better than the other in all situations, the major drawback to a type 2 driver is that you need the database client installed.  Which is not always possible (or wanted).  If the benchmarks show a significant improvement in using the type 2 driver, then use it.  You can always change it later (should be configurable!).
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10823410
>>Using the MessageFormat class would over complicate things a bit too much!  

Why so? - there's very little more to add and will be quite a bit easier than concatenating Strings or using StringBuffers. Of course, it won't have the Date formatting capabilities of PS, nor will it escape Strings automatically, but that's hardly the point
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
The viewer will learn how to implement Singleton Design Pattern in Java.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now