Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    16

    Question Unanswered: Slow delete and high processor usage

    Hi All,

    I have a table with 400 records. When I make the exclusion of these records a trigger is triggered to delete records from another table. However this process is taking 10 seconds and raising the processor utilization at 100%. Has anyone experienced this problem? There is some suggestion?
    I'm using DB2 9.7 Express-C

    Thanks.
    Davis

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You probably have a bad access-plan for the DELETE on the other table, probably you have in the trigger a "delete from other-table WHERE...." ( a searched delete) that is underperforming.

    Verify that runstats are up to date on both tables, and all indexes on both tables.

    Verify that the searched-delete has an appropriate index (use the index advisor db2advis or use the (free) Data-Studio 3.1.1 to recommend indexes).

    If indexes are already present, verify that you don't have any row-locking issues when you perform the delete (i.e. not waiting for locks).

    But most likely it is just a poor access-plan that you can fix with indexes and/or runstats.

  3. #3
    Join Date
    Mar 2012
    Posts
    16
    Thanks, but now I have observed that slowness is caused by referential integrity. When you delete a record from the parent table records from four tables are also excluded. What should I do? Remove referential integrity?


    Quote Originally Posted by db2mor View Post
    You probably have a bad access-plan for the DELETE on the other table, probably you have in the trigger a "delete from other-table WHERE...." ( a searched delete) that is underperforming.

    Verify that runstats are up to date on both tables, and all indexes on both tables.

    Verify that the searched-delete has an appropriate index (use the index advisor db2advis or use the (free) Data-Studio 3.1.1 to recommend indexes).

    If indexes are already present, verify that you don't have any row-locking issues when you perform the delete (i.e. not waiting for locks).

    But most likely it is just a poor access-plan that you can fix with indexes and/or runstats.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What is the definition of the RI clause involved?
    Is it a cascaded delete?
    Are the foreign-key indexes *present* and run-statted ?

  5. #5
    Join Date
    Mar 2012
    Posts
    16
    Ok! I forgot to create the indices. Now works fine!
    Thanks.

    Quote Originally Posted by db2mor View Post
    What is the definition of the RI clause involved?
    Is it a cascaded delete?
    Are the foreign-key indexes *present* and run-statted ?

Posting Permissions

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