• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement Vs Statement

 
Karthik Veeramani
Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Renju Philip
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vamshi Krishna
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic