If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 Delete millions of row - slow performance

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 77
DB2 Delete millions of row - slow performance

Hi,
DB2 9.7 fp 4 / Win 2008 / 6 CPU / 8GB RAM

DBSIZE around 800GB.

This is the first time we are deleting data using a java apps which deletes older than YY-MM-DD.
The java apps was develop by our developers and uses correct indexes (no table scan).

Out of 400 million rows from 8 tables, we need to delete around 100 million rows.
The deletion ran around 12 hours and deleted only 10 million rows. So, I had to kill the job in the middle.
I setup infinitive logs each 100 MB size. I can see now around 150 LOGS were archived.
How can i investigate its performance ? what are the areas i may need to tune / check ?
Please advise.
Thanks,
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,333
Quote:
Originally Posted by db2mtrk View Post
The deletion ran around 12 hours and deleted only 10 million rows.
That's about 4.5 ms per delete, which is probably as good as you can get if the application deletes rows one by one.

Look at the delete statement explain plan and at the monreport.dbsummary() output -- that might give you an idea where you can find improvement.
__________________
---
"It does not work" is not a valid problem statement.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,227
Quote:
Originally Posted by n_i View Post
That's about 4.5 ms per delete, which is probably as good as you can get if the application deletes rows one by one.

...
I guessed similar thing as n_i(if the application deletes rows one by one), too.

Please try to use SQL's set oriented capabilities,
i.e. delete multiple rows by one SQL delete statement which includes predicates(to judge the rows to be deleted) in it,
not to include the logic(to judge the rows to be deleted) in Java code.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 77
Thank you.

All,
Thanks for your contribution, very helpfull.
Let see hou much that i can dig.

Regards,
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 731
In the old days (my mainframe years) we used the 80/20 rule: when you have to delete more than 20% it is more efficient to dump the table to a sequential file(only the 80% of the rows which have to stay) and use that file to reload the table.

I have no reason to believe that the above rule is not valid anymore....
__________________
Somewhere between " too small" and " too large" lies the size that is just right.
- Scott Hayes
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 731
system does not allow me to delete the doublepost
sorry guys
__________________
Somewhere between " too small" and " too large" lies the size that is just right.
- Scott Hayes
Reply With Quote
  #7 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Quote:
Originally Posted by dr_te_z View Post
system does not allow me to delete the doublepost
sorry guys
deleted now
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Location: India
Posts: 71
Hi,

delete completed ?
Have you did any changes to anything to improve performance ?
Reply With Quote
  #9 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Quote:
Originally Posted by niteshtheone View Post
Hi,

delete completed ?
Have you did any changes to anything to improve performance ?
My previous post on delete was about deleting a post in the thread.
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 77
No yet

Hi,
I had to backout the deletion and I restored the DB now.
Our developers are now reviewing the code.

This was not a stright forward delete. There is a business rules too, to find the correct data. Also couple of tables were joined.
I was not sure that the issue was related to the DB or Code.

While deletion was running, I switched ON all the monitor switches but I couldn't see much information to find why its running slow.

This type of situation, which monitoring tool is better ? eventmonitor ? OR DB2Trace OR FFODC ?

Thanks.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,227
Quote:
This was not a stright forward delete. There is a business rules too, to find the correct data. Also couple of tables were joined.
I want to repeat again
Quote:
Originally Posted by tonkuma View Post
I guessed similar thing as n_i(if the application deletes rows one by one), too.

Please try to use SQL's set oriented capabilities,
i.e. delete multiple rows by one SQL delete statement which includes predicates(to judge the rows to be deleted) in it,
not to include the logic(to judge the rows to be deleted) in Java code.
Many people don't know capabiliies of SQL language enough, and sometimes don't know their ignorance for SQL too.

Please try to integrate the rules/logics("business rules to find the correct data" , "couple of tables were joined") into
one final(sometimes, more than one) SQL statement(not by using intermediate/temporary tables, nor built-in to host language),
by compensation of efforts to improve your SQL programming skills,
if you want to get better execution performance.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Location: India
Posts: 71
Few time back I used this kind of delete :
create a a dublicate table ... insert the data into that duplicate table through NLI or cursor loading. when it finished then replace table names... and if everything went smooth you can load original table from NULL with replace option and then delete table.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 731
Quote:
Originally Posted by niteshtheone View Post
Few time back I used this kind of delete :
create a a dublicate table ... insert the data into that duplicate table through NLI or cursor loading. when it finished then replace table names... and if everything went smooth you can load original table from NULL with replace option and then delete table.
Like that idea
__________________
Somewhere between " too small" and " too large" lies the size that is just right.
- Scott Hayes
Reply With Quote
  #14 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Quote:
Originally Posted by niteshtheone View Post
Few time back I used this kind of delete :
create a a dublicate table ... insert the data into that duplicate table through NLI or cursor loading. when it finished then replace table names... and if everything went smooth you can load original table from NULL with replace option and then delete table.
That is a good idea.

But, I think it depends on the practical circumstances ... You can do this if you can afford downtime till you rebuild the new table, indexes and collect stats.
You can take this approach even when the db is online if you are dealing with a insert-only table (say, a business logging table). Rename of the table can be done with almost-no downtime.

Renames get tricky when you have dependent objects - Child tables, views, routines etc.
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 731
Quote:
Originally Posted by sathyaram_s View Post
You can do this if you can afford downtime till you rebuild the new table, indexes and collect stats.
Agree excluding collect stats. That's no extra time. Gotta be done in every senario.
__________________
Somewhere between " too small" and " too large" lies the size that is just right.
- Scott Hayes
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On