Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2011
    Posts
    79

    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,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,431
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,481
    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.

  4. #4
    Join Date
    Nov 2011
    Posts
    79

    Thank you.

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

    Regards,

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  7. #7
    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.

  8. #8
    Join Date
    Jul 2011
    Location
    India
    Posts
    77
    Hi,

    delete completed ?
    Have you did any changes to anything to improve performance ?

  9. #9
    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.

  10. #10
    Join Date
    Nov 2011
    Posts
    79

    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.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,481
    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.

  12. #12
    Join Date
    Jul 2011
    Location
    India
    Posts
    77
    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.

  13. #13
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  14. #14
    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.

  15. #15
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •