NOTE: I used the OracleConnectionPoolDataSource class to connect to Oracle9i for these tests.
Most of my reading about JDBC indicated that we should use the PreparedStatement class to take advantage of bind variables and pre-compilation of SQL. However, one article said that using the Statement class was better because of how vendors implement the JDBC drivers. So, of couse, I felt compelled to perform my own timing tests.
I executed the following simple select statement 1,000 times with both classes. Of course, when using the PreparedStatement, the string literal was replaced by a bind variable.
select uuid, display_name, description from dr_locales where uuid = 'EN-US'
The example using the statement class took 3,564 milliseconds to run. While the PreparedStatement example took 2,594 seconds to run.
Of course, I dug a little deeper. How much overhead is involved in instantiating the two classes? In order to answer, I placed the object instantation inside the loop:
InsideLoop OutsideLoop Statement: 3,915 3,564 PreparedStatement: 4,486 2,594 <-- best elapsed time.
The following figure shows the relationships graphically.
This test shows that it really pays to reuse PreparedStatement objects.
The last question I tried to answer in my tests was; Which class is better if no bind variables are used?
Statement: 4,636 PreparedStatement: 3,445
So, my empiric results agree with my common sense and the majority of the literature that I've read.
It seems that the PreparedStatement class should be used in all cases.