I have seen many topics saying the benefits of PreparedStatement over Statement. But for doing a simple query e.g. "select * from TableName", I think no one will suggest use PreparedStatement. Could you please tell me if there is some advantage of using Statement rather than using PreparedStatement?
Sanjeev, I disagree. Assuming the simple query is run a few times over the life of the application, there is a benefit to creating the execution plan once. Granted the plan is a simple one, but this is one less thing the database needs to do each time you run the query. A Statement can be better than a PreparedStatements under very specific conditions. You would have to be running queries that do not repeat in form. You would also have to be willing to accept the lack of all the benefits that are usually listed for prepared statements.
There are times when a PreparedStatement isn't what you want. If up aren't running a CRUD operation, (create, read, update or delete) and are doing something like dynamically creating tables or table grants etc, a PreparedStatement will ruin your sql by putting single quotes around everything. Another time a Statement will be better than a PreparedStatement is when you know exactly the query you want to run, it doesn't have any variables, and you only want to run it once. Such as: "Select name from tblPeople where system_id=1" to auto-load the admin user. In this case, you don't gain anything from using a PreparedStatement, but you don't lose anything either. If however you had "Select name from tblPeople where system_id=?" and you only ran it once with the value '1', the first would be faster, but you wouldn't be able to tell the difference by watching. In this case I'd probably still stick to a PreparedStatement. Dave
Processing a user's ad hoc query would be easier to do using in Statements as you can't be sure how many or what the arguments of the query will be. Also people often talk about the performance benefits of using PreparedStatement. I suspect this is db specific and rarely have I seen people quantify this performance benefit. I once read that in Oracle (this was a couple years back) it took about 75 executions before the overhead of PreparedStatement made using it worthwhile over a Statement (from a performance standpoint). Also often in J2EE environments when using DataSources the Statements and PreparedStatement are thrown away after one execution. I'm not sure if you would ever get a performance improvement over Statements in this case. It would be a great test for someone to quantify the performance gains of using PreparedStatements. JAMon is perfect for this type of test.
Originally posted by steve souza: ... I once read that in Oracle (this was a couple years back) it took about 75 executions before the overhead of PreparedStatement made using it worthwhile over a Statement (from a performance standpoint). ...
I read that too, in the following: http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html It also says that using the Oracle thin driver, it takes more like 125 executions to make a PreparedStatement worthwhile. I read another article "Top 10 Oracle JDBC Tips" http://www.onjava.com/pub/a/onjava/2001/12/19/oraclejdbc.html?page=1 For one thing, tip #3 and tip #5 seem contradictory to one another. #3 seems to promote the use of Statement and #5 seems to promote the use of PreparedStatement. Much of what both of these articles seem to say, go against what I've always been taught: If you're going to execute a query more than once where the only difference is the values on the right-hand-side of the = in you where clause, then use a PreparedStatement. These articles seem to suggest that more often than not, you should use a Statement instead. I think part of my confusion comes from being unclear on the definition of "dynamic" as the author uses it in the latter article. I think when he says "dynamic SQL statments" he means the overall query changes every time, not just the values that put in. If your query changes each time then it would have to relook up the metadata twice everytime when executing a select or whatever. I personally would consider different values put in the where claus every time to be "dynamic"... but I think the author has a different idea of what dynamic means. Does anyone have any thoughts on either of these articles? Or any additional discussion on PreparedStatement vs. Statement in general? Thanks
After reviewing both of the articles I have come to my conclusion. I believe the author in the second link is referring to dynamic sql as appending the sql statement before executing it. Each subsequent time the sql statement is being executed it is a different sql string all together. Not only are the values changing but maybe the columns he is selecting from changes as well. For instance, maybe I'll use a Stringbuffer to generate an sql statement depending on the parameters that are passed in to a method. Each time this method is called the sql string is different all together. I believe this is what the author is referring to as dynamic sql. If this is the case, then a Statement would be much more efficient than a PreparedStatement. From what I have gathered out of the first article mentioned by gayle is that "Every time you execute a SELECT statement, the JDBC driver makes two round trips to the database. On the first round trip, it retrieves the metadata for the columns you are selecting. On the second round trip, it retrieves the actual data you selected." This rule applies to a Statement and a PreparedStatement. If you are executing a dynamic sql statement then there is overhead with the PreparedStatement since the PreparedStatement, on the first call to the database, stores it's metadata in memory. I can't imagine this being too much overhead since we are writing to memory and not disk, but it is still extra work being performed. In my opinion, a Statement is always going to be more efficient than a PreparedStatement if executing dynamic sql. If an sql statement is not dynamic, meaning the columns and where clause stay the same during every iteration, than a PreparedStatement is more efficient than a Statement since after the first iteration the PreparedStatement stores the metadata. Each subsequent query only takes one round trip to get the actual data. When the second query is executed the PreparedStatement will always be 1 ahead of the Statement since the PreparedStatement stored it's metadata on the first iteration. So to answer the question, it just depends on how you plan to execute your queries. If it's dynamic, use a Statement object, if only the values are changing and not the columns (metadata) use the PreparedStatement Object. Keep in mind that the articles referenced above only pertain to Oracle. Every JDBC driver adheres to the same implementation but access times may vary on different databases. This is just my $.02 worth cheers Ryan
SCJP 1.4, SCWCD
Java: The power, elegance, and simplicity of a hand grenade
Guess I should read up on the physical implementation ... just what gets saved where with a prepared statement. The extra trip to get meta-data before executing a query was interesting. Back in mainframe days we talked a lot about the effort the database has to go through to develop and optimize the plan for a query. If you have a lot of tricky joins or other operations this adds up, and the prepared statement saves this plan info somewhere so it can reuse it. In the statement object? Also back in the mainframe days we were allowed prepared (bound)queries only, no dynamic (build SQL on the fly) queries. For an ad-hoc query screen with many valid key combinations, I made a bitmap of the fields entered, and used the bitmap to get the right statement.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi