473,574 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question regarding CLI array Insert

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.

Sep 19 '06 #1
4 2898
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.
Sep 19 '06 #2
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).

Sep 19 '06 #3
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/
Sep 19 '06 #4
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
Sep 20 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4708
by: TThai | last post by:
HI, I'm trying to insert records to a table using bcp command. The problem is the input file to the bcp is a text file that looks like this: Text file data: 1234 abc def ghi jkl mno Expected result: column1 1234
7
2448
by: Squignibbler | last post by:
Hi all, I have a question regarding the C++ programming language regarding the nature of the relationship between pointers and arrays. If the statement MyArray is functionally identical to *(MyArray+x), what statement is functionally identical to MyArray? I ask this question because when I create a dynamic array with one
2
4725
by: JMCN | last post by:
Here is a simple question regarding insert into and select insert into statements. I have the follwing sql from my table make query. how can i add other query with the same field names as tbl1? basically, i will have 2 other queries to insert into tblCombine. when i did add the other 2 queries, i ended up with new fields for instance,...
5
3569
by: Humble Geek | last post by:
Hi all. Quick and perhaps silly question, but... I am using Pg 7.3. I am writing a function using pgplsql. This function will perform multiple inserts. Let's say two of the inserts are as follows: -- id is primary key insert into users (id, username) values (nextval('someSeq'),'somename'); -- id is also a PK insert into log (id, uid,...
21
3186
by: yeti349 | last post by:
Hi, I'm using the following code to retrieve data from an xml file and populate a javascript array. The data is then displayed in html table form. I would like to then be able to sort by each column. Once the array elements are split, what is the best way to sort them? Thank you. //populate data object with data from xml file. //Data is a...
8
2248
by: Roger | last post by:
I have a question regarding the behaviour of sql with OR and fetch first 1 rows only : I have a table with data : ACNO NAME TELNO CITY ZIP 1000 tim 8887778888 denver 1000 tim 8887778888 denver 56465
39
1827
by: Daz | last post by:
Hello all, my question is more regarding advice on a script design. I have about 3600 entries in my database, the user submits a list, which is then checked against those in the database to confirm whether or not they already own a particular item. If they do, then it's not added to the user table, whereas if it is, then it _is_ added to...
18
2851
by: Nobody | last post by:
I've been looking for a job for a while now, and have run into this interview question twice now... and have stupidly kind of blown it twice... (although I've gotten better)... time to finally figure this thing out... basically the interview question is: given an unsorted listed such as: 3,1,3,7,1,2,4,4,3 find the FIRST UNIQUE number,...
14
1763
by: ablock | last post by:
I have an array to which i have a added a method called contains. I would like to transverse this array using for...in...I understand fully that for...in is really meant for Objects and not Arrays, but I purposely had this array filled unsequentially because the key for the array is meant to act as an ID which has a contextual meaning in my...
0
7814
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7826
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8107
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6464
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5631
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5307
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3756
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2252
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1071
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.