aspose file tools*
The moose likes JDBC and the fly likes preparedstatement advantage (jdbc experts) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "preparedstatement advantage (jdbc experts)" Watch "preparedstatement advantage (jdbc experts)" New topic
Author

preparedstatement advantage (jdbc experts)

Manoj Bajpai
Greenhorn

Joined: May 07, 2003
Posts: 1
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Jun 26, 2001
Posts: 46
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: preparedstatement advantage (jdbc experts)