Difference between Statement and preparedStatement (for SQL databases) ?

K

Ken Philips

Sometimes I can see pure Statement declaration for accessing SQL databases
others use preparedStatements.

What is the difference?

Ken
 
M

Michael Fortin

Sometimes I can see pure Statement declaration for accessing SQL databases
others use preparedStatements.

What is the difference?

Ken

exerpt from http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

"Statement Versus PreparedStatement

There's a popular belief that using a PreparedStatement object is
faster than using a Statement object. After all, a prepared statement
has to verify its metadata against the database only once, while a
statement has to do it every time. So how could it be any other way?
Well, the truth of the matter is that it takes about 65 iterations of
a prepared statement before its total time for execution catches up
with a statement. This has performance implications for your
application, and exploring these issues is what this section is all
about.

When it comes to which SQL statement object performs better under
typical use, a Statement or a PreparedStatement, the truth is that the
Statement object yields the best performance. When you consider how
SQL statements are typically used in an application--1 or 2 here,
maybe 10-20 (rarely more) per transaction--you realize that a
Statement object will perform them in less time than a
PreparedStatement object."
 
L

Luke Webber

Michael said:
exerpt from http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

"Statement Versus PreparedStatement

There's a popular belief that using a PreparedStatement object is
faster than using a Statement object. After all, a prepared statement
has to verify its metadata against the database only once, while a
statement has to do it every time. So how could it be any other way?
Well, the truth of the matter is that it takes about 65 iterations of
a prepared statement before its total time for execution catches up
with a statement. This has performance implications for your
application, and exploring these issues is what this section is all
about.

When it comes to which SQL statement object performs better under
typical use, a Statement or a PreparedStatement, the truth is that the
Statement object yields the best performance. When you consider how
SQL statements are typically used in an application--1 or 2 here,
maybe 10-20 (rarely more) per transaction--you realize that a
Statement object will perform them in less time than a
PreparedStatement object."

While I find O'Reilly a very good publisher, I take some issue with the
above. The performance of a PreparedStatement is very definitely
implementation-dependent, and not to be dismissed so lightly.

Luke
 
T

Tor Iver Wilhelmsen

Luke Webber said:
While I find O'Reilly a very good publisher, I take some issue with
the above. The performance of a PreparedStatement is very definitely
implementation-dependent, and not to be dismissed so lightly.

And it ignores the other advantages of PreparedStatement, such as
escaping quotes in setString() plus other data conversions, and the
security implications in just throwing together a Statement with
String concatenation, which can let a user bypass constraints by
"commenting them out" - or even execute malicious SQL.
 
Joined
May 11, 2008
Messages
7
Reaction score
0
The prepared statement concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.

The advantages of Prepared Statements are:

1. As the execution plan get cached, performance will be better.
2. It is a good way to code against SQL Injection as escapes the input values.
3. When it comes to a Statement with no unbound variables, the database is free to optimize to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

Other than training purpose it is better to use PreparedStatement to get full benefits and close all loopholes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,832
Latest member
GlennSmall

Latest Threads

Top