Thread: Performance of batches with Statements and PreparedStatements

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.

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

All advice is greatly appreciated.

Thanks.

Jeff

> 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.

seems to me that it wouldn't be as slow. for one thing you'd eliminate all
the network traffic.

john

  well, someone could make executeBatch() work this way, if it's really better

On Wed, 9 Apr 2003 06:36:54 -0400
"John Guthrie" <jguthrie@psynapsetech.net> wrote:

> > 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.
>
> seems to me that it wouldn't be as slow. for one thing you'd eliminate all
> the network traffic.
>
> john
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--

 /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
 \ / Ribbon Campaign  Analista de Sistemas
  X  Against HTML     Cel.: 51-91287530
 / \ Email!           Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341

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