wood burning stoves 2.0*
The moose likes JDBC and the fly likes PreparedStatement Vs Statement 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 "PreparedStatement Vs Statement" Watch "PreparedStatement Vs Statement" New topic
Author

PreparedStatement Vs Statement

Karthik Veeramani
Ranch Hand

Joined: Dec 22, 2002
Posts: 132
I've read that PreparedStatements are faster than Statements. But when I wrote a small piece of code to compare the speeds, it turned out that the latter is faster... Is there any special condition, only under which PreparedStatements are faster?


Thanks<br />Karthik<br />SCJP 1.4, CCNA.<br /> <br />"Success is relative. More the success, more the relatives."
Renju Philip
Greenhorn

Joined: Oct 28, 2002
Posts: 4
Prepared Statements are used only if you need to run a the same sql query in a loop ( with the value of parameters changing).
If your requirement doesnt mach this functionality its better you go for ordinary Statemets.
Thanks
renju
sumeetg
Greenhorn

Joined: Mar 03, 2003
Posts: 3
Basically, a Statement is compiled only when its execute is called. A PreparedStatement, on the other hand, is pre-compiled during its initialisation, with unfilled parameter values. As such, multiple updations to the SQL Area of the database (having the same query) using Prepared statement takes less time as compared to multiple updations using Statement.
So, in your test piece of code, if you iterate over the insertions/updations for, say, 500 times, you'll clearly see the expected difference.
regards
nagappan rm
Greenhorn

Joined: Jun 10, 2002
Posts: 2
Test Results givin in "Java Programming with Oracle JDBC" Book
Thin driver timings (in milliseconds)
Inserts Statement PreparedStatement
1 10 113
1,000 2,583 1,739
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
There is another reason other than iterations that make PreparedStatements more performant than standard Statements.
If you reuse identical queries but the only part of the query that changes is the id of the record(s) that you are looking for then if you use a PreparedStatement the database is much more likely to cache and re-use the SQL execution plan. This can have a considerable benefit to the performance of the application.
I also personally find that PrepareStatements are much easier to read than SQL statements that contain a lot of string concatenation, addition of quotes, date formatting etc.
As an added bonus PreparedStatements also handle potentially awkward situations where a String may contain characters such as single or double quotes.


Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Vamshi Krishna
Greenhorn

Joined: Apr 22, 2003
Posts: 4
Prepared Statements being in the pre complied format, they are always executed fater then the general statements.
However if you are comparing it for the first execution, then the vauue may differ becuase prepared statements are bit slow when complied for the first time.
Also please make sure that you use the same statement changing the parameters and also the number of parameters you use in the statement and prepared statement be the same.
If you have prepared statement with 5 parameters and statement object with 2 parameters, i think the excution time will be faster for statement object.
If you realy find any reason opposing my statement, please send it to me at vamshikondala@yahoo.com. I would appreciate wyou help in this regard.


Krishi.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Hi Karthik,
In case you haven't already read it, I recommend the following excerpt from Donald Bales' book, Java Programming with Oracle JDBC
http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html
Also, you need to be careful when measuring execution performance of your code. This Java Q&A issue, details some of the things to watch out for:
http://www.javaworld.com/javaworld/javaqa/2003-01/01-qa-0110-timing.html
Hope this helps you.
Good Luck,
Avi.
 
 
subject: PreparedStatement Vs Statement
 
Similar Threads
Which yields the best performance?
JDBC statements
Why do these queries break this loop????? Im lost!
Filtering Data to prevent SQL Injection
How this code can be improved?