• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement

 
Chinmay Bajikar
Ranch Hand
Posts: 159
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am trying to fire an insert query on a single table,but the number of columns is large.typically in the order of 20-25.
and I m right now using PreparedStatement.executeUpdate().Will using PreparedStatement.execute() make any difference in terms of speed?
Thanks,
Chinmay
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The answer probably varies with the database, or at least the database driver. PreparedStatement is an interface.
In the specific case of Oracle, yes it tends to make a difference. However, you don't get the difference for free. There is overhead in preparing the statement. In general, you use a prepared statement when either:
1) you will use it multiple times.
2) you need functionality not available in a Statement.

I did some timing tests a few years ago, and back then it turned out that the break-even point for Oracle prepared statements (I think it was against a 7.3 database) was around 4 repeats. It could be very different now.
Easy way to find out: put some time checks in your code, and try it both ways.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yup, I can see that making sense in Oracle, since the cache is based on the literal text of the statement. With the prepared statement Oracle can conclude "yes, I've seen that before" when it wouldn't otherwise. Good point.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic