How to improve the operation of insert and update

elibs

How to improve the operation of insert and update
Hello,All:

I have a serious problem to the performance of my large table which has 40M rows and continues been enlarged.

The case is that, I have a routine batch job devoloped by AP to insert 200K rows into this table daily.I am very frustrated that this job always take me about 20 minutes to complete, althought I have add index to it and patition it into 4 parts, and reorg ,restat and rebind it every week,it still mean nothing.

Could someone kindly help me?

Thanks in advance

Larry

Edward Vassie

Re: How to improve the operation of insert and update
(in response to elibs)
What version of DB2, what platform, and what version of operating system are
you using?

As general guidance for most platforms, check that your space parameters for
incremental growth are not too small, and check that your bufferpools are
large enough. Anything more specific needs answers to the above questions.


From Edward Vassie...



-----Original Message-----
From: elibs [mailto:[login to unmask email]
Sent: 05 January 2001 13:51
To: [login to unmask email]
Subject: How to improve the operation of insert and update



Hello,All:

I have a serious problem to the performance of my large table which has
40M rows and continues been enlarged.

The case is that, I have a routine batch job devoloped by AP to insert
200K rows into this table daily.I am very frustrated that this job always
take me about 20 minutes to complete, althought I have add index to it and
patition it into 4 parts, and reorg ,restat and rebind it every week,it
still mean nothing.

Could someone kindly help me?

Thanks in advance

Larry



John Hardy

Re: How to improve the operation of insert and update
(in response to Edward Vassie)
Does your 'insert' process simply consist of 200,000 iterations of a random
SQL Insert? If so, then why do you think that adding an extra index and
partitioning your table will help? (I say an extra index as I assume that
your 40 million row was already indexed!?).

If your process does consist simply of a series of random Inserts, then
inserting the rows in clustering sequence often dramatically improves
performance.

If on the other hand your 'insert' process consists of a read of the table
(with or without a cursor) followed by an insert (hence your new index)
then others on the list might be able to give you some tips/further lines
of investigation.

I am afraid that you will need to supply more information.




On Fri, 5 Jan 2001 08:50:45 -0500, elibs <[login to unmask email]> wrote:

>Hello,All:
>
> I have a serious problem to the performance of my large table which has
40M rows and continues been enlarged.
>
> The case is that, I have a routine batch job devoloped by AP to insert
200K rows into this table daily.I am very frustrated that this job always
take me about 20 minutes to complete, althought I have add index to it and
patition it into 4 parts, and reorg ,restat and rebind it every week,it
still mean nothing.
>
> Could someone kindly help me?
>
>Thanks in advance
>
>Larry
>



Scott Lindsey

Re: How to improve the operation of insert and update
(in response to John Hardy)
Is your input dataset sorted in the order that the table is sequenced in??? If your
table is clustered, your input dataset should be sorted in the clustering sequence.
This will help!

> On Fri, 5 Jan 2001 08:50:45 -0500, elibs <[login to unmask email]> wrote:
>
>Hello,All:
>
> I have a serious problem to the performance of my large table which
> has 40M rows and continues been enlarged.
>
> The case is that, I have a routine batch job devoloped by AP to insert
> 200K rows into this table daily.I am very frustrated that this job
> always take me about 20 minutes to complete, althought I have add
> index to it and patition it into 4 parts, and reorg ,restat and rebind
> it every week,it still mean nothing.
>
> Could someone kindly help me?
>
>Thanks in advance
>
>Larry



Eric Pearson

Re: How to improve the operation of insert and update
(in response to Scott Lindsey)
Larry,
Check the response time on your active logs.
Insert and update are synchronous on the active logs.
Poor log response can really wreck insert/update times.
Also, see if much of your time is in lock wait. If so,
add COMMITs to everything which uses the table.




regards,

eric pearson
NS ITO Database Support

-----Original Message-----
From: elibs [mailto:[login to unmask email]
Sent: Friday, January 05, 2001 8:51 AM
To: [login to unmask email]
Subject: How to improve the operation of insert and update



Hello,All:

I have a serious problem to the performance of my large table which has
40M rows and continues been enlarged.

The case is that, I have a routine batch job devoloped by AP to insert
200K rows into this table daily.I am very frustrated that this job always
take me about 20 minutes to complete, althought I have add index to it and
patition it into 4 parts, and reorg ,restat and rebind it every week,it
still mean nothing.

Could someone kindly help me?

Thanks in advance

Larry