Re: Performance of batches with Statements and PreparedStatements

Jeff,

Jeff Kolesky wrote:
> I have some code that is inserting many rows into the database,
> executing Statements repeatedly. I can run the inserts as batches using
> the JDBC batch functionality, or I can concatenate the inserts with
> semi-colons and execute one large statement.
>
> If I take the semi-colon approach, I cannot use a PreparedStatement
> easily, which is fine. I read somewhere that the implementation of
> executeBatch() just executes all of the statements one-by-one, which
> would be as slow as executing separate statements.
>
> I would like some advice as to which method would run faster and if
> using PreparedStatements is a good performance boost.
>
The desision to use or not use PreparedStatements generally isn't done
on the basis of performance. You use PreparedStatements if you have
bind values to add to a sql statement. So in general you should always
use a PreparedStatement unless your SQL is a constant in which case
Statement if fine. Building up SQL dynamically to then execute via a
regular Statement has security issues if you are ever using any user
supplied values and should be avoided.

The jdbc API has the batch capability so that driver implementations can
optimize the performance of a large set of operations. Unfortunately
the current code in the driver doesn't do any optimization and as you
stated just sends each individual command to the server.

So if you are coding for performance and portability then you should use
the batch api. If you don't care about portability then you can work
around the poor batch implementation by concatenating the statements
together and doing one big execute.

> Is there any limit to the number of statements that can be executed in a
> batch?
No.

thanks,
--Barry