Batch insert support

[Michael Pflueger <Mi...@sma.de>]

Hello,

I'm new to OpenJPA and can't find anything about batch insert support.

I know about the batchLimit option, but it only seems to batch several insert operations and executes them together, but I'm actually looking for a single SQL insert statement inserting multiple rows/entities into the database.

Can anyone provide some info about this?

Regards,
Michael
___________________________________________________

SMA Solar Technology AG
Aufsichtsrat: Dr. Erik Ehrentraut (Vorsitzender)
Vorstand: Guenther Cramer, Peter Drews, Juergen Dolle, Roland Grebe,
Uwe Hertel, Pierre-Pascal Urbon, Marko Werner
Handelsregister: Amtsgericht Kassel HRB 3972
Sitz der Gesellschaft: 34266 Niestetal
USt-ID-Nr. DE 113 08 59 54
WEEE-Reg.-Nr. DE 95881150
___________________________________________________

AW: Batch insert support

[Michael Pflueger <Mi...@sma.de>]

Thank you, that's exactly what I needed.
It's called "rewriteBatchedStatements" in the MySQL driver.
Inserts are now about 2.5 times faster with this option.

Regards,
Michael

-----Ursprüngliche Nachricht-----
Von: Miłosz Tylenda [mailto:mtylenda@o2.pl] 
Gesendet: Freitag, 6. Mai 2011 16:32
An: users@openjpa.apache.org
Betreff: Re: Batch insert support

Hi Michael,

You probably mean a variant of INSERT statement with multiple rows which is supported by some databases. There is no direct support for such INSERTs but a JDBC driver might internally rewrite single-row INSERTs into one multiple-row INSERT if you use the batchLimit option. I remember the MySQL driver having such an option.

Greetings,
Milosz 
___________________________________________________

SMA Solar Technology AG
Aufsichtsrat: Dr. Erik Ehrentraut (Vorsitzender)
Vorstand: Guenther Cramer, Peter Drews, Juergen Dolle, Roland Grebe,
Uwe Hertel, Pierre-Pascal Urbon, Marko Werner
Handelsregister: Amtsgericht Kassel HRB 3972
Sitz der Gesellschaft: 34266 Niestetal
USt-ID-Nr. DE 113 08 59 54
WEEE-Reg.-Nr. DE 95881150
___________________________________________________

Re: Batch insert support

[Miłosz Tylenda <mt...@o2.pl>]

Hi Michael,

You probably mean a variant of INSERT statement with multiple rows which is supported by some databases. There is no direct support for such INSERTs but a JDBC driver might internally rewrite single-row INSERTs into one multiple-row INSERT if you use the batchLimit option. I remember the MySQL driver having such an option.

Greetings,
Milosz


> Hello,
> 
> I'm new to OpenJPA and can't find anything about batch insert support.
> 
> I know about the batchLimit option, but it only seems to batch several insert operations and executes them together, but I'm actually looking for a single SQL insert statement inserting multiple rows/entities into the database.
> 
> Can anyone provide some info about this?
> 
> Regards,
> Michael
> ___________________________________________________
> 
> SMA Solar Technology AG
> Aufsichtsrat: Dr. Erik Ehrentraut (Vorsitzender)
> Vorstand: Guenther Cramer, Peter Drews, Juergen Dolle, Roland Grebe,
> Uwe Hertel, Pierre-Pascal Urbon, Marko Werner
> Handelsregister: Amtsgericht Kassel HRB 3972
> Sitz der Gesellschaft: 34266 Niestetal
> USt-ID-Nr. DE 113 08 59 54
> WEEE-Reg.-Nr. DE 95881150
> ___________________________________________________
> 
> 

Re: Batch insert support

["Azamat S. Kalimoulline" <tu...@bazon.ru>]

> I'm new to OpenJPA and can't find anything about batch insert support.
> 
> I know about the batchLimit option, but it only seems to batch several
> insert operations and executes them together, but I'm actually looking for
> a single SQL insert statement inserting multiple rows/entities into the
> database.
> 
> Can anyone provide some info about this?

 <property name="openjpa.jdbc.DBDictionary" value="batchLimit=20000" />

is what you want. It equivevalents to Statement.addBatch(); and 
Statement.executeBatch(); in JDBC. I.e. it applicable to PreparedStatements.

-1 instead number gives us unlimited batch.


-- 
WBR Turtle//BAZON Group