Question regarding CLI array Insert

[Michel Esber]

Hello,

Environment: db2 V8 FP 13 LUW

Our application currently uses:

insert into table values ('A'),('B'),...('Z')

We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
current model took exactly 270 seconds.

In average, the application will insert 50-100 rows at a time. There
are some cases that up to 5k rows may be inserted. Multiple
applications insert data into the same table concurrently.

In regards to concurrency, should I expect a performance impact using
CLI arrays with larger array/commit sizes ? Also, is it fair to say the
CLI array inserts is faster than the traditional insert, even with
small amounts of data ?

Thanks in advance.

[Phil Sherman]

Potential performance issues caused by locking are always an issue when
multiple updates (inserts in your case) occur concurrently. The larger
the "blocks" of inserts are between commits, the higher the probability
of running into lock interference. Application knowledge will be useful
in determining potential interference effects of decreasing the commit
frequency.

Array inserts should always give better performance than traditional
because the array insert of 1k rows has a single interaction between the
cli and the database server instead of the 1k interactions for the
traditional case. I'd compare this to the difference between pulling a
single weed (from the garden) and carrying it 10' to a bag vs gathering
up all the weeds in reach and making a single trip to the bag. It's
almost always faster to batch up the work and avoid the overhead.

Phil Sherman
Michel Esber wrote:

Hello,

Environment: db2 V8 FP 13 LUW

Our application currently uses:

insert into table values ('A'),('B'),...('Z')

We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
current model took exactly 270 seconds.

In average, the application will insert 50-100 rows at a time. There
are some cases that up to 5k rows may be inserted. Multiple
applications insert data into the same table concurrently.

In regards to concurrency, should I expect a performance impact using
CLI arrays with larger array/commit sizes ? Also, is it fair to say the
CLI array inserts is faster than the traditional insert, even with
small amounts of data ?

Thanks in advance.

[Mark A]

Michel Esber wrote:

Hello,

Environment: db2 V8 FP 13 LUW

Our application currently uses:

insert into table values ('A'),('B'),...('Z')

We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
current model took exactly 270 seconds.

In average, the application will insert 50-100 rows at a time. There
are some cases that up to 5k rows may be inserted. Multiple
applications insert data into the same table concurrently.

In regards to concurrency, should I expect a performance impact using
CLI arrays with larger array/commit sizes ? Also, is it fair to say the
CLI array inserts is faster than the traditional insert, even with
small amounts of data ?

Thanks in advance.

An insert of 2 rows at one time takes about the same time as inserting
1 row.

If you keep increasing the amount of rows per insert, at some point you
may see diminishing returns.

I would set the LOGBUFSZ to about 256 (pages) and increase the DBHEAP
by the same amount (LOGBUFSZ comes out of DBHEAP).

[Serge Rielau]

Michel Esber wrote:

Hello,

Environment: db2 V8 FP 13 LUW

Our application currently uses:

insert into table values ('A'),('B'),...('Z')

We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
current model took exactly 270 seconds.

In average, the application will insert 50-100 rows at a time. There
are some cases that up to 5k rows may be inserted. Multiple
applications insert data into the same table concurrently.

In regards to concurrency, should I expect a performance impact using
CLI arrays with larger array/commit sizes ? Also, is it fair to say the
CLI array inserts is faster than the traditional insert, even with
small amounts of data ?

Thanks in advance.

I'm not sure if your comparison is fair. ARRAY INSERT uses one INSERT
with parameter markers over and over again.
When you use literals that statement needs to be compiled.
See what happens when you use INSERTs with 1, 10, 100, 1000 rows of
parameter markers.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

Serge Rielau wrote:

Michel Esber wrote:

>Hello,

Environment: db2 V8 FP 13 LUW

Our application currently uses:

insert into table values ('A'),('B'),...('Z')

We have used CLI arrays inserts (1000 array and commit size) and
managed to insert 1 Million rows into an empty table in 32 seconds. Our
current model took exactly 270 seconds.

In average, the application will insert 50-100 rows at a time. There
are some cases that up to 5k rows may be inserted. Multiple
applications insert data into the same table concurrently.

In regards to concurrency, should I expect a performance impact using
CLI arrays with larger array/commit sizes ? Also, is it fair to say the
CLI array inserts is faster than the traditional insert, even with
small amounts of data ?

Thanks in advance.

I'm not sure if your comparison is fair. ARRAY INSERT uses one INSERT
with parameter markers over and over again.
When you use literals that statement needs to be compiled.
See what happens when you use INSERTs with 1, 10, 100, 1000 rows of
parameter markers.

I've done exactly that once with as many rows as are allowed for an insert
statement limited by 32K statement size. This is really fast, i.e. 3x
faster than single inserts (with parameter markers) - and there was a
Spatial Extender UDF involved as well, which eats quite a bit of time.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany