Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Performance using Statement and Prepared Statement

 
Chandra Bairi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hai all,
I have a doubt. I am executing a query only once. Which one is more beneficial
using a Statement or preparedStatement.
I guess statement will be beneficial than preparedstatement because the query is executed only once and for the compiled query need not be stored in the database.
Is this true?
Thanks
shekar.
 
Jason Steele
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it's being executed only once, I don't suppose there's any harm in using statement. However, there is still a benefit to using PreparedStatement. That is that you do not have to mess with escaping characters, coverting dates, and such. PreparedStatement does all that for you.
 
Chandra Bairi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply but I just wanted to know if there would be any performance variance in using preparedstatement for statement when the query executed is only once.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Chandra Bairi:
Thanks for the reply but I just wanted to know if there would be any performance variance in using preparedstatement for statement when the query executed is only once.

using a straight statement will execute faster. But, using a straight statement can be dangerous, if not used properly:
- statements are vulnerable to SQL injection ( depending on how they are used )
- can blow up in your face if you don't do proper edit checking and escaping. Once you manually do all the edit checks and escaping, the performance becomes more comparable to a PreparedStatement for a once off query.
Jamie
 
Chandra Bairi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so there would be performance increase when the statement is used when the query is executed only once. and can you please explain the problems that crop up when statement is not used properly. What exaclty is not using the statement properly and injection mean

Thanks
Shekar
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. try inserting "O'Connor" into a database using a straight SQL insert/update from a statement.
2. have a look at Hack-Proofing Oracle9i Application Server (Continued) - Application-Level Attacks for information on different hacking strategies that all databases and platforms are susceptible to. ( here is part 1 if your interested )
[ January 22, 2004: Message edited by: Jamie Robertson ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also keep in mind that the exact same statement doesn't need to be run more than once to gain the performance benefits of a prepared statement. It just needs to be a statement of the same form, such as:
select * from table where field = ?
Also, if you really never repeat statements of the same form, you probably aren't running enough queries to have a performance problem. I would start with the prepared statement.
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've rarely seen anyone back up the claims that preparedStatement is faster.
Once I saw an article that stated that the developer stated that his break even point for using PreparedStatement over Statement was 76 executions in Oracle. Unless you have those kinds of numbers then you are just blowing smoke. I'm sure this is performance is db dependent too. I just wish instead of repeating the same tired conclusion that PreparedStatement performs better people would back it up with some numbers. I also suspect in web environments most PreparedStatements are thrown away after one execution, so never having any performance advantages.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not completely correct. PreparedStatements are supported by the database - not the driver or the java code - if the database has this feature. Therefore if the database supports PreparedStatements, it matches on the string and not the Java PreparedStatement object. You can throw the PreparedStatement away and any other PreparedStatement with the same String value will be matched with previous executions on the database, and the database won't try to compile the database statement again. Once again we're talking about compilation in the dtaabase sense and not the java sense.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by steve souza:
I've rarely seen anyone back up the claims that preparedStatement is faster.
Once I saw an article that stated that the developer stated that his break even point for using PreparedStatement over Statement was 76 executions in Oracle. Unless you have those kinds of numbers then you are just blowing smoke. I'm sure this is performance is db dependent too. I just wish instead of repeating the same tired conclusion that PreparedStatement performs better people would back it up with some numbers. I also suspect in web environments most PreparedStatements are thrown away after one execution, so never having any performance advantages.

Steve, I have posted this link many times, you must have missed it:
Java Programming with Oracle JDBC - Chapter 19 - Performance. It specifically uses Oracle and compares many different aspects between the Oracle thin/thick drivers as well as Statement/PreparedStatement/CallableStatement.
** NOTE ** - the performance test doesn't take into account any safety checking/escaping within the Statement's SQL which you would have to do in real life. This is already done in the PreparedStatement.
Jamie
[ January 23, 2004: Message edited by: Jamie Robertson ]
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Always use PreparedStatement. If you are only executing it once, why would you care about the few milliseconds of overhead?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic