File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Statement versus Prepared Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Statement versus Prepared Statement" Watch "Statement versus Prepared Statement" New topic
Author

Statement versus Prepared Statement

Janis Lee
Greenhorn

Joined: Dec 22, 2005
Posts: 27
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

Joined: Apr 14, 2004
Posts: 10336

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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

Joined: Jan 10, 2002
Posts: 61095
    
  66

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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

Joined: Apr 14, 2004
Posts: 10336


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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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.
 
Consider Paul's rocket mass heater.
 
subject: Statement versus Prepared Statement