Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Delete record takes a long time

    Hi -

    Database - UDB 7.1
    OS - AIX 4.3

    I am having a problem with deleting records that is taking a long time. The delete is quite simple.

    Here is the statement:

    delete from CTS$.DEBT where DEBT_NO = 0;

    There are about 47 millions rows in this table. The column DEBT_NO is indexed. If I make this into a select statement, it comes back in less than 10 seconds. There are no triggers on this table. I do have logging turn on for the database. Does anyone know if logging could cause the slow response time or could it be something else ?

    Thank you for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you have integrity constraints defined with CTS$.DEBT as a parent that could be the reason.

  3. #3
    Join Date
    Feb 2003
    Posts
    24
    Logging, indexes, and referential constraints are some of the factors that slows down delete in a table. The more indexes you have in a table the slower.

  4. #4
    Join Date
    Feb 2003
    Posts
    24
    Logging, indexes, and referential constraints are some of the factors that slows down delete in a table. The more indexes you have in a table the slower.

  5. #5
    Join Date
    Aug 2004
    Posts
    5
    Thank you for all your help. Disabling the constraint and then running the delete work.

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    When you are doing bulk deletes, inserts, or updates, it may be helpful to know that it is often much slower to update an index over and over (as must be done for each and every row if the index is active), than it is to rebuild the whole darned index from scratch. So, switch off the index, do the bulk operation, and turn the index back on again. When you re-activate the index, it will be rebuilt in a single pass through the table.

    Constraints are the same. If you know that the constraint will never be violated by the bulk-op, it is significantly faster to disable the constraint for the duration.

    It is also helpful to break large bulk inserts and updates into smaller chunks which can be committed as separate transactions. The larger a transaction becomes, the more a "million-pound elephant" it becomes. And the larger and heavier it becomes, the more vulnerable it is to being slowed down by all sorts of external factors. And certainly, as it becomes disproportionate in size to the other operations that are being done ont the server, the more it impacts (splat!) other users.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Mar 2003
    Posts
    343

    Question

    Is there actually a way to disable and enable an index? Or do you really mean drop and recreate?

Posting Permissions

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