aspose file tools*
The moose likes JDBC and the fly likes Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statements" Watch "Prepared Statements" New topic
Author

Prepared Statements

David Attard
Greenhorn

Joined: May 13, 2003
Posts: 26
How much performance increase would the usage of Prepared Statements provide? E.g. for a 200+ web-app one should consider the use of Prepared Statements or wouldn't they provide a significant performace increase?


There's no place like 127.0.0.1
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

PreparedStatement is faster than Statement excpet for the first time it is invoked.
Check this link
http://www.onjava.com/lpt/a/1480


Groovy
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by David Attard:
How much performance increase would the usage of Prepared Statements provide? E.g. for a 200+ web-app one should consider the use of Prepared Statements or wouldn't they provide a significant performace increase?

depends. Every JDBC implementation is different is so is the level of optimization ( and effort put into the driver ) by the driver vendor. One comparison I've seen using Oracle drivers is Java Programming with Oracle JDBC - Chapter 19: Performance.
Jamie
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Hi
Can somebody explain which is better for dynamically created SQL? PreparedStatement or Statement?

Thanks
-------------------
Sainudheen
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by Sainudheen Mydeen:
Hi
Can somebody explain which is better for dynamically created SQL? PreparedStatement or Statement?

Thanks
-------------------
Sainudheen

You mean to say the insert/update values are dynamic ?
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
I've recently encountered a situation where prepared statements appeared to be significantly slower than non-prepared statements. This was on Microsoft SQL Server. One hypothesis is that the SQL Server optimizer can actually take the distribution of data into account when deciding which indexes to use; if this is the case, then it might happen that the execution plan for the prepared statement (where the parameter values are totally unknown) is worse than that used for a normal statement (where all the values in the where clause are known when the plan is compiled). Another hypothesis is that this is simply a driver bug. Please note that I have not investigated this phenomenon in great detail, nor the internals of the SQL Server optimizer, so take all of this with a good pinch of salt. The main point is that under exceptional circumstances prepared statements might actually perform worse.
- Peter
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Do you mean to say we need to do a time measurement for each db to find out WHETHER PS is better than Statement?
Originally posted by Peter den Haan:
I've recently encountered a situation where prepared statements appeared to be significantly slower than non-prepared statements. This was on Microsoft SQL Server. One hypothesis is that the SQL Server optimizer can actually take the distribution of data into account when deciding which indexes to use; if this is the case, then it might happen that the execution plan for the prepared statement (where the parameter values are totally unknown) is worse than that used for a normal statement (where all the values in the where clause are known when the plan is compiled). Another hypothesis is that this is simply a driver bug. Please note that I have not investigated this phenomenon in great detail, nor the internals of the SQL Server optimizer, so take all of this with a good pinch of salt. The main point is that under exceptional circumstances prepared statements might actually perform worse.
- Peter
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Originally posted by Pradeep Bhat:

You mean to say the insert/update values are dynamic ?

Yes Pradeep.
---------------
Sainudheen
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by Sainudheen Mydeen:

Yes Pradeep.
---------------
Sainudheen

I would go for PreparedStatement if I am using in a loop for example say inserting a list of products into the database. Use PS if you using the statement more than once (if it has paramterized values).
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Pradeep Bhat:
Do you mean to say we need to do a time measurement for each db to find out WHETHER PS is better than Statement?
No, certainly not. You should always use PreparedStatement by default IMHO. What I mean to say is that if during testing you find you are getting some odd performance results, you should (after eliminating some of the more likely causes) consider the possibility that the PreparedStatements might not work quite the way they ought to.
- Peter
[ October 07, 2003: Message edited by: Peter den Haan ]
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Peter,
From the link Jamie has posted I find that PrepareStatement performs better only when it used the number of inserts is very large (1,000), if the number is less say some 50 then Statement outperforms the Prepared one.
What do you think we should be doing? :roll:
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Pradeep Bhat:
What do you think we should be doing? :roll:
Use PreparedStatement regardless. Especially when you're operating on strings or dates. You don't want to escape special characters in your strings by hand. You probably also don't want to face date format issues; I've had my fingers burnt a couple of times where different database installations used different date formats (although you can use SQL escapes to avoid this -- but almost no-one seems to know about them). With PreparedStatements, all of that is handled for you.
So what about the performance difference then? Well, if you allow me to simplify somewhat, there are two possible cases.
  • Your SQL is executed only a few times. In that case, who cares whether it takes 10ms or 100ms?
  • Your SQL is executed yonks of times. In that case, PreparedStatement is going to be significantly faster.
  • The bottom line is then that using PreparedStatement may not help, but it won't hurt either, whereas using Statement can certainly hurt you performance-wise. Of course this is a simplification and there are definitely exceptions, but most of the time it's true enough.
    Finally, be aware that you may be executing your (prepared) statement far more often than you think. Oracle maintains a server-side statement cache. A prepared statement can effectively be reused even if it is a different PreparedStatement object, using a different connection. So in, say, a web application scenario PreparedStatements are virtually always worth it.
    - Peter
    [ October 07, 2003: Message edited by: Peter den Haan ]
    David Attard
    Greenhorn

    Joined: May 13, 2003
    Posts: 26
    Thanks, interesting thread.
    D
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Prepared Statements