Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » dynamic batch-writing with MySql
dynamic batch-writing with MySql [message #541093] Fri, 18 June 2010 10:22 Go to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
How to get dynamic batch writing enabled with a mysql database? i read the doc and set Parameters in persistence.xml:

<property name="eclipselink.jdbc.batch-writing" value="JDBC" />

i even switched from autoincrement-ID to a ID-Table (i read somewhere that batch writing only works with table or sequence id-generation)

in my code i have 2 persists (in one transaction) in one transaction and eclipselink always generates always 4 insert-statements (2 Tables 1:1 relation).
So how to get it work? What settings in persistence.xml are needed? how to bundle inserts in code? and are there restrictions to database design to get batch writing enabled?

Report message to a moderator

Re: dynamic batch-writing with MySql [message #541154 is a reply to message #541093] Fri, 18 June 2010 13:30 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
Hello,

Can you post the portion of the log with logging on finest where EclipseLink is performing the inserts? What is the sequence of the inserts? (ie is it insert table_a, insert table_b, insert table_a etc) Can you post your table generator setup?

Restrictions are that any statement that must be executed that is different than the last statement forces the batch to execute. For instance, a common problem is setting the sequence size to 1. This will mean that EclipseLink will need to go to the database after every insert to get another sequence number after every insert, forcing the batch to be flushed with a size of 1.

Best Regards,
Chris

Report message to a moderator

Re: dynamic batch-writing with MySql [message #541422 is a reply to message #541154] Mon, 21 June 2010 05:55 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
as i said i made a sequence table so there is also an update for it in log:

7 Query	SET autocommit=0
7 Query	UPDATE id_tabelle SET id = id + 50 WHERE PK_Name = 'table1ID'
7 Query	SELECT id FROM id_tabelle WHERE PK_Name = 'table1ID'
7 Query	commit
7 Query	SET autocommit=1
7 Query	SET autocommit=0
7 Query	UPDATE id_tabelle SET id = id + 50 WHERE PK_Name = 'table2ID'
7 Query	SELECT id FROM id_tabelle WHERE PK_Name = 'table2ID'
7 Query	commit
7 Query	SET autocommit=1
7 Query	SET autocommit=0
7 Query	INSERT IGNORE INTO table2 (table2ID, ...
7 Query	INSERT IGNORE INTO table2 (table2ID, ...
7 Query	INSERT IGNORE INTO table1 (table1ID, ...,table2ID //FK
7 Query	INSERT IGNORE INTO table1 (table1ID, ...,table2ID //FK
7 Query	commit
7 Query	SET autocommit=1


my idea of batch writing would be to have only one insert per table. My Sequence size is 50 isn't it? So in theory it should be possible to make batches of 50 inserts?!

[Updated on: Mon, 21 June 2010 08:19]

Report message to a moderator

Re: dynamic batch-writing with MySql [message #541823 is a reply to message #541422] Tue, 22 June 2010 16:02 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
Hello,

The log shows that binding is not being used. What version are you using and what properties have you set? Batch writing relies on parameter binding or the prepared statements cannot be reused.

Best Regards,
Chris

Report message to a moderator

Re: dynamic batch-writing with MySql [message #541898 is a reply to message #541823] Wed, 23 June 2010 05:21 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx for your time chris. here is the relevant? part of perstence.xml:

<property name="eclipselink.jdbc.batch-writing" value="JDBC" />


(are probably other settings relevant?)

i am using eclipselink 2 and read this: http://wiki.eclipse.org/Optimizing_the_EclipseLink_Applicati on_%28ELUG%29#How_to_Use_Batch_Writing_for_Optimization

the "binding" you mentioned is this the "Parameterized SQL (Parameter Binding)" mentioned there? I thought i could use it "...without parameterized SQL, this is known as dynamic batch writing."?

But in any case i'm uncertain how to write code that utilizes any kind of batch writing. at the moment i have only some "persist" between "tx.begin" and "tx.commit".

Report message to a moderator

Re: dynamic batch-writing with MySql [message #541991 is a reply to message #541898] Wed, 23 June 2010 13:12 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
Hello,

Because you are not using parameterized statements, statements cannot be reused, forcing each statement to be a batch of size 1. If you do not want to use parameter binding/parameter SQL, you can set the batch-writing option to Buffered. Buffered will concatinate multiple SQL strings into one statement for execution. Most people go with parameter binding though, as it allows statement reuse and is turned off through the eclipselink.jdbc.bind-parameters property.

Dynamic batching (buffered) is different from JDBC batch writing as it will not force a flush when statements are different (the calls seperate, but are just sent to the database in a single statement), but the principle is the same. EclipseLink will process all the objects it has to insert/update/delete within a flush or commit call and add them to the batch one by one. This order does is not dependent on the order you persist or merge entities, and is calculated upfront on deploy based on object relationships. You will need to look at how your application will be used inorder to tune the batch size appropriately, and also note that flush calls will push out all statements in the transaction so far - so it can limit the size of batches. For instance, if you call persist on 25 objects with a batch size of 50, calling flush will execute that statement early, so that the next 25 persist calls will go into a new batch statement.

Hope that makes sense.

Best Regards,
Chris

Report message to a moderator

Re: dynamic batch-writing with MySql [message #542038 is a reply to message #541991] Wed, 23 June 2010 15:26 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx
"Hope that makes sense." - partially for me.

ok let's say i want "buffered" batch writing? how to get it work? what persitence.xml- parameters are missing (how to set "batch-writing option to Buffered") and how to write code that utilizes buffered batching?

Report message to a moderator

Re: dynamic batch-writing with MySql [message #543152 is a reply to message #541093] Mon, 28 June 2010 15:02 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
Setting batch writing to JDBC will still work with parameter binding turned off, but it requires support from your JDBC driver. EclipseLink wil use the JDBC addBatch() API, some JDBC drivers will ignore this, and just execute the statements one by one.

You attached a database log, can you also attach an EclipseLink log, it will show if EclipseLink is using batch writing or not.

You can use the "Buffered" by:

<property name="eclipselink.jdbc.batch-writing" value="Buffered" />

This option does not rely on JDBC support, but does require database specific SQL, which may or may not be supported by your database platform.

You may also need to set the size (in number of chars), using the property:

"eclipselink.jdbc.batch-writing.size"

In general using parameter binding, and batching the same statements is normally more efficient, but this may depend on the database.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #544628 is a reply to message #543152] Mon, 05 July 2010 06:23 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx for the detailed answer,

first i will try "buffered" (the description for it in manual was a little confusing: "do not use either JDBC batch writing nor native platform batch writing")

to add the eclipselink log i nee d to know where it is. i tried to find infos about the default logging level and default log file location in manual but didn't find them.
can you please tell where to find eclipselink log or if it's necessary to turn it on before.

Report message to a moderator

Re: dynamic batch-writing with MySql [message #544805 is a reply to message #541093] Mon, 05 July 2010 15:01 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
To enable logging see,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #544928 is a reply to message #544805] Tue, 06 July 2010 06:18 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
after reading this the only question remained was: if logging is enabled where to find the log-file?

Report message to a moderator

Re: dynamic batch-writing with MySql [message #545633 is a reply to message #541093] Thu, 08 July 2010 13:28 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
It goes to System.out, or your console.

To redirect to a file the "eclipselink.logging.file" property can be set.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #547245 is a reply to message #541093] Fri, 16 July 2010 06:17 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx to you i am no eclipselink super noob anymore -- but only a noob.

now i got a eclipslink log - first the part of preallocating id's:
[EL Finer]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--begin transaction
[EL Finest]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Thread(Thread[main,5,main])--Execute query DataModifyQuery(sql="UPDATE ID_Tabelle SET Zaehlerstand = Zaehlerstand + #PREALLOC_SIZE WHERE PK_Name = #PK_Name")
[EL Finest]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(sql="SELECT Zaehlerstand FROM ID_Tabelle WHERE PK_Name = #PK_Name")
[EL Finer]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--Begin batch statements
[EL Fine]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--UPDATE ID_Tabelle SET Zaehlerstand = Zaehlerstand + ? WHERE PK_Name = ?
[EL Fine]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [50, table1ID]
[EL Finer]: 2010-07-16 07:29:24.054--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--End Batch Statements
[EL Fine]: 2010-07-16 07:29:24.163--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--SELECT Zaehlerstand FROM ID_Tabelle WHERE PK_Name = ?
	bind => [table1ID]
[EL Finest]: 2010-07-16 07:29:24.163--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--local sequencing preallocation for table1ID: objects: 50 , first: 9.551, last: 9.600
[EL Finer]: 2010-07-16 07:29:24.163--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--commit transaction
[EL Finest]: 2010-07-16 07:29:24.21--ServerSession(14600371)--Connection(1399004)--Thread(Thread[main,5,main])--local sequencing preallocation is copied to preallocation after transaction commit
[EL Finest]: 2010-07-16 07:29:24.21--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.551 -> ....table1[meldungID=null])
[EL Finest]: 2010-07-16 07:29:24.21--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....Table2[table2ID=null].
[EL Finer]: 2010-07-16 07:29:24.21--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--begin transaction
[EL Finest]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Thread(Thread[main,5,main])--Execute query DataModifyQuery(sql="UPDATE ID_Tabelle SET Zaehlerstand = Zaehlerstand + #PREALLOC_SIZE WHERE PK_Name = #PK_Name")
[EL Finest]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(sql="SELECT Zaehlerstand FROM ID_Tabelle WHERE PK_Name = #PK_Name")
[EL Finer]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--Begin batch statements
[EL Fine]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--UPDATE ID_Tabelle SET Zaehlerstand = Zaehlerstand + ? WHERE PK_Name = ?
[EL Fine]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [50, table2ID]
[EL Finer]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--End Batch Statements
[EL Fine]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--SELECT Zaehlerstand FROM ID_Tabelle WHERE PK_Name = ?
	bind => [table2ID]
[EL Finest]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--local sequencing preallocation for table2ID: objects: 50 , first: 9.551, last: 9.600
[EL Finer]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--commit transaction

is it possible to set the "50" dynamically at runtime? How to set it statically in persitence.xml? (i only know how to set it in java)

The 2nd part is the "btach-writing":
[EL Finest]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--local sequencing preallocation for table2ID: objects: 50 , first: 9.551, last: 9.600
[EL Finer]: 2010-07-16 07:29:24.273--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--commit transaction
[EL Finest]: 2010-07-16 07:29:24.304--ServerSession(14600371)--Connection(1399004)--Thread(Thread[main,5,main])--local sequencing preallocation is copied to preallocation after transaction commit
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.551 -> ....table2[table2ID=null])
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table1[table1ID=null].
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.552 -> ....table1[table1ID=null])
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table2[table2ID=null].
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.552 -> ....table2[table2ID=null])
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table1[table1ID=null].
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.553 -> ....table1[table1ID=null])
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table2[table2ID=null].
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.553 -> ....table2[table2ID=null])
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table1[table1ID=null].
[EL Finest]: 2010-07-16 07:29:24.304--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.554 -> ....table1[table1ID=null])
[EL Finest]: 2010-07-16 07:29:24.319--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table2[table2ID=null].
[EL Finest]: 2010-07-16 07:29:24.319--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.554 -> ....table2[table2ID=null])
[EL Finest]: 2010-07-16 07:29:24.319--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table1[table1ID=null].
[EL Finest]: 2010-07-16 07:29:24.319--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.555 -> ....table1[table1ID=null])
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--PERSIST operation called on: ....table2[table2ID=null].
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--assign sequence to the object (9.555 -> ....table2[table2ID=null])
[EL Finer]: 2010-07-16 07:29:24.335--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--begin transaction
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table2[table2ID=9553])
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table2[table2ID=9555])
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table2[table2ID=9551])
[EL Finest]: 2010-07-16 07:29:24.335--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table2[table2ID=9552])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table2[table2ID=9554])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query UpdateObjectQuery(....table0[ordnungsnummer=080000010692])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query UpdateObjectQuery(....table0[ordnungsnummer=080000012440])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query UpdateObjectQuery(....table0[ordnungsnummer=080000010553])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query UpdateObjectQuery(....table0[ordnungsnummer=080000012617])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query UpdateObjectQuery(....table0[ordnungsnummer=080000006638])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table1[table1ID=9551])
[EL Finest]: 2010-07-16 07:29:24.351--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(....table2[table2ID=9551])
[EL Finer]: 2010-07-16 07:29:24.351--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--Begin batch statements
[EL Fine]: 2010-07-16 07:29:24.351--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--INSERT IGNORE INTO table2 (table2ID, ...) VALUES (?, ...)
[EL Fine]: 2010-07-16 07:29:24.351--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9553, ...]
[EL Fine]: 2010-07-16 07:29:24.351--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9555, ...]
[EL Fine]: 2010-07-16 07:29:24.366--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9551, ...]
[EL Fine]: 2010-07-16 07:29:24.366--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9552, ...]
[EL Fine]: 2010-07-16 07:29:24.366--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9554, ...]
[EL Finer]: 2010-07-16 07:29:24.366--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--End Batch Statements
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table1[table1ID=9554])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(....table2[table2ID=9554])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table1[table1ID=9555])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(....table2[table2ID=9555])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table1[table1ID=9552])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(....table2[table2ID=9552])
[EL Finest]: 2010-07-16 07:29:24.366--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(....table1[table1ID=9553])
[EL Finest]: 2010-07-16 07:29:24.382--UnitOfWork(29670618)--Thread(Thread[main,5,main])--Execute query WriteObjectQuery(....table2[table2ID=9553])
[EL Finer]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--Begin batch statements
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--INSERT IGNORE INTO table1 (table1ID, ...) VALUES (?, ...)
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9551, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9554, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9555, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9552, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--	bind => [9553, ...]
[EL Finer]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--End Batch Statements
[EL Finer]: 2010-07-16 07:29:24.397--ClientSession(2331941)--Connection(1399004)--Thread(Thread[main,5,main])--commit transaction
[EL Finer]: 2010-07-16 07:29:24.413--UnitOfWork(29670618)--Thread(Thread[main,5,main])--end unit of work commit

the MySql Log is similar to my previous post - now with 6 inserts and 2 id updates. So I guess you were right james? ("some JDBC drivers will ignore this, and just execute the statements one by one.") - or is only a misinterpretation of the sql log? Or are there other settings to get dynamic batch writing with mysql?

The problem with parameter binding is that those inserts are no "persists" in java code but only "table1.setTable2(...), table0.setTable1(..)". (table1 and table2 are "child-tables" of a big legacy table. ) If i would use jpql-statements to insert those entries i would lose all that made me use JPA.

and at the and one global sorry for my English - i hope my bad syntax don't hides too much of the semantic Smile.

[Updated on: Fri, 16 July 2010 06:19]

Report message to a moderator

Re: dynamic batch-writing with MySql [message #549535 is a reply to message #541093] Tue, 27 July 2010 06:48 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
push

Report message to a moderator

Re: dynamic batch-writing with MySql [message #550908 is a reply to message #547245] Wed, 04 August 2010 15:38 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
You can set the sequence preallocation size using the @TableGenerator annotation. Or using code using the Session and Sequence API. You could set it dynamically in code at runtime, but probably not a good idea in a concurrent application.

From your log EclipseLin is using parametrized batch writing. If you are not seeing this on the database, then the JDBC driver is ignoring it.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #551341 is a reply to message #541093] Fri, 06 August 2010 05:38 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx,

but what exactly is means " If you are not seeing this on the database, then the JDBC driver is ignoring it."

how does it look like if db doesn't ignore it? i posted some db log at the beginning of the thread - i assume my db driver ignored batch writing?!

so is there a way to get parameterized batch-writing? or will this also be ignored?

Report message to a moderator

Re: dynamic batch-writing with MySql [message #551839 is a reply to message #541093] Mon, 09 August 2010 17:44 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
From your log, you are using parametrized batch writing, and it is working correctly from EclipseLink. In terms of figuring out what your database is doing, you would have to consult your database or JDBC documentation.

If you time both operations, if your database is supporting batch writing, then the batch should be faster, this would be one way to determine if it is working.

We have a performance comparison test for batch writing, and I ran it on MySQL, from the results, it appears that MySQL JDBC does not optimize batch writing. I found the code for the JDBC driver, and it seemed to just execute the statements one by one when executeBatch() was called.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #552145 is a reply to message #541093] Wed, 11 August 2010 05:20 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
thx for the info

if i understood the first replies in this thread correctcly, then i am using "dynamic batch writing" - the "parametrization" is done by eclipslink. (in java code i use only many setters within a loop and at the end a make a commit).
but as you said: mysql seem to ignore batch writing at all Sad
(it is good to know that it seems that i configured eclipselink correctly )

but how to know what data bases support batch writing?

or in general how to know what databases support what features in conjunction with specific jpa providers?
(i don't even know how to search for this info - google yields no useful results)

[Updated on: Wed, 11 August 2010 05:22]

Report message to a moderator

Re: dynamic batch-writing with MySql [message #554716 is a reply to message #541093] Tue, 24 August 2010 08:28 Go to previous messageGo to next message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
one last question:

how does working batchwriting looks like? can someone post settings in persistence.xml, the java code that uses batchwriting and the resulting logs of correctly working batch writing?

or in general how to recognize working batch-writing? my idea was that an batch insert has to look like this
"insert into table (1,2,3) values (,,,),(),()..."

Report message to a moderator

Re: dynamic batch-writing with MySql [message #555401 is a reply to message #541093] Thu, 26 August 2010 13:56 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
To enable batch writing use,

<property name="eclipselink.jdbc.batch-writing" value="JDBC" />

In the log you will see,

[code]
[EL Finer]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])--Begin batch statements
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])--INSERT IGNORE INTO table1 (table1ID, ...) VALUES (?, ...)
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])-- bind => [9551, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])-- bind => [9554, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])-- bind => [9555, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])-- bind => [9552, ...]
[EL Fine]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])-- bind => [9553, ...]
[EL Finer]: 2010-07-16 07:29:24.382--ClientSession(2331941)--Connection(1399004)--T hread(Thread[main,5,main])--End Batch Statements
[code]

The key token to look for is, "Begin batch statements"

If you are using dynamic batch writing (no parameter binding) then you will see this token, then all of the SQL without parameters.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: dynamic batch-writing with MySql [message #558176 is a reply to message #541093] Fri, 10 September 2010 12:15 Go to previous message
moritz du is currently offline moritz duFriend
Messages: 102
Registered: February 2010
Senior Member
in meantime i asked mySql support an here is the solution. the only thing missing is:

"?rewriteBatchedStatements=true"
at the End of the data base url. Now i got multi inserts in sql logs! Smile

Report message to a moderator

Previous Topic:[Teneo/Eclipselink] Incorrect Id mappings for subclasses
Next Topic:EclipseLink 2.1 does not work together with obfuscated jar
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 17:20:40 GMT 2024

Powered by FUDForum. Page generated in 0.04999 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly