• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Statement versus Prepared Statement

 
Janis Lee
Greenhorn
Posts: 27
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have to execute a query that takes some input parameters. I have to execute this query only once. I read that when u have to execute a statement only once use Statement and not PreparedStatement as this is more efficient. But arent preparedstatements to be used when u have input parameters to supply?
What is the best thing for me to do?
Thanks in advance.
Janis
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would be very suprised if PreparedStatements had a significant negative impact on performance. Of course, you could write a test application an find out.

An advantage of PreparedStatements is that they allow you to bind values that are hard to handle in Statements - Dates being the most common problem type. I'd always use a PreparedStatement, unless I was dynamically building the actual query, rather than just dynamically assigning values.
[ May 09, 2006: Message edited by: Paul Sturrock ]
 
stu derby
Ranch Hand
Posts: 333
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:
I would be very suprised if PreparedStamenets had a significant negative impact on performance. Of course, you could write a test application an find out.



It actually depends a bit on the particular driver and database you're using. For Oracle database and driver, there is no significant difference in performance between a Statement and a PreparedStatement that is execute only once, ever. However, if the PreparedStatement is executed again an hour or a day or a week later and it has bind variables ("?"s in the SQL), then the statement might still be in the database's cache and you'll get a performance improvement on the following executions.

Sadly, a widely published book on Oracle (an O'Reilly book) did a side-by-side test of the two and made a very significant mistake in the test, creating the false conclusion that Statement was faster for a small number of repetitions. The mistake was that the Oracle driver loads the Statement class when a connection is obtained and the test was including the time to load the PreparedStatement class with the time to execute it and not including the class loading time against the time to execute a Statement.
[ May 09, 2006: Message edited by: stu derby ]
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64205
83
IntelliJ IDE Java jQuery Mac Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any difference in performance would be marginal and should not be the deciding factor. Use what makes the code clearest and the least error-prone. In that department, the PreparedStatement wins hands-down for any SQL statement that takes parameterized values.
 
stu derby
Ranch Hand
Posts: 333
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Bear Bibeault:
Any difference in performance would be marginal and should not be the deciding factor.


Not true, at least on some databases. On Oracle, for highly repeated, highly concurrent statements, PreparedStatement can be very significantly faster.

Tom Kyte wrote a test that showed 1,000 executions of a particular bit of SQL took half the time using PreparedStatement, single threaded. Because of the way Oracle works, the difference with a mutlithreaded test would be even more dramatic.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10128287191505
(maybe 3 or 4 sections down...).
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

On Oracle, for highly repeated, highly concurrent statements, PreparedStatement can be very significantly faster.

That's what I'd expect on most databases, however Janis Lee is asking about a bit of SQL that is executed once.
 
Paul Clapham
Sheriff
Pie
Posts: 20208
26
MySQL Database
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Sturrock:
That's what I'd expect on most databases, however Janis Lee is asking about a bit of SQL that is executed once.
And it's rarely worth bothering to optimize something that only happens once in a long-running program.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic