Tuesday, March 04, 2003

Is using JDBC's Prepared Class really faster than the Statement Class?

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.

A rehash of the text table.

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.

Post a Comment