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. 4 2798
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.
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).
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/
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
...
|
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...
|
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? ...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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,...
|
by: Naresh1 |
last post by:
What is WebLogic Admin Training?
WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
|
by: ezappsrUS |
last post by:
Hi,
I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
|
by: jack2019x |
last post by:
hello, Is there code or static lib for hook swapchain present?
I wanna hook dxgi swapchain present for dx11 and dx9.
|
by: DizelArs |
last post by:
Hi all)
Faced with a problem, element.click() event doesn't work in Safari browser.
Tried various tricks like emulating touch event through a function:
let clickEvent = new Event('click', {...
| |