db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jun Li" <allanju...@gmail.com>
Subject Re: How to speed up its' work on Oracle.
Date Thu, 27 Apr 2006 07:14:51 GMT
The xml was generated by DdlUtils, so the layout order was guaranteed as:
<A....>
....
<A...>
<B...>
.....
<B ...>
<C....>

The interesting thing is about the foreign key relationship, let's say A has
a foreign key to B, and B has a foreign to C.

Now if I want to insert record A1 which has a foreign key to record B1, B1
should be insert first, no problem. However B1 has a foreign key to C1, so
the insert order is:
C1, B1, A1.

Back to A2, it has a foreign key to B2 not B1 which has been inserted, and
B2 has a foreign key to C2. So: C2, B2, A2.

Batch mode is useless in this case.
However, what if you do this: insert C1....n, then B1....n, follow by
A1...n. Batch mode can help then.

So, what I am saying is if the record you are inserting has a foreign key
relation to another table, just insert all the records in that table not
just the one required by the current insertion, because it's most likely the
records in the foreign key table will be needed later. And this will prevent
the insert operation jumping between tables which will turn batch mode off.

Therefore, the actually insertion order is not exactly the same as the
layout in xml, it should follow the table relation hierarchy.


On 4/13/06, Thomas Dudziak <tomdzk@gmail.com> wrote:
>
> On 4/13/06, Jun Li <allanjunli@gmail.com> wrote:
>
>
> Speed of batch mode largely depends on the batch size (which depends
> on the amount of memory you have and the type of data, e.g. usually
> you can specify 10000 easily).
> And even more important is the order in which you insert. Say, if you
> have three tables A, B, C, then an XML where you have
>
> <A ...>
> <B ...>
> <C ...>
> <A ...>
> <B ...>
> <C ...>
> ...
>
> will effectively turn batch mode off because in batch mode you can
> only insert into a single table. Hence, a change of tables will result
> in the commit of the batch.
> So you should order your XML such that
>
> <A ...>
> ...
> <A ...>
> <B ...>
> ...
> <B ...>
> <C ...>
> ...
> <C ...>
>
> and this if possible in foreign key order with the referenced tables
> before the referencing tables.
>
>
> Tom
>



--
--------------
Cheers,
Jun

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message