Originally posted by Todd Jain:
Does PreparedStatement always give better performance than Statement?
Originally posted by Glen Cai:
When you have a table that contains a millenium rows of data and you have a Index set up for the table, your PreparedStatement can not take the advantage of the Index for queries.
SQL Injection should be handled in Business tier before reaching JDBC calls...
Statement Versus PreparedStatement
There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.
When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.
PreparedStatement's "execution plan" is stored/cached
avoidance of escaping issues