Re: Prepare Statements VS Statements
Vince wrote:
My question now: How would you solve it? Would the PrepareStatement
still improve the performance, considering though the variable amount
of columns?
Arne VajhQj wrote:
1) PreparedStatement usually but not always performs better than
Statement [when] executed multiple times.
2) My experience is that the gain for Oracle of using prepared
statements can be really huge.
3) I will assume that you can reuse some prepared statements. 25000
distinct INSERT statements does not sound likely.
All statements get "prepared" by the database engine, in the sense that the
SQL string gets sort-of-compiled by the engine just to run it. The
performance advantage of PreparedStatement comes perhaps from the ability of
the JDBC driver to understand that a statement will be reused, and certainly
from any ability the DBMS engine has to reuse its sort-of-compiled version of
the statement.
Some engines, e.g., Postgres, only take full advantage of a prepared (in its
sense) statement within a single "session", which I believe corresponds to the
lifetime of the Java connection to the database. If understand the
implications correctly, closing a connection would flush Postgres's cache of
prepared statements from that connection. This is likely not a problem if the
JDBC connections are pooled, as they should be in production. Pooled
connections rarely close from the DBMS's point of view. The point of this
whole paragraph is twofold: that getting the full performance benefit of
PreparedStatement depends on the tuning of a lot of operational factors, and
that operations is an art unto itself.
4) Besides the performance issue PreparedStatement is also much
better to handle date formats and strings with quotes in.
Often it seems that performance is pursued before correctness is assured.
Arne makes the point here that PreparedStatement has advantages irrespective
of its performance comparison to other kinds of Statement.
5) Multiple prepare statement calls can be batched just like
multiple ordinary statement calls can.
So based on the little info available I will recommend you to
look into prepared statements.
In addition to Arne's excellent advice, and expanding on point 4 above,
PreparedStatements provide a degree of type safety to run-time data in SQL
actions. This is particularly important for data provided by the public -
well-known "SQL injection" attacks will not work on PreparedStatement forms
because the text is better encapsulated than in the more casual forms.
Even in more internal usage, it's a lot harder to insert, say, a misconfigured
date to a query when the argument must be of type java.sql.Date to start.
PreparedStatement lets you enforce certain guarantees on dynamic data.
--
Lew