This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Oracle JDBC:  Statements is better ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle JDBC:  Statements is better ?" Watch "Oracle JDBC:  Statements is better ?" New topic
Author

Oracle JDBC: Statements is better ?

Edward Chen
Ranch Hand

Joined: Dec 23, 2003
Posts: 798

Use the Statement object for time-critical or dynamic SQL statements

When it comes to executing a SQL statement, you have two choices: you can use a PreparedStatement object or a Statement object. A PreparedStatement parses and compiles a SQL statement once, no matter how many times you reuse it. When you use a Statement, each time a SQL statement is executed, it is again parsed and compiled. This might lead you to think that using a PreparedStatement would be faster than using a Statement; however, my tests show this is not the case on the client side. So, for time-critical SQL operations, unless you are going to batch SQL statements, you should consider using Statements.

http://www.onjava.com/pub/a/onjava/2001/12/19/oraclejdbc.html

Anybody has your voices for/against the points above ? is this statement testable?

Thanks.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

Oh its testible, just create a preparedstatement that does not insert any values and compare it with the same excution as a statement.

Since a preparedstatement is a more complex data structure than a statement I am not surprised that it takes longer to run, but most of the time for code maintainability I would pick a prepared statement over a statement even if I was not sending it any parameters.


My Blog: Down Home Country Coding with Scott Selikoff
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

And to guard against SQL injection attacks, I would use a PreparedStatement instead of a Statement.

(Also note that the article in question was published in 2001. In my opinion, 5-year-old benchmarks are of little value.)
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
In Oracle if you re-use the SQL in a statement with literal values substituted then each is regarded as a unique piece of SQL by the query optimiser, which will hard parse it to generate the query plan.

With a PreparedStatement with Bind variables, after the first use and hard parse, subsequent executions will re-use the already generated query plan.

Hard coded literals and hard parsing will not only use more resources in the database, but will also serialize access to shared memory structures used by the database. The application will not scale.

Any performance gain on the client side from using a Statement object will be lost on the DB side if it is re-used.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

Actually here's the real question, could one take a preparedstatement and write an implementation that uses a statement and therefore is always as fast? The answer is probably yes. Sometimes the java implementation might be such that more advanced structures are slower but the hope is that by using the advanced data structure, the implementation will eventually improve such that the users could take advantage of the data structure without any performance tradeoffs.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Oracle JDBC: Statements is better ?
 
Similar Threads
to create login.jsp
how to implement a global cache in Tomcat?
EJBQL
How to specify EJB-QL
benefit of Statement over PreparedStatement?