executeBatch()

Hi there,
when I use a PreparedStatement for a executing a batch of inserts, the
driver executes a single sp_executesql for EACH statement in the batch.
Is this a limitation of the database or of the driver?
Kein Plan berlebt die erste Feindberhrung.
Steffen Ramlow wrote:

> Hi there,
> when I use a PreparedStatement for a executing a batch of inserts, the
> driver executes a single sp_executesql for EACH statement in the batch.
> Is this a limitation of the database or of the driver?
The question is whether those sp_executesql calls are sent one-at-a-time
with a wait for a DBMS response for each one, or whether they are sent
all together in one network packet, with whatever parameter calls needed
in between. The best performance would of course be to send everything in
one packet. If it doesn't, it's because the driver implemented the syntax of
batches without reaching for the best performance.
Joe

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> The question is whether those sp_executesql calls are sent
> one-at-a-time with a wait for a DBMS response for each one, or
> whether they are sent
> all together in one network packet, with whatever parameter calls
> needed
> in between. The best performance would of course be to send
> everything in one packet. If it doesn't, it's because the driver
> implemented the syntax of batches without reaching for the best
> performance.
> Joe
One call per statement. Maybe this is necessary to get the results for each
statement?
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> One call per statement. Maybe this is necessary to get the results for each
> statement?
>
Nope. the DBMS will send everything back in order anyway. In fact you can do your own
speedy batching that is optimal:
StringBuffer myBatch = new StringBuffer();
while (make batch)
{
myBatch.append("insert into my table values(" + myVal1 + ", " + myVal2 + ")\n"); // newline at end is important
}
stmt.executeUpdate( myBatch.toString() ); // all goes at once.

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Nope. the DBMS will send everything back in order anyway. In fact you
> can do your own
> speedy batching that is optimal:
> StringBuffer myBatch = new StringBuffer();
> while (make batch)
> {
> myBatch.append("insert into my table values(" + myVal1 + ", " +
> myVal2 + ")\n"); // newline at end is important }
> stmt.executeUpdate( myBatch.toString() ); // all goes at once.
If it is so easy (and i think it is), why makes the driver a single call per
statement?
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> If it is so easy (and i think it is), why makes the driver a single call per
> statement?
>
Well, it really isn't so easy all the time, because it is superior for
whole-system performance for the DBMS to get the SQL to be repeated once,
and compile it once to be re-used, rather than to has the DBMS parse it
each time every time, but you and I agree that there is no reason the driver
couldn't do the batching of the execute-sql calls and send them all at once.
That would be faster.
Joe
> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Well, it really isn't so easy all the time, because it is superior for
> whole-system performance for the DBMS to get the SQL to be repeated
> once, and compile it once to be re-used, rather than to has the DBMS
> parse it each time every time, but you and I agree that there is no
> reason the driver couldn't do the batching of the execute-sql calls
> and send them all at once. That would be faster.
Well - it depends. I made some tests against mssql 2000 and oracle 9.
1000 insert statements (varchar(10), varchar(50), numeric(10)), no index.
It took about 2,5 s to do this with a prepared batch and mssql 2000.
It took about 0,1 s to do this with a prepared batch and oracle.
It took about 1,1 s to do this with a single db-call, with all statements in
it and mssql 2000.
It took about 4,8 s to do this with a single db-call, with all statements in
it and oracle.
So it is very DB-dependend, how efficient the driver AND the DB can handle
this sort of task.
This specific DB-behavior should be known to the driver vendor.
Hm, the oracle-driver guys know their DB, the mssql-driver guys not!? ;)
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> Well - it depends. I made some tests against mssql 2000 and oracle 9.
> 1000 insert statements (varchar(10), varchar(50), numeric(10)), no index.
> It took about 2,5 s to do this with a prepared batch and mssql 2000.
> It took about 0,1 s to do this with a prepared batch and oracle.
> It took about 1,1 s to do this with a single db-call, with all statements in
> it and mssql 2000.
> It took about 4,8 s to do this with a single db-call, with all statements in
> it and oracle.
> So it is very DB-dependend, how efficient the driver AND the DB can handle
> this sort of task.
Good test, but were you able to determine whether the oracle driver sent the real batch
execute in one packet, like we want the MS driver to do? It might be that the MS
driver/DBMS was also extremely fast is it sent all the sp_execute calls at once too,
and this also seems to show a weakness in the oracle sql parser...

> This specific DB-behavior should be known to the driver vendor.
> Hm, the oracle-driver guys know their DB, the mssql-driver guys not!? ;)
Well, the MS driver comes from a company that makes drivers for several DBMSes,
so there is probably a considerable amount of generic code and algoritms that
is being used in the MS driver.

> --
> Kein Plan berlebt die erste Feindberhrung.
>
|||Joe Weinstein wrote:

> Good test, but were you able to determine whether the oracle driver
> sent the real batch execute in one packet,
Yes, (IIRC) I could see it in the oracle trace I made.

> It might be that the MS driver/DBMS was also extremely fast is
> it sent all the sp_execute calls at once too,
No SQL Server Profiler says one call per statement.

> and this also seems to
> show a weakness in the oracle sql parser...
It seems that these guys think, everybody uses bind variables

> Well, the MS driver comes from a company that makes drivers for
> several DBMSes,
> so there is probably a considerable amount of generic code and
> algoritms that
> is being used in the MS driver.
Should I test merants oracle driver too!?
But what I did, I have tested i-net's seropto, it took 2,8 s instead of the
0,1 s with the oracle driver.
Kein Plan berlebt die erste Feindberhrung.
|||Steffen Ramlow wrote:

> Joe Weinstein wrote:
>
>
> Yes, (IIRC) I could see it in the oracle trace I made.
>
>
> No SQL Server Profiler says one call per statement.
>
>
> It seems that these guys think, everybody uses bind variables
>
>
> Should I test merants oracle driver too!?
> But what I did, I have tested i-net's seropto, it took 2,8 s instead of the
> 0,1 s with the oracle driver.
> --
> Kein Plan berlebt die erste Feindberhrung.
god luck. e live in interesting times...
Joe
>
>