preparedstatement advantage (jdbc experts)

[Manoj Bajpai]

we all know that preparedstatements improve performance due to precompiled sql statement.
eg
con = getConnection();
ps = con.prepareStatement("INSERT INTO...
...) VALUES(?,?,?,?,?)");
however, if a new instance of con is used each time, is there really an advantage. which means to
truly get the benefit i should make sure i am using the same connection, correct ???
thanks,

[Peter den Haan]

Not necessarily correct. For example, Oracle maintains a statement cache (quite independent from the JDBC driver). This cache will work properly only when your SQL statement text is completely identical to some statement submitted previously, i.e. in most cases only when you use PreparedStatement.
- Peter

[Jacob George]

Originally posted by Manoj Bajpai:
we all know that preparedstatements improve performance due to precompiled sql statement.
eg
con = getConnection();
ps = con.prepareStatement("INSERT INTO...
...) VALUES(?,?,?,?,?)");
however, if a new instance of con is used each time, is there really an advantage. which means to
truly get the benefit i should make sure i am using the same connection, correct ???
thanks,

Hi,
I would like to stress that, most of the type 4 drivers presently available in market does not make use of pre-compiled temporary procedures.(Most of my experiance was on MS SQL Server drivers)
The main reason is that creation of temporary stored procedures (thats how precompilation is done) doesn't cause much performance enhancements in real-world scenarios and applications. Only the initial query in PreparedStatement (select, insert, update) is pre-compiled; mostly the bulk will be the information passes to the PreparedStatement using its setXXX() methods, which the driver has to send each time to the database server. So as far as the n/w latency goes, there is little performance gain compared to usage of normal Statement.
The only advantage might be on the time taken by server to execute the query. But unlike stored procedures, PreparedStatements usually consists of only a single line query like Select, Update or Insert; So, here also we are not seeing much boost in application performance (since PreparedStatement queries are usually simple ones).
Also, the JDBC spec doesn't enforce that pre-compilation be used for PreparedStatement. From my experiance its noted that there isn't any performance difference between using PreparedStatement with/without precompilation(driver dependent). (Note: Batch operations has some nice performance advantages)
So, as an answer to u'r question, I think you won't be feeling any performance hike/de-gradation even if you are using same/different connection(may be that your driver is not even using pre-compilation).
cheers Jacob

[Peter den Haan]

The main performance improvement when using prepared statements comes, as you note, from the fact that the compilation of the statement into an execution plan can be skipped. You would expect to see the most significant effect when statement compilation is expensive relative to statement execution, i.e. when you have a statement which has a complicated structure or at least makes the optimizer work hard, but can be executed efficiently and doesn't munge lots of data.
Under these circumstances we have observed a 20-30% improvement in performance using PreparedStatements (Oracle 8, type 4 driver). With trivial statements, or statements which take some time to execute, the improvement is usually negligible and lost in the noise.
- Peter
[ May 09, 2003: Message edited by: Peter den Haan ]

[SJ Adnams]

google, as always will provide the answer.
http://home.clara.net/dwotton/dba/java_insert.htm
http://www.hpl.hp.com/semweb/doc/RDB/rdb-performance.html
The difference is significant.

[Jamie Robertson]

let us not forget the other benefits of PrepareStatement.
try inserting "O'Brian" using a statement. I think not. So even if there is no performance gain( which I think there is ), it has some value added. I also think code using PreparedStatements is more readable than Statements & Strings for medium to complicated SQL queries.
Jamie