Batch insert

[Leen Toelen]

Hi,

what is the recommended way to batch insert an object tree using eclipselink? I have a three-level object tree, where each parent has a one tot many relationship with its children.

A

- B

   - C

   - C

   - C

   - C

   - C

- B

   - C

   - C

   - C

-B

   - C

When I use em.persist(a) with logging set to FINEST, I notice that each object generates a separate insert statement, and a "select @@IDENTITY" after the insert. The persist is transactional, so all inserts are committed at the same time, but is there a way to only create a single insert statement fot the C objects with multiple parameters like this

insert into C(col1,col2,B_ID)

values

(...,...,...)

(...,...,...)

(...,...,...)

(...,...,...)

(...,...,...)

and execute this in one go?

I tried eclipselink.jdbc.batch-writing "JDBC", but this only batches a single insert of C, which does not solve the problem.

Does anyone has an idea to improve batch insertion time?

Regards,

Leen Toelen

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[tch]

Check out the wiki on batch writing:
http://wiki.eclipse.org/Optimizing_the_EclipseLink_Application_(ELUG)#How_to_Use_Batch_Writing_for_Optimization

./tch



On Wed, Jun 3, 2009 at 6:23 AM, Leen Toelen <toelen@...> wrote:

> Hi,
> what is the recommended way to batch insert an object tree using
> eclipselink? I have a three-level object tree, where each parent has a one
> tot many relationship with its children.
> A
> - B
>    - C
>    - C
>    - C
>    - C
>    - C
> - B
>    - C
>    - C
>    - C
> -B
>    - C
> When I use em.persist(a) with logging set to FINEST, I notice that each
> object generates a separate insert statement, and a "select @@IDENTITY"
> after the insert. The persist is transactional, so all inserts are committed
> at the same time, but is there a way to only create a single insert
> statement fot the C objects with multiple parameters like this
> insert into C(col1,col2,B_ID)
> values
> (...,...,...)
> (...,...,...)
> (...,...,...)
> (...,...,...)
> (...,...,...)
> and execute this in one go?
> I tried eclipselink.jdbc.batch-writing "JDBC", but this only batches a
> single insert of C, which does not solve the problem.
> Does anyone has an idea to improve batch insertion time?
> Regards,
> Leen Toelen
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[James Sutherland]

You should also not use IDENTITY sequencing, instead use TABLE sequencing, then you can make use of sequence preallocation, and batch writing.

tch wrote:

Check out the wiki on batch writing:
http://wiki.eclipse.org/Optimizing_the_EclipseLink_Application_(ELUG)#How_to_Use_Batch_Writing_for_Optimization

./tch



On Wed, Jun 3, 2009 at 6:23 AM, Leen Toelen <[e-mail]> wrote:
> Hi,
> what is the recommended way to batch insert an object tree using
> eclipselink? I have a three-level object tree, where each parent has a one
> tot many relationship with its children.
> A
> - B
>    - C
>    - C
>    - C
>    - C
>    - C
> - B
>    - C
>    - C
>    - C
> -B
>    - C
> When I use em.persist(a) with logging set to FINEST, I notice that each
> object generates a separate insert statement, and a "select @@IDENTITY"
> after the insert. The persist is transactional, so all inserts are committed
> at the same time, but is there a way to only create a single insert
> statement fot the C objects with multiple parameters like this
> insert into C(col1,col2,B_ID)
> values
> (...,...,...)
> (...,...,...)
> (...,...,...)
> (...,...,...)
> (...,...,...)
> and execute this in one go?
> I tried eclipselink.jdbc.batch-writing "JDBC", but this only batches a
> single insert of C, which does not solve the problem.
> Does anyone has an idea to improve batch insertion time?
> Regards,
> Leen Toelen