Originally posted by Reid M. Pinchback:
In general, you use a prepared statement when either:
1) you will use it multiple times.
There's a lovely little subtlety here. At least with Oracle, you reap most of the benefits of PreparedStatement even if you don't reuse the PreparedStatement object itself.
Oracle 8 (and later) maintains a
statement cache with the last N statements executed (not sure about 7). If you fire a statement at the database that is in the cache, you save yourself the effort of compiling and optimizing it into an execution plan.
The efficiency difference between a Statement and a PreparedStatement becomes obvious in when the statement has parameters. Say you update a raft of rows in a table, one at a time. You can do this with a statement
UPDATE EMP SET SALARY = SALARY + 2430 WHERE EMPNO = 1
UPDATE EMP SET SALARY = SALARY + 8790 WHERE EMPNO = 2
UPDATE EMP SET SALARY = SALARY + 4340 WHERE EMPNO = 3
UPDATE EMP SET SALARY = SALARY + 4820 WHERE EMPNO = 4
...
Because the statement text is different for each row, each statement in the Statement approach will have to be compiled and optimized separately. Not only will this be slower, it will thrash the statement cache and slow down other concurrently running statements or queries as well.
Or you can use a PreparedStatement
UPDATE EMP SET SALARY = SALARY + ? WHERE EMPNO = ?
And use that for every row. You may create a new PreparedStatement object for every row; it will be a little bit less efficient, but the most important thing is that the statement text stays the same. Every time it is executed, the database will be able to pick up the compiled statement from the cache and execute it.
- Peter