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?
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
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
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
posted
0
Originally posted by Pradeep Bhat:
You mean to say the insert/update values are dynamic ?
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
posted
0
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 ]
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
posted
0
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 ]
0
